'Unknown column' when re-executing a prepared query optimized with materialization

[Philip Stoev]

When the following query:

SELECT table1 .`col_varchar_key`
FROM (
SELECT *
FROM BB ) table1 STRAIGHT_JOIN B ON ( 'd' , 'f' ) IN (
SELECT `col_varchar_nokey` SUBQUERY2_field1 , `col_varchar_nokey`
FROM view_B )

is executed with subquery cache by preparing it and running it twice, the server returns the following error:

1054: 'Unknown column 'materialized subselect.SUBQUERY2_field1' in 'order clause'

The main problem aside, it may not be wise to output optimizer-internal information in error messages. A generic "internal optimizer error" or a direct assertion may be less confusing than a message about a table or a column the user never created herself.

[Philip Stoev (pstoev-askmonty)]

Test case. Views seem to be required for this particular query, but the bug has also been observed without them.

SET LOCAL optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off';

CREATE TABLE `BB` (
  `col_int_key` int(11) NOT NULL,
  `col_varchar_key` varchar(1) NOT NULL,
  `col_varchar_nokey` varchar(1) NOT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (9,'p','p');
CREATE TABLE `B` (
  `col_int_key` int(11) NOT NULL,
  `col_varchar_key` varchar(1) NOT NULL,
  `col_varchar_nokey` varchar(1) NOT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (7,'p','p');

CREATE VIEW view_B AS SELECT * FROM B;

PREPARE st1 FROM "
SELECT table1 .`col_varchar_key`
FROM (
SELECT *
FROM BB ) table1 STRAIGHT_JOIN B ON ( 'd' , 'f' ) IN (
SELECT `col_varchar_nokey` SUBQUERY2_field1 , `col_varchar_nokey`
FROM view_B ) ";

EXECUTE st1;
--error 0
EXECUTE st1;

DEALLOCATE PREPARE st1;

[Philip Stoev (pstoev-askmonty)]

This is a materialization bug:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
1 PRIMARY B system NULL NULL NULL NULL 1 100.00
3 SUBQUERY B system NULL NULL NULL NULL 1 100.00
2 DERIVED BB system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select 'p' AS `col_varchar_key` from (select '9' AS `col_int_key`,'p' AS `col_varchar_key`,'p' AS `col_varchar_nokey` from `test`.`BB`) `table1` straight_join `test`.`B` where <in_optimizer>(('d','f'),('d','f') in ( <materialize> (select 'p','p' from `test`.`B` ), <primary_index_lookup>('d' in <temporary table> on distinct_key where (('d' = `materialized subselect`.`SUBQUERY2_field1`) and ('f' = `materialized subselect`.`col_varchar_nokey`)))))

[Timour Katchaounov (timour)]

The bug is not reproducible in the 5.3-mwl89 tree, most likely as
a result of proper subquery optimization during the optimization
phase.

Therefore I will mark the bug as fixed. The test case for the bug
will be added to the 5.3-mwl89 tree, so that it appears in the main
5.3 tree once MWL#89 is merged.

[Timour Katchaounov (timour)]

Simplified test query:

PREPARE st1 FROM "
SELECT BB.col_varchar_key
FROM BB STRAIGHT_JOIN B
           ON ('d') IN (SELECT `col_varchar_nokey` SUBQUERY2_field1 FROM view_B )
";

[Timour Katchaounov (timour)]

Analysis:
After the first execution of the prepared statement it turns out that the string
that represents the name of of the field in the temporary table has been
freed. Thus, when during the second exec call, name resolution is called to
re-resolve the synthetically created equi-join conditions for the materialized
IN execution, the corresponding field is not found in the materialized temp
table, because temp_table->field[0]->field_name is 0.

I have not investigated exactly why the string "SUBQUERY2_field1" has
been freed, but my hypothesis is that the Item that represents the SELECT
list of the view 'view_B' is been freed/reallocated between executions. It is
this Item's name that is used as the name of the temp table field.

Why this works in 5.3-mwl#89:
It works because MWL#89 changes creation of all execution related objects
for materialized subquery execution to be re-created at each PS execution.
In addition, the Items created for the materialized equi-join conditions are
already marked as fixed so they don't need to be resolved at all. This is
also true for the first exec call in the main 5.3.