429 lines
15 KiB
Text
429 lines
15 KiB
Text
#
|
|
# test of safe selects
|
|
#
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
--enable_warnings
|
|
|
|
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, MAX_JOIN_SIZE=8;
|
|
create table t1 (a int auto_increment primary key, b char(20)) charset utf8mb4;
|
|
insert into t1 values(1,"test");
|
|
SELECT SQL_BUFFER_RESULT * from t1;
|
|
update t1 set b="a" where a=1;
|
|
delete from t1 where a=1;
|
|
insert into t1 values(1,"test"),(2,"test2");
|
|
SELECT SQL_BUFFER_RESULT * from t1;
|
|
update t1 set b="a" where a=1;
|
|
select 1 from t1,t1 as t2,t1 as t3;
|
|
|
|
# The following should give errors:
|
|
--error 1175
|
|
update t1 set b="a";
|
|
--error 1175
|
|
update t1 set b="a" where b="test";
|
|
--error 1175
|
|
delete from t1;
|
|
--error 1175
|
|
delete from t1 where b="test";
|
|
--error 1175
|
|
delete from t1 where a+0=1;
|
|
# Reads t1 (two rows) five times to build the hash tables. Then reads
|
|
# one row from the outermost t1, to produce 32 rows, which hits the
|
|
# sql_select_limit of 4. Total number of rows read: 2 * 5 + 1 = 11.
|
|
--error ER_TOO_BIG_SELECT
|
|
select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5, t1 as t6;
|
|
--error ER_TOO_BIG_SELECT
|
|
select /*+ SET_VAR(max_join_size = 10) */
|
|
1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5, t1 as t6;
|
|
|
|
# The following should be ok:
|
|
--skip_if_hypergraph # Different plan hits max_join_size. Bug#33664699.
|
|
select /*+ SET_VAR(max_join_size = 11) */
|
|
1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5, t1 as t6;
|
|
update t1 set b="a" limit 1;
|
|
update t1 set b="a" where b="b" limit 2;
|
|
delete from t1 where b="test" limit 1;
|
|
delete from t1 where a+0=1 limit 2;
|
|
|
|
# Test SQL_BIG_SELECTS
|
|
|
|
alter table t1 add key b (b);
|
|
SET MAX_JOIN_SIZE=1;
|
|
SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
|
|
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT * from t1 order by a;
|
|
SET SQL_BIG_SELECTS=1;
|
|
SELECT * from t1 order by a;
|
|
SET MAX_JOIN_SIZE=1;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT * from t1;
|
|
SET MAX_JOIN_SIZE=DEFAULT;
|
|
SELECT * from t1 order by a;
|
|
|
|
#
|
|
# Test MAX_SEEKS_FOR_KEY
|
|
#
|
|
analyze table t1;
|
|
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
|
|
ANALYZE TABLE t1;
|
|
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
|
|
set MAX_SEEKS_FOR_KEY=1;
|
|
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
|
|
SET MAX_SEEKS_FOR_KEY=DEFAULT;
|
|
|
|
drop table t1;
|
|
|
|
# BUG#8726
|
|
create table t1 (a int);
|
|
insert into t1 values (1),(2),(3),(4),(5);
|
|
insert into t1 select * from t1;
|
|
insert into t1 select * from t1;
|
|
insert into t1 select * from t1;
|
|
analyze table t1;
|
|
|
|
# No error because SQL_SELECT_LIMIT=4.
|
|
set local max_join_size=4;
|
|
select * from (select * from t1) x;
|
|
|
|
set max_join_size=3;
|
|
--error ER_TOO_BIG_SELECT
|
|
select * from (select * from t1) x;
|
|
|
|
set local max_join_size=1;
|
|
--error ER_TOO_BIG_SELECT
|
|
select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x;
|
|
|
|
set local max_join_size=1;
|
|
# Does not access any base tables, so no error is expected.
|
|
--sorted_result
|
|
select * from (select 1 union select 2 union select 3) x;
|
|
drop table t1;
|
|
|
|
SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, MAX_JOIN_SIZE=DEFAULT;
|
|
|
|
# End of 4.1 tests
|
|
|
|
--echo #
|
|
--echo # Bug #28145710: SQL_SAFE_UPDATES ERROR IS INSUFFICIENTLY INFORMATIVE
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL,
|
|
UNIQUE KEY idx1 (c1), UNIQUE KEY idx2 (c2));
|
|
CREATE TABLE t2 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL,
|
|
UNIQUE KEY idx1 (c1));
|
|
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
|
|
INSERT INTO t2 VALUES (11, 'a'), (12, 'b'), (3, 'c'), (14, 'd');
|
|
ANALYZE TABLE t1, t2;
|
|
|
|
SET SESSION sql_safe_updates=1;
|
|
|
|
SET RANGE_OPTIMIZER_MAX_MEM_SIZE= 1;
|
|
#DELETE with range_optimizer_max_mem_size warning
|
|
let query1= DELETE FROM t1 WHERE c1 IN (1,22);
|
|
#UPDATE with range_optimizer_max_mem_size warning
|
|
let query2= UPDATE t1 SET c1=20 WHERE c1 IN (1,22);
|
|
#multi-table DELETE with conversion warning and target table uses table scan
|
|
let query3= DELETE t1 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
|
|
#multi-table UPDATE with conversion warning and target table uses table scan
|
|
let query4= UPDATE t1, t2 SET t1.c1=20 WHERE t1.c2 = t2.c1;
|
|
#multi-table DELETE with target table uses eq_ref (no error)
|
|
let query5= DELETE t2 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
|
|
#DELETE with conversion warning
|
|
let query6= DELETE FROM t1 WHERE c2 IN(1,2222);
|
|
#DELETE with conversion warning
|
|
let query7= UPDATE t1 SET c1=20 WHERE c2 IN(1,2222);
|
|
#DELETE with non-indexed column returns error
|
|
let query8= DELETE FROM t2 WHERE c2 IN('a','e');
|
|
#DELETE full table and test with binlog disabled
|
|
let query9= DELETE FROM t2;
|
|
|
|
|
|
|
|
eval EXPLAIN $query1;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query1;
|
|
|
|
eval EXPLAIN $query2;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query2;
|
|
SET RANGE_OPTIMIZER_MAX_MEM_SIZE= default;
|
|
|
|
eval EXPLAIN $query3;
|
|
--skip_if_hypergraph # Different wording in error message.
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query3;
|
|
|
|
eval EXPLAIN $query4;
|
|
--skip_if_hypergraph # Different wording in error message.
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query4;
|
|
|
|
eval EXPLAIN $query5;
|
|
--skip_if_hypergraph # Does not use index. Bug#33583337?
|
|
eval $query5;
|
|
|
|
eval EXPLAIN $query6;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query6;
|
|
|
|
eval EXPLAIN $query7;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query7;
|
|
|
|
eval EXPLAIN $query8;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query8;
|
|
|
|
--replace_column 12 #
|
|
eval EXPLAIN $query9;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query9;
|
|
|
|
SET sql_log_bin= 0;
|
|
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
|
|
eval $query9;
|
|
SET sql_log_bin= default;
|
|
|
|
DROP TABLE t1, t2;
|
|
SET SESSION sql_safe_updates=default;
|
|
|
|
--echo #
|
|
--echo # Bug#25118903: MAX_JOIN_SIZE DOES NOT MATCH ROW ESTIMATES
|
|
--echo #
|
|
|
|
# Create a table with 100 rows.
|
|
CREATE TABLE t1(id INT PRIMARY KEY, x INT);
|
|
INSERT INTO t1
|
|
WITH RECURSIVE qn AS (SELECT 1 AS n UNION ALL SELECT 1+n FROM qn WHERE n < 100)
|
|
SELECT n, n FROM qn;
|
|
|
|
# Create a table with 10 rows.
|
|
CREATE TABLE t2(id INT PRIMARY KEY, x INT);
|
|
INSERT INTO t2 SELECT * FROM t1 WHERE id <= 10;
|
|
|
|
ANALYZE TABLE t1, t2;
|
|
|
|
# Hash join reads each table once, so 100 + 10 row accesses.
|
|
SELECT /*+ SET_VAR(max_join_size = 110) */
|
|
COUNT(*) FROM t1, t2 WHERE t1.x = t2.x;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 109) */
|
|
COUNT(*) FROM t1, t2 WHERE t1.x = t2.x;
|
|
|
|
# Nested loop join reads the outer table once and the inner table once
|
|
# per row in the outer table. The inner table is an index lookup on
|
|
# t1.id here, accessing only one row per outer row, so expect 10 row
|
|
# accesses in t2 + 10 * 1 row accesses in t1.
|
|
SELECT /*+ SET_VAR(max_join_size = 20) */
|
|
COUNT(*) FROM t1, t2 WHERE t1.id = t2.x;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 19) */
|
|
COUNT(*) FROM t1, t2 WHERE t1.id = t2.x;
|
|
|
|
# UNION needs to read each side once, so there are 100 + 10 row accesses.
|
|
SELECT /*+ SET_VAR(max_join_size = 110) */ COUNT(x) FROM t1
|
|
UNION ALL SELECT COUNT(x) FROM t2;
|
|
SELECT /*+ SET_VAR(max_join_size = 110) */ COUNT(x) FROM t1
|
|
UNION SELECT COUNT(x) FROM t2;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 109) */ COUNT(x) FROM t1
|
|
UNION ALL SELECT COUNT(x) FROM t2;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 109) */ COUNT(x) FROM t1
|
|
UNION SELECT COUNT(x) FROM t2;
|
|
|
|
# The hash join reads 100 rows from t1 and 10 rows from t2.
|
|
# Additionally, 10 rows will be read from t3 for each row returned
|
|
# from the join. Since the estimated number of rows returned from the
|
|
# join is 100 (which is 10x higher than the actual number), the
|
|
# estimated number of row accesses is 100 + 10 + 100 * 10 = 1110.
|
|
# (Actually, it's just above 1110 because of inexactness in
|
|
# floating-point calculations, so the max_join_size must be 1111 to
|
|
# let the query through.)
|
|
SELECT /*+ SET_VAR(max_join_size = 1111) */ COUNT(*) FROM t1, t2
|
|
WHERE t1.x=t2.x AND (SELECT MAX(t1.id+t2.id+t3.id) FROM t2 AS t3);
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 1109) */ COUNT(*) FROM t1, t2
|
|
WHERE t1.x=t2.x AND (SELECT MAX(t1.id+t2.id+t3.id) FROM t2 AS t3);
|
|
|
|
# Variant of the previous query having the subquery in the join
|
|
# condition instead of the WHERE clause. Same estimated number of
|
|
# accesses (1110).
|
|
SET max_join_size = 1111;
|
|
--disable_query_log
|
|
if (`SELECT NOT FIND_IN_SET('hypergraph_optimizer=on', @@optimizer_switch)`) {
|
|
# The old optimizer estimates a "filter_effect" of 1 for the join
|
|
# condition, and therefore expects a lot more invocations of the
|
|
# subquery, getting a total of 10110 estimated row accesses.
|
|
SET max_join_size = 10111;
|
|
}
|
|
--enable_query_log
|
|
SELECT COUNT(*) FROM t1 LEFT JOIN t2
|
|
ON t1.x=t2.x AND (SELECT MAX(t1.id+t2.id+t3.id) FROM t2 AS t3);
|
|
SET max_join_size = @@max_join_size - 2;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT COUNT(*) FROM t1 LEFT JOIN t2
|
|
ON t1.x=t2.x AND (SELECT MAX(t1.id+t2.id+t3.id) FROM t2 AS t3);
|
|
SET max_join_size = DEFAULT;
|
|
|
|
# Test with an uncorrelated subquery which only needs to be executed
|
|
# once. The hash join reads 100 + 10 rows. The subquery is executed
|
|
# once and reads 10 rows from t2. Total: 100 + 10 + 10 = 120 rows.
|
|
SET max_join_size = 120;
|
|
--disable_query_log
|
|
if (`SELECT NOT FIND_IN_SET('hypergraph_optimizer=on', @@optimizer_switch)`) {
|
|
# The old optimizer executes the uncorrelated subquery during
|
|
# optimization, and therefore doesn't count the 10 rows read by it.
|
|
# Adjust for it.
|
|
SET max_join_size = @@max_join_size - 10;
|
|
}
|
|
--enable_query_log
|
|
SELECT COUNT(*) FROM t1, t2
|
|
WHERE t1.x=t2.x AND (SELECT DISTINCT t3.x>0 FROM t2 AS t3);
|
|
SET max_join_size = @@max_join_size - 1;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT COUNT(*) FROM t1, t2
|
|
WHERE t1.x=t2.x AND (SELECT DISTINCT t3.x>0 FROM t2 AS t3);
|
|
SET max_join_size = DEFAULT;
|
|
|
|
# Each derived table is materialized once, so there's one full scan of
|
|
# each base table, giving 100 + 10 = 110 rows.
|
|
SELECT /*+ SET_VAR(max_join_size=110) */ COUNT(*) FROM
|
|
(SELECT DISTINCT x FROM t1) AS dt1,
|
|
(SELECT DISTINCT x FROM t2) AS dt2 WHERE dt1.x=dt2.x;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size=109) */ COUNT(*) FROM
|
|
(SELECT DISTINCT x FROM t1) AS dt1,
|
|
(SELECT DISTINCT x FROM t2) AS dt2 WHERE dt1.x=dt2.x;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test that LIMIT is taken into account.
|
|
CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, x INT, y INT, KEY (x));
|
|
INSERT INTO t(x, y) VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
|
|
(6, 6), (7, 7), (8, 8), (9, 9), (10, 10);
|
|
INSERT INTO t(x, y) SELECT x, y FROM t;
|
|
INSERT INTO t(x, y) SELECT x, y FROM t;
|
|
INSERT INTO t(x, y) SELECT x, y FROM t;
|
|
ANALYZE TABLE t;
|
|
|
|
# These queries need to read 10 rows from the base table or index.
|
|
SET max_join_size = 9;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT 1 FROM t LIMIT 10;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT x FROM t ORDER BY x LIMIT 10;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT * FROM t ORDER BY id LIMIT 10;
|
|
|
|
SET max_join_size = 10;
|
|
SELECT 1 FROM t LIMIT 10;
|
|
SELECT x FROM t ORDER BY x LIMIT 10;
|
|
SELECT * FROM t ORDER BY id LIMIT 10;
|
|
|
|
# Testing a quirk for the old optimizer. Given this query:
|
|
#
|
|
# SELECT 1 FROM t WHERE y = 3 ORDER BY x LIMIT 3;
|
|
#
|
|
# The old optimizer chooses the following plan:
|
|
#
|
|
# -> Limit: 3 row(s) (cost=0.28 rows=0.3)
|
|
# -> Filter: (t.y = 3) (cost=0.28 rows=0.3)
|
|
# -> Index scan on t using x (cost=0.28 rows=3)
|
|
#
|
|
# In the row estimate for the index scan, it has ignored the effect of
|
|
# the filter on the limit. All the row estimates in this plan should
|
|
# have been ten times higher (given the heuristic which estimates 10%
|
|
# selectivity on the equality predicate).
|
|
#
|
|
# max_join_size adjusts for this by taking the row estimate from
|
|
# another source for index scans with the old optimizer, and expects
|
|
# to read 30 rows.
|
|
SET max_join_size = 29;
|
|
if (`SELECT FIND_IN_SET('hypergraph_optimizer=on', @@optimizer_switch)`) {
|
|
# The hypergraph optimizer chooses a different plan (table scan +
|
|
# sort) and estimates a higher number of rows to read.
|
|
--disable_query_log
|
|
SET max_join_size = 79;
|
|
--enable_query_log
|
|
}
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT 1 FROM t WHERE y = 3 ORDER BY x LIMIT 3;
|
|
SET max_join_size = @@max_join_size + 1;
|
|
SELECT 1 FROM t WHERE y = 3 ORDER BY x LIMIT 3;
|
|
|
|
# Ordering on a non-indexed column requires reading the entire table.
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT /*+ SET_VAR(max_join_size = 79) */ y FROM t ORDER BY y LIMIT 10;
|
|
SELECT /*+ SET_VAR(max_join_size = 80) */ y FROM t ORDER BY y LIMIT 10;
|
|
|
|
# Assume that aggregation needs to read the entire table (which might
|
|
# be too pessimistic for streaming aggregation with explicit
|
|
# grouping).
|
|
SET max_join_size = 79;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT SUM(y) FROM t LIMIT 10;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT SUM(y) FROM t GROUP BY x LIMIT 10;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT SUM(x) FROM t GROUP BY y LIMIT 10;
|
|
|
|
SET max_join_size = 80;
|
|
SELECT SUM(y) FROM t LIMIT 10;
|
|
--sorted_result
|
|
SELECT SUM(y) FROM t GROUP BY x LIMIT 10;
|
|
--sorted_result
|
|
SELECT SUM(x) FROM t GROUP BY y LIMIT 10;
|
|
|
|
# This should read 3 + 4 rows from the base tables.
|
|
SET max_join_size = 6;
|
|
--error ER_TOO_BIG_SELECT
|
|
(SELECT 1 FROM t LIMIT 3) UNION DISTINCT (SELECT 1 FROM t LIMIT 4);
|
|
--error ER_TOO_BIG_SELECT
|
|
(SELECT 1 FROM t LIMIT 3) UNION DISTINCT (SELECT 1 FROM t LIMIT 4) LIMIT 1;
|
|
--error ER_TOO_BIG_SELECT
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4);
|
|
--error ER_TOO_BIG_SELECT
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4) LIMIT 10;
|
|
|
|
SET max_join_size = 7;
|
|
(SELECT 1 FROM t LIMIT 3) UNION DISTINCT (SELECT 1 FROM t LIMIT 4);
|
|
(SELECT 1 FROM t LIMIT 3) UNION DISTINCT (SELECT 1 FROM t LIMIT 4) LIMIT 1;
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4);
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4) LIMIT 10;
|
|
|
|
# With UNION ALL, a LIMIT on the top-level may further restrict the
|
|
# number of rows needed.
|
|
SET max_join_size = 4;
|
|
--error ER_TOO_BIG_SELECT
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4) LIMIT 5;
|
|
SET max_join_size = 5;
|
|
(SELECT 1 FROM t LIMIT 3) UNION ALL (SELECT 1 FROM t LIMIT 4) LIMIT 5;
|
|
|
|
# Nested loop join. With a join selectivity of 10%, we expect to have
|
|
# to read two rows from the outer table before we reach the limit.
|
|
# (Because the join returns 80 * 80 * 0.1 = 640 rows. The outer table
|
|
# has 80 rows. Since the join returns 8 rows per row in the outer
|
|
# table, we expect it's enough to read 2 rows to reach the limit of
|
|
# 16.) For each of those 2 rows, we do an index lookup with 10%
|
|
# selectivity (8 rows) on the inner table. Total estimated number of
|
|
# row accesses: 2 + 2 * 8 = 18.
|
|
SET max_join_size = 17;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT 1 FROM t AS t1, t AS t2 WHERE t1.x = t2.x LIMIT 16;
|
|
SET max_join_size = @@max_join_size + 1;
|
|
SELECT 1 FROM t AS t1, t AS t2 WHERE t1.x = t2.x LIMIT 16;
|
|
|
|
# Hash join. We have to read the entire build table once. As in the
|
|
# nested loop case above, we expect it's enough to read two rows from
|
|
# the probe table to reach the limit of 16. Total: 80 + 2 = 82 rows.
|
|
SET max_join_size = 81;
|
|
--error ER_TOO_BIG_SELECT
|
|
SELECT 1 FROM t AS t1, t AS t2 WHERE t1.y = t2.y LIMIT 16;
|
|
SET max_join_size = @@max_join_size + 1;
|
|
SELECT 1 FROM t AS t1, t AS t2 WHERE t1.y = t2.y LIMIT 16;
|
|
|
|
SET max_join_size = DEFAULT;
|
|
DROP TABLE t;
|