Incorrect parsing literals that consisting of digits and letters, beginning with digits #804

Activity

1. DiamondMofeng commented on Feb 3, 2023

   

   DiamondMofeng

   on Feb 3, 2023

   Last edited by DiamondMofeng

   Author

   More actions

   Althrough nodody would name their columns like 1col 00column, mysql does allow this.

   CREATE DATABASE test;
   use test;
   CREATE TABLE my_table (
   	id int,
   	1col int,
       2col int
   );
   
   INSERT INTO my_table VALUES (1,2,3);
   INSERT INTO my_table VALUES (2,3,4);
   INSERT INTO my_table VALUES (3,4,5);
    
   SELECT * FROM my_table;
   SELECT 1col FROM my_table

   MariaDB [(none)]> CREATE DATABASE test;
   Query OK, 1 row affected (0.000 sec)
   
   MariaDB [(none)]> use test;
   INSERT INTO my_table VALUES (3,4,5);
   
   SELECT * FROM my_table;
   SELECT 1col FROM my_table
   Database changed
   MariaDB [test]> CREATE TABLE my_table (
       -> id int,
       -> 1col int,
       ->     2col int
       -> );
   Query OK, 0 rows affected (0.029 sec)
   
   MariaDB [test]>
   MariaDB [test]> INSERT INTO my_table VALUES (1,2,3);
   Query OK, 1 row affected (0.005 sec)
   
   MariaDB [test]> INSERT INTO my_table VALUES (2,3,4);
   Query OK, 1 row affected (0.003 sec)
   
   MariaDB [test]> INSERT INTO my_table VALUES (3,4,5);
   Query OK, 1 row affected (0.007 sec)
   
   MariaDB [test]>
   MariaDB [test]> SELECT * FROM my_table;
   +------+------+------+
   | id   | 1col | 2col |
   +------+------+------+
   |    1 |    2 |    3 |
   |    2 |    3 |    4 |
   |    3 |    4 |    5 |
   +------+------+------+
   3 rows in set (0.000 sec)
   
   MariaDB [test]> SELECT 1col FROM my_table
       -> ;
   +------+
   | 1col |
   +------+
   |    2 |
   |    3 |
   |    4 |
   +------+
   3 rows in set (0.000 sec)
   
   MariaDB [test]>
   
2. DiamondMofeng commented on Feb 3, 2023

   

   DiamondMofeng

   on Feb 3, 2023

   Last edited by DiamondMofeng

   Author

   More actions

   

   As you can see, SELECT 1col FROM mytable was treated as SELECT 1 col FROM mytable
3. DiamondMofeng commented on Feb 6, 2023

   

   DiamondMofeng

   on Feb 6, 2023

   Last edited by DiamondMofeng

   Author

   More actions

   Okay, I think it has nothing to do with if some dialect supports column name beginning with numbers. This is simply a bug about parsing word tokens incorrectly
   
4. Jefffrey commented on Feb 6, 2023

   

   Jefffrey

   on Feb 6, 2023

   Contributor

   More actions

   I believe this is intended behaviour, at least for Generic/Postgres dialect. Relevant bit of discussion here:

   #768 (comment)

   Though some dialects can support having their identifiers begin with a number, such as Hive:

   https://github.com/sqlparser-rs/sqlparser-rs/blob/4955863bdf0e9d90e0fff72bca7912b3d20daac2/src/dialect/hive.rs#L23-L28

   Though this does cause some issues with parsing numbers, for example exponent parsing does not work (related to above discussion)

   Since Hive supports it, same could be enabled for MySql now which has an existing comment about it:

   https://github.com/sqlparser-rs/sqlparser-rs/blob/4955863bdf0e9d90e0fff72bca7912b3d20daac2/src/dialect/mysql.rs#L19-L29
5. AviRaboah commented on Apr 20, 2023

   

   AviRaboah

   on Apr 20, 2023

   Contributor

   More actions

   Any update about this issue? do this issue should be fixed in the near future for mysql?

   thanks.
6. alamb

   closed this as completedin #856on Apr 26, 2023