[MDEV-16981] INSERT INTO td SELECT now() WHERE 1=1 Works in 10.1, Fails in 10.2 and 10.3 Created: 2018-08-14  Updated: 2020-08-25  Resolved: 2018-09-12

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.2.17, 10.3.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Chris Calender (Inactive) Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Queries of the following type (auto-generated) work in MariaDB 10.1.33, but fail in 10.2.17 and 10.3.8:

INSERT INTO td SELECT now() WHERE 1=1;

It is common for the code to auto add the "WHERE 0=1" or "WHERE 1=1".

10.1:

mysql> CREATE TABLE `td` (
 
    -> `t` datetime DEFAULT NULL
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
 
Query OK, 0 rows affected (1.87 sec)
 
 
 
mysql> insert into td select now() where 1=1;
 
Query OK, 1 row affected (0.02 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
mysql> select * from td;
 
+---------------------+
 
| t                   |
 
+---------------------+
 
| 2018-08-14 16:59:44 |
 
+---------------------+
 
1 row in set (0.00 sec)

In both 10.2 and 10.3, we see:

mysql> CREATE TABLE `td` (
 
    -> `t` datetime DEFAULT NULL
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
 
Query OK, 0 rows affected (1.67 sec)
 
 
 
mysql> insert into td select now() where 1=1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where 1=1' at line 1

One strange thing about 10.1 is that the sub-query ("select now() where 1=1;") fails when issued by itself, but works when it is part of the INSERT INTO:

mysql> select now() where 1=1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where 1=1' at line 1



 Comments   
Comment by Elena Stepanova [ 2018-08-24 ]

serg,
We discussed it briefly, but I don't believe we had made any conclusion. I leave it to you to decide whether it needs to be fixed.

Comment by Sergei Golubchik [ 2018-08-30 ]

WHERE without FROM never worked in SELECT. By some unintended parser glitch it worked in INSERT ... SELECT, in 10.2 in the course of parser refactoring or some bug fix this part of the grammar was unified, duplicated rules were removed and INSERT ... SELECT now uses the same grammar as SELECT. That's why WHERE without FROM stopped working. It was never supposed to work in the first place.

But, really, I don't see a reason why it shouldn't work. So we'll try to allow this syntax if it won't negatively affect the rest of the grammar.

Comment by Alexander Barkov [ 2018-09-12 ]

Discussed with Sergei again.

According to the SQL standard, <where clause> is a part of <table expression>.

 <table expression>    ::=
 
         <from clause>
 
         [ <where clause> ]
 
         [ <group by clause> ]
 
         [ <having clause> ]
 
         [ <window clause> ]
 
 
 
<from clause>    ::=   FROM <table reference list>

WHERE is not possible without FROM.

We won't fix this.

As a workaround, one can use LIMIT 1 and LIMIT 0 in auto-generated queries.

Generated at Thu Feb 08 08:33:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.