Bug #3091 Field named 'time' can not be updated with '...time=time+1'

• Submitted: 7 Mar 2004 6:47
• Modified: 7 Mar 2004 11:48
• Reporter: Elifant'ev Oleg
• Status: Won't fix
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.18
• OS: FreeBSD (FreeBSD 5.0-5.2)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[7 Mar 2004 6:47] Elifant'ev Oleg

Description:
Field named 'time' can not be updated with query like '...time=time+1...'

How to repeat:
mysql> create table tsttbl (time int not null);
Query OK, 0 rows affected (0.31 sec)

mysql> update tsttbl set time=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update tsttbl set time=time+1;
ERROR 1064: 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 '+1' at line 1

But if we go this way -->
--------------------------------------
mysql> create table tsttbl2 (time int not null,bug int not null);
Query OK, 0 rows affected (0.16 sec)

mysql> update tsttbl2 set time=0,bug=0;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update tsttbl2 set bug=bug+1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update tsttbl2 set time=bug;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
--------------------------------------
everything works

[7 Mar 2004 11:48] Sergei Golubchik

The bug is in how lexer splits an incoming stream into tokens.
"time" is a keyword (though not reserved) lexer erroneously parser the statement as

  keyword("update") identifier("tsttbl") keyword("set")
  keyword("time") operator("=") keyword("time") number("+1")

while the correct splitting is

  ... keyword("time") operator("+") number("1")

It is fixed in 4.1 already, but unfortunately the fix is too big and too intrusive to go into the stable version (such as 4.0).

As a workaround you may put a space between "+" and "1".