156 lines
5.3 KiB
Text
156 lines
5.3 KiB
Text
|
||
--echo #
|
||
--echo # Bug#35686098 Assertion `n < size()' failed in Element_type& Mem_root_array_YY
|
||
--echo #
|
||
|
||
# At report time, the query used crash, later due to "commit 88d716a -
|
||
# Bug#35813111" it no longer failed, but fell back on tmp table
|
||
# de-duplication. With this patch, it spills to disk - normal
|
||
# behavior since default buffer (set_operations_buffer_size) is too
|
||
# small for the data - but no longer falls back on tmp table
|
||
# de-duplication.
|
||
|
||
CREATE TABLE t1(
|
||
c1 TEXT,
|
||
c2 CHAR(255) DEFAULT NULL
|
||
);
|
||
LOAD DATA INFILE '../../std_data/t1_2cols.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
|
||
ANALYZE TABLE t1;
|
||
|
||
SET optimizer_trace="enabled=on";
|
||
|
||
let $show_trace=
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,"$.steps[*].join_execution"))
|
||
FROM information_schema.optimizer_trace;
|
||
|
||
let $pattern=$elide_trace_costs_and_rows;
|
||
# elide some sorting statistics:
|
||
let $pattern=$pattern /num_initial_chunks_spilled_to_disk\": [0-9.]+/num_initial_chunks_spilled_to_disk\": "elided"/;
|
||
let $pattern=$pattern /peak_memory_used\": [0-9.]+/peak_memory_used\": "elided"/;
|
||
let $pattern=$pattern /num_rows_estimate\": [0-9.]+/num_rows_estimate\": "elided"/;
|
||
# Allow both 4 and 8 chunk files: the latter is seen with ASAN
|
||
let $pattern=$pattern /chunk files\": [48]/chunk files\": "elided"/;
|
||
|
||
|
||
SELECT MAX( c1 ) OVER ( ORDER BY c2 ROWS CURRENT ROW ) FROM t1
|
||
INTERSECT DISTINCT
|
||
SELECT "can't" OR 447938560 FROM t1;
|
||
|
||
--replace_regex $pattern
|
||
--skip_if_hypergraph
|
||
eval $show_trace;
|
||
|
||
SET optimizer_trace=default;
|
||
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug#35970620 hash_set_operations optimizer off assertion error
|
||
--echo #
|
||
|
||
# Prepare with hashing ON
|
||
|
||
PREPARE p0 FROM '(SELECT 3 AS three) EXCEPT (SELECT 1)';
|
||
SET SESSION optimizer_switch = 'hash_set_operations=off';
|
||
SET SESSION optimizer_trace = 'enabled=on';
|
||
EXECUTE p0;
|
||
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=on';
|
||
EXECUTE p0;
|
||
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=off';
|
||
EXECUTE p0;
|
||
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=on';
|
||
EXECUTE p0;
|
||
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
# Prepare with hashing OFF
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=off';
|
||
PREPARE p0 FROM '(SELECT 3 AS three) EXCEPT (SELECT 1)';
|
||
SET SESSION optimizer_switch = 'hash_set_operations=on';
|
||
EXECUTE p0;
|
||
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=off';
|
||
|
||
EXECUTE p0;
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=on';
|
||
|
||
EXECUTE p0;
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
SET SESSION optimizer_switch = 'hash_set_operations=off';
|
||
|
||
EXECUTE p0;
|
||
SELECT JSON_PRETTY(JSON_EXTRACT(trace,
|
||
'$.steps[*].join_execution.steps[*]."materialize for except"')) AS j
|
||
FROM information_schema.OPTIMIZER_TRACE;
|
||
|
||
DROP PREPARE p0;
|
||
|
||
SET SESSION optimizer_trace = 'enabled=default';
|
||
SET SESSION optimizer_switch = 'hash_set_operations=default';
|
||
|
||
--echo #
|
||
--echo # Bug#36307622 Wrong result from query with WHERE integer IN (SELECT 2 EXCEPT SELECT 4)
|
||
--echo #
|
||
CREATE TABLE c (
|
||
pk int NOT NULL AUTO_INCREMENT,
|
||
col_datetime_key datetime DEFAULT NULL,
|
||
col_varchar_key varchar(1) DEFAULT NULL,
|
||
PRIMARY KEY (pk),
|
||
KEY idx_c_col_datetime_key (col_datetime_key),
|
||
KEY idx_c_col_varchar_key (col_varchar_key)
|
||
);
|
||
|
||
INSERT INTO c VALUES (1,'2022-10-30 08:18:58','o');
|
||
INSERT INTO c VALUES (2,'1998-01-19 17:27:57','䋠');
|
||
INSERT INTO c VALUES (3,'2015-09-01 16:34:18','X');
|
||
INSERT INTO c VALUES (4,'2020-08-29 15:09:33','m');
|
||
INSERT INTO c VALUES (5,'2018-07-01 22:36:45','d');
|
||
INSERT INTO c VALUES (6,'2028-02-07 02:02:10','q');
|
||
INSERT INTO c VALUES (7,'2016-02-04 17:29:46','8');
|
||
INSERT INTO c VALUES (8,'2037-07-02 21:02:05','0');
|
||
INSERT INTO c VALUES (9,'1970-08-03 04:25:41','旘');
|
||
INSERT INTO c VALUES (10,'1973-07-18 13:38:35','v');
|
||
INSERT INTO c VALUES (11,'1990-08-03 14:18:01','υ');
|
||
INSERT INTO c VALUES (12,'2036-07-05 20:41:55','dž');
|
||
INSERT INTO c VALUES (13,'2035-02-11 10:59:22','䩈');
|
||
INSERT INTO c VALUES (14,'1992-10-24 00:44:20','L');
|
||
INSERT INTO c VALUES (15,'1995-05-04 07:35:45','W');
|
||
INSERT INTO c VALUES (16,'2027-01-15 17:09:03','η');
|
||
INSERT INTO c VALUES (17,'1998-02-15 07:48:55','V');
|
||
INSERT INTO c VALUES (18,'1998-02-16 17:42:54','᥋');
|
||
INSERT INTO c VALUES (19,'1991-03-04 15:36:40','D');
|
||
INSERT INTO c VALUES (20,'1973-06-24 15:12:44','O');
|
||
|
||
SELECT pk FROM c WHERE pk IN (SELECT 2 EXCEPT SELECT 4);
|
||
SELECT pk FROM c WHERE pk IN (SELECT 2 EXCEPT SELECT 4) ORDER BY pk;
|
||
|
||
DROP TABLE c;
|