Bug #95095 Issue with SQL parser

• Submitted: 24 Apr 2019 1:07
• Modified: 24 Apr 2019 5:54
• Reporter: Sangamesh Bellad
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[24 Apr 2019 1:07] Sangamesh Bellad

Description:
SQL statement INSERT INTO ... ON DUPLICATE KEY UPDATE ... allows for incorrect UPDATE statement. Gives wrong error.

How to repeat:
CREATE TABLE test.`job_status_test` (
  `run_id` char(16) NOT NULL,
  `app` varchar(45) NOT NULL,
  `status` varchar(45) NOT NULL,
  `meta` varchar(1024) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- INVALID SQL executes
INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
ON DUPLICATE KEY UPDATE `status`='STARTED'  AND  `updated`= CURRENT_TIMESTAMP()

1 row(s) affected

Run above query again:
-- INVALID SQL executes
INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
ON DUPLICATE KEY UPDATE `status`='STARTED'  AND  `updated`= CURRENT_TIMESTAMP()

Error Code: 1292. Truncated incorrect DOUBLE value: 'STARTED'

Suggested fix:
UPDATE status=? AND updated=CURRENT_TIMESTAMP()
is wrong

The correct is 
INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
ON DUPLICATE KEY UPDATE `status`='STARTED' ,  `updated`= CURRENT_TIMESTAMP()

The usage of AND is incorrect (should be comma) discovered by mistake

[24 Apr 2019 5:54] MySQL Verification Team

Hi Sangamesh,

Thank you for the bug report.
Imho this is not a bug. Your update query "UPDATE `status`='STARTED'  AND  `updated`= CURRENT_TIMESTAMP()" is actually interpreted and executed as UPDATE `status`=('STARTED'  AND  `updated`= CURRENT_TIMESTAMP()). 

Also, see Bug# 84659, Bug #82647, Bug #19398, Bug #7068

- 8.0.15, default sql_mode
mysql> select ('STARTED'  AND  'aaaaa'= CURRENT_TIMESTAMP());
+--------------------------------------------------+
| ('STARTED'  AND  'aaaaa'= CURRENT_TIMESTAMP()) |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: 'updated' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'STARTED'   |
+---------+------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
    -> ON DUPLICATE KEY UPDATE `status`='STARTED'  AND  `updated`= CURRENT_TIMESTAMP();
Query OK, 1 row affected (0.26 sec)

mysql> select * from test.job_status_test;
+--------+------+---------+------+---------------------+---------------------+
| run_id | app  | status  | meta | created             | updated             |
+--------+------+---------+------+---------------------+---------------------+
| 101    | app1 | STARTED |      | 2019-04-24 11:13:31 | 0000-00-00 00:00:00 |
+--------+------+---------+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
    -> ON DUPLICATE KEY UPDATE `status`='STARTED'  AND  `updated`= CURRENT_TIMESTAMP();
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'STARTED'
mysql>
mysql> select * from test.job_status_test;
+--------+------+---------+------+---------------------+---------------------+
| run_id | app  | status  | meta | created             | updated             |
+--------+------+---------+------+---------------------+---------------------+
| 101    | app1 | STARTED |      | 2019-04-24 11:13:31 | 0000-00-00 00:00:00 |
+--------+------+---------+------+---------------------+---------------------+
1 row in set (0.00 sec)

thanks,
Umesh