diff options
Diffstat (limited to 'mysql-test/suite/vcol/r/vcol_select_myisam.result')
| -rw-r--r-- | mysql-test/suite/vcol/r/vcol_select_myisam.result | 515 |
1 files changed, 515 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result new file mode 100644 index 00000000..05f86347 --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -0,0 +1,515 @@ +SET @@session.default_storage_engine = 'MyISAM'; +create table t1 (a int, +b int as (-a), +c int as (-a) persistent, +index (c)); +insert into t1 (a) values (2), (1), (1), (3), (NULL); +create table t2 like t1; +insert into t2 (a) values (1); +create table t3 (a int primary key, +b int as (-a), +c int as (-a) persistent unique); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); +# select_type=SIMPLE, type=system +select * from t2; +a b c +1 -1 -1 +explain select * from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 +select * from t2 where c=-1; +a b c +1 -1 -1 +explain select * from t2 where c=-1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system c NULL NULL NULL 1 +# select_type=SIMPLE, type=ALL +select * from t1 where b=-1; +a b c +1 -1 -1 +1 -1 -1 +explain select * from t1 where b=-1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +# select_type=SIMPLE, type=const +select * from t3 where a=1; +a b c +1 -1 -1 +explain select * from t3 where a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 +# select_type=SIMPLE, type=range +select * from t3 where c>=-1; +a b c +1 -1 -1 +explain select * from t3 where c>=-1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 1 Using index condition +# select_type=SIMPLE, type=ref +select * from t1,t3 where t1.c=t3.c and t3.c=-1; +a b c a b c +1 -1 -1 1 -1 -1 +1 -1 -1 1 -1 -1 +explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const c c 5 const 1 +1 SIMPLE t1 ref c c 5 const 2 +# select_type=PRIMARY, type=index,ALL +select * from t1 where b in (select c from t3); +a b c +2 -2 -2 +1 -1 -1 +1 -1 -1 +3 -3 -3 +explain select * from t1 where b in (select c from t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index +# select_type=PRIMARY, type=range,ref +select * from t1 where c in (select c from t3 where c between -2 and -1); +a b c +2 -2 -2 +1 -1 -1 +1 -1 -1 +explain select * from t1 where c in (select c from t3 where c between -2 and -1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index +1 PRIMARY t1 ref c c 5 test.t3.c 2 +# select_type=UNION, type=system +# select_type=UNION RESULT, type=<union1,2> +select * from t1 union select * from t2; +a b c +2 -2 -2 +1 -1 -1 +3 -3 -3 +NULL NULL NULL +explain select * from t1 union select * from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +2 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +# select_type=DERIVED, type=system +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +select * from (select a,b,c from t1) as t11; +a b c +2 -2 -2 +1 -1 -1 +1 -1 -1 +3 -3 -3 +NULL NULL NULL +explain select * from (select a,b,c from t1) as t11; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +set optimizer_switch=@tmp_optimizer_switch; +### +### Using aggregate functions with/without DISTINCT +### +# SELECT COUNT(*) FROM tbl_name +select count(*) from t1; +count(*) +5 +explain select count(*) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +# SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name +select count(distinct a) from t1; +count(distinct a) +3 +explain select count(distinct a) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +# SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name +select count(distinct b) from t1; +count(distinct b) +3 +explain select count(distinct b) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +# SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name +select count(distinct c) from t1; +count(distinct c) +3 +explain select count(distinct c) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by +### +### filesort & range-based utils +### +# SELECT * FROM tbl_name WHERE <vcol expr> +select * from t3 where c >= -2; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where c >= -2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 2 Using index condition +# SELECT * FROM tbl_name WHERE <non-vcol expr> +select * from t3 where a between 1 and 2; +a b c +1 -1 -1 +2 -2 -2 +explain select * from t3 where a between 1 and 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition +# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> +select * from t3 where b between -2 and -1; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where b between -2 and -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where +# SELECT * FROM tbl_name WHERE <indexed vcol expr> +select * from t3 where c between -2 and -1; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where c between -2 and -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 2 Using index condition +# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> +select * from t3 where a between 1 and 2 order by c; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where a between 1 and 2 order by c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort +# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> +select * from t3 where b between -2 and -1 order by a; +a b c +1 -1 -1 +2 -2 -2 +explain select * from t3 where b between -2 and -1 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort +# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol> +select * from t3 where c between -2 and -1 order by a; +a b c +1 -1 -1 +2 -2 -2 +explain select * from t3 where c between -2 and -1 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort +# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> +select * from t3 where b between -2 and -1 order by b; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where b between -2 and -1 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort +# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> +select * from t3 where c between -2 and -1 order by b; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where c between -2 and -1 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort +# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> +select * from t3 where b between -2 and -1 order by c; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where b between -2 and -1 order by c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort +# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> +select * from t3 where c between -2 and -1 order by c; +a b c +2 -2 -2 +1 -1 -1 +explain select * from t3 where c between -2 and -1 order by c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c c 5 NULL 2 Using index condition +# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> +select sum(b) from t1 group by b; +sum(b) +NULL +-3 +-2 +-2 +explain select sum(b) 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 +# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> +select sum(c) from t1 group by c; +sum(c) +NULL +-3 +-2 +-2 +explain select sum(c) from t1 group by c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c 5 NULL 5 Using index +# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> +select sum(b) from t1 group by c; +sum(b) +NULL +-3 +-2 +-2 +explain select sum(b) from t1 group by c; +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 +# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> +select sum(c) from t1 group by b; +sum(c) +NULL +-3 +-2 +-2 +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 IGNORE INTO t2 VALUES (187,187), (9,9), (187,187); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't2' has been 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; +create table t1 ( +pk integer auto_increment, +bi integer not null, +vi integer generated always as (bi) persistent, +bc varchar(1) not null, +vc varchar(2) generated always as (concat(bc, bc)) persistent, +primary key (pk), +key (vi, vc)); +insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v'); +create table t2 ( +pk integer auto_increment, +bi integer not null, +vi integer generated always as (bi) persistent, +bc varchar(257) not null, +vc varchar(2) generated always as (concat(bc, bc)) persistent, +primary key (pk), +key (vi, vc)); +insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y'); +explain # should be using join buffer +select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL vi 10 NULL 19 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) +select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); +vi +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +6 +6 +6 +6 +7 +7 +8 +8 +8 +8 +9 +9 +drop table t2,t1; +create table t1 ( +pk integer auto_increment, +bi integer not null, +vi integer generated always as (bi) virtual, +bc varchar(1) not null, +vc varchar(2) generated always as (concat(bc, bc)), +primary key (pk), +key (vi, vc)); +insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v'); +create table t2 ( +pk integer auto_increment, +bi integer not null, +vi integer generated always as (bi) virtual, +bc varchar(257) not null, +vc varchar(2) generated always as (concat(bc, bc)), +primary key (pk), +key (vi, vc)); +insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y'); +explain # should be using join buffer +select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL vi 10 NULL 19 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) +select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); +vi +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +6 +6 +6 +6 +7 +7 +8 +8 +8 +8 +9 +9 +drop table t2,t1; |