3783 Roy Lyseng 2012-03-15
Bug#13773979: Missing rows on second execution of prepared statement
This problem may occur with prepared statements that reference views
and perform semi-join transformation.
A field reference through a view is replaced during resolving for each
execution of the prepared statement. Semi-join transformation occurs
after resolving, so the fix_after_pullout() stage applied to all
Item_field objects will not be carried out. fix_after_pullout() builds
a new resolver context for the fields, but obviously this is not updated
for the replaced field. On second resolving, the select_lex context
refers to the deleted select_lex and it appears that the field comes
from an outer table.
The fix is to update the select_lex pointer in the context object
that is contained in the select_lex object which will be removed,
to point to the "parent" select_lex object.
This context object is used for every field reference that is not
a join condition. I checked with view references in join conditions as
well, but apparently these do not cause any trouble.
mysql-test/include/subquery_sj.inc
Added test case for bug#13773979.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13773979.
sql/item.cc
Removed an assert because it no longer holds when resolver context
is updated earlier.
sql/sql_optimizer.cc
In convert_subquery_to_semijoin(), update the select_lex pointer in
the context object that is contained in the deleted select_lex object,
to point to the "parent" select_lex object.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/item.cc
sql/sql_optimizer.cc
3782 Olav Sandstaa 2012-03-15
Post-push fix for BUG#12365385.
Fix compiler warning about unused variable that occured in non-debug builds.
modified:
sql/handler.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-02-29 15:28:26 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-15 13:15:19 +0000
@@ -4829,4 +4829,39 @@ DROP TABLE t1;
--echo # End of test for bug#13726217.
+--echo # BUG#13773979: Missing rows on second execution of prepared statement
+
+CREATE TABLE t1 (
+ col_int_nokey INT,
+ col_int_key INT,
+ col_varchar_key VARCHAR(1)
+);
+
+INSERT INTO t1 VALUES
+ (1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+ (2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+let $query=
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+ SELECT alias1.col_int_nokey AS field1
+ FROM v1 AS alias1
+ WHERE alias1.col_varchar_key < 'v'
+);
+
+eval $query;
+eval prepare stmt FROM "$query";
+execute stmt;
+execute stmt;
+
+DEALLOCATE PREPARE stmt;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo # End of test for bug#13773979.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-15 13:15:19 +0000
@@ -7953,5 +7953,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-15 13:15:19 +0000
@@ -7958,6 +7958,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7959,6 +7959,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7959,6 +7959,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-15 13:15:19 +0000
@@ -7937,5 +7937,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-15 13:15:19 +0000
@@ -7938,6 +7938,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7946,6 +7946,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7939,6 +7939,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-15 13:15:19 +0000
@@ -7936,6 +7936,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-15 13:15:19 +0000
@@ -7937,6 +7937,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7945,6 +7945,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7938,6 +7938,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-15 13:15:19 +0000
@@ -7938,5 +7938,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-15 13:15:19 +0000
@@ -7939,6 +7939,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7947,6 +7947,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7940,6 +7940,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+2 2 i
+4 9 l
+7 0 s
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-15 13:15:19 +0000
@@ -7951,5 +7951,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-15 13:15:19 +0000
@@ -7952,6 +7952,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7953,6 +7953,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7953,6 +7953,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-15 13:15:19 +0000
@@ -8019,5 +8019,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-15 13:15:19 +0000
@@ -7934,5 +7934,50 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-15 13:15:19 +0000
@@ -7935,6 +7935,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-15 13:15:19 +0000
@@ -7935,6 +7935,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-02-29 15:28:26 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-15 13:15:19 +0000
@@ -7936,6 +7936,51 @@ HAVING b
0
DROP TABLE t1;
# End of test for bug#13726217.
+# BUG#13773979: Missing rows on second execution of prepared statement
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,7,'v'), (7,0,'s'), (4,9,'l'), (7,3,'y'),
+(2,2,'i'), (9,5,'h'), (0,1,'a'), (9,3,'v');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+);
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+prepare stmt FROM "SELECT *
+FROM t1
+WHERE col_int_key IN (
+SELECT alias1.col_int_nokey AS field1
+FROM v1 AS alias1
+WHERE alias1.col_varchar_key < 'v'
+)";
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+execute stmt;
+col_int_nokey col_int_key col_varchar_key
+1 7 v
+7 0 s
+4 9 l
+2 2 i
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1;
+# End of test for bug#13773979.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2012-03-13 13:16:27 +0000
+++ b/sql/item.cc 2012-03-15 13:15:19 +0000
@@ -2870,8 +2870,6 @@ void Item_ident::fix_after_pullout(st_se
DBUG_ASSERT(type() == FIELD_ITEM);
return;
}
- DBUG_ASSERT(context->select_lex == NULL ||
- context->select_lex != depended_from);
if (context->select_lex == removed_select ||
context->select_lex == parent_select)
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-07 08:01:17 +0000
+++ b/sql/sql_optimizer.cc 2012-03-15 13:15:19 +0000
@@ -6402,6 +6402,12 @@ static bool convert_subquery_to_semijoin
/* Unlink the child select_lex: */
subq_lex->master_unit()->exclude_level();
/*
+ Update the resolver context - needed for Item_field objects that have been
+ replaced in the item tree for this execution, but are still needed for
+ subsequent executions.
+ */
+ subq_lex->context.select_lex= parent_lex;
+ /*
Walk through sj nest's WHERE and ON expressions and call
item->fix_table_changes() for all items.
*/
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3782 to 3783) Bug#13773979 | Roy Lyseng | 15 Mar |
