diff options
Diffstat (limited to 'mysql-test/suite/vcol')
| -rw-r--r-- | mysql-test/suite/vcol/r/vcol_select_myisam.result | 30 | ||||
| -rw-r--r-- | mysql-test/suite/vcol/r/vcol_view_innodb.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/vcol/r/vcol_view_myisam.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/vcol/t/vcol_select_myisam.test | 20 |
4 files changed, 54 insertions, 4 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index d4868340ab5..934d047f6bf 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -265,3 +265,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 1906 The value specified for computed column 'b' in table 't2' ignored +Warning 1906 The value specified for computed column 'b' in table 't2' ignored +Warning 1906 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 (flat, BNL join) +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 (flat, BNL join) +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/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result index fe666e0e400..c9a4897e230 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 <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> 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 <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> 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 78c20f79f5e..e3fb2ec61ca 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 <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> 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 <derived2> ALL NULL NULL NULL NULL 4 +1 PRIMARY <derived2> 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/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; |