Description:
Declared variables test as NULL after a 'select ... into variable' statement. However, if one does not test them, they work as though not NULLs.
How to repeat:
Pardon the ERRORS, I made a few typos. I do not believe the errors are relevant to reproducing this bug, but I thought it best to be precise in what I did.
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table t1(pk INT PRIMARY_KEY AUTO_INCREMENT, n INT, s VARCHAR(50));
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 'PRIMARY_KEY AUTO_INCREMENT, n INT, s VARCHAR(50))' at line 1
mysql> create table t1(pk INT PRIMARY KEY AUTO_INCREMENT, n INT, s VARCHAR(50));
Query OK, 0 rows affected (0.04 sec)
mysql> create table t2(pk INT PRIMARY KEY AUTO_INCREMENT, n INT, s VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE p1(IN n INT)
-> BEGIN
-> DECLARE nVar INT;
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 '' at line 3
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1(IN n INT)
-> BEGIN
-> DECLARE nVar INT;
-> DECLARE sVar VARCHAR(50);
-> SELECT n, s INTO nVar, sVar FROM t1 WHERE pk = n;
-> IF nVar IS NULL THEN
-> SELECT * FROM nVarIsNULL;
-> END IF;
-> INSERT t2 (n, s) VALUES (nVar, sVar);
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE p2(IN n INT)
-> BEGIN
-> DECLARE nVar INT;
-> DECLARE sVar VARCHAR(50);
-> SELECT n, s INTO nVar, sVar FROM t1 WHERE pk = n;
-> INSERT t2 (n, s) VALUES (nVar, sVar);
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> INSERT INTO t1 (n, s) VALUES (1, 'one'),(2, 'two'),(3, 'three');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+------+-------+
| pk | n | s |
+----+------+-------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> call p1(2);
ERROR 1146 (42S02): Table 'db1.nVarIsNULL' doesn't exist
mysql> SELECT * FROM t2;
Empty set (0.00 sec)
mysql> call p2(3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t2;
+----+------+-------+
| pk | n | s |
+----+------+-------+
| 1 | 3 | three |
+----+------+-------+
1 row in set (0.00 sec)
mysql> SELECT version;
ERROR 1054 (42S22): Unknown column 'version' in 'field list'
mysql> SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 5.0.4-beta-standard-log |
+-------------------------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
I cannot suggest a fix. However, the If xxx IS NOT NULL should produce an accurate result.
Description: Declared variables test as NULL after a 'select ... into variable' statement. However, if one does not test them, they work as though not NULLs. How to repeat: Pardon the ERRORS, I made a few typos. I do not believe the errors are relevant to reproducing this bug, but I thought it best to be precise in what I did. mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> use db1; Database changed mysql> create table t1(pk INT PRIMARY_KEY AUTO_INCREMENT, n INT, s VARCHAR(50)); 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 'PRIMARY_KEY AUTO_INCREMENT, n INT, s VARCHAR(50))' at line 1 mysql> create table t1(pk INT PRIMARY KEY AUTO_INCREMENT, n INT, s VARCHAR(50)); Query OK, 0 rows affected (0.04 sec) mysql> create table t2(pk INT PRIMARY KEY AUTO_INCREMENT, n INT, s VARCHAR(50)); Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE p1(IN n INT) -> BEGIN -> DECLARE nVar INT; 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 '' at line 3 mysql> DELIMITER $$ mysql> CREATE PROCEDURE p1(IN n INT) -> BEGIN -> DECLARE nVar INT; -> DECLARE sVar VARCHAR(50); -> SELECT n, s INTO nVar, sVar FROM t1 WHERE pk = n; -> IF nVar IS NULL THEN -> SELECT * FROM nVarIsNULL; -> END IF; -> INSERT t2 (n, s) VALUES (nVar, sVar); -> END; -> $$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE p2(IN n INT) -> BEGIN -> DECLARE nVar INT; -> DECLARE sVar VARCHAR(50); -> SELECT n, s INTO nVar, sVar FROM t1 WHERE pk = n; -> INSERT t2 (n, s) VALUES (nVar, sVar); -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> INSERT INTO t1 (n, s) VALUES (1, 'one'),(2, 'two'),(3, 'three'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +----+------+-------+ | pk | n | s | +----+------+-------+ | 1 | 1 | one | | 2 | 2 | two | | 3 | 3 | three | +----+------+-------+ 3 rows in set (0.00 sec) mysql> call p1(2); ERROR 1146 (42S02): Table 'db1.nVarIsNULL' doesn't exist mysql> SELECT * FROM t2; Empty set (0.00 sec) mysql> call p2(3); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t2; +----+------+-------+ | pk | n | s | +----+------+-------+ | 1 | 3 | three | +----+------+-------+ 1 row in set (0.00 sec) mysql> SELECT version; ERROR 1054 (42S22): Unknown column 'version' in 'field list' mysql> SELECT VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.0.4-beta-standard-log | +-------------------------+ 1 row in set (0.00 sec) mysql> Suggested fix: I cannot suggest a fix. However, the If xxx IS NOT NULL should produce an accurate result.