diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_exists_to_in.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_mat.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_opts.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_scache.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 41 | ||||
| -rw-r--r-- | mysql-test/suite/parts/r/longname.result | 37 | ||||
| -rw-r--r-- | mysql-test/suite/parts/t/longname.test | 32 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/circular_serverid0.result | 30 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/circular_serverid0.cnf | 30 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/circular_serverid0.test | 104 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 40 |
12 files changed, 467 insertions, 52 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 49a033626ee..da418accd6c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4701,7 +4701,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)); @@ -5576,7 +5576,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 @@ -6188,7 +6188,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 # @@ -7145,9 +7145,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 @@ -7155,9 +7156,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; # @@ -7172,6 +7174,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 # @@ -7198,4 +7223,4 @@ 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 diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index d968a81c638..5382e291dfc 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.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 @@ -6188,7 +6188,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 # @@ -7145,9 +7145,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 @@ -7155,9 +7156,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; # @@ -7172,6 +7174,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 # @@ -7198,7 +7223,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 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 19a936e86c7..255f8e30ccf 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -4701,7 +4701,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)); @@ -5576,7 +5576,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 @@ -6183,7 +6183,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 # @@ -7138,9 +7138,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 @@ -7148,9 +7149,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; # @@ -7165,6 +7167,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 # @@ -7191,7 +7216,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 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 1293e1de412..938c8edc2e9 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -4697,7 +4697,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)); @@ -5572,7 +5572,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 @@ -6179,7 +6179,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 # @@ -7136,9 +7136,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 @@ -7146,9 +7147,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; # @@ -7163,6 +7165,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 # @@ -7189,5 +7214,5 @@ 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 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 cc3d20ef163..9a37719c38a 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.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 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 2bbde7b397f..c66fef2eb9b 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -4697,7 +4697,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)); @@ -5572,7 +5572,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 @@ -6179,7 +6179,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 # @@ -7136,9 +7136,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 @@ -7146,9 +7147,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; # @@ -7163,6 +7165,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 # @@ -7189,6 +7214,6 @@ 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 set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result new file mode 100644 index 00000000000..6424ba28297 --- /dev/null +++ b/mysql-test/suite/parts/r/longname.result @@ -0,0 +1,37 @@ +set names utf8; +create database mysqltest1; +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +database_name table_name length(table_name) +CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( +id int(10) unsigned NOT NULL, +id2 int(10) unsigned NOT NULL, +PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) +SUBPARTITION BY HASH ( id2 ) +SUBPARTITIONS 2 ( +PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, +PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); +Warnings: +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +database_name table_name length(table_name) +CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( +id int(10) unsigned NOT NULL, +id2 int(10) unsigned NOT NULL, +PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) +SUBPARTITION BY HASH ( id2 ) +SUBPARTITIONS 2 ( +PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB, +PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); +ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@ is too long. +drop database mysqltest1; diff --git a/mysql-test/suite/parts/t/longname.test b/mysql-test/suite/parts/t/longname.test new file mode 100644 index 00000000000..0f7378ef8e3 --- /dev/null +++ b/mysql-test/suite/parts/t/longname.test @@ -0,0 +1,32 @@ +source include/have_innodb.inc; +source include/have_partition.inc; +set names utf8; + +create database mysqltest1; +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; +CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1'; + +--error ER_PATH_LENGTH +CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé ( + id int(10) unsigned NOT NULL, + id2 int(10) unsigned NOT NULL, + PRIMARY KEY ( id, id2 ) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +PARTITION BY RANGE ( id ) + SUBPARTITION BY HASH ( id2 ) + SUBPARTITIONS 2 ( + PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB, + PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); + +drop database mysqltest1; diff --git a/mysql-test/suite/rpl/r/circular_serverid0.result b/mysql-test/suite/rpl/r/circular_serverid0.result new file mode 100644 index 00000000000..899b4bd343b --- /dev/null +++ b/mysql-test/suite/rpl/r/circular_serverid0.result @@ -0,0 +1,30 @@ +include/rpl_init.inc [topology=1->2->1] +include/rpl_connect.inc [creating M4] +include/rpl_connect.inc [creating M2] +SET @old_debug= @@global.debug; +STOP SLAVE; +SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging"; +START SLAVE IO_THREAD; +include/wait_for_slave_io_to_start.inc +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb; +INSERT INTO `t1` VALUES (null, repeat('a', 1024)), (null, repeat('b', 1024)); +SET debug_sync='now WAIT_FOR start_sql_thread'; +START SLAVE SQL_THREAD; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb +SET debug_sync='now SIGNAL go_on_relay_logging'; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +SET GLOBAL debug_dbug= @old_debug; +SET debug_sync='RESET'; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/circular_serverid0.cnf b/mysql-test/suite/rpl/t/circular_serverid0.cnf new file mode 100644 index 00000000000..277aac2869b --- /dev/null +++ b/mysql-test/suite/rpl/t/circular_serverid0.cnf @@ -0,0 +1,30 @@ +!include ../my.cnf + +[mysqld.1] +gtid-domain-id=4 +server-id=4 +# +log-slave-updates +slave-parallel-threads=0 +gtid-strict-mode=1 +gtid-ignore-duplicates=1 + +# +# Max-size row events to minimum with the idea to create +# a number of Rows_log_event per Query. +# +binlog-row-event-max-size=1024 + +[mysqld.2] +gtid-domain-id=2 +server-id=2 +# +log-slave-updates +slave-parallel-threads=0 +gtid-strict-mode=1 +gtid-ignore-duplicates=1 +binlog-row-event-max-size=1024 +# The slave will be initialized with a @@global.dbug-var value +skip-slave-start=1 + + diff --git a/mysql-test/suite/rpl/t/circular_serverid0.test b/mysql-test/suite/rpl/t/circular_serverid0.test new file mode 100644 index 00000000000..20ad58e2c52 --- /dev/null +++ b/mysql-test/suite/rpl/t/circular_serverid0.test @@ -0,0 +1,104 @@ +# +# Testing chain/circular replication scenario of MDEV-9670 +# The effect of the bug was that we got a commit with a GTID with server_id +# + +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--let $rpl_topology= 1->2->1 +--source include/rpl_init.inc + +--let $rpl_connection_name= M4 +--let $rpl_server_number= 1 +--source include/rpl_connect.inc + +--let $rpl_connection_name= M2 +--let $rpl_server_number= 2 +--source include/rpl_connect.inc + +# The parameter reflects binlog-row-event-max-size @cnf. +--let $row_size=1024 + +SET @old_debug= @@global.debug; + +--connection M2 +STOP SLAVE; +SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging"; +START SLAVE IO_THREAD; +--source include/wait_for_slave_io_to_start.inc + +--connection M2 +# This query also creates a Gtid event group whose Gtid will remain in +# ignored status for too long causing a following group split. + +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb; +--sync_slave_with_master M4 + +# This INSERT will be logged as two Write_log events which the buggy +# slave applier would split. + +--connection M4 +eval INSERT INTO `t1` VALUES (null, repeat('a', $row_size)), (null, repeat('b', $row_size)); + +# START M2 IO thread and wait for its signal to follow with the SQL +# thread start. At this moment the SQL thread shall be having 2 and +# "half" groups to execute. The "hafl" one would be committed by the +# buggy applier after which the IO is released to queue the rest of +# the 3rd group which the SQL thread commits separately to complete +# the split. + +--connection M2 + +# wait for IO signal to start the SQL thread. IO will be hanging upon that. +SET debug_sync='now WAIT_FOR start_sql_thread'; + +# Now the slave server has relay log whose last group is incomplete. +# An unfixed slave server would go to "insert" a "fake" +# Gtid_list_log_event event which actually would commit the incomplete +# group. However before to actual commit do_apply_event() hits some assert. +# In the fixed server the fake Gtid_list_log_event is *not* inserted +# in the middle of a group. +START SLAVE SQL_THREAD; + +# Sleep for a little time to give SQL thread a chance to commit while +# the IO thread is hanging (see +# DBUG_EXECUTE_IF("dbug.rows_events_to_delay_relay_logging"...) in +# queue_event). Alternatively to reproduce the case when buggy slave +# wait for the 1st group commit + +#--let $count= 1 +#--let $table= t1 +#--source include/wait_until_rows_count.inc + +--sleep 2 + +# Demonstrate either no split group in the correct slave or the 1nd +# group in the buggy one +--source include/show_binlog_events.inc + +# Release the IO thread +SET debug_sync='now SIGNAL go_on_relay_logging'; + +# Sync servers +--sync_slave_with_master M4 +--connection M4 +--sync_slave_with_master M2 +--connection M2 + +# Demonstrate replication goes correctly not to create any split, or +# the 2nd group in the buggy slave +--source include/show_binlog_events.inc + +# +# Cleanup +# +--connection M4 +drop table t1; + +--connection M2 +SET GLOBAL debug_dbug= @old_debug; +SET debug_sync='RESET'; +--source include/rpl_end.inc diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index cfb5042299a..bc84b6b9e26 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3600,7 +3600,7 @@ SET SESSION sql_mode=@old_sql_mode; drop table t2, t1; drop view v1; ---echo End of 5.0 tests. +--echo # End of 5.0 tests. # # Test [NOT] IN truth table (both as top-level and general predicate). @@ -4685,7 +4685,7 @@ select * from t2 where a=(select a from t1) and a='x'; drop table t1,t2; ---echo End of 5.1 tests +--echo # End of 5.1 tests --echo # --echo # Bug #11765713 58705: @@ -5156,7 +5156,7 @@ eval SELECT * FROM ( $subq ) AS alias3; DROP TABLE t1,t2; ---echo End of 5.2 tests +--echo # End of 5.2 tests --echo # --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in @@ -6004,11 +6004,17 @@ INSERT INTO t2 VALUES (3); CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0; SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 ); delete from t1; +--error ER_WRONG_GROUP_FIELD +SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq; +--error ER_WRONG_GROUP_FIELD SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; drop view v2; @@ -6024,6 +6030,32 @@ SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); DROP TABLE t1; +--echo # +--echo # MDEV-10146: Wrong result (or questionable result and behavior) +--echo # with aggregate function in uncorrelated SELECT subquery +--echo # +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 ( SELECT MAX(f1) FROM t2 ) FROM v1; + +INSERT INTO t2 VALUES (4); + +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1; +--error ER_SUBQUERY_NO_1_ROW +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +drop view v1; +drop table t1,t2; + +--echo # End of 10.0 tests --echo # --echo # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops @@ -6051,4 +6083,4 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo'); DROP TABLE t1, t2; SET NAMES default; ---echo End of 10.1 tests +--echo # End of 10.1 tests |