Description:
information_schema.`TABLES` has been optimized few months ago. This might be a residue of it.
Basic form that leads to anomaly is :
SELECT 1 FROM information_schema.`TABLES` T
WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
But the anomaly is not detectable by a simple SELECT.
The following form raises an exception (Error#1049):
SET @a = (SELECT 1 ..);
But the really weird case is observed in the following form inside a stored procedure :
SELECT 1 INTO btmp ..
If there is no exception handler around, it runs as expected.
If there is an exception handler, it catches god-knows-what error !
How to repeat:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1624
Server version: 5.1.33-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> -- basic behaviours :
mysql>
mysql> SELECT 1 FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
Empty set (0.00 sec)
mysql>
mysql> SET @a = (SELECT 1 FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1);
ERROR 1049 (42000): Unknown database 'nosuchdb'
mysql>
mysql> SELECT 1 INTO @a FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql>
mysql> -- SET within a stored procedure :
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySet` $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `test`.`TestISTablesBySet` ()
-> BEGIN
-> DECLARE btmp BOOL;
->
-> SET btmp = (SELECT 1 FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1);
->
-> SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> CALL `test`.`TestISTablesBySet`();
ERROR 1049 (42000): Unknown database 'nosuchdb'
mysql>
mysql>
mysql> -- SELECT .. INTO within a stored procedure :
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySelectInto1` $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `test`.`TestISTablesBySelectInto1` ()
-> BEGIN
-> DECLARE btmp BOOL;
->
-> SELECT 1 INTO btmp FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
->
-> SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL `test`.`TestISTablesBySelectInto1`();
+-----------------------------------------+
| CONCAT('btmp is ', IFNULL(btmp,'NULL')) |
+-----------------------------------------+
| btmp is NULL |
+-----------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql>
mysql> -- SELECT .. INTO and HANDLER FOR SQLEXCEPTION within a stored procedure:
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySelectInto2` $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `test`.`TestISTablesBySelectInto2` ()
-> BEGIN
-> DECLARE btmp BOOL;
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
-> SELECT 'Actually there is a catchable "silent" (?!) exception.'
-> AS BEWARE;
-> END;
->
-> SELECT 1 INTO btmp FROM information_schema.`TABLES` T
-> WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
->
-> SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> CALL `test`.`TestISTablesBySelectInto2`();
+--------------------------------------------------------+
| BEWARE |
+--------------------------------------------------------+
| Actually there is a catchable "silent" (?!) exception. |
+--------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>