3845 Roy Lyseng 2012-02-07
Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
Added a test that used to crash the server.
mysql-test/include/subquery.inc
New test case for bug#11829691.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
New test case results for bug#11829691.
modified:
mysql-test/include/subquery.inc
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
3844 Norvald H. Ryeng 2012-02-06
Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
WITHOUT UNION ALL
Problem: Some outer join queries using views as inner tables do not
evaluate conditions correctly.
Inner tables of outer joins are tagged by setting the outer_join flag
in the TABLE_LIST structure for the table. However, if the inner table
is a view, the tables of the view are not tagged unless they are inner
tables of a view defined as an outer join query. This means that
tables referenced by views should some times be treated as inner
tables of an outer join even if they are not tagged as such.
In JOIN::optimize(), calls to list_contains_unique_index(), are used
to check if GROUP BY or DISTINCT can be skipped. The function checks
that enough columns to make it a unique key are mentioned and that
none of the columns can return NULL. The check for NULL fails since it
only checks the outer_join flag, which may not be set because of view
usage. The result is the erroneous conclusion that GROUP BY or
DISTINCT can be skipped, which in the end causes the query to return
too many or too few rows.
Fix: In list_contains_unique_index(), check if the table in question
is in the inner part of an outer join (directly or nested) and
therefore may return NULL values.
This patch also adds checks for nested outer joins in
Item_field::is_outer_field() and internal_remove_eq_conds().
@ mysql-test/r/group_by.result
Fix erroneous result.
@ mysql-test/r/join_outer.result
Add test case for bug #13464334.
@ mysql-test/r/join_outer_bka.result
Add test case for bug #13464334.
@ mysql-test/r/join_outer_bka_nixbnl.result
Add test case for bug #13464334.
@ mysql-test/t/join_outer.test
Add test case for bug #13464334.
@ sql/item.h
Include nested outer joins in check for outer join.
@ sql/sql_optimizer.cc
Include nested outer joins in checks for outer join.
modified:
mysql-test/r/group_by.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_bka.result
mysql-test/r/join_outer_bka_nixbnl.result
mysql-test/t/join_outer.test
sql/item.h
sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2011-09-29 12:47:32 +0000
+++ b/mysql-test/include/subquery.inc 2012-02-07 07:07:38 +0000
@@ -5643,3 +5643,20 @@ WHERE (col_varchar_nokey, 'x') IN
--echo
DROP TABLE it, ot;
+
+--echo #
+--echo # Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+--echo #
+
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DEALLOCATE PREPARE stmt;
+
+DROP TABLE t1, t2;
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/subquery_all.result 2012-02-07 07:07:38 +0000
@@ -6822,4 +6822,17 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key2` = 'x') and (`test`.`it`.`col_varchar_key` = 'x') and isnull(`test`.`it`.`col_int_key`))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-02-07 07:07:38 +0000
@@ -6823,5 +6823,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key2` = 'x') and (`test`.`it`.`col_varchar_key` = 'x') and isnull(`test`.`it`.`col_int_key`))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2011-12-01 11:30:35 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-02-07 07:07:38 +0000
@@ -6823,5 +6823,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from (`test`.`it`) where ((`test`.`it`.`col_varchar_key2` = 'x') and (`test`.`it`.`col_varchar_key` = 'x') and isnull(`test`.`it`.`col_int_key`))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2012-02-07 07:07:38 +0000
@@ -6822,4 +6822,17 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result 2012-02-07 07:07:38 +0000
@@ -6823,5 +6823,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-02-07 07:07:38 +0000
@@ -6823,5 +6823,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_none.result 2012-02-07 07:07:38 +0000
@@ -6821,4 +6821,17 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_none_bka.result 2012-02-07 07:07:38 +0000
@@ -6822,5 +6822,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result 2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result 2012-02-07 07:07:38 +0000
@@ -6822,5 +6822,18 @@ Warnings:
Note 1003 /* select#1 */ select '1' AS `col_int_nokey` from dual where <in_optimizer>(('x','x'),<exists>(<index_lookup>(<cache>('x') in it on idx_cvk_cvk2_cik where (isnull(`test`.`it`.`col_int_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key`) and (<cache>('x') = `test`.`it`.`col_varchar_key2`)))))
DROP TABLE it, ot;
+#
+# Bug #11829691: Pure virtual method called in Item_bool_func2::fix...()
+#
+CREATE TABLE t1(a INTEGER);
+CREATE TABLE t2(b INTEGER);
+PREPARE stmt FROM "
+SELECT SUM(b) FROM t2 GROUP BY b HAVING b IN (SELECT b FROM t1)";
+EXECUTE stmt;
+SUM(b)
+EXECUTE stmt;
+SUM(b)
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1, t2;
set optimizer_switch=default;
set optimizer_switch=default;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3844 to 3845) Bug#11829691 | Roy Lyseng | 7 Feb |
