Description:
Statement that executes time conversion to integer
1.Execute time to integer sql statement, prompt error，It's right
mysql> SELECT * from stmt_test where int_col < 24:59:59.000000;
ERROR 1064 (42000): 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 ':59:59.000000' at line 1
2.When executing the prepare time to integer statement for the first time, a statement error message appears, which is also correct.
mysql> prepare stmt2 from "SELECT * from stmt_test where int_col < ?";SET @a = 24:59:59.000000;EXECUTE stmt2 USING @a;deallocate prepare stmt2;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
ERROR 1064 (42000): 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 ':59:59.000000' at line 1
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.However, in the same session,after executing other correct prepare statements, executing the second statement prompts an error, but there is output
prepare stmt from "SELECT * from stmt_test where int_col <?";SET @a ='10.2';EXECUTE stmt USING @a;deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
Query OK, 0 rows affected (0.00 sec)
+--------+---------+
| id_col | int_col |
+--------+---------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 7 |
+--------+---------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> prepare stmt2 from "SELECT * from stmt_test where int_col < ?";SET @a = 24:59:59.000000;EXECUTE stmt2 USING @a;deallocate prepare stmt2;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
ERROR 1064 (42000): 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 ':59:59.000000' at line 1
+--------+---------+
| id_col | int_col |
+--------+---------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 7 |
+--------+---------+
4 rows in set (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
How to repeat:
create database test;
use test;
CREATE TABLE `stmt_test` (
`id_col` int unsigned NOT NULL,
`int_col` int DEFAULT NULL
) ENGINE=InnoDB ;
INSERT INTO stmt_test (id_col, int_col)VALUES(1, 2),(2, 4),(3, 5),(4, 7),(5, 11);
SELECT * from stmt_test where int_col < 24:59:59.000000;
prepare stmt2 from "SELECT * from stmt_test where int_col < ?";SET @a = 24:59:59.000000;EXECUTE stmt2 USING @a;deallocate prepare stmt2;
prepare stmt from "SELECT * from stmt_test where int_col <?";SET @a ='10.2';EXECUTE stmt USING @a;deallocate prepare stmt;
prepare stmt2 from "SELECT * from stmt_test where int_col < ?";SET @a = 24:59:59.000000;EXECUTE stmt2 USING @a;deallocate prepare stmt2;
Suggested fix:
When executing this statement, an error should be reported and there should be no output.
prepare stmt2 from "SELECT * from stmt_test where int_col < ?";SET @a = 24:59:59.000000;EXECUTE stmt2 USING @a;deallocate prepare stmt2;
