From 40c42468bcbe215a971f93edb9af27545156b8a7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 29 Dec 2011 15:09:20 -0800 Subject: Fixed LP bug #806057. A table expression with a natural join or a USING clause is transformed into an equivalent expression with equi-join ON conditions. If a reference to a virtual column happened to occur only in these generated equi-join conditions then it was not erroneously marked in the TABLE::vcol_set bitmap. This could lead to wrong results for queries containing natural join expressions or USING clauses. --- mysql-test/suite/vcol/r/vcol_select_myisam.result | 30 +++++++++++++++++++++++ mysql-test/suite/vcol/t/vcol_select_myisam.test | 20 +++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test/suite/vcol') diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 45e4defd315..04ae7e0f7cd 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -262,3 +262,33 @@ NULL explain select sum(c) from t1 group by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +# +# Bug #806057: join with USING over a virtual column +# +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); +CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); +INSERT INTO t2 VALUES (187,187), (9,9), (187,187); +Warnings: +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN t2 USING (b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`) +SELECT * FROM t1 JOIN t2 USING (b); +b a +EXPLAIN EXTENDED +SELECT * FROM t1 NATURAL JOIN t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`) +SELECT * FROM t1 NATURAL JOIN t2; +b a +DROP TABLE t1,t2; diff --git a/mysql-test/suite/vcol/t/vcol_select_myisam.test b/mysql-test/suite/vcol/t/vcol_select_myisam.test index 855e02ac113..c14faba576d 100644 --- a/mysql-test/suite/vcol/t/vcol_select_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_select_myisam.test @@ -48,3 +48,23 @@ eval SET @@session.storage_engine = 'MyISAM'; #------------------------------------------------------------------------------# # Cleanup --source suite/vcol/inc/vcol_cleanup.inc + +--echo # +--echo # Bug #806057: join with USING over a virtual column +--echo # + +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); + +CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); +INSERT INTO t2 VALUES (187,187), (9,9), (187,187); + +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN t2 USING (b); +SELECT * FROM t1 JOIN t2 USING (b); + +EXPLAIN EXTENDED +SELECT * FROM t1 NATURAL JOIN t2; +SELECT * FROM t1 NATURAL JOIN t2; + +DROP TABLE t1,t2; -- cgit v1.2.1 From 746dbbe583f8528b34065b0669136842128e27b2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 18 Jan 2012 03:31:20 -0800 Subject: Fixed LP bug #917990. If the expression for a derived table of a query contained a LIMIT clause the estimate of the number of rows in this derived table returned by the EXPLAIN command could be badly off since the optimizer ignored the limit number from the LIMIT clause when getting the estimate. The call of the method SELECT_LEX_UNIT->set_limit added in the code of mysql_derived_optimize() will be needed also in maria-5.5 where parameters in the LIMIT clause are supported. --- mysql-test/suite/vcol/r/vcol_view_innodb.result | 4 ++-- mysql-test/suite/vcol/r/vcol_view_myisam.result | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/suite/vcol') diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result index 94c311fb8b9..d117b030f94 100644 --- a/mysql-test/suite/vcol/r/vcol_view_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result @@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; create view v1 as select c+1 from t1 order by 1 desc limit 2; @@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; drop table t1; diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result index b96e003e1cc..fe48be4c794 100644 --- a/mysql-test/suite/vcol/r/vcol_view_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result @@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; create view v1 as select c+1 from t1 order by 1 desc limit 2; @@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; drop table t1; -- cgit v1.2.1