Wrong result on execution of a query as a PS (both 1st and further executions)

revno: 4030

revision-id: monty@askmonty.org-20140124125018-qwln430o4bu3pued

parent: monty@askmonty.org-20140124123019-2ts6q2ip6076gmqg

committer: Michael Widenius <monty@askmonty.org>

branch nick: maria-5.5

timestamp: Fri 2014-01-24 14:50:18 +0200

message:

  Fix for MDEV-5531: double call procedure in one session - hard shutdown the server

  Main fix was to not cache derivied tables as they may be temporary tables that are deleted before the next query.

  This was a bit tricky as Item_field::fix_fields depended on cached_tables to be set to resolve some columns.

CREATE TABLE t (a INT NOT NULL) ENGINE=MyISAM;

INSERT INTO t VALUES (0),(8);

PREPARE stmt FROM "

SELECT a FROM ( SELECT DISTINCT t1.* FROM t AS t1, t AS t2 ) AS sq 

WHERE a IN ( SELECT MIN(t3.a) FROM ( t AS t3 INNER JOIN t AS t4 ON (t3.a = t4.a) ) ) ORDER BY a LIMIT 10

";

execute stmt;

execute stmt;

MariaDB [test]> execute stmt;

+---+

| a |

+---+

| 0 |

| 8 |

+---+

2 rows in set (0.01 sec)

MariaDB [test]> execute stmt;

Empty set (0.00 sec)

MariaDB [test]> SELECT a FROM ( SELECT DISTINCT t1.* FROM t AS t1, t AS t2 ) AS sq 

    -> WHERE a IN ( SELECT MIN(t3.a) FROM t AS t3 INNER JOIN t AS t4 ON (t3.a = t4.a) ) ORDER BY a LIMIT 10

    -> ;

+---+

| a |

+---+

| 0 |

+---+

1 row in set (0.00 sec)