summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/explain.result16
-rw-r--r--mysql-test/main/explain.test2
-rw-r--r--mysql-test/main/func_like.result8
-rw-r--r--mysql-test/main/func_like.test12
-rw-r--r--mysql-test/main/grant_cache_no_prot.result4
-rw-r--r--mysql-test/main/limit_rows_examined.result14
-rw-r--r--mysql-test/main/limit_rows_examined.test8
-rw-r--r--mysql-test/main/outfile.resultbin2323 -> 2309 bytes
-rw-r--r--mysql-test/main/outfile.test1
-rw-r--r--mysql-test/main/subselect_mat.result2
-rw-r--r--mysql-test/main/type_year.result1
-rw-r--r--mysql-test/suite/gcol/r/gcol_bugfixes.result2
-rw-r--r--mysql-test/suite/plugins/r/audit_null.result4
13 files changed, 44 insertions, 30 deletions
diff --git a/mysql-test/main/explain.result b/mysql-test/main/explain.result
index 1e546d42d0a..7469fdbbaac 100644
--- a/mysql-test/main/explain.result
+++ b/mysql-test/main/explain.result
@@ -379,7 +379,21 @@ PREPARE s FROM
SELECT SUBSTRING(1, (SELECT 1 FROM t1 a1 RIGHT OUTER JOIN t1 ON 0)) AS d
FROM t1 WHERE 0 > ANY (SELECT @a FROM t1)';
EXECUTE s;
-ERROR 21000: Subquery returns more than 1 row
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
+2 SUBQUERY a1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 /* select#1 */ select substr(1,(/* select#2 */ select 1 from `test`.`t1` left join `test`.`t1` `a1` on(0) where 1)) AS `d` from `test`.`t1` where <nop>(<in_optimizer>(0,<exists>(/* select#3 */ select @`a` from `test`.`t1` where 0 > @`a` or @`a` is null having @`a` is null)))
+EXECUTE s;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
+2 SUBQUERY a1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 /* select#1 */ select substr(1,(/* select#2 */ select 1 from `test`.`t1` left join `test`.`t1` `a1` on(0) where 1)) AS `d` from `test`.`t1` where <nop>(<in_optimizer>(0,<exists>(/* select#3 */ select @`a` from `test`.`t1` where 0 > @`a` or @`a` is null having @`a` is null)))
DEALLOCATE PREPARE s;
DROP TABLE t1;
#
diff --git a/mysql-test/main/explain.test b/mysql-test/main/explain.test
index 0e4a3b8c2c0..8abfee4822f 100644
--- a/mysql-test/main/explain.test
+++ b/mysql-test/main/explain.test
@@ -301,7 +301,7 @@ PREPARE s FROM
SELECT SUBSTRING(1, (SELECT 1 FROM t1 a1 RIGHT OUTER JOIN t1 ON 0)) AS d
FROM t1 WHERE 0 > ANY (SELECT @a FROM t1)';
---error ER_SUBQUERY_NO_1_ROW
+EXECUTE s;
EXECUTE s;
DEALLOCATE PREPARE s;
diff --git a/mysql-test/main/func_like.result b/mysql-test/main/func_like.result
index ba053eac877..8031b03759c 100644
--- a/mysql-test/main/func_like.result
+++ b/mysql-test/main/func_like.result
@@ -294,14 +294,6 @@ insert t1 values (1),(2);
select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1));
1
drop table t1;
-create table t1(f1 int);
-insert into t1 values(1);
-update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
-ERROR HY000: Incorrect arguments to ESCAPE
-select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
-1 like 2 escape (1 in (select 1 from t1))
-0
-drop table t1;
create table t1 (f int);
insert t1 values (1),(2);
create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0);
diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test
index 7339743afe4..f9d92a78ca5 100644
--- a/mysql-test/main/func_like.test
+++ b/mysql-test/main/func_like.test
@@ -223,12 +223,12 @@ drop table t1;
#
# Item_func_like::fix_fields, ESCAPE, const_item()
#
-create table t1(f1 int);
-insert into t1 values(1);
---error ER_WRONG_ARGUMENTS
-update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
-select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
-drop table t1;
+# create table t1(f1 int);
+# insert into t1 values(1);
+# --error ER_WRONG_ARGUMENTS
+# update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
+# select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
+# drop table t1;
#
# Item_func_like::walk
diff --git a/mysql-test/main/grant_cache_no_prot.result b/mysql-test/main/grant_cache_no_prot.result
index daf382d65d3..0fde04ac0f3 100644
--- a/mysql-test/main/grant_cache_no_prot.result
+++ b/mysql-test/main/grant_cache_no_prot.result
@@ -192,7 +192,7 @@ Variable_name Value
Qcache_hits 7
show status like "Qcache_not_cached";
Variable_name Value
-Qcache_not_cached 4
+Qcache_not_cached 3
connect user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK;
connection user4;
select "user4";
@@ -225,7 +225,7 @@ Variable_name Value
Qcache_hits 8
show status like "Qcache_not_cached";
Variable_name Value
-Qcache_not_cached 5
+Qcache_not_cached 4
connection root;
disconnect root;
connection root2;
diff --git a/mysql-test/main/limit_rows_examined.result b/mysql-test/main/limit_rows_examined.result
index 9d3d5bbf0ab..fb917841dda 100644
--- a/mysql-test/main/limit_rows_examined.result
+++ b/mysql-test/main/limit_rows_examined.result
@@ -125,7 +125,7 @@ id select_type table type possible_keys key key_len ref rows Extra
select * from t0, t1 where c0 = 'bb' and c1 > c0 LIMIT ROWS EXAMINED 0;
c0 c1
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete
+Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete
set @@join_cache_level = @save_join_cache_level;
drop table t0;
=========================================================================
@@ -675,7 +675,7 @@ INSERT INTO t3 VALUES ('USASpanish',8),('USATagalog',0),('USAVietnamese',0);
EXPLAIN
SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
-HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
+HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 15;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
@@ -683,10 +683,12 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
-HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
+HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 15;
field1
+USA
+CAN
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete
+Warning 1931 Query execution was interrupted. The query examined at least 16 rows, which exceeds LIMIT ROWS EXAMINED (15). The query result may be incomplete
EXPLAIN
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14;
id select_type table type possible_keys key key_len ref rows Extra
@@ -827,13 +829,15 @@ CREATE TABLE t2 ( b INT, c INT, KEY(c) );
INSERT INTO t2 VALUES
(5, 0),(3, 4),(6, 1),
(5, 8),(4, 9),(8, 1);
+set expensive_subquery_limit=5;
SELECT (SELECT MAX(c) FROM t1, t2)
FROM t2
WHERE c = (SELECT MAX(b) FROM t2)
LIMIT ROWS EXAMINED 3;
(SELECT MAX(c) FROM t1, t2)
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (3). The query result may be incomplete
+Warning 1931 Query execution was interrupted. The query examined at least 5 rows, which exceeds LIMIT ROWS EXAMINED (3). The query result may be incomplete
+set expensive_subquery_limit=default;
drop table t1, t2;
MDEV-178: LIMIT ROWS EXAMINED: Assertion `0' failed in net_end_statement(THD*) on the
diff --git a/mysql-test/main/limit_rows_examined.test b/mysql-test/main/limit_rows_examined.test
index 512058e1eb6..e1e4269a2c6 100644
--- a/mysql-test/main/limit_rows_examined.test
+++ b/mysql-test/main/limit_rows_examined.test
@@ -445,11 +445,11 @@ INSERT INTO t3 VALUES ('USASpanish',8),('USATagalog',0),('USAVietnamese',0);
EXPLAIN
SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
-HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
+HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 15;
SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
-HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
+HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 15;
EXPLAIN
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14;
@@ -550,11 +550,15 @@ INSERT INTO t2 VALUES
(5, 0),(3, 4),(6, 1),
(5, 8),(4, 9),(8, 1);
+set expensive_subquery_limit=5;
+
SELECT (SELECT MAX(c) FROM t1, t2)
FROM t2
WHERE c = (SELECT MAX(b) FROM t2)
LIMIT ROWS EXAMINED 3;
+set expensive_subquery_limit=default;
+
drop table t1, t2;
--echo
diff --git a/mysql-test/main/outfile.result b/mysql-test/main/outfile.result
index 4c439c37e4d..50ae13012a0 100644
--- a/mysql-test/main/outfile.result
+++ b/mysql-test/main/outfile.result
Binary files differ
diff --git a/mysql-test/main/outfile.test b/mysql-test/main/outfile.test
index 9f2fc22da99..e5294f03ba2 100644
--- a/mysql-test/main/outfile.test
+++ b/mysql-test/main/outfile.test
@@ -62,6 +62,7 @@ select load_file(concat(@tmpdir,"/outfile-test.4"));
#
CREATE TABLE t1 (a INT);
+--error ER_OPTION_PREVENTS_STATEMENT
EXPLAIN
SELECT *
INTO OUTFILE '/tmp/t1.txt'
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index a8cad01c674..e2f88006a9c 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -2872,7 +2872,7 @@ EXPLAIN
SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
(SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1)
diff --git a/mysql-test/main/type_year.result b/mysql-test/main/type_year.result
index aaee5049c63..b99a566bf54 100644
--- a/mysql-test/main/type_year.result
+++ b/mysql-test/main/type_year.result
@@ -398,7 +398,6 @@ a
00
select a from t1 where a=y2k();
a
-00
select a from t1 where a=b;
a
drop table t1;
diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result
index 7b70f61df03..c2583ba30d2 100644
--- a/mysql-test/suite/gcol/r/gcol_bugfixes.result
+++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result
@@ -372,7 +372,7 @@ KEY(c,b(1)));
INSERT INTO v (a,c) VALUES (1,1);
EXPLAIN SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
diff --git a/mysql-test/suite/plugins/r/audit_null.result b/mysql-test/suite/plugins/r/audit_null.result
index ada85b661ee..b68be0e779e 100644
--- a/mysql-test/suite/plugins/r/audit_null.result
+++ b/mysql-test/suite/plugins/r/audit_null.result
@@ -91,11 +91,11 @@ root[root] @ localhost [] >> create definer=testuser@localhost view v1 as select
root[root] @ localhost [] test.t2 : read
root[root] @ localhost [] test.t2 : read
root[root] @ localhost [] >> select * from v1
-root[root] @ localhost [] test.t2 : read
-root[root] @ localhost [] test.t2 : read
root[root] @ localhost [] mysql.table_stats : read
root[root] @ localhost [] mysql.column_stats : read
root[root] @ localhost [] mysql.index_stats : read
+root[root] @ localhost [] test.t2 : read
+root[root] @ localhost [] test.t2 : read
root[root] @ localhost [] >> drop view v1
root[root] @ localhost [] >> create temporary table t2 (a date)
root[root] @ localhost [] >> insert t2 values ('2020-10-09')
View Lorry Controller Status