summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/analyze_format_json.result60
-rw-r--r--mysql-test/r/subselect.result43
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result43
-rw-r--r--mysql-test/r/subselect_no_mat.result43
-rw-r--r--mysql-test/r/subselect_no_opts.result43
-rw-r--r--mysql-test/r/subselect_no_scache.result43
-rw-r--r--mysql-test/r/subselect_no_semijoin.result43
7 files changed, 264 insertions, 54 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index e077f919aa0..7991379bc55 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -758,3 +758,63 @@ ANALYZE
}
}
drop table t1,t2,t3;
+#
+# MDEV-13286: Floating point exception in Filesort_tracker::print_json_members(Json_writer*)
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, c int);
+insert into t1 select a,a,a from t0;
+create table t2 as select * from t1;
+analyze format=json select a, (select t2.b from t2 where t2.a<t1.a order by t2.c limit 1) from t1 where t1.a<0;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 10,
+ "r_rows": 10,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 0,
+ "attached_condition": "t1.a < 0"
+ },
+ "subqueries": [
+ {
+ "expression_cache": {
+ "state": "uninitialized",
+ "r_loops": 0,
+ "query_block": {
+ "select_id": 2,
+ "read_sorted_file": {
+ "r_rows": null,
+ "filesort": {
+ "sort_key": "t2.c",
+ "r_loops": null,
+ "r_limit": null,
+ "r_used_priority_queue": null,
+ "r_output_rows": null,
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "rows": 10,
+ "r_rows": null,
+ "filtered": 100,
+ "r_filtered": null,
+ "attached_condition": "t2.a < t1.a"
+ }
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+}
+drop table t0,t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 98a279c28e9..9f07b1fefff 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4707,7 +4707,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5582,7 +5582,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6194,7 +6194,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7151,9 +7151,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7161,9 +7162,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7178,6 +7180,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7204,7 +7229,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7218,4 +7243,4 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 5cee4076ed8..e4b57d96b38 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -4709,7 +4709,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5584,7 +5584,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6194,7 +6194,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7151,9 +7151,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7161,9 +7162,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7178,6 +7180,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7204,7 +7229,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7218,7 +7243,7 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%exists_to_in=off%';
@@optimizer_switch like '%exists_to_in=off%'
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index e7409b0b09c..22082774668 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4707,7 +4707,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5582,7 +5582,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6189,7 +6189,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7144,9 +7144,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7154,9 +7155,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7171,6 +7173,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7197,7 +7222,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7211,7 +7236,7 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
@@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 3bdc91686d7..5f71700c4ce 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4703,7 +4703,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5578,7 +5578,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6185,7 +6185,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7142,9 +7142,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7152,9 +7153,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7169,6 +7171,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7195,7 +7220,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7209,5 +7234,5 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 3beba7c338d..8500fa2434c 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4713,7 +4713,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5588,7 +5588,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6200,7 +6200,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7157,9 +7157,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7167,9 +7168,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7184,6 +7186,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7210,7 +7235,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7224,7 +7249,7 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%subquery_cache=on%';
@@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index f2d97078772..48bf9ce90c9 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4703,7 +4703,7 @@ pk
SET SESSION sql_mode=@old_sql_mode;
drop table t2, t1;
drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
create table t_in (a2 char(2), b2 char(2), c2 char(2));
@@ -5578,7 +5578,7 @@ insert into t2 values ('x'), ('y');
select * from t2 where a=(select a from t1) and a='x';
ERROR 21000: Subquery returns more than 1 row
drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
#
# Bug #11765713 58705:
# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6185,7 +6185,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey
DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
#
# BUG#779885: Crash in eliminate_item_equal with materialization=on in
#
@@ -7142,9 +7142,10 @@ INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT, KEY(f2));
INSERT INTO t2 VALUES (3);
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
f2
3
@@ -7152,9 +7153,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
count(*)
1
delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
drop view v2;
drop table t1,t2;
#
@@ -7169,6 +7171,29 @@ f1 f2
foo bar
DROP TABLE t1;
#
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
# MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
# with UNION in ALL subquery
#
@@ -7195,7 +7220,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
f1 f2 f3
DROP TABLE t1, t2;
SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
#
# MDEV-12564: IN TO EXISTS transformation for rows after
# conversion an outer join to inner join
@@ -7209,6 +7234,6 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
-End of 10.2 tests
+# End of 10.2 tests
set @optimizer_switch_for_subselect_test=null;
set @join_cache_level_for_subselect_test=NULL;
View Lorry Controller Status