MySQL Forums
Forum List  »  Optimizer & Parser

Optimizer being overly aggressive
Posted by: Adrian Cornish
Date: July 06, 2012 08:18PM

I've reduced my issue down to this simple SP. The column names are getting cached in the SELECT * at the end. I have no idea why or how to stop it. I tried adding SQL_NO_CACHE but that makes no difference.

DROP TABLE IF EXISTS foo;
CREATE TABLE foo(
col1 int,
col2 int);
INSERT INTO foo VALUES(1,2),(3,4),(5,6);
DROP PROCEDURE IF EXISTS mysp;
DELIMITER ;;
CREATE DEFINER=root@localhost PROCEDURE mysp(c INT)
BEGIN
DROP TABLE IF EXISTS mydata;

SET @mycol='col1';

IF c > 0 THEN SET @mycol:='col2';
END IF;

SET @s=CONCAT('CREATE TEMPORARY TABLE mydata AS SELECT ', @mycol, ' FROM foo');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- The following select call fails on 2nd and subsequent executions of the SP
SELECT SQL_NO_CACHE * FROM mydata;
SELECT "Please see new temp table mydata" as Result;
END ;;
DELIMITER ;

Version

mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.15-log |
+------------+
1 row in set (0.00 sec)

First run works fine as expected

mysql> CALL mysp(0);
+------+
| col1 |
+------+
| 1 |
| 3 |
| 5 |
+------+
3 rows in set (0.17 sec)

+----------------------------------+
| Result |
+----------------------------------+
| Please see new temp table mydata |
+----------------------------------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.17 sec)

Now if I try and run it again using the other column

mysql> CALL mysp(1);
ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col1' in 'field list'
mysql> SELECT @mycol;
+--------+
| @mycol |
+--------+
| col2 |
+--------+
1 row in set (0.00 sec)

If I recreate the storedprocedure again its works

mysql> CALL mysp(1);
+------+
| col2 |
+------+
| 2 |
| 4 |
| 6 |
+------+
3 rows in set (0.18 sec)

+----------------------------------+
| Result |
+----------------------------------+
| Please see new temp table mydata |
+----------------------------------+
1 row in set (0.18 sec)

Query OK, 0 rows affected (0.18 sec)

But if I try switching back to the first column - even if I try dropping the temp table first - it still doesn't work

mysql> CALL mysp(0);
ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'
mysql> DROP TABLE mydata;
Query OK, 0 rows affected (0.03 sec)

mysql> CALL mysp(0);
ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'
mysql>

**Additional info asked for by eggyal. Also I tried this on another mysql version with same result. **

mysql> CALL mysp(1);
+------+
| col2 |
+------+
| 2 |
| 4 |
| 6 |
+------+
3 rows in set (0.20 sec)

+----------------------------------+
| Result |
+----------------------------------+
| Please see new temp table mydata |
+----------------------------------+
1 row in set (0.20 sec)

Query OK, 0 rows affected (0.20 sec)

mysql> describe mydata;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col2 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CALL mysp(0);
ERROR 1054 (42S22): Unknown column 'test.mydata.col2' in 'field list'
mysql> describe mydata;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Interesting development of a fix - changing the last few lines to a prepared statement works - but using exactly the same query as before.

-- The following select call fails on 2nd and subsequent executions of the SP
PREPARE stmt FROM 'SELECT SQL_NO_CACHE * FROM mydata';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT "Please see new temp table mydata" as Result;



Edited 1 time(s). Last edit at 07/07/2012 02:36PM by Adrian Cornish.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizer being overly aggressive
2674
July 06, 2012 08:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.