mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-28 06:04:59 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/gcol/r/gcol_select_myisam.result
Yuchen Pei 8cdee25952
MDEV-36132 Substitute vcol expressions with indexed vcol fields in ORDER BY and GROUP BY 
Also expand vcol field index coverings to include indexes covering all
the fields in the expression. The reasoning goes as follows: let f(c1,
c2, ..., cn) be a function on applied to columns c1, c2, ..., cn, if
f(...) is covered by an index, so should vc whose expression is
f(...).

For example, if t.vf = t.c1 + t.c2, and t has three indexes (vf), (c1,
c2), (c1).

Before this change, vf's index covering is a singleton {(vf)}. Let's call
that the "conventional" index covering.

After this change vf's index covering is now {(vf), (c1, c2)}, since
(c1, c2) covers both c1 and c2. Let's call (c1, c2) in this case the
"extra" covering.

With the coverings updated, when an index in the "extra" covering is
chosen for keyread, the vcol also needs to be calculated. In this case
we mark vcol in the table read_set, and ensure it is computed.

With these changes, we see various improvements, including from using
full table scan + filesort to full index scan + filesort when ORDER BY
an indexed vcol (here vc = c + 1 is a vcol and both c and vc are
indexes):

 explain select c + 1 from t order by vc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	10000	Using filesort
+1	SIMPLE	t	index	NULL	c	5	NULL	10000	Using index; Using filesort

The substitutions are followed updates to all_fields which include a
copy of the ORDER BY/GROUP BY item pointers, as well as corresponding
updates to ref_pointer_array so that the all_fields and
ref_pointer_array remain in sync.

Another, related change is the recomputation of table index covering
on substitutions. It not only reflects the correct table index
covering after the substitutions, but also improve executions where
the vcol index can be chosen, such as this example (here vc = c + 1
and vc is the only index in the table), from full table scan +
filesort to full index scan:

select vc from t order by c + 1;

We do it in SELECT as well as in single table DELETE/UPDATE.
2025-07-22 10:44:12 +10:00

1611 lines
47 KiB
Text
Raw Permalink Blame History

DROP VIEW IF EXISTS v1,v2;
DROP TABLE IF EXISTS t1,t2,t3;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
DROP TRIGGER IF EXISTS trg1;
DROP TRIGGER IF EXISTS trg2;
set sql_warnings = 0;
SET @@session.default_storage_engine = 'MyISAM';
SET optimizer_switch='derived_merge=off';
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
create table t1 (a int,
b int generated always as (-a) virtual,
c int generated always as (-a) stored,
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 generated always as (-a) virtual,
c int generated always as (-a) stored unique);
insert into t3 (a) values (2),(1),(3),(5),(4),(7);
analyze table t1,t2,t3;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
test.t3 analyze status Engine-independent statistics collected
test.t3 analyze status OK
# 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 Using index
# 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
1 -1 -1
1 -1 -1
2 -2 -2
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
1 -1 -1
1 -1 -1
2 -2 -2
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 1
# select_type=UNION, type=system
# select_type=UNION RESULT, type=<union1,2>
select * from t1 union select * from t2;
a b c
1 -1 -1
2 -2 -2
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
select * from (select a,b,c from t1) as t11;
a b c
1 -1 -1
1 -1 -1
2 -2 -2
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
###
### 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-gcol>) 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 gcol>) 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 gcol>) 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 5 Using index for group-by
###
### filesort & range-based utils
###
# SELECT * FROM tbl_name WHERE <gcol expr>
select * from t3 where c >= -2;
a b c
1 -1 -1
2 -2 -2
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-gcol 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 where; Using index
# SELECT * FROM tbl_name WHERE <non-indexed gcol expr>
select * from t3 where b between -2 and -1;
a b c
1 -1 -1
2 -2 -2
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 index NULL PRIMARY 4 NULL 6 Using where; Using index
# SELECT * FROM tbl_name WHERE <indexed gcol expr>
select * from t3 where c between -2 and -1;
a b c
1 -1 -1
2 -2 -2
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
# bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
CREATE TABLE t4 (
`pk` int(11) NOT NULL ,
`col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED,
`col_int_key` int(11) DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_datetime_key` (`col_datetime_key`)
);
INSERT INTO t4 VALUES
(1,default,4,'2008-12-05','1900-01-01 00:00:00');
SELECT
SQL_BIG_RESULT
GRANDPARENT1 . `col_int_nokey` AS g1
FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 .
`col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` )
GROUP BY GRANDPARENT1 . `pk`;
g1
5
DROP TABLE t4;
# SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol>
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 where; Using index; Using filesort
# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol>
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 index NULL PRIMARY 4 NULL 6 Using where; Using index
# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol>
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 index c PRIMARY 4 NULL 6 Using where; Using index
# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol>
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 index NULL PRIMARY 4 NULL 6 Using where; Using index; Using filesort
# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol>
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 gcol expr> ORDER BY <indexed gcol>
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 index NULL PRIMARY 4 NULL 6 Using where; Using index; Using filesort
# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol>
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 gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
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 gcol>) FROM tbl_name GROUP BY <indexed gcol>
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 gcol>) FROM tbl_name GROUP BY <indexed gcol>
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 gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
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
drop table t1;
#
# Bug#20241655: WL411:FAILING ASSERTION ASSERTION
#
CREATE TABLE BB (
col_time_key time NOT NULL,
col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
col_datetime_key datetime NOT NULL);
INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25');
Warnings:
Note 1265 Data truncated for column 'col_time_nokey' at row 1
CREATE TABLE CC (
col_time_key time NOT NULL,
col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
col_datetime_key datetime NOT NULL
);
INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00');
Warnings:
Note 1265 Data truncated for column 'col_time_nokey' at row 1
SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey);
g1
1
DROP TABLE BB, CC;
#
# Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL
# JUMP WHILE SELECTING FROM VIEW
#
CREATE TABLE A (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
PRIMARY KEY (pk)
);
CREATE TABLE C (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
col_varchar_nokey VARCHAR(1),
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key, col_int_key)
);
INSERT INTO C (
col_int_nokey,
col_varchar_nokey
) VALUES (4, 'v'),(62, 'v'),(7, 'c'),(1, NULL),(0, 'x'),(7, 'i'),(7, 'e'),(1, 'p'),(7, 's'),(1, 'j'),(5, 'z'),(2, 'c'),(0, 'a'),(1, 'q'),(8, 'y'),(1, NULL),(1, 'r'),(9, 'v'),(1, NULL),(5, 'r');
CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1.
col_varchar_key AS field1 , alias1.pk AS field2, alias2.
col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON
alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1
.pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key
> 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3
LIMIT 100 OFFSET 6;
Warnings:
Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
SELECT * FROM V1;
field1 field2 field3
qq 14 NULL
rr 17 NULL
ss 9 NULL
xx 5 NULL
DROP VIEW V1;
DROP TABLE A,C;
#
# Bug#20406510: WL411:VALGRIND WARNINGS WITH
# COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN
#
CREATE TABLE A (
pk INTEGER AUTO_INCREMENT,
col_time_key TIME NOT NULL,
col_datetime_key DATETIME NOT NULL,
PRIMARY KEY (pk),
KEY (col_time_key),
KEY (col_datetime_key)
);
CREATE TABLE C (
pk INTEGER AUTO_INCREMENT,
col_int_key INTEGER NOT NULL,
col_varchar_key VARCHAR(1) NOT NULL,
col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_key, col_varchar_key)),
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key, col_int_key)
);
INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd');
SELECT MIN( alias2 . col_int_key ) AS field1,
COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2
FROM ( A AS alias1 , C AS alias2 )
ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC;
field1 field2
NULL 0
DROP TABLE A,C;
#
# Bug#20566325: WL8149: INNODB: FAILING ASSERTION:
# COL_NR < TABLE->N_DEF
#
CREATE TABLE A (
pk INTEGER AUTO_INCREMENT,
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk)
);
INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k');
CREATE TABLE CC (
pk INTEGER AUTO_INCREMENT,
col_datetime_nokey DATETIME /*! NULL */,
col_time_nokey TIME /*! NULL */,
col_time_key TIME GENERATED ALWAYS AS
(ADDTIME(col_datetime_nokey, col_time_nokey)),
col_varchar_nokey VARCHAR(1) /*! NULL */,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk));
INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES
('13:06:13.033877','1900-01-01 00:00:00', 'p'),
(NULL, '2007-05-25 11:58:54.015689', 'g');
SELECT
table1.col_time_key AS field1,
'z' AS field2
FROM
(CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON
(table3.col_varchar_key = table2.col_varchar_nokey)) ON
(table3.col_varchar_key = table2.col_varchar_nokey))
WHERE
table2.pk != 6
AND table1.col_varchar_key IN ('l', 's' , 'b' )
AND table3.col_varchar_key != table1.col_varchar_key
ORDER BY table1.col_varchar_key , field1 , field2;
field1 field2
DROP TABLE A,CC;
#
# Bug#20573302: WL8149: SEGV IN HA_INNOBASE::
# BUILD_TEMPLATE AT INNOBASE/HANDLER/HA_INNODB.CC:665
#
CREATE TABLE c (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER NOT NULL,
col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
col_date_nokey DATE NOT NULL,
col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
col_datetime_nokey DATETIME NOT NULL,
col_time_nokey TIME NOT NULL,
col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key),
KEY (col_date_key),
KEY (col_time_key),
KEY (col_datetime_key),
KEY (col_int_key, col_varchar_key),
KEY (col_int_key, col_varchar_key, col_date_key,
col_time_key, col_datetime_key));
INSERT /*! IGNORE */ INTO c (
col_int_nokey,
col_date_nokey,
col_time_nokey,
col_datetime_nokey,
col_varchar_nokey
) VALUES
(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'),
(6, '2007-06-18', NULL, '2002-08-20 22:48:00.035785', 'd'),
(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),
(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'),
(8, NULL, '07:05:51.006712', '1900-01-01 00:00:00', 'm'),
(4, '2006-03-09', '19:22:21.057406', '2008-05-16 08:09:06.002924', 'b'),
(4, '2001-06-05', '03:53:16.001370', '2001-01-20 12:47:23.022022', 'x'),
(7, '2006-05-28', '09:16:38.034570', '2008-07-02 00:00:00', 'g'),
(4, '2001-04-19', '15:37:26.028315', '1900-01-01 00:00:00', 'p'),
(1, '1900-01-01', '00:00:00', '2002-12-08 11:34:58.001571', 'q'),
(9, '2004-08-20', '05:03:03.047452', '1900-01-01 00:00:00', 'w'),
(4, '2004-10-10', '02:59:24.063764', '1900-01-01 00:00:00', 'd'),
(8, '2000-04-02', '00:01:58.064243', '2002-08-25 20:35:06.064634', 'e'),
(4, '2006-11-02', '00:00:00', '2001-10-22 11:13:24.048128', 'b'),
(8, '2009-01-28', '02:20:16.024931', '2003-03-12 02:00:34.029335', 'y');
Warnings:
Note 1265 Data truncated for column 'col_time_key' at row 1
Note 1265 Data truncated for column 'col_time_key' at row 2
Note 1265 Data truncated for column 'col_time_key' at row 3
Note 1265 Data truncated for column 'col_time_key' at row 4
Note 1265 Data truncated for column 'col_time_key' at row 5
Note 1265 Data truncated for column 'col_time_key' at row 6
Warning 1048 Column 'col_time_nokey' cannot be null
Note 1265 Data truncated for column 'col_time_key' at row 7
Note 1265 Data truncated for column 'col_time_key' at row 8
Note 1265 Data truncated for column 'col_time_key' at row 9
Warning 1048 Column 'col_date_nokey' cannot be null
Warning 1292 Incorrect datetime value: '0000-00-00'
Note 1265 Data truncated for column 'col_time_key' at row 10
Note 1265 Data truncated for column 'col_time_key' at row 11
Note 1265 Data truncated for column 'col_time_key' at row 12
Note 1265 Data truncated for column 'col_time_key' at row 13
Note 1265 Data truncated for column 'col_time_key' at row 14
Note 1265 Data truncated for column 'col_time_key' at row 15
Note 1265 Data truncated for column 'col_time_key' at row 16
Note 1265 Data truncated for column 'col_time_key' at row 17
Note 1265 Data truncated for column 'col_time_key' at row 18
Note 1265 Data truncated for column 'col_time_key' at row 19
Note 1265 Data truncated for column 'col_time_key' at row 20
CREATE TABLE cc (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER NOT NULL,
col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,
col_date_nokey DATE NOT NULL,
col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
col_datetime_nokey DATETIME NOT NULL,
col_time_nokey TIME NOT NULL,
col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)),
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key),
KEY (col_date_key),
KEY (col_time_key),
KEY (col_datetime_key),
KEY (col_int_key, col_varchar_key),
KEY (col_int_key, col_varchar_key, col_date_key,
col_time_key, col_datetime_key));
INSERT /*! IGNORE */ INTO cc (
col_int_nokey,
col_date_nokey,
col_time_nokey,
col_datetime_nokey,
col_varchar_nokey
) VALUES
(0, '2003-02-06', '22:02:09.059926', '2003-08-07 14:43:09.011144', 'x'),
(0, '2005-04-16', '19:33:15.014160', '2005-12-11 00:00:00', 'n'),
(1, '2005-07-23', '22:03:16.058787', '2005-12-26 20:48:07.043628', 'w'),
(7, '2001-11-15', '06:31:23.027263', '2008-06-12 06:41:21.012493', 's'),
(0, '2006-03-24', '02:19:08.013275', '2007-10-11 18:46:28.030000', 'a'),
(4, '2008-07-10', NULL, '2006-04-04 22:22:40.057947', 'd'),
(1, '2009-12-07', NULL, '2002-08-10 20:52:58.035137', 'w'),
(1, '2008-05-01', '10:28:01.038587', '2008-10-03 11:17:23.005299', 'j'),
(1, '2008-06-22', '00:00:00', '2009-01-06 20:11:01.034339', 'm'),
(4, '2001-11-11', '15:02:50.048785', '2009-09-19 00:00:00', 'k'),
(7, '2000-12-21', '05:29:13.012729', '2007-09-02 12:14:27.029187', 't'),
(4, '2007-09-03', '23:45:33.048507', '2003-09-26 00:00:00', 'k'),
(2, '2003-02-18', '19:10:53.057455', '2001-11-18 18:10:16.063189', 'e'),
(0, '2008-12-01', '01:45:27.037313', '2005-02-15 04:08:17.015554', 'i'),
(1, '2008-10-18', '03:56:03.060218', '2009-06-13 23:04:40.013006', 't'),
(91, '2004-08-28', '12:43:17.023797', '1900-01-01 00:00:00', 'm'),
(6, '2006-10-05', '13:33:46.053634', '2005-03-20 02:48:24.045653', 'z'),
(3, '2003-05-16', NULL, '2002-03-16 11:47:27.045297', 'c'),
(6, '2008-10-10', NULL, '2000-05-22 00:00:00', 'i'),
(8, '2002-01-19', '05:18:40.006865', '2009-02-12 00:00:00', 'v');
Warnings:
Note 1265 Data truncated for column 'col_time_key' at row 1
Note 1265 Data truncated for column 'col_time_key' at row 2
Note 1265 Data truncated for column 'col_time_key' at row 3
Note 1265 Data truncated for column 'col_time_key' at row 4
Note 1265 Data truncated for column 'col_time_key' at row 5
Warning 1048 Column 'col_time_nokey' cannot be null
Note 1265 Data truncated for column 'col_time_key' at row 6
Warning 1048 Column 'col_time_nokey' cannot be null
Note 1265 Data truncated for column 'col_time_key' at row 7
Note 1265 Data truncated for column 'col_time_key' at row 8
Note 1265 Data truncated for column 'col_time_key' at row 9
Note 1265 Data truncated for column 'col_time_key' at row 10
Note 1265 Data truncated for column 'col_time_key' at row 11
Note 1265 Data truncated for column 'col_time_key' at row 12
Note 1265 Data truncated for column 'col_time_key' at row 13
Note 1265 Data truncated for column 'col_time_key' at row 14
Note 1265 Data truncated for column 'col_time_key' at row 15
Note 1265 Data truncated for column 'col_time_key' at row 16
Note 1265 Data truncated for column 'col_time_key' at row 17
Warning 1048 Column 'col_time_nokey' cannot be null
Note 1265 Data truncated for column 'col_time_key' at row 18
Warning 1048 Column 'col_time_nokey' cannot be null
Note 1265 Data truncated for column 'col_time_key' at row 19
Note 1265 Data truncated for column 'col_time_key' at row 20
EXPLAIN
SELECT subquery2_t2.col_int_key AS subquery2_field1
FROM (c AS subquery2_t1 RIGHT JOIN
(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
ORDER BY subquery2_field1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE subquery2_t2 index NULL col_int_key_2 10 NULL # #
1 SIMPLE subquery2_t3 ALL NULL NULL NULL NULL # #
1 SIMPLE subquery2_t1 index NULL PRIMARY 4 NULL # #
SELECT subquery2_t2.col_int_key AS subquery2_field1
FROM (c AS subquery2_t1 RIGHT JOIN
(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
ORDER BY subquery2_field1;
subquery2_field1
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
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
4
6
6
6
6
7
7
8
8
8
8
9
9
24
SELECT subquery2_t2.col_int_key AS subquery2_field1
FROM (c AS subquery2_t1 RIGHT JOIN
(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON
(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON
(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key))
ORDER BY subquery2_field1;
subquery2_field1
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
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
4
6
6
6
6
7
7
8
8
8
8
9
9
24
DROP TABLE c,cc;
#
# Bug#2081065: WL8149:RESULT DIFF SEEN FOR SIMPLE
# RANGE QUERIES WITH ORDER BY
#
CREATE TABLE cc (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER NOT NULL,
col_int_key INTEGER GENERATED ALWAYS AS
(col_int_nokey + col_int_nokey) VIRTUAL,
PRIMARY KEY (pk),
KEY (col_int_key)
);
INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5);
EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cc range col_int_key col_int_key 5 NULL # #
SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3;
pk
5
6
3
EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cc range col_int_key col_int_key 5 NULL # #
SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1;
pk
3
5
6
DROP TABLE cc;
#
# Bug#20849676 :WL8149:ASSERTION `!TABLE || (!TABLE->READ_SET
# || BITMAP_IS_SET(TABLE->READ_SET
#
CREATE TABLE c (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER NOT NULL,
col_int_key INTEGER GENERATED ALWAYS AS
(col_int_nokey + col_int_nokey) VIRTUAL,
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key),
KEY (col_int_key, col_varchar_key)
) ;
INSERT INTO c (col_int_nokey, col_varchar_nokey) VALUES
(1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'),(6, 'd'),
(2, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'),(1, 'q'),
(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y');
CREATE TABLE a (
pk INTEGER AUTO_INCREMENT,
col_datetime_nokey DATETIME NOT NULL,
col_time_nokey TIME NOT NULL,
col_datetime_key DATETIME GENERATED ALWAYS AS
(ADDTIME(col_datetime_nokey, col_time_nokey)),
col_time_key TIME GENERATED ALWAYS AS
(ADDTIME(col_datetime_nokey, col_time_nokey)),
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
PRIMARY KEY (pk),
KEY (col_varchar_key),
KEY (col_time_key),
KEY (col_datetime_key),
KEY (col_varchar_key, col_time_key, col_datetime_key)
);
INSERT INTO a (
col_time_nokey,
col_datetime_nokey,
col_varchar_nokey) VALUES
('04:08:02.046897', '2001-11-04 19:07:55.051133', 'k');
Warnings:
Note 1265 Data truncated for column 'col_time_key' at row 1
ANALYZE TABLE a, c;
Table Op Msg_type Msg_text
test.a analyze status Engine-independent statistics collected
test.a analyze status OK
test.c analyze status Engine-independent statistics collected
test.c analyze status OK
EXPLAIN
SELECT
table1.pk AS field1 ,
table1.col_datetime_key AS field2
FROM
( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT
SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2
STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key =
SUBQUERY1_t2.col_varchar_key ) )
ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key
OR SUBQUERY1_t1.col_int_key <> 1 ) )
WHERE SUBQUERY1_t2.pk >= 9 ) AS table3
ON (table3.col_int_key = table2.col_int_key ) ) )
ON (table3.col_int_nokey = table2.pk ) )
GROUP BY field1, field2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 system NULL NULL NULL NULL #
1 PRIMARY table2 ALL PRIMARY,col_int_key,col_int_key_2 NULL NULL NULL # Using where
1 PRIMARY <derived2> ref key0 key0 9 test.table2.pk,test.table2.col_int_key #
2 DERIVED SUBQUERY1_t2 ALL PRIMARY,col_int_key,col_varchar_key,col_int_key_2 NULL NULL NULL # Using where
2 DERIVED SUBQUERY1_t3 ref PRIMARY,col_varchar_key col_varchar_key 5 test.SUBQUERY1_t2.col_varchar_key #
2 DERIVED SUBQUERY1_t1 ALL col_int_key,col_int_key_2 NULL NULL NULL # Using where; Using join buffer (flat, BNL join)
SELECT
table1.pk AS field1 ,
table1.col_datetime_key AS field2
FROM
( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT
SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2
STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key =
SUBQUERY1_t2.col_varchar_key ) )
ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key
OR SUBQUERY1_t1.col_int_key <> 1 ) )
WHERE SUBQUERY1_t2.pk >= 9 ) AS table3
ON (table3.col_int_key = table2.col_int_key ) ) )
ON (table3.col_int_nokey = table2.pk ) )
GROUP BY field1, field2;
field1 field2
1 2001-11-04 23:15:57
DROP TABLE IF EXISTS c,a;
CREATE TABLE c (
col_int_nokey INTEGER NOT NULL,
col_int_key INTEGER GENERATED ALWAYS AS
(col_int_nokey + col_int_nokey) VIRTUAL,
col_varchar_nokey VARCHAR(1) NOT NULL,
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
KEY (col_int_key),
KEY (col_int_key, col_varchar_key)
) ;
INSERT INTO c (
col_int_nokey,
col_varchar_nokey
) VALUES (1, 'c'),(8, 'm'),(9, 'd'),(24, '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');
CREATE TABLE cc (
col_int_nokey INTEGER,
col_int_key INTEGER GENERATED ALWAYS AS
(col_int_nokey + col_int_nokey) VIRTUAL,
col_varchar_nokey VARCHAR(1),
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
(CONCAT(col_varchar_nokey, col_varchar_nokey)),
KEY (col_int_key),
KEY (col_varchar_key),
KEY (col_int_key, col_varchar_key),
KEY (col_int_key, col_int_nokey),
KEY (col_varchar_key, col_varchar_nokey)
);
INSERT INTO cc (
col_int_nokey,
col_varchar_nokey
) VALUES (8, 'p'),(9, 'g'),(9, 'i'),(4, 'p'),(7, 'h'),(1, 'e'),(8, 'e'),(6, 'u'),
(6, 'j'),(6, 'e'),(1, 'z'),(227, 'w'),(NULL, 't'),(9, 'i'),(1, 'i'),(8, 'i'),
(5, 'b'),(8,'m'),(7, 'j'),(2, 'v');
ANALYZE TABLE c, cc;
Table Op Msg_type Msg_text
test.c analyze status Engine-independent statistics collected
test.c analyze status OK
test.cc analyze status Engine-independent statistics collected
test.cc analyze status OK
EXPLAIN SELECT
alias2 . col_varchar_key AS field1
FROM ( cc AS alias1 , cc AS alias2 )
WHERE
( alias2 . col_int_key , alias1 . col_int_nokey )
NOT IN
(
SELECT
DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 ,
SQ1_alias1 . col_int_key AS SQ1_field2
FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 )
GROUP BY SQ1_field1 , SQ1_field2
)
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 index NULL col_int_key_3 10 NULL # Using index; Using temporary; Using filesort
1 PRIMARY alias2 index NULL col_int_key_2 10 NULL # Using where; Using index; Using join buffer (flat, BNL join)
2 MATERIALIZED SQ1_alias1 index col_int_key,col_int_key_2,col_int_key_3 col_int_key 5 NULL # Using index
2 MATERIALIZED SQ1_alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
SELECT
alias2 . col_varchar_key AS field1
FROM ( cc AS alias1 , cc AS alias2 )
WHERE
( alias2 . col_int_key , alias1 . col_int_nokey )
NOT IN
(
SELECT
DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 ,
SQ1_alias1 . col_int_key AS SQ1_field2
FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 )
GROUP BY SQ1_field1 , SQ1_field2
)
GROUP BY field1;
field1
bb
ee
gg
hh
ii
jj
mm
pp
uu
ww
DROP TABLE IF EXISTS c,cc;
SET @save_old_sql_mode= @@sql_mode;
SET sql_mode="";
CREATE TABLE d (
col_int int(11) DEFAULT NULL,
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) GENERATED ALWAYS AS (col_int+col_int) VIRTUAL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT(SUBSTRING(col_varchar_10_utf8, -1), 5)) VIRTUAL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY cover_key1 (col_int_key, col_varchar_10_utf8_key)
);
INSERT INTO d (col_int, col_varchar_10_utf8) VALUES ('qhlhtrovam',1),('how',2),('htrovamzqr',3),('rovamzqrdc',4),('well',5),('g',6),('rdcenchyhu',7),('want',8);
SELECT table1.pk AS field1 FROM d AS table1 LEFT JOIN d AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_10_utf8_key WHERE table1.col_int_key IS NULL GROUP BY table1.pk ;
field1
DROP TABLE d;
#
# Bug#21153237: WL8149: QUERIES USING FILESORT
# ON VIRTUAL GC HAVING INDEX GIVES WRONG RESULTS
#
CREATE TABLE j (
col_int int(11),
pk int(11) NOT NULL,
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL,
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8 GENERATED ALWAYS AS
(col_varchar_10_utf8) VIRTUAL,
PRIMARY KEY (pk),
KEY cover_key1 (col_int, col_varchar_255_utf8_key));
INSERT INTO j(col_int, pk, col_varchar_10_utf8) VALUES(9, 1, '951910400'),
(-1934295040, 2, '1235025920'),(-584581120, 3, '-1176633344'),(3, 4, '1074462720');
EXPLAIN SELECT col_varchar_255_utf8_key FROM j ORDER BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE j index NULL cover_key1 773 NULL # Using index; Using filesort
SELECT col_varchar_255_utf8_key FROM j ORDER BY col_varchar_255_utf8_key;
col_varchar_255_utf8_key
-117663334
1074462720
1235025920
951910400
DROP TABLE j;
set sql_mode= @save_old_sql_mode;
CREATE TABLE cc (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED,
col_date_nokey date NOT NULL,
col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED,
col_datetime_nokey datetime NOT NULL,
col_time_nokey time NOT NULL,
col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED,
col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED,
col_varchar_nokey varchar(1) NOT NULL,
col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED,
PRIMARY KEY (pk),
KEY gc_idx1 (col_int_key),
KEY gc_idx2 (col_varchar_key),
KEY gc_idx3 (col_date_key),
KEY gc_idx4 (col_time_key),
KEY gc_idx5 (col_datetime_key),
KEY gc_idx6 (col_varchar_key,col_int_key),
KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key),
KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key,
col_datetime_key)
);
INSERT INTO cc (
col_int_nokey,
col_date_nokey,
col_time_nokey,
col_datetime_nokey,
col_varchar_nokey
) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't');
SET @save_old_sql_mode= @@sql_mode;
SET sql_mode="";
SELECT DISTINCT alias1.col_varchar_key AS field1
FROM ( cc AS alias1 STRAIGHT_JOIN
(( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON
(alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON
(( alias3 .pk >= alias2.col_int_nokey ) AND
(alias3 .pk >= alias2.col_int_nokey ) ))
WHERE alias1.col_varchar_key <= 'v'
GROUP BY field1 HAVING field1 = 91
ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC,
alias1.col_time_key ASC, field1;
field1
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'c'
Warning 1292 Truncated incorrect DECIMAL value: 't'
Warning 1292 Truncated incorrect DECIMAL value: 'm'
Warning 1292 Truncated incorrect DECIMAL value: 'd'
Warning 1292 Truncated incorrect DECIMAL value: 'd'
DROP TABLE cc;
SET sql_mode=@save_old_sql_mode;
#
# Bug#20797941: WL8149:ASSERTION !TABLE ||
# (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET
#
CREATE TABLE t(a int, b int as(a+1));
INSERT INTO t(a) values(1),(2);
SELECT * FROM t ORDER BY b;
a b
1 2
2 3
DROP TABLE t;
#
# Testing a few index-based accesses on the virtual column
#
CREATE TABLE t1 (
id int(11) NOT NULL,
b int(11) GENERATED ALWAYS AS (id+1) VIRTUAL,
UNIQUE KEY (b) );
INSERT INTO t1 (id) VALUES(NULL);
ERROR 23000: Column 'id' cannot be null
INSERT INTO t1 (id) VALUES(2),(3);
EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE b=3;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const b b 5 const 1
EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where
1 SIMPLE t1 eq_ref b b 5 test.t2.b 1
EXPLAIN SELECT b FROM t1 FORCE INDEX(b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL b 5 NULL 2 Using index
INSERT INTO t1 (id) VALUES(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range b b 5 NULL 3 Using where; Using index
EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY t1 index_subquery b b 5 func 3 Using index; Full scan on NULL key
DROP TABLE t1;
DROP TABLE t2, t3;
#
# Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(2147483647);
ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL;
ERROR 22003: Out of range value for column 'b' at row 1
ALTER TABLE t1 DROP COLUMN b;
ERROR 42000: Can't DROP COLUMN `b`; check that it exists
ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL;
ERROR 22003: Out of range value for column 'c' at row 1
ALTER TABLE t1 DROP COLUMN c;
ERROR 42000: Can't DROP COLUMN `c`; check that it exists
ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL;
ERROR 22003: Out of range value for column 'd' at row 1
ALTER TABLE t1 DROP COLUMN d;
ERROR 42000: Can't DROP COLUMN `d`; check that it exists
ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL;
ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL;
ERROR 22003: Out of range value for column 'c' at row 1
ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL;
ERROR 22003: Out of range value for column 'c' at row 1
SELECT * FROM t1;
a c
2147483647 2147483647
DROP TABLE t1;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(2147483647);
ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL;
ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY;
ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
Got one of the listed errors
ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE;
Got one of the listed errors
ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED;
ERROR 22003: Out of range value for column 'f' at row 1
ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE;
ERROR 22003: Out of range value for column 'g' at row 1
DROP TABLE t1;
#
# Bug#21980430 GCOLS: CRASHING
#
CREATE TABLE t (
a INT,
b BLOB,
c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL,
UNIQUE KEY i0008 (a)
);
INSERT INTO t(a,b) VALUES(1,'cccc');
EXPLAIN SELECT 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
WHERE b.b>c.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'cccc'
Warning 1292 Truncated incorrect DECIMAL value: 'cccc'
SELECT 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
WHERE b.b>c.a;
c
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'cccc'
Warning 1292 Truncated incorrect DECIMAL value: 'cccc'
DROP TABLE t;
set @optimizer_switch_save = @@optimizer_switch;
set optimizer_switch='mrr_cost_based=off';
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=32;
CREATE TABLE t0 (
i1 INTEGER NOT NULL
);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (
pk INTEGER NOT NULL,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL,
v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL,
PRIMARY KEY (pk),
INDEX idx(i1)
);
INSERT INTO t1 (pk, i1, i2)
SELECT a0.i1 + a1.i1*10 + a2.i1*100,
a0.i1 + a1.i1*10,
a0.i1 + a1.i1*10
FROM t0 AS a0, t0 AS a1, t0 AS a2;
EXPLAIN SELECT * FROM t1
WHERE i1 > 41 AND i1 <= 43;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx idx 4 NULL 20 Using index condition
SELECT * FROM t1
WHERE i1 > 41 AND i1 <= 43;
pk i1 i2 v1 v2
142 42 42 43 1
143 43 43 44 1
242 42 42 43 1
243 43 43 44 1
342 42 42 43 1
343 43 43 44 1
42 42 42 43 1
43 43 43 44 1
442 42 42 43 1
443 43 43 44 1
542 42 42 43 1
543 43 43 44 1
642 42 42 43 1
643 43 43 44 1
742 42 42 43 1
743 43 43 44 1
842 42 42 43 1
843 43 43 44 1
942 42 42 43 1
943 43 43 44 1
ALTER TABLE t1 ADD INDEX idx2(v1);
EXPLAIN SELECT * FROM t1
WHERE v1 > 41 AND v1 <= 43;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx2 idx2 5 NULL # Using index condition
SELECT * FROM t1
WHERE v1 > 41 AND v1 <= 43;
pk i1 i2 v1 v2
141 41 41 42 1
142 42 42 43 1
241 41 41 42 1
242 42 42 43 1
341 41 41 42 1
342 42 42 43 1
41 41 41 42 1
42 42 42 43 1
441 41 41 42 1
442 42 42 43 1
541 41 41 42 1
542 42 42 43 1
641 41 41 42 1
642 42 42 43 1
741 41 41 42 1
742 42 42 43 1
841 41 41 42 1
842 42 42 43 1
941 41 41 42 1
942 42 42 43 1
DROP TABLE t0, t1;
set optimizer_switch= @optimizer_switch_save;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
#
# Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN
# SQL_JOIN_BUFFER.CC
#
#
# Test 1: Dynamic range scan with one covering index
#
CREATE TABLE t1 (
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL
);
INSERT INTO t1
VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g');
CREATE TABLE t2 (
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL
);
INSERT INTO t2
VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e');
CREATE TABLE t3 (
pk INTEGER NOT NULL,
i1 INTEGER,
i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL,
PRIMARY KEY (pk)
);
# Add a covering index. The reason for this index being covering is that
# secondary indexes in InnoDB include the primary key.
ALTER TABLE t3 ADD INDEX v_idx (i2_key);
INSERT INTO t3 (pk, i1)
VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5),
(6, 39), (7, 6), (8, 8), (9, 3);
CREATE TABLE t4 (
i1 INTEGER NOT NULL,
c1 VARCHAR(1) NOT NULL
);
INSERT INTO t4
VALUES (1, 'j'), (2, 'c'), (0, 'a');
ANALYZE TABLE t1, t2, t3, t4;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
test.t3 analyze status Engine-independent statistics collected
test.t3 analyze status OK
test.t4 analyze status Engine-independent statistics collected
test.t4 analyze status OK
EXPLAIN SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
c1 i1
c 2
c 5
c 7
c 7
c 9
g 2
g 5
g 7
g 7
g 9
i 2
i 5
i 7
i 7
i 9
t 2
t 2
t 5
t 5
t 7
t 7
t 7
t 7
t 9
t 9
#
# Test 2: Two alternative covering indexes for the range scan
#
ALTER TABLE t3 ADD INDEX v_idx2 (i2_key, i1);
EXPLAIN SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
c1 i1
c 2
c 5
c 7
c 7
c 9
g 2
g 5
g 7
g 7
g 9
i 2
i 5
i 7
i 7
i 9
t 2
t 2
t 5
t 5
t 7
t 7
t 7
t 7
t 9
t 9
#
# Test 3: One covering index including the base column for the virtual
# column
#
# Drop the index with only the virtual column
ALTER TABLE t3 DROP INDEX v_idx;
EXPLAIN SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
c1 i1
c 2
c 5
c 7
c 7
c 9
g 2
g 5
g 7
g 7
g 9
i 2
i 5
i 7
i 7
i 9
t 2
t 2
t 5
t 5
t 7
t 7
t 7
t 7
t 9
t 9
#
# Test 4: One non-covering index
#
# Drop the index on two columns, add index on just one virtual column
ALTER TABLE t3 DROP INDEX v_idx2;
ALTER TABLE t3 ADD INDEX v_idx (i2_key);
# Add more data to the table so that it will run the dynamic range scan
# as both table scan and range scan (the purpose of this is to make the
# table scan more expensive).
INSERT INTO t3 (pk, i1)
VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1),
(19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1),
(28,1), (29,1);
# Change the query to read an extra column (t3.i1) making the index
# non-covering.
EXPLAIN SELECT t1.c1, t2.i1, t3.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
SELECT t1.c1, t2.i1, t3.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o'
)
)
AND t1.i1 <= t3.i2_key;
c1 i1 i1
c 2 48
c 5 48
c 7 48
c 7 48
c 9 48
g 2 48
g 5 48
g 7 48
g 7 48
g 9 48
i 2 48
i 5 48
i 7 48
i 7 48
i 9 48
t 2 1
t 2 48
t 5 1
t 5 48
t 7 1
t 7 1
t 7 48
t 7 48
t 9 1
t 9 48
#
# Test 5: Test where the added primary key to secondary indexes is
# used after it has been included in the join buffer
#
EXPLAIN SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
)
)
AND t1.i1 <= t3.i2_key;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join)
SELECT t1.c1, t2.i1
FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
WHERE ( t3.pk IN
(
SELECT t4.i1
FROM t4
WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
)
)
AND t1.i1 <= t3.i2_key;
c1 i1
c 2
c 5
c 7
c 7
c 9
g 2
g 5
g 7
g 7
g 9
i 2
i 5
i 7
i 7
i 9
t 2
t 2
t 5
t 5
t 7
t 7
t 7
t 7
t 9
t 9
DROP TABLE t1, t2, t3, t4;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
SET optimizer_switch='derived_merge=default';
DROP VIEW IF EXISTS v1,v2;
DROP TABLE IF EXISTS t1,t2,t3;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
DROP TRIGGER IF EXISTS trg1;
DROP TRIGGER IF EXISTS trg2;
set sql_warnings = 0;
Reference in a new issue View git blame Copy permalink