Understanding SQL Parsing: A Comparison of MySQL and Oracle
When it comes to SQL parsing, many students are often confused about the differences between MySQL and Oracle. In this article, we will delve into the parsing process of both databases and explore how to test and verify the parsing process.
The Parsing Process
The entire parsing process can be broken down into several stages:
- Grammar Checking: The first stage is to check for grammatical errors in the SQL statement, such as syntax errors or spelling mistakes.
- Data Dictionary Check: The next stage is to check the data dictionary to see if the objects involved in the statement exist.
- Object Name Conversion: If the statement uses synonyms or aliases, the parser needs to convert the object names to their corresponding physical names.
- Access Check: The parser needs to check if the user has access to the objects involved in the statement.
- Execution Plan: The final stage is to generate an execution plan for the statement.
Testing the Parsing Process
To test the parsing process, we can use simple SQL statements and analyze the errors produced by the parser. Let’s start with MySQL.
MySQL Parser Simple Test
We can create a table in MySQL to test the parsing process:
CREATE TABLE test (id INT, name VARCHAR(30));
If we submit a syntax error SQL, MySQL provides limited information on the analytical aspect:
SELECT1 id3 FROM2 test1 WHERE3 id2 = 'aaa' GROUP BY4 id1 ORDER BY5 id0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT1 id3 FROM2 test1 WHERE3 id2 = 'aaa' GROUP BY4 id1 ORDER BY5 id0' at line 1
To verify the parsing process, we can try to resolve the situation in the MySQL side. Let’s start with the FROM clause:
SELECT id1 FROM test WHERE id = 100 GROUP BY id2 ORDER BY id3;
ERROR 1054 (42S22): Unknown column 'id1' in 'field list'
We can fix the error in the SELECT clause:
SELECT id FROM test WHERE id = 100 GROUP BY id2 ORDER BY id3;
ERROR 1054 (42S22): Unknown column 'id2' in 'group statement'
We can fix the error in the GROUP BY clause:
SELECT id FROM test WHERE id = 100 GROUP BY id ORDER BY id3;
ERROR 1054 (42S22): Unknown column 'id3' in 'order clause'
We can fix the error in the ORDER BY clause.
The parsing order and execution order vary greatly between MySQL and Oracle. The main difference is that MySQL performs the parsing process from left to right, while Oracle performs the parsing process from right to left.
Oracle Parser Simple Test
We can create a table in Oracle to test the parsing process:
CREATE TABLE test (id NUMBER, name VARCHAR2(30));
If we submit a syntax error SQL, Oracle provides detailed information on the analytical aspect:
SELECT1 id3 FROM2 test1 WHERE3 id2 = 'aaa' GROUP BY4 id1 ORDER BY5 id0;
ORA-20001: Query must begin with SELECT or WITH
To verify the parsing process, we can try to resolve the situation in the Oracle side. Let’s start with the SELECT clause:
SELECT id1 FROM test1 WHERE id1 = 'aaa' GROUP BY id1 ORDER BY id1;
ORA-00923: FROM keyword not found where expected
We can fix the error in the FROM clause:
SELECT id3 FROM test1 WHERE id2 = 'aaa' GROUP BY id1 ORDER BY id0;
ORA-00933: SQL command not properly ended
We can fix the error in the WHERE clause:
SELECT id3 FROM test1 WHERE id2 = 'aaa' GROUP BY id1 ORDER BY id0;
ORA-00924: missing BY keyword
We can fix the error in the GROUP BY clause:
SELECT id3 FROM test1 WHERE id2 = 'aaa' GROUP BY id1 ORDER BY id;
ORA-00942: table or view does not exist
We can fix the error in the table name:
SELECT id3 FROM test WHERE id2 = 'aaa' GROUP BY id1 ORDER BY id;
ORA-00904: "ID0": invalid identifier
We can fix the error in the ORDER BY clause:
SELECT id3 FROM test WHERE id2 = 'aaa' GROUP BY id1 ORDER BY id;
ORA-00904: "ID1": invalid identifier
We can fix the error in the GROUP BY clause:
SELECT id3 FROM test WHERE id2 = 'aaa' GROUP BY id ORDER BY id;
ORA-00904: "ID2": invalid identifier
We can fix the error in the WHERE clause:
SELECT id3 FROM test WHERE id = 'aaa' GROUP BY id ORDER BY id;
ORA-00904: "ID3": invalid identifier
We can fix the error in the SELECT clause.
Through the above error test, we can basically deal with the statement to resolve the situation. However, it’s worth noting that the order of parsing SQL and SQL data processing order is not the same. The idea of one kind of reference is that the order of parsing SQL is from left to right, while the order of SQL data processing is from right to left.
In conclusion, the parsing process of MySQL and Oracle varies greatly. By testing and verifying the parsing process, we can gain a deeper understanding of the SQL parsing process and improve our skills in database development.