diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2022-01-29 15:41:05 +0100 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2022-01-29 15:41:05 +0100 |
commit | 41a163ac5ccf4ac5394edc84e40b3f47acea6b08 (patch) | |
tree | 60d5259e290b4a0166d8ef1651975b14b5afe304 /mysql-test/main | |
parent | a85d942be9008cf19086d8bd330c4be83a18167f (diff) | |
parent | e2b50213cf12623da31c8b49be4d40772876223c (diff) | |
download | mariadb-git-41a163ac5ccf4ac5394edc84e40b3f47acea6b08.tar.gz |
Merge branch '10.2' into 10.3mariadb-10.3.33
Diffstat (limited to 'mysql-test/main')
47 files changed, 1575 insertions, 116 deletions
diff --git a/mysql-test/main/alter_user.result b/mysql-test/main/alter_user.result index 7ba9549e73c..46420420386 100644 --- a/mysql-test/main/alter_user.result +++ b/mysql-test/main/alter_user.result @@ -91,3 +91,13 @@ select * from mysql.user where user = 'foo'; Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time % foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N Y N N N N N N N N N Y N N N N SPECIFIED text foo_issuer foo_subject 10 20 30 40 N N 0.000000 drop user foo; +# +# Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION +# +create user foo@localhost; +connect x,localhost,foo; +alter user current_user identified with 'something'; +ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation +connection default; +disconnect x; +drop user foo@localhost; diff --git a/mysql-test/main/alter_user.test b/mysql-test/main/alter_user.test index 3a1052a98f6..e71fba271b1 100644 --- a/mysql-test/main/alter_user.test +++ b/mysql-test/main/alter_user.test @@ -77,3 +77,14 @@ alter user foo with MAX_QUERIES_PER_HOUR 10 MAX_USER_CONNECTIONS 40; select * from mysql.user where user = 'foo'; drop user foo; + +--echo # +--echo # Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION +--echo # +create user foo@localhost; +--connect x,localhost,foo +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +alter user current_user identified with 'something'; +--connection default +--disconnect x +drop user foo@localhost; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f917509e20..c3421a288a1 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1204,6 +1204,59 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); drop view v1,v2,v3,v4; drop table t1,t2,t3; # +# MDEV-20325: Assertion `outer_context || !*from_field || *from_field == not_found_field' failed in Item_field::fix_outer_field | `!derived->is_excluded()' failed in TABLE_LIST::set_check_materialized | SIGEGV in st_select_lex::mark_as_dependent (optimized builds) +# +CREATE TABLE t1 (a INT); +# Check that re-execution of a stored routine containing +# a query with subquery in the FROM clause doesn't result in +# assert failure in case the 'derived_merge' optimizer option +# has been turned on/off +CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb; +CALL sp(); +a +SET optimizer_switch='derived_merge=off'; +# Without the patch the following statement would result in assert +# failure +CALL sp(); +a +# Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=on'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +a +SET optimizer_switch='derived_merge=off'; +# Without the patch the following statement would result in assert +# failure +EXECUTE stmt; +a +DEALLOCATE PREPARE stmt; +# Here check the reverse test case - first turn off the 'derived_merge' +# optimizer option, run the stored routine containing a query with +# subquery in the FROM clause, then turn on the 'derived_merge' +# optimizer option and re-execute the same stored routine to check that +# the routine is finished successfully. +CREATE PROCEDURE sp1() SELECT * FROM (SELECT a FROM t1) tb; +SET optimizer_switch='derived_merge=off'; +CALL sp1(); +a +SET optimizer_switch='derived_merge=on'; +CALL sp1(); +a +# Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=off'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +a +SET optimizer_switch='derived_merge=on'; +# Without the patch the following statement would result in assert +# failure +EXECUTE stmt; +a +DEALLOCATE PREPARE stmt; +DROP PROCEDURE sp; +DROP PROCEDURE sp1; +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce8550..4ff0cf4165c 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1037,6 +1037,57 @@ drop view v1,v2,v3,v4; drop table t1,t2,t3; --echo # +--echo # MDEV-20325: Assertion `outer_context || !*from_field || *from_field == not_found_field' failed in Item_field::fix_outer_field | `!derived->is_excluded()' failed in TABLE_LIST::set_check_materialized | SIGEGV in st_select_lex::mark_as_dependent (optimized builds) +--echo # +CREATE TABLE t1 (a INT); + +--echo # Check that re-execution of a stored routine containing +--echo # a query with subquery in the FROM clause doesn't result in +--echo # assert failure in case the 'derived_merge' optimizer option +--echo # has been turned on/off +CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb; +CALL sp(); +SET optimizer_switch='derived_merge=off'; +--echo # Without the patch the following statement would result in assert +--echo # failure +CALL sp(); + +--echo # Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=on'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +SET optimizer_switch='derived_merge=off'; +--echo # Without the patch the following statement would result in assert +--echo # failure +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # Here check the reverse test case - first turn off the 'derived_merge' +--echo # optimizer option, run the stored routine containing a query with +--echo # subquery in the FROM clause, then turn on the 'derived_merge' +--echo # optimizer option and re-execute the same stored routine to check that +--echo # the routine is finished successfully. +CREATE PROCEDURE sp1() SELECT * FROM (SELECT a FROM t1) tb; +SET optimizer_switch='derived_merge=off'; +CALL sp1(); +SET optimizer_switch='derived_merge=on'; +CALL sp1(); + +--echo # Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=off'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +SET optimizer_switch='derived_merge=on'; +--echo # Without the patch the following statement would result in assert +--echo # failure +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP PROCEDURE sp; +DROP PROCEDURE sp1; +DROP TABLE t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 65a1adcaddd..af5f8cc1d3b 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3498,4 +3498,134 @@ a 3 drop view v1; drop table t1; +# +# MDEV-24454 Second execution of SELECT containing set function +# MDEV-25086: whose only argument is an outer reference to a column +# of mergeable view/derived/table/CTE +# +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +drop view v1; +drop table t1,t2; +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT +(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, +GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; +CREATE PROCEDURE p1() +SELECT * FROM v3; +CALL p1(); +f2 f3 +3 aa +CALL p1(); +f2 f3 +3 aa +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; # End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 9ab1ddd3a76..584bde2d75d 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2289,4 +2289,115 @@ select * from ((select a from t1 limit 2) order by a desc) dt; drop view v1; drop table t1; +--echo # +--echo # MDEV-24454 Second execution of SELECT containing set function +--echo # MDEV-25086: whose only argument is an outer reference to a column +--echo # of mergeable view/derived/table/CTE +--echo # + +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +drop view v1; +drop table t1,t2; + +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT + (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, + GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; + +CREATE PROCEDURE p1() + SELECT * FROM v3; +CALL p1(); +CALL p1(); + +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/mysql-test/main/events_embedded.test b/mysql-test/main/events_embedded.test index 9922ea6dfee..f6921f302bf 100644 --- a/mysql-test/main/events_embedded.test +++ b/mysql-test/main/events_embedded.test @@ -2,4 +2,3 @@ --error 1193 set global event_scheduler=ON; - diff --git a/mysql-test/main/func_default.result b/mysql-test/main/func_default.result index 9699f0795e3..8721270ca1c 100644 --- a/mysql-test/main/func_default.result +++ b/mysql-test/main/func_default.result @@ -29,6 +29,9 @@ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL; ERROR HY000: Field 'mi' doesn't have a default value DROP TABLE t1; +# +# Start of 10.2 tests +# set timestamp=unix_timestamp('2001-01-01 10:20:30.123456'); create table t1 (a int default 1, b int default (a+1), c varchar(100) default 'foo', d text default 'bar', @@ -40,3 +43,121 @@ default(a) default(b) default(c) default(d) default(e) default(f) 1 2 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000 1 11 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000 drop table t1; +# +# MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause +# +CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '10' AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10'; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,DATE'2001-01-01'))); +INSERT INTO t1 VALUES ('2000-01-01'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce +FROM t1; +a DEFAULT(a) c ce +2000-01-01 2001-01-01 2001-01-01 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01'; +a +2000-01-01 +DROP TABLE t1; +CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,TIME'10:20:30'))); +INSERT INTO t1 VALUES ('10:00:00'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce +FROM t1; +a DEFAULT(a) c ce +10:00:00 10:20:30 10:20:30 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30'; +a +10:00:00 +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30'))); +INSERT INTO t1 VALUES ('2000-01-01 10:00:00'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce +FROM t1; +a DEFAULT(a) c ce +2000-01-01 10:00:00 2001-01-01 10:20:30 2001-01-01 10:20:30 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30'; +a +2000-01-01 10:00:00 +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE))); +INSERT INTO t1 VALUES (10); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce +FROM t1; +a DEFAULT(a) c ce +10 0 0 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE; +a +10 +DROP TABLE t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/func_default.test b/mysql-test/main/func_default.test index 332bfca021f..cba7842c68f 100644 --- a/mysql-test/main/func_default.test +++ b/mysql-test/main/func_default.test @@ -34,9 +34,9 @@ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL; DROP TABLE t1; -# -# 10.2 tests -# +--echo # +--echo # Start of 10.2 tests +--echo # set timestamp=unix_timestamp('2001-01-01 10:20:30.123456'); create table t1 (a int default 1, b int default (a+1), @@ -46,3 +46,99 @@ insert t1 () values (); insert t1 (a) values (10); select default(a),default(b),default(c),default(d),default(e),default(f) from t1; drop table t1; + +--echo # +--echo # MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause +--echo # + +CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '10' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10'; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,DATE'2001-01-01'))); +INSERT INTO t1 VALUES ('2000-01-01'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01'; +DROP TABLE t1; + +CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,TIME'10:20:30'))); +INSERT INTO t1 VALUES ('10:00:00'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30'; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30'))); +INSERT INTO t1 VALUES ('2000-01-01 10:00:00'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30'; +DROP TABLE t1; + +CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE))); +INSERT INTO t1 VALUES (10); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE; +DROP TABLE t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 6656e0d5e51..cba57b0853e 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -940,6 +940,24 @@ SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ) ["x"] # +# MDEV-18284 JSON casting using JSON_COMPACT doesn't always work +# with values from subqueries +# +CREATE TABLE json_test(a JSON, b JSON); +INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); +SELECT * FROM json_test; +a b +[1,2,3] {"a":"foo"} +SELECT json_object("a", json_compact(a), "b", b) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", b) +{"a": [1,2,3], "b": "{\"a\":\"foo\"}"} +SELECT json_object("a", json_compact(a), "b", json_compact(b)) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", json_compact(b)) +{"a": [1,2,3], "b": {"a":"foo"}} +DROP TABLE json_test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 24f2ae9267c..aa461433dfe 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -558,6 +558,21 @@ SELECT SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); --echo # +--echo # MDEV-18284 JSON casting using JSON_COMPACT doesn't always work +--echo # with values from subqueries +--echo # + +CREATE TABLE json_test(a JSON, b JSON); +INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); +SELECT * FROM json_test; + +SELECT json_object("a", json_compact(a), "b", b) + FROM (SELECT * FROM json_test) AS json_test_values; +SELECT json_object("a", json_compact(a), "b", json_compact(b)) + FROM (SELECT * FROM json_test) AS json_test_values; +DROP TABLE json_test; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result index 23ec1724d59..a7168567115 100644 --- a/mysql-test/main/func_str.result +++ b/mysql-test/main/func_str.result @@ -5023,6 +5023,59 @@ SELECT NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux'); NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux') NULL # +# Bug#31374305 - FORMAT() NOT DISPLAYING WHOLE NUMBER SIDE CORRECTLY +# FOR ES_MX AND ES_ES LOCALES +# +CREATE PROCEDURE load_locale_format_table() +BEGIN +DECLARE locale_list VARCHAR(1000) DEFAULT ' + es_AR,es_BO,es_CL,es_CO,es_CR,es_DO,es_EC,es_ES,es_GT,es_HN, + es_MX,es_NI,es_PA,es_PE,es_PR,es_PY,es_SV,es_US,es_UY,es_VE'; +SET @fmt_stmt = 'INSERT INTO locale_format VALUES + (?, FORMAT(12131254123412541,2,?));'; +PREPARE stmt FROM @fmt_stmt; +WHILE locale_list != '' DO +/* get the first locale from the list */ +SET @locale = +TRIM(REPLACE((SUBSTRING_INDEX(locale_list, ',', 1)), '\n','')); +EXECUTE stmt USING @locale, @locale; +/* remove the first locale from the list */ +IF LOCATE(',', locale_list) > 0 THEN +SET locale_list = +SUBSTRING(locale_list, LOCATE(',', locale_list) + 1); +ELSE +SET locale_list = ''; +END IF; +END WHILE; +DEALLOCATE PREPARE stmt; +END| +CREATE TABLE locale_format(locale VARCHAR(10), formatted_string VARCHAR(100)); +CALL load_locale_format_table(); +SELECT * FROM locale_format; +locale formatted_string +es_AR 12.131.254.123.412.541,00 +es_BO 12.131.254.123.412.541,00 +es_CL 12.131.254.123.412.541,00 +es_CO 12.131.254.123.412.541,00 +es_CR 12 131 254 123 412 541,00 +es_DO 12,131,254,123,412,541.00 +es_EC 12.131.254.123.412.541,00 +es_ES 12.131.254.123.412.541,00 +es_GT 12,131,254,123,412,541.00 +es_HN 12,131,254,123,412,541.00 +es_MX 12,131,254,123,412,541.00 +es_NI 12,131,254,123,412,541.00 +es_PA 12,131,254,123,412,541.00 +es_PE 12,131,254,123,412,541.00 +es_PR 12,131,254,123,412,541.00 +es_PY 12.131.254.123.412.541,00 +es_SV 12,131,254,123,412,541.00 +es_US 12,131,254,123,412,541.00 +es_UY 12.131.254.123.412.541,00 +es_VE 12.131.254.123.412.541,00 +DROP PROCEDURE load_locale_format_table; +DROP TABLE locale_format; +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test index b65cc98ff5a..11bafe4988a 100644 --- a/mysql-test/main/func_str.test +++ b/mysql-test/main/func_str.test @@ -1997,6 +1997,42 @@ DROP TABLE t1; SELECT NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux'); +--echo # +--echo # Bug#31374305 - FORMAT() NOT DISPLAYING WHOLE NUMBER SIDE CORRECTLY +--echo # FOR ES_MX AND ES_ES LOCALES +--echo # + +DELIMITER |; +CREATE PROCEDURE load_locale_format_table() +BEGIN + DECLARE locale_list VARCHAR(1000) DEFAULT ' + es_AR,es_BO,es_CL,es_CO,es_CR,es_DO,es_EC,es_ES,es_GT,es_HN, + es_MX,es_NI,es_PA,es_PE,es_PR,es_PY,es_SV,es_US,es_UY,es_VE'; + SET @fmt_stmt = 'INSERT INTO locale_format VALUES + (?, FORMAT(12131254123412541,2,?));'; + PREPARE stmt FROM @fmt_stmt; + WHILE locale_list != '' DO + /* get the first locale from the list */ + SET @locale = + TRIM(REPLACE((SUBSTRING_INDEX(locale_list, ',', 1)), '\n','')); + EXECUTE stmt USING @locale, @locale; + /* remove the first locale from the list */ + IF LOCATE(',', locale_list) > 0 THEN + SET locale_list = + SUBSTRING(locale_list, LOCATE(',', locale_list) + 1); + ELSE + SET locale_list = ''; + END IF; + END WHILE; + DEALLOCATE PREPARE stmt; +END| +DELIMITER ;| + +CREATE TABLE locale_format(locale VARCHAR(10), formatted_string VARCHAR(100)); +CALL load_locale_format_table(); +SELECT * FROM locale_format; +DROP PROCEDURE load_locale_format_table; +DROP TABLE locale_format; --echo # --echo # End of 10.2 tests diff --git a/mysql-test/main/func_system.result b/mysql-test/main/func_system.result index 06fb7e44cf0..688f4a30b50 100644 --- a/mysql-test/main/func_system.result +++ b/mysql-test/main/func_system.result @@ -46,7 +46,7 @@ create table t1 (version char(60)) select database(), user(), version() as 'vers show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `database()` varchar(34) CHARACTER SET utf8 DEFAULT NULL, + `database()` varchar(64) CHARACTER SET utf8 DEFAULT NULL, `user()` varchar(141) CHARACTER SET utf8 DEFAULT NULL, `version` char(60) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -95,3 +95,21 @@ select left(concat(a,version()),1) from t1; left(concat(a,version()),1) a drop table t1; +# +# Start of 10.2 tests +# + +MDEV-27544 database() function under UNION ALL truncates results to 34 characters + + +SET NAMES utf8; +create database betäubungsmittelverschreibungsverordnung; +use betäubungsmittelverschreibungsverordnung; +select database() as "database" union all select database(); +database +betäubungsmittelverschreibungsverordnung +betäubungsmittelverschreibungsverordnung +drop database betäubungsmittelverschreibungsverordnung; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/func_system.test b/mysql-test/main/func_system.test index fa09e81a300..d9f2bda750a 100644 --- a/mysql-test/main/func_system.test +++ b/mysql-test/main/func_system.test @@ -55,3 +55,23 @@ select left(concat(a,version()),1) from t1; drop table t1; # End of 4.1 tests + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo +--echo MDEV-27544 database() function under UNION ALL truncates results to 34 characters +--echo +--echo + +SET NAMES utf8; +create database betäubungsmittelverschreibungsverordnung; +use betäubungsmittelverschreibungsverordnung; +select database() as "database" union all select database(); +drop database betäubungsmittelverschreibungsverordnung; + + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 0663b2d4e90..bf60bda2708 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -173,17 +173,29 @@ or id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where explain select * from t0 where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) +or +((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where +((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where +((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4)) +or +((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where select * from t0 where key1 < 5 or key8 < 4 order by key1; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 diff --git a/mysql-test/main/information_schema_inno.result b/mysql-test/main/information_schema_inno.result index d952e4372ca..c81631728f0 100644 --- a/mysql-test/main/information_schema_inno.result +++ b/mysql-test/main/information_schema_inno.result @@ -1,4 +1,3 @@ -DROP TABLE IF EXISTS t1,t2,t3; CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id, id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE, diff --git a/mysql-test/main/information_schema_inno.test b/mysql-test/main/information_schema_inno.test index 3cdbb8111d9..6c50ff3ece0 100644 --- a/mysql-test/main/information_schema_inno.test +++ b/mysql-test/main/information_schema_inno.test @@ -1,8 +1,6 @@ -- source include/testdb_only.inc -- source include/have_innodb.inc ---disable_warnings -DROP TABLE IF EXISTS t1,t2,t3; ---enable_warnings +-- source include/have_symlink.inc # # Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index 5094638c92b..e85c4982137 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -865,3 +865,22 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; End of 5.5 tests +# +# Beginning of 10.2 test +# +# MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same +# table: rows are counted twice +# +CREATE TABLE t1(a TINYINT); +INSERT INTO t1 VALUES (1), (100); +INSERT INTO t1 SELECT a*2 FROM t1; +Warnings: +Warning 1264 Out of range value for column 'a' at row 2 +TRUNCATE TABLE t1; +# using ORDER BY +INSERT INTO t1 VALUES(1), (2), (100), (3); +INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; +Warnings: +Warning 1264 Out of range value for column 'a' at row 4 +DROP TABLE t1; +# End of 10.2 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 0b5cdf95daf..91b2cc6f981 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -435,3 +435,28 @@ show create table t2; drop table t1, t2; --echo End of 5.5 tests + +--echo # +--echo # Beginning of 10.2 test +--echo # +--echo # MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same +--echo # table: rows are counted twice +--echo # + +CREATE TABLE t1(a TINYINT); + +INSERT INTO t1 VALUES (1), (100); + +INSERT INTO t1 SELECT a*2 FROM t1; + +TRUNCATE TABLE t1; + +--echo # using ORDER BY + +INSERT INTO t1 VALUES(1), (2), (100), (3); + +INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; + +DROP TABLE t1; + +--echo # End of 10.2 test diff --git a/mysql-test/main/mysql_client_test-master.opt b/mysql-test/main/mysql_client_test-master.opt index 8d49abf6a10..e3d42121b45 100644 --- a/mysql-test/main/mysql_client_test-master.opt +++ b/mysql-test/main/mysql_client_test-master.opt @@ -3,3 +3,4 @@ --log-output=FILE,TABLE --max-allowed-packet=32000000 --proxy-protocol-networks=* +--sequence=on diff --git a/mysql-test/main/mysql_client_test_comp-master.opt b/mysql-test/main/mysql_client_test_comp-master.opt index 6c4a5e4c782..ccaa4a8ee6e 100644 --- a/mysql-test/main/mysql_client_test_comp-master.opt +++ b/mysql-test/main/mysql_client_test_comp-master.opt @@ -1,3 +1,4 @@ --loose-enable-performance-schema --max-allowed-packet=32000000 --proxy-protocol-networks=::1/32,127.0.0.0/8,localhost +--sequence=on diff --git a/mysql-test/main/mysql_client_test_nonblock-master.opt b/mysql-test/main/mysql_client_test_nonblock-master.opt index a39d0089562..f306d27feac 100644 --- a/mysql-test/main/mysql_client_test_nonblock-master.opt +++ b/mysql-test/main/mysql_client_test_nonblock-master.opt @@ -1,3 +1,4 @@ --general-log --general-log-file=$MYSQLTEST_VARDIR/log/master.log --log-output=FILE,TABLE --max-allowed-packet=32000000 --proxy-protocol-networks=::1,::ffff:127.0.0.1/97,localhost +--sequence=on diff --git a/mysql-test/main/mysql_upgrade-6984.test b/mysql-test/main/mysql_upgrade-6984.test index 9bbfbeb3f87..89ebd46e3ba 100644 --- a/mysql-test/main/mysql_upgrade-6984.test +++ b/mysql-test/main/mysql_upgrade-6984.test @@ -13,6 +13,7 @@ update mysql.user set password=password("foo") where user='root'; +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ --exec $MYSQL_UPGRADE connect(con1,localhost,root,foo,,,); @@ -21,3 +22,6 @@ update mysql.user set password='' where user='root'; flush privileges; # Load event table set global event_scheduler=OFF; + +let MYSQLD_DATADIR= `select @@datadir`; +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index f4c51e17c04..d750f8f91b5 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -47,7 +47,8 @@ test Phase 7/7: Running 'FLUSH PRIVILEGES' OK Run it again - should say already completed -This installation of MySQL is already upgraded to VERSION, use --force if you still need to run mysql_upgrade +This installation of MariaDB is already upgraded to VERSION.There is no need to run mysql_upgrade again for VERSION. +You can use --force if you still want to run mysql_upgrade Force should run it regardless of whether it has been run before Phase 1/7: Checking and upgrading mysql database Processing databases @@ -145,11 +146,12 @@ test Phase 7/7: Running 'FLUSH PRIVILEGES' OK DROP USER mysqltest1@'%'; -Version check failed. Got the following error when calling the 'mysql' command line client +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'mysqltest1'@'localhost' (using password: YES) FATAL ERROR: Upgrade failed Run mysql_upgrade with a non existing server socket -mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client +ERROR 2005 (HY000): Unknown MySQL server host 'not_existing_host' (errno) FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; Phase 1/7: Checking and upgrading mysql database @@ -413,9 +415,12 @@ OK # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR # Run mysql_upgrade with unauthorized access -Version check failed. Got the following error when calling the 'mysql' command line client +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) FATAL ERROR: Upgrade failed +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client +ERROR 1045 (errno): Access denied for user 'root'@'localhost' (using password: YES) +FATAL ERROR: Upgrade failed # # MDEV-4332 Increase username length from 16 characters # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names @@ -868,6 +873,34 @@ GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION GRANT USAGE ON *.* TO `aRole` DROP ROLE `aRole`; FLUSH PRIVILEGES; +# +# MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed +# +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +Looking for 'mysql' as: mysql +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +# +# MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it +# +Looking for 'mysql' as: mysql +Empty or non existent ...mysql_upgrade_info. Assuming mysql_upgrade has to be run! +# +# MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change +# +Looking for 'mysql' as: mysql +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +You can use --force if you still want to run mysql_upgrade +# +# MDEV-27279: mariadb_upgrade check-if-upgrade with major version change +# +Major version upgrade detected from MariaDB to MariaDB . Check required! +Looking for 'mysql' as: mysql +Major version upgrade detected from MariaDB to MariaDB . Check required! End of 10.2 tests # # Ensure that mysql_upgrade correctly sets truncate_versioning_priv diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test index afc7c97f7d0..0e42bb81fd9 100644 --- a/mysql-test/main/mysql_upgrade.test +++ b/mysql-test/main/mysql_upgrade.test @@ -17,7 +17,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; file_exists $MYSQLD_DATADIR/mysql_upgrade_info; --echo Run it again - should say already completed ---replace_regex /upgraded to .*, use/upgraded to VERSION, use/ +--replace_regex /upgraded to [^\n].*/upgraded to VERSION./ /again for [^\n]*/again for VERSION./ --exec $MYSQL_UPGRADE 2>&1 # It should have created a file in the MySQL Servers datadir @@ -126,7 +126,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/mysql_upgrade_info --echo # Running mysql_upgrade with --skip-write-binlog.. ---replace_result $MYSQLTEST_VARDIR var +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ --exec $MYSQL_UPGRADE --skip-write-binlog # mysql_upgrade must have created mysql_upgrade_info file, @@ -140,6 +140,9 @@ let $MYSQLD_DATADIR= `select @@datadir`; --echo Run mysql_upgrade with unauthorized access --error 1 --exec $MYSQL_UPGRADE --skip-verbose --user=root --password=wrong_password 2>&1 +--replace_regex /.*mysqlcheck.*: Got/mysqlcheck: Got/ /\([0-9|-]*\)/(errno)/ +--error 1 +--exec $MYSQL_UPGRADE --skip-verbose --skip-version-check --user=root --password=wrong_password 2>&1 --echo # --echo # MDEV-4332 Increase username length from 16 characters @@ -235,6 +238,7 @@ FLUSH TABLES mysql.user; FLUSH PRIVILEGES; SHOW CREATE TABLE mysql.user; +--replace_result $MYSQLTEST_VARDIR var --exec $MYSQL_UPGRADE --force 2>&1 SHOW CREATE TABLE mysql.user; @@ -279,6 +283,79 @@ SHOW GRANTS; DROP ROLE `aRole`; --exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql FLUSH PRIVILEGES; + +--echo # +--echo # MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed +--echo # + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it +--echo # + +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed + +--replace_regex /'mysql.* as:[^\n]*/'mysql' as: mysql/ /open .* Assuming/open XXX. Assuming/ /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change +--echo # + +# take 3rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.$2.0$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with major version change +--echo # + +# take 2rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.0.$3$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose --remove_file $MYSQLD_DATADIR/mysql_upgrade_info --echo End of 10.2 tests diff --git a/mysql-test/main/mysql_upgrade_noengine.result b/mysql-test/main/mysql_upgrade_noengine.result index 7b3b1610ee0..8bd82dce7af 100644 --- a/mysql-test/main/mysql_upgrade_noengine.result +++ b/mysql-test/main/mysql_upgrade_noengine.result @@ -53,6 +53,7 @@ Warnings: Level Warning Code 1286 Message Unknown storage engine 'ARCHIVE' +# upgrade from 10.1 - engines aren't enabled Phase 1/7: Checking and upgrading mysql database Processing databases mysql @@ -142,6 +143,8 @@ Level Warning Code 1286 Message Unknown storage engine 'ARCHIVE' alter table mysql.user drop column default_role, drop column max_statement_time; +# still upgrade from 10.1 +Major version upgrade detected from MariaDB to MariaDB . Check required! Phase 1/7: Checking and upgrading mysql database Processing databases mysql @@ -231,6 +234,7 @@ Level Warning Code 1286 Message Unknown storage engine 'ARCHIVE' alter table mysql.user drop column default_role, drop column max_statement_time; +# upgrade from 10.0 - engines are enabled Phase 1/7: Checking and upgrading mysql database Processing databases mysql diff --git a/mysql-test/main/mysql_upgrade_noengine.test b/mysql-test/main/mysql_upgrade_noengine.test index 1d65e7ffa1c..cf7b84feff4 100644 --- a/mysql-test/main/mysql_upgrade_noengine.test +++ b/mysql-test/main/mysql_upgrade_noengine.test @@ -28,7 +28,8 @@ uninstall plugin archive; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; -# upgrade from 10.1 - engines aren't enabled +--echo # upgrade from 10.1 - engines aren't enabled +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; @@ -42,7 +43,8 @@ write_file $datadir/mysql_upgrade_info; 10.1.10-MariaDB EOF -# still upgrade from 10.1 +--echo # still upgrade from 10.1 +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; @@ -50,7 +52,8 @@ select table_catalog, table_schema, table_name, table_type, engine, row_format, alter table mysql.user drop column default_role, drop column max_statement_time; remove_file $datadir/mysql_upgrade_info; -# upgrade from 10.0 - engines are enabled +--echo # upgrade from 10.0 - engines are enabled +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; diff --git a/mysql-test/main/mysql_upgrade_view.result b/mysql-test/main/mysql_upgrade_view.result index 813138b57a8..172ef7e4159 100644 --- a/mysql-test/main/mysql_upgrade_view.result +++ b/mysql-test/main/mysql_upgrade_view.result @@ -211,7 +211,7 @@ v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI show create view v4; View Create View character_set_client collation_connection v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -MySQL upgrade detected +MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql @@ -336,7 +336,7 @@ drop view v1,v2,v3,v4; rename table mysql.event to mysql.ev_bk; flush tables; The --upgrade-system-tables option was used, user tables won't be touched. -MySQL upgrade detected +MariaDB upgrade detected Phase 1/7: Checking and upgrading mysql database Processing databases mysql diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index c8f63f881cc..214d848981a 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3485,6 +3485,29 @@ b a 40 1 30 4 drop table t1,t2; +# +# MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string +# +SET sql_mode=''; +SET @@SESSION.max_sort_length=4; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '4' +CREATE TABLE t1 (c TIMESTAMP(1)); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; +SET sql_mode=''; +SET @@SESSION.max_sort_length=1; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '1' +CREATE TEMPORARY TABLE t1 (c DATETIME); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; # End of 10.2 tests # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 08d5982b220..1a0235c5c53 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2309,6 +2309,29 @@ explain (select b,a from t2 order by a limit 3) order by b desc; drop table t1,t2; +--echo # +--echo # MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string +--echo # + +SET sql_mode=''; +SET @@SESSION.max_sort_length=4; +CREATE TABLE t1 (c TIMESTAMP(1)); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; + +SET sql_mode=''; +SET @@SESSION.max_sort_length=1; +CREATE TEMPORARY TABLE t1 (c DATETIME); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; + + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/partition_sp.result b/mysql-test/main/partition_sp.result new file mode 100644 index 00000000000..585e2c7ab9d --- /dev/null +++ b/mysql-test/main/partition_sp.result @@ -0,0 +1,22 @@ +# +# MDEV-8652: Partitioned table creation problem when +# creating from procedure context twice in same session +# +CREATE PROCEDURE p1() +BEGIN +DROP TABLE IF EXISTS t1 ; +CREATE TABLE t1 ( +id INT PRIMARY KEY +) +PARTITION BY RANGE (id) ( +PARTITION P1 VALUES LESS THAN (2), +PARTITION P2 VALUES LESS THAN (3) +); +END | +call p1(); +call p1(); +drop procedure p1; +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/partition_sp.test b/mysql-test/main/partition_sp.test new file mode 100644 index 00000000000..1d3d1d707c7 --- /dev/null +++ b/mysql-test/main/partition_sp.test @@ -0,0 +1,35 @@ +--source include/have_partition.inc + +--echo # +--echo # MDEV-8652: Partitioned table creation problem when +--echo # creating from procedure context twice in same session +--echo # + + +DELIMITER |; + +CREATE PROCEDURE p1() +BEGIN + DROP TABLE IF EXISTS t1 ; + + CREATE TABLE t1 ( + id INT PRIMARY KEY + ) + PARTITION BY RANGE (id) ( + PARTITION P1 VALUES LESS THAN (2), + PARTITION P2 VALUES LESS THAN (3) + ); +END | + +DELIMITER ;| + +call p1(); +call p1(); + +drop procedure p1; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # + diff --git a/mysql-test/main/partition_symlink.result b/mysql-test/main/partition_symlink.result index 90048eb3438..b5a976e3a9e 100644 --- a/mysql-test/main/partition_symlink.result +++ b/mysql-test/main/partition_symlink.result @@ -177,3 +177,97 @@ partition by key (a) (partition p0, partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); Got one of the listed errors +# +# MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory. +# +CREATE TABLE t1 (a INT) +ENGINE = MyISAM +PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +PARTITION p2 VALUES IN (2)); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`a`) +(PARTITION `p0` VALUES IN (0) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + PARTITION `p1` VALUES IN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`a`) +(PARTITION `p0` VALUES IN (0) ENGINE = MyISAM, + PARTITION `p1` VALUES IN (1) ENGINE = MyISAM, + PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) +DROP TABLE t1, t2; +CREATE TABLE t1 ( +ID int(11) NOT NULL, +type int(11)) Engine=MyISAM +PARTITION BY RANGE(ID) +SUBPARTITION BY HASH(type) +( +PARTITION p01 VALUES LESS THAN(100) +(SUBPARTITION s11 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +SUBPARTITION s12 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' + ), +PARTITION p11 VALUES LESS THAN(200) +(SUBPARTITION s21, SUBPARTITION s22), +PARTITION p21 VALUES LESS THAN MAXVALUE +(SUBPARTITION s31 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +SUBPARTITION s32 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' + ) +); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ID` int(11) NOT NULL, + `type` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`ID`) +SUBPARTITION BY HASH (`type`) +(PARTITION `p01` VALUES LESS THAN (100) + (SUBPARTITION `s11` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + SUBPARTITION `s12` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM), + PARTITION `p11` VALUES LESS THAN (200) + (SUBPARTITION `s21` ENGINE = MyISAM, + SUBPARTITION `s22` ENGINE = MyISAM), + PARTITION `p21` VALUES LESS THAN MAXVALUE + (SUBPARTITION `s31` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + SUBPARTITION `s32` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM)) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ID` int(11) NOT NULL, + `type` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`ID`) +SUBPARTITION BY HASH (`type`) +(PARTITION `p01` VALUES LESS THAN (100) + (SUBPARTITION `s11` ENGINE = MyISAM, + SUBPARTITION `s12` ENGINE = MyISAM), + PARTITION `p11` VALUES LESS THAN (200) + (SUBPARTITION `s21` ENGINE = MyISAM, + SUBPARTITION `s22` ENGINE = MyISAM), + PARTITION `p21` VALUES LESS THAN MAXVALUE + (SUBPARTITION `s31` ENGINE = MyISAM, + SUBPARTITION `s32` ENGINE = MyISAM)) +DROP TABLE t1, t2; diff --git a/mysql-test/main/partition_symlink.test b/mysql-test/main/partition_symlink.test index 8f6e837299a..7e09c7d0642 100644 --- a/mysql-test/main/partition_symlink.test +++ b/mysql-test/main/partition_symlink.test @@ -220,3 +220,58 @@ ENGINE = MyISAM partition by key (a) (partition p0, partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); + +--echo # +--echo # MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory. +--echo # +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 (a INT) +ENGINE = MyISAM +PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (0) + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + PARTITION p1 VALUES IN (1) + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + PARTITION p2 VALUES IN (2)); + +CREATE TABLE t2 LIKE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 ( + ID int(11) NOT NULL, + type int(11)) Engine=MyISAM +PARTITION BY RANGE(ID) +SUBPARTITION BY HASH(type) +( + PARTITION p01 VALUES LESS THAN(100) + (SUBPARTITION s11 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + SUBPARTITION s12 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' + ), + PARTITION p11 VALUES LESS THAN(200) + (SUBPARTITION s21, SUBPARTITION s22), + PARTITION p21 VALUES LESS THAN MAXVALUE + (SUBPARTITION s31 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + SUBPARTITION s32 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' + ) +); + +CREATE TABLE t2 LIKE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result index f2349f26571..df111bc05be 100644 --- a/mysql-test/main/range_innodb.result +++ b/mysql-test/main/range_innodb.result @@ -108,3 +108,108 @@ DROP TABLE t0,t1; SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; # End of 10.1 tests +# +# MDEV-27262: Index intersection with full scan over an index +# +CREATE TABLE t1 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +p char(32) DEFAULT NULL, +es tinyint(3) unsigned NOT NULL DEFAULT 0, +er tinyint(3) unsigned NOT NULL DEFAULT 0, +x mediumint(8) unsigned NOT NULL DEFAULT 0, +PRIMARY KEY (id), +INDEX es (es), +INDEX x (x), +INDEX er (er,x), +INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set optimizer_switch='index_merge_sort_intersection=on'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=off'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=on'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id p es er x +14007 foobar 4 2 0 +14008 foobar 4 2 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=off'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id p es er x +14007 foobar 4 2 0 +14008 foobar 4 2 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=default'; +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; +# End of 10.2 tests diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test index 428e5c26e5c..b8a6a7c960c 100644 --- a/mysql-test/main/range_innodb.test +++ b/mysql-test/main/range_innodb.test @@ -116,3 +116,96 @@ SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; --echo # End of 10.1 tests + +--echo # +--echo # MDEV-27262: Index intersection with full scan over an index +--echo # + +--source include/have_sequence.inc + +CREATE TABLE t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + p char(32) DEFAULT NULL, + es tinyint(3) unsigned NOT NULL DEFAULT 0, + er tinyint(3) unsigned NOT NULL DEFAULT 0, + x mediumint(8) unsigned NOT NULL DEFAULT 0, + PRIMARY KEY (id), + INDEX es (es), + INDEX x (x), + INDEX er (er,x), + INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; + +let $q= +SELECT * FROM t1 + WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; + +set optimizer_switch='index_merge_sort_intersection=on'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=off'; +# execution of $q and explain for it led to an assertion failure in 10.4 +# (with the optimizer switch rowid_filter set to 'on') +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +let $q= +SELECT * FROM t1 + WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; + +set optimizer_switch='index_merge_sort_intersection=on'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=off'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=default'; + +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/show.result b/mysql-test/main/show.result index d1b373d8969..4a9e2738346 100644 --- a/mysql-test/main/show.result +++ b/mysql-test/main/show.result @@ -40,5 +40,16 @@ nm varchar(32) YES NULL a int(11) YES NULL drop table t1; # +# MDEV-4621 select returns null for information_schema.statistics.collation field +# +create table t1 (f varchar(64), key(f)); +select index_name, column_name, collation, cardinality from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +index_name column_name collation cardinality +f f A NULL +select index_name, column_name, collation from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +index_name column_name collation +f f A +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/main/show.test b/mysql-test/main/show.test index f2f6efc4e45..9b0b58349d5 100644 --- a/mysql-test/main/show.test +++ b/mysql-test/main/show.test @@ -35,5 +35,13 @@ show fields from test.t1 where field in drop table t1; --echo # +--echo # MDEV-4621 select returns null for information_schema.statistics.collation field +--echo # +create table t1 (f varchar(64), key(f)); +select index_name, column_name, collation, cardinality from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +select index_name, column_name, collation from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 6510eaf77f5..6e8ae437d8b 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8502,6 +8502,34 @@ b-c 0 drop procedure p1| drop function f1| +# +# MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); +CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, KEY idx_c2(c2)); +INSERT INTO t1 (c1, c2) SELECT seq, seq FROM seq_1_to_10000; +INSERT INTO t2 (c1, c2) SELECT seq, seq FROM seq_1_to_20000; +CREATE OR REPLACE PROCEDURE p1() +begin +DECLARE done INT DEFAULT FALSE; +DECLARE a INT; +DECLARE cur1 CURSOR FOR +SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 +WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN cur1; +read_loop: LOOP +FETCH cur1 INTO a; +IF done THEN +LEAVE read_loop; +END IF; +END LOOP; +CLOSE cur1; +END $ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; #End of 10.2 tests # # MDEV-12007 Allow ROW variables as a cursor FETCH target diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index 18317e02f3e..f311baccd08 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -1,4 +1,6 @@ --source include/have_partition.inc +--source include/have_sequence.inc + # # Basic stored PROCEDURE tests # @@ -10045,6 +10047,46 @@ drop procedure p1| drop function f1| delimiter ;| +--echo # +--echo # MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT +--echo # + +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); +CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, KEY idx_c2(c2)); + +INSERT INTO t1 (c1, c2) SELECT seq, seq FROM seq_1_to_10000; +INSERT INTO t2 (c1, c2) SELECT seq, seq FROM seq_1_to_20000; + +--delimiter $ + +CREATE OR REPLACE PROCEDURE p1() +begin + DECLARE done INT DEFAULT FALSE; + DECLARE a INT; + + DECLARE cur1 CURSOR FOR + SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 + WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; + + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; + + OPEN cur1; + read_loop: LOOP + FETCH cur1 INTO a; + IF done THEN + LEAVE read_loop; + END IF; + END LOOP; + CLOSE cur1; +END $ + +--delimiter ; + +CALL p1(); + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; --echo #End of 10.2 tests diff --git a/mysql-test/main/trigger.result b/mysql-test/main/trigger.result index 912b3383adf..49b8b9977fe 100644 --- a/mysql-test/main/trigger.result +++ b/mysql-test/main/trigger.result @@ -2424,6 +2424,13 @@ CREATE TRIGGER t1_trigger BEFORE INSERT ON t1 FOR EACH ROW BEGIN END; INSERT INTO t1 () VALUES (); DROP TABLE t1; # +# Bug#33141958 - THE FIRST ASAN UAF ISSUE OF MYSQL SERVER +# +create table t1 (a int); +create trigger tr1 after insert on t1 for each row alter table t1 tablespace s2; +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/trigger.test b/mysql-test/main/trigger.test index 581015a45e6..56e9246b95c 100644 --- a/mysql-test/main/trigger.test +++ b/mysql-test/main/trigger.test @@ -2757,6 +2757,14 @@ INSERT INTO t1 () VALUES (); DROP TABLE t1; --echo # +--echo # Bug#33141958 - THE FIRST ASAN UAF ISSUE OF MYSQL SERVER +--echo # +create table t1 (a int); +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger tr1 after insert on t1 for each row alter table t1 tablespace s2; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/type_enum.result b/mysql-test/main/type_enum.result index 02d71c6bd9f..58c723ac1eb 100644 --- a/mysql-test/main/type_enum.result +++ b/mysql-test/main/type_enum.result @@ -2225,6 +2225,27 @@ a DROP TABLE t1; # +# MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column +# +CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E'); +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +res +a +a +SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +res +a +a +DROP TABLE IF EXISTS t1,t2; +# +# End of 10.2. tests +# +# # Start of 10.3 tests # # diff --git a/mysql-test/main/type_enum.test b/mysql-test/main/type_enum.test index a79335960bc..5c461922166 100644 --- a/mysql-test/main/type_enum.test +++ b/mysql-test/main/type_enum.test @@ -458,6 +458,26 @@ SELECT * FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column +--echo # + +CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +# without the following insert the bug doesn't show, was fixed in MDEV-6978 +INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E'); +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +DROP TABLE IF EXISTS t1,t2; + + +--echo # +--echo # End of 10.2. tests +--echo # + +--echo # --echo # Start of 10.3 tests --echo # diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 34dd8f3c8dc..a410ab741f5 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6777,49 +6777,6 @@ sum(z) DROP TABLE t1; DROP VIEW v1; # -# MDEV-24454: Crash at change_item_tree -# -CREATE TABLE t1(f0 INT); -CREATE VIEW v1 AS -SELECT -f0 AS f1 -FROM t1; -CREATE VIEW v2 AS -SELECT -(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') -FROM v1 n) AS f2, -GROUP_CONCAT('' SEPARATOR ', ') AS f3 -FROM v1; -CREATE VIEW v3 AS -SELECT 1 as f4 FROM v2; -CREATE PROCEDURE p1() -SELECT * FROM v3; -CALL p1(); -f4 -1 -CALL p1(); -f4 -1 -drop procedure p1; -drop view v1,v2,v3; -drop table t1; -# -# MDEV-25631: Crash in st_select_lex::mark_as_dependent with -# VIEW, aggregate and subquery -# -CREATE TABLE t1 (i1 int); -insert into t1 values (1),(2),(3); -CREATE VIEW v1 AS -SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -ERROR 21000: Subquery returns more than 1 row -delete from t1 where i1 > 1; -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -1 -1 -drop view v1; -drop table t1; -# # MDEV-26299: Some views force server (and mysqldump) to generate # invalid SQL for their definitions # @@ -6831,6 +6788,24 @@ drop view v1; CREATE VIEW v1 AS select `t1`.`12345678901234567890123456789012345678901234567890123456789012345` AS `Name_exp_1` from (select '12345678901234567890123456789012345678901234567890123456789012345') `t1`; drop view v1; # +# MDEV-25631: view with outer reference in select used +# as argument of set function +# +create table t1 (c int); +insert into t1 values (1); +create view v1 as select c from t1 where (select t1.c from t1 t) = 1; +select * from (select sum((select * from v1)) as r) dt; +r +1 +with cte as (select c from t1 where (select t1.c from t1 t) = 1) +select * from (select sum((select * from cte)) as r) dt1 +union +select * from (select sum((select * from cte)) as r) dt2; +r +1 +drop view v1; +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 7dd5f63396e..431dfdb86f6 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6500,56 +6500,6 @@ DROP TABLE t1; DROP VIEW v1; --echo # ---echo # MDEV-24454: Crash at change_item_tree ---echo # - -CREATE TABLE t1(f0 INT); - -CREATE VIEW v1 AS -SELECT - f0 AS f1 -FROM t1; - -CREATE VIEW v2 AS -SELECT - (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') - FROM v1 n) AS f2, - GROUP_CONCAT('' SEPARATOR ', ') AS f3 -FROM v1; - -CREATE VIEW v3 AS -SELECT 1 as f4 FROM v2; - -CREATE PROCEDURE p1() - SELECT * FROM v3; - -CALL p1(); -CALL p1(); - -drop procedure p1; -drop view v1,v2,v3; -drop table t1; - ---echo # ---echo # MDEV-25631: Crash in st_select_lex::mark_as_dependent with ---echo # VIEW, aggregate and subquery ---echo # - -CREATE TABLE t1 (i1 int); -insert into t1 values (1),(2),(3); #not important -CREATE VIEW v1 AS - SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); - ---error ER_SUBQUERY_NO_1_ROW -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -delete from t1 where i1 > 1; -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; - -drop view v1; -drop table t1; - - ---echo # --echo # MDEV-26299: Some views force server (and mysqldump) to generate --echo # invalid SQL for their definitions --echo # @@ -6565,9 +6515,29 @@ drop view v1; eval CREATE VIEW v1 AS $definition; + drop view v1; --echo # +--echo # MDEV-25631: view with outer reference in select used +--echo # as argument of set function +--echo # + +create table t1 (c int); +insert into t1 values (1); +create view v1 as select c from t1 where (select t1.c from t1 t) = 1; + +select * from (select sum((select * from v1)) as r) dt; + +with cte as (select c from t1 where (select t1.c from t1 t) = 1) +select * from (select sum((select * from cte)) as r) dt1 +union +select * from (select sum((select * from cte)) as r) dt2; + +drop view v1; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # |