234 lines
6.3 KiB
Text
234 lines
6.3 KiB
Text
--echo #
|
|
--echo # Bug#13464493 "19 X HIGHER THAN EXPECTED EXEC. TIME FOR
|
|
--echo # MYSQL_BENCH/COUNT_DISTINCT_KEY_PREFIX"
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(1) NOT NULL,
|
|
i1 INTEGER NOT NULL,
|
|
i2 INTEGER NOT NULL,
|
|
PRIMARY KEY (c1,i1),
|
|
UNIQUE KEY k1 (c1,i2)
|
|
) ENGINE=InnoDB, CHARSET utf8mb4;
|
|
|
|
INSERT INTO t1 VALUES ('A',0,999),('A',6,993),('A',12,987),
|
|
('A',18,981),('A',24,975),('A',30,969),('A',36,963),('A',42,957),
|
|
('A',48,951),('A',54,945),('A',60,939),('A',66,933),('A',72,927),
|
|
('A',78,921),('A',84,915),('A',90,909),('A',96,903),('A',102,897),
|
|
('A',108,891),('A',114,885),('A',120,879),('A',126,873),('A',132,867),
|
|
('A',138,861),('A',144,855),('A',150,849),('A',156,843),('A',162,837),
|
|
('A',168,831),('A',174,825),('A',180,819),('A',186,813),('A',192,807),
|
|
('A',198,801),('A',204,795),('B',210,789),('B',216,783),('B',222,777),
|
|
('B',228,771),('B',234,765),('B',240,759),('B',246,753),('B',252,747),
|
|
('B',258,741),('B',264,735),('B',270,729),('B',276,723),('B',282,717),
|
|
('B',288,711),('B',294,705),('B',300,699),('B',306,693),('B',312,687),
|
|
('B',318,681),('B',324,675),('B',330,669),('B',336,663),('B',342,657),
|
|
('B',348,651),('B',354,645),('B',360,639),('B',366,633),('B',372,627),
|
|
('B',378,621),('C',384,615),('C',390,609),('C',396,603),('C',402,597),
|
|
('C',408,591),('C',414,585),('C',420,579),('C',426,573),('C',432,567),
|
|
('C',438,561),('C',444,555),('C',450,549),('C',456,543),('C',462,537),
|
|
('C',468,531),('C',474,525),('C',480,519),('C',486,513),('C',492,507),
|
|
('C',498,501),('C',504,495),('C',510,489),('C',516,483),('C',522,477),
|
|
('C',528,471),('C',534,465),('C',540,459),('C',546,453),('C',552,447),
|
|
('C',558,441),('C',564,435),('C',570,429),('C',576,423),('C',582,417),
|
|
('C',588,411),('C',594,405);
|
|
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
|
|
# The following query should use loose index scan on the secondary
|
|
# index k1. The Extra field should show "Using index for group-by".
|
|
|
|
let query=
|
|
SELECT COUNT(DISTINCT c1) FROM t1;
|
|
|
|
eval EXPLAIN $query;
|
|
eval $query;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#11757108 "CHANGE IN EXECUTION PLAN FOR COUNT_DISTINCT_GROUP_ON_KEY
|
|
--echo # CAUSES PEFORMANCE REGRESSIONS"
|
|
--echo #
|
|
|
|
CREATE TABLE t0 (
|
|
i1 INTEGER NOT NULL
|
|
);
|
|
|
|
INSERT INTO t0 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
|
|
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
|
|
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);
|
|
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(1) NOT NULL,
|
|
i1 INTEGER NOT NULL,
|
|
i2 INTEGER NOT NULL,
|
|
PRIMARY KEY (c1,i1),
|
|
UNIQUE KEY k1 (c1,i2)
|
|
) ENGINE=InnoDB, CHARSET utf8mb4;
|
|
|
|
INSERT INTO t1 SELECT 'A',i1,i1 FROM t0;
|
|
INSERT INTO t1 SELECT 'B',i1,i1 FROM t0;
|
|
INSERT INTO t1 SELECT 'C',i1,i1 FROM t0;
|
|
INSERT INTO t1 SELECT 'D',i1,i1 FROM t0;
|
|
INSERT INTO t1 SELECT 'E',i1,i1 FROM t0;
|
|
INSERT INTO t1 SELECT 'F',i1,i1 FROM t0;
|
|
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
|
|
# The following query should use loose index scan on the secondary
|
|
# index k1. The Extra field should show "Using index for group-by (scanning)".
|
|
|
|
let query=
|
|
select c1,count(distinct i2) from t1 group by c1;
|
|
|
|
eval EXPLAIN $query;
|
|
FLUSH STATUS;
|
|
eval $query;
|
|
|
|
# The query should be run using the scanning option for loose index scan.
|
|
# It should only do a few read key calls and many read next calls:
|
|
--skip_if_hypergraph # Does not support this access method yet.
|
|
SHOW STATUS LIKE 'Handler_read_key';
|
|
--skip_if_hypergraph # Does not support this access method yet.
|
|
SHOW STATUS LIKE 'Handler_read_next';
|
|
|
|
DROP TABLE t0, t1;
|
|
|
|
--echo #
|
|
--echo # Bug#17222452 SELECT COUNT(DISTINCT A,B) INCORRECTLY COUNTS
|
|
--echo # ROWS CONTAINING NULL
|
|
--echo #
|
|
CREATE TABLE t (a INT, b INT,KEY k(a,b));
|
|
INSERT INTO t VALUES (1,2),
|
|
(NULL,3),(3,3),(1,NULL),
|
|
(NULL,2), (NULL,NULL);
|
|
--replace_column 10 # 11 #
|
|
EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t;
|
|
SELECT COUNT(DISTINCT a,b) FROM t;
|
|
--replace_column 10 #
|
|
EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t IGNORE INDEX (k);
|
|
SELECT COUNT(DISTINCT a,b) FROM t IGNORE INDEX (k);
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#16982071: ASSERT `TAB->KEYS.IS_SET(KEYNO)' FAILED IN
|
|
--echo # SETUP_SEMIJOIN_DUPS_ELIMINATION
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
pk INTEGER,
|
|
col_int INTEGER,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
|
|
CREATE TABLE t2 (
|
|
pk INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key)
|
|
) CHARSET utf8mb4;
|
|
|
|
INSERT INTO t2 VALUES (1, 'g');
|
|
|
|
CREATE TABLE t3 (
|
|
pk INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_varchar_key)
|
|
) CHARSET utf8mb4;
|
|
|
|
INSERT INTO t3 VALUES (1, 'v'),(2, NULL);
|
|
|
|
let $query=
|
|
SELECT t1.col_int
|
|
FROM t1, t3
|
|
WHERE t3.col_varchar_key IN (
|
|
SELECT t2.col_varchar_key FROM t2 WHERE t2.pk > t1.col_int
|
|
);
|
|
|
|
--replace_column 10 #
|
|
--eval EXPLAIN $query
|
|
--eval $query
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo #
|
|
--echo # Bug #30659810: INCORRECT QUERY RESULTS
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
a INTEGER NOT NULL
|
|
);
|
|
INSERT INTO t1 VALUES (2),(2);
|
|
|
|
CREATE TABLE t2 (
|
|
b INTEGER
|
|
);
|
|
INSERT INTO t2 VALUES (2),(11),(11);
|
|
|
|
CREATE TABLE t3 (
|
|
b INTEGER,
|
|
pk INTEGER,
|
|
KEY b_key (b)
|
|
);
|
|
INSERT INTO t3 VALUES (2,5);
|
|
|
|
CREATE TABLE t4 (
|
|
pk INTEGER NOT NULL
|
|
);
|
|
INSERT INTO t4 VALUES (5),(7);
|
|
|
|
ANALYZE TABLE t1,t2,t3,t4;
|
|
|
|
let $query =
|
|
SELECT *
|
|
FROM t1
|
|
JOIN t2 ON t1.a = t2.b
|
|
WHERE t2.b IN (
|
|
SELECT t3.b
|
|
FROM t3 JOIN t4 ON t3.pk = t4.pk
|
|
);
|
|
|
|
# Verify that the left (outer) side of the nested loop semijoin iterator
|
|
# only contains a single table; anything else would give the wrong results
|
|
# (deduplicating away rows that should not be deduplicated).
|
|
--skip_if_hypergraph # Different query plan.
|
|
eval EXPLAIN FORMAT=TREE $query;
|
|
eval $query;
|
|
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
|
--echo #
|
|
--echo # Bug #32597719: WL#14325: ASSERTION `IS_NULL_ROW == FALSE' FAILED|SQL/SORTING_ITERATOR.H
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
col_int INTEGER,
|
|
col_varchar_key VARCHAR(1)
|
|
);
|
|
|
|
CREATE TABLE t2 (
|
|
pk INTEGER,
|
|
j JSON
|
|
);
|
|
|
|
INSERT INTO t2 VALUES (1,'true'),(2,'true'),(3,'true'),(4,'true'),(5,'true');
|
|
|
|
ANALYZE TABLE t1, t2;
|
|
|
|
# Sets up a plan where the sort is _not_ on the first table,
|
|
# but produces no rows, so that the final AggregateIterator
|
|
# needs to set the NULL row flag on a sort that has not
|
|
# been started yet.
|
|
SELECT SUM(t1.col_int)
|
|
FROM t1, t2
|
|
WHERE t2.j IN (
|
|
SELECT t3.j FROM t2 JOIN t2 AS t3 ON t2.pk <> t3.pk
|
|
) AND t1.col_varchar_key='';
|
|
|
|
DROP TABLE t1, t2;
|