A brief comparison of SQL parsing details in MySQL and Oracle

A brief comparison of SQL parsing details in MySQL and Oracle

The SQL syntax parser is a powerful built-in tool set that involves a lot of compilation principles, including syntax analysis and lexical analysis .. There are a lot of things that seem very theoretical, but they seem boring, and their value is even more obvious.
From the original words on the Internet: if we look at the history, we will find that many people called Program Design masters are experts in the compilation field. write Bill Gates, the first Basic language running on a micro-machine, and design the "world's most amazing programmer" of Borland in Delphi. The father of Sun's JAVA and the father of C ++ at Bell's lab.
The starting point is somewhat higher. The case I shared with you today is a very simple SQL statement that can resolve problems in MySQL, but it can be successfully parsed in Oracle, this detail shows some differences between Oracle and MySQL.
The statement to be run is as follows:
SELECT THREAD_ID, threads. NAME, SUM (COUNT_STAR) AS Totalcount, SUM (SUM_TIMER_WAIT) AS Totaltime
FROM performance_schema.events_waits_summary_by_thread_by_event_name
Inner join performance_schema.threads USING (THREAD_ID)
WHERE threads. name like 'thread/SQL/slave \-%'
Group by THREAD_ID, threads. NAME;
ERROR 1630 (42000): FUNCTION performance. sum does not exist. Check the 'function Name Parsing and resolution' section in the Reference Manual
At first glance, I felt that something was wrong. I did not find any obvious statement problem after I found it for a while, but from the perspective of the error, the problem seemed to happen near the word sum.
Simply put
Change SUM (COUNT_STAR) AS Totalcount To SUM (COUNT_STAR) AS Totalcount. It can be seen that the problem is a very small problem. Strictly speaking, it is true that the statement is not strictly written. However, in my impression, Oracle seems to be very hard at hand, but I have never heard of such problems.
Let's simply simulate this problem in MySQL and Oracle to see how it works.
In MySQL
Create table parse_test (id int, name varchar (30 ));
Insert into parse_test values (1, 'A ');
Insert into parse_test values (2, 'bb ');
Commit;

Mysql> select count (id) from parse_test;
+ ----------- +
| Count (id) |
+ ----------- +
| 2 |
+ ----------- +
1 row in set (0.00 sec)

Mysql> select count (id) from parse_test;
ERROR 1630 (42000): FUNCTION test. count does not exist. Check the 'function Name Parsing and resolution' section in the Reference Manual

There is no problem in Oracle.
Create table parse_test (id number, name varchar2 (30 ));
Insert into parse_test values (1, 'A ');
Insert into parse_test values (2, 'bb ');
Commit;
Select count (id) from parse_test;
COUNT (ID)
----------
2
Select count (id) from parse_test;
COUNT (ID)
----------
2

Aside from the advantages of Oracle parsing complex SQL statements, we can see that Oracle does support a higher level of detail.
However, the statements with errors in MySQL at the beginning are stored in Oracle (if the table structure data exists), they cannot be run. The reason is that it is defined in Oracle to indicate that the maximum length is 30 bits, but MySQL supports a lot more, up to 64 bits.

Mysql> create table events_waits_summary_by_thread_by_event_name567890123456789012345 (id int, name varchar (30 ));
ERROR 1059 (42000): Identifier name 'events _ waits_summary_by_thread_by_event_name567890123456789012345 'is too long
Mysql> select length ('events _ waits_summary_by_thread_by_event_name567890123456789012345 ');
+ ----------------------------------------------------------------------------- +
| Length ('events _ waits_summary_by_thread_by_event_name567890123456789012345 ') |
+ ----------------------------------------------------------------------------- +
| 65 |
+ ----------------------------------------------------------------------------- +
1 row in set (0.01 sec)

Of course, so far, I have also picked up a small stone and made some water. If I look into the SQL parser more deeply, from the MySQL code level, you can analyze the problem to achieve a basic goal.