MySQL Bugs: #45651: Update command not giving Syntax Error

Bug #45651	Update command not giving Syntax Error
Submitted:	22 Jun 2009 10:09	Modified:	3 Aug 2009 8:56
Reporter:	Dhananjay Joshi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (CentOS release 4.7 (Final), Ubuntu 8.10 amd 64)
Assigned to:		CPU Architecture:	Any

Description:
I am trying the Update query on a table in MySQL which has a Syntax error.

Update TABLENAME SET COLUMN1='COLUMN1' AND COLUMN2 ='current_timestamp' 
WHERE COLUMN='COLUMN3';

After Executing above Query is not giving me any error.
It Says

Query OK, 29 rows affected (0.00 sec)
Rows matched: 29  Changed: 29  Warnings: 0

Update query has systex error still it is execution.
The query should be like 
Update TABLENAME SET COLUMN1='COLUMN1' , COLUMN2 ='current_timestamp' 
WHERE COLUMN='COLUMN3';

Note:- We have another column in the table "COLUMN4" which has default
value set to 'current_timestamp'. COLUMN1 is varchar(100), COLUMN2 and COLUMN4 are type of TIMESTAMP.

How to repeat:
Can be repeated easily with above information.

Suggested fix:
Need to check the Syntex error when query is parseing.

When we execute incorrect update command it makes the COLUMN1 value to "0".

Well 'current_timestamp' is a string because it is 'quoted'!  Such value is not valid for a TIMESTAMP column.  So it will truncated to '0' (zero).  Try execute SHOW WARNINGS after the statement! The exact behaviour depends on the sql_mode with recent server versions (I do not remember details about sql_mode in 4.1).

Correct statement syntax would be to specify the value as current_timestamp (without quotes) and not 'current_timestamp'!  

(and btw: you should upgrade that server .. at least to 4.1.20/4.1.22!)

This is *not a bug* in my understanding! 

Peter
(not a MySQL person)

Hi Peter,

Sorry Its my mistake,current_timestamp should not be in 'quoted'.
Please consider it in wihtout 'quotes'. 

So Updated Incorrect Query will be.

Update TABLENAME SET COLUMN1='COLUMN1' AND COLUMN2 = current_timestamp

WHERE COLUMN='COLUMN3';

sorry .. I missed some point here!  I missed the AND here

Update TABLENAME SET COLUMN1='COLUMN1' AND COLUMN2 ='current_timestamp' 
WHERE COLUMN='COLUMN3';

I also do not fully understand your table, but if I do

CREATE TABLE `tablename1` (
  `column` varchar(100) DEFAULT NULL,
  `column1` varchar(100) DEFAULT NULL,
  `column2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `column4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

then 

SELECT 'COLUMN1' AND COLUMN2 ='current_timestamp' FROM tablename1;

returns 

a header like 'COLUMN1' AND COLUMN2 ='current_timestamp'
------------------------------------------
. .and no rows

and

UPDATE TABLENAME1 SET COLUMN1='COLUMN1' AND COLUMN2 ='current_timestamp' 
WHERE COLUMN='COLUMN3';

.. returns syntax error on server 5.1.35

@Dhananjay

I think you should paste the returns of "SHOW CREATE TABLE tablename". and maybe even a few INSERT to populate the table with a few rows of data.

I think the SELECT output in my post looks weird - also with 5.1.35!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

CREATE TABLE `TEST` (
  `COL1` varchar(100) NOT NULL default '',
  `COL2` varchar(100) default NULL,
  `COL3` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `COL4` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO TEST(COL1, COL2, COL3, COL4)
  VALUES('1', 'COL1', '2009-06-22 22:49:01.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
  VALUES('2', 'COL2', '2009-06-22 19:19:17.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
  VALUES('3', 'COL3', '2009-06-22 19:19:17.0', NULL);

Now execute Following query

Update TEST SET COL2='NEW-DATA' AND COL4 =current_timestamp WHERE COL1='1';
commit;

Above query has syntex error, but MySQL is not giving any error and execute the query successfully.

Now execute following query
select * from TEST ;

It will show COL2 = '0'.

Please let me know if you required any other information.

on 5.1.35: 

set sql_mode = '';

CREATE TABLE `TEST` (
 `COL1` VARCHAR(100) NOT NULL DEFAULT '',
 `COL2` VARCHAR(100) DEFAULT NULL,
 `COL3` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
 `COL4` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY  (`COL1`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('1', 'COL1', '2009-06-22 22:49:01.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('2', 'COL2', '2009-06-22 19:19:17.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('3', 'COL3', '2009-06-22 19:19:17.0', NULL);
 
UPDATE TEST SET COL2='NEW-DATA' AND COL4 =CURRENT_TIMESTAMP WHERE
COL1='1';

SHOW WARNINGS; 
/*
Level      Code  Message                                     
-------  ------  --------------------------------------------
Warning    1292  Truncated incorrect DOUBLE value: 'NEW-DATA'
*/

SELECT * FROM test;
/*
COL1    COL2                   COL3               COL4  
------  ------  -------------------  -------------------
1       0       2009-06-22 16:05:47  2009-06-22 16:05:22
2       COL2    2009-06-22 19:19:17  2009-06-22 16:05:23
3       COL3    2009-06-22 19:19:17  2009-06-22 16:05:24
*/
.. 'Truncated incorrect DOUBLE value' explains why it becomes '0'.  But there is no DOUBLE column in the statement or in the table for that sake. It is a varchar and 'NEW-DATA' is valid data here!  But teh statement is not - however the parser, optimizer or server goes astray with "UPDATE TEST SET COL2='NEW-DATA' AND .."

This is a bug in my opinion - also with recent servers!

Hi Miguel Solorzano,

Following URL is visited and checked.
http://dev.mysql.com/doc/refman/4.1/en/update.html

I understand that, this bug may be fixed in the later version.
But I want to confirm that this is a Bug in Version 4.1.11.

Please let me know in case of any doubt.

Verified as described with actual source tree (5.1.37)

Test case:

set sql_mode = '';

CREATE TABLE `TEST` (
 `COL1` VARCHAR(100) NOT NULL DEFAULT '',
 `COL2` VARCHAR(100) DEFAULT NULL,
 `COL3` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
 `COL4` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY  (`COL1`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('1', 'COL1', '2009-06-22 22:49:01.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('2', 'COL2', '2009-06-22 19:19:17.0', NULL);
INSERT INTO TEST(COL1, COL2, COL3, COL4)
 VALUES('3', 'COL3', '2009-06-22 19:19:17.0', NULL);
 
UPDATE TEST SET COL2='NEW-DATA' AND COL4 =CURRENT_TIMESTAMP WHERE
COL1='1';

SHOW WARNINGS;

According to 

http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_and

this is not a bug.

Susanne Ebrecht:
According to 

http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_and

I guess the above url is applicable to select statement.

I wanted to show you that the SQL is correct. AND is a operator in SQL which means that the statement is correct SQL statement.

Susanne Ebrecht:
Please refer the following for the Syntex.

http://dev.mysql.com/doc/refman/5.1/en/update.html

You will able to see the right syntext.

http://dev.mysql.com/doc/refman/5.1/en/update.html

IF you see the above documentation, syntex is mention as:-

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] 

It is clearly mention the "," SET col_name1={expr1|DEFAULT} [, col_name2...

Thank you for the feedback.

('NEW-DATA' AND COL4 =CURRENT_TIMESTAMP) is correct expression in UPDATE TEST SET COL2='NEW-DATA' AND COL4 =CURRENT_TIMESTAMP WHERE COL1='1'; query. So this is not a bug.

Sveta Smirnova:
If you see the following documentation.They have clearly mention the exact syntex.

http://dev.mysql.com/doc/refman/5.1/en/update.html

So it is a bug in mysql.

Thank you for the feedback.

Page you mentioned does not abolish syntax for other valid SQL operators. Particularly, AND. See also http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_and

We are talking about Update Query and not Logical operatorts.
Please try to understand that because of syntex error, it is defeting the purpose of Update command...

Update syntax supports valid SQL syntax. I can contain function, operators and everything allowed in SQL. This is not a bug.

http://dev.mysql.com/doc/refman/5.1/en/update.html

IF you see the above documentation, syntex is mention as:-

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] 

It is clearly mention the "," SET col_name1={expr1|DEFAULT} [, col_name2...

See the Syntex above...

http://dev.mysql.com/doc/refman/5.1/en/update.html

IF you see the above documentation, syntex is mention as:-

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] 

It is clearly mention the "," SET col_name1={expr1|DEFAULT} [,
col_name2...

See the Syntex above...

Correct:

> SET col_name1={expr1|DEFAULT}

Where expr1 can be COL2='NEW-DATA' AND COL4 =current_timestamp

Correct:-
SET col_name1={expr1|DEFAULT} See after this. It is as follows.

 [, col_name2={expr2|DEFAULT}] 

It is clearly mention the "," SET col_name1={expr1|DEFAULT} [,
col_name2...

See the Syntex above...

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

I am not asking any help on using MySQL products.
But I am saying, there is a syntax error and MySQL is executes the Update command and which is absolutely incorrect.
It is defeating the purpose the Update command (DML command).

So requesting you all , please relook into it.

As Sveta and others explained you many times, 'NEW-DATA' AND COL4 =current_timestamp is a valid expression with AND operator and two operands. This is NOT a bug.

I think you are thinking the problem with diff angle.
Please see if there is a syntex error , how it can be a valid UPDATE Statement.
It is defeating the purpose the Update command (DML command).
Requesting you to please check all the ANSI SQL standards also.

I think you are thinking the problem with diff angle.
Please see if there is a syntex error , how it can be a valid UPDATE Statement.
It is defeating the purpose the Update command (DML command).
Requesting you to please check all the ANSI SQL standards also.

SET col_name1='NEW-DATA' AND COL4 =current_timestamp 

also is a valid syntax according to SQL Standard (SQL95, SQL99, SQL2003 and SQL2008)

I tested three other RDBMS here. They accepted Syntax too. Two of them just gave me an error message because they don't support automatic casting and so they didn't know which data type they should use here. But Syntax itself was accepted.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Bug: http://bugs.mysql.com/bug.php?id=56131  marked as duplicate of this one.