diff options
Diffstat (limited to 'mysql-test')
35 files changed, 684 insertions, 75 deletions
diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index ade8a4f7549..e57d2ef1dcf 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -230,7 +230,7 @@ insert into t2 (a) values (1023); do (f2(23)); Warnings: Error 1062 Duplicate entry '23' for key 'a' -Note 4092 At line 4 in test.f2 +Note 4093 At line 4 in test.f2 select * from t2; a 1023 diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result index 79e0bdf5e20..c880df7b39f 100644 --- a/mysql-test/r/create_drop_binlog.result +++ b/mysql-test/r/create_drop_binlog.result @@ -160,7 +160,7 @@ Note 1050 Table 'v1' already exists DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info # # Format_desc 1 # VER diff --git a/mysql-test/r/create_drop_view.result b/mysql-test/r/create_drop_view.result index d23b9b713ad..8dc10297bdb 100644 --- a/mysql-test/r/create_drop_view.result +++ b/mysql-test/r/create_drop_view.result @@ -55,5 +55,5 @@ id DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' DROP TABLE t1; diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index d0e42cf4042..96884e848ba 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1147,3 +1147,61 @@ SELECT * FROM cte_test; a 1 DROP VIEW cte_test; +# +# MDEV-13453: privileges checking for CTE +# +create database db; +use db; +create table t1 (i int); +insert into t1 +values (3), (7), (1), (4), (2), (3), (1); +create table t2 (a int, b int); +insert into t2 +values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; +connect con1,localhost,foo,,; +use db; +with cte as (select * from t1 where i < 4) +select * from cte; +i +3 +1 +2 +3 +1 +with cte as (select * from t1 where i < 4 group by i) +select * from cte; +i +1 +2 +3 +with cte as (select * from t1 where i < 4) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select * from t1 where i < 4 group by i) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select b from t2 where a < 4) +select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2' +with cte as (select a from t2 where a < 4) +select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; +a +1 +3 +connection default; +revoke SELECT on db.t1 from foo@localhost; +connection con1; +with cte as (select * from t1 where i < 4) +select * from cte; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' +disconnect con1; +connection default; +drop database db; +drop user foo@localhost; diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 3fd5370f470..37005fb017e 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -209,10 +209,10 @@ Note 1051 Unknown table 'test.table1' Note 1051 Unknown table 'test.table2' DROP VIEW IF EXISTS view1,view2,view3,view4; Warnings: -Note 4090 Unknown VIEW: 'test.view1' -Note 4090 Unknown VIEW: 'test.view2' -Note 4090 Unknown VIEW: 'test.view3' -Note 4090 Unknown VIEW: 'test.view4' +Note 4091 Unknown VIEW: 'test.view1' +Note 4091 Unknown VIEW: 'test.view2' +Note 4091 Unknown VIEW: 'test.view3' +Note 4091 Unknown VIEW: 'test.view4' # Test error message when trigger does not find table CREATE TABLE table1(a int); diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 15e4fbec605..25d47574b59 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -28,6 +28,9 @@ NULL select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); json_value('{"key1": [1,2,3], "key1":123}', '$.key1') 123 +select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z'); +JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') +Mon"t"y select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2') NULL @@ -725,6 +728,9 @@ json_contains_path('{"foo":"bar"}', 'one', '$[]') NULL Warnings: Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3 +select JSON_VALID(0x36f0c8dccd83c5eac156da); +JSON_VALID(0x36f0c8dccd83c5eac156da) +0 # # Start of 10.3 tests # diff --git a/mysql-test/r/get_diagnostics.result b/mysql-test/r/get_diagnostics.result index a75b775297c..63ed6867096 100644 --- a/mysql-test/r/get_diagnostics.result +++ b/mysql-test/r/get_diagnostics.result @@ -590,7 +590,7 @@ DROP PROCEDURE p1; SHOW WARNINGS; Level Code Message Error 54321 MESSAGE_TEXT text -Note 4092 At line 16 in test.p1 +Note 4093 At line 16 in test.p1 CREATE PROCEDURE p1() BEGIN DECLARE var INT; diff --git a/mysql-test/r/gis-json.result b/mysql-test/r/gis-json.result index d888b08351d..1d6e2193fc9 100644 --- a/mysql-test/r/gis-json.result +++ b/mysql-test/r/gis-json.result @@ -89,6 +89,21 @@ ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 1) SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5); ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5) {"bbox": [10, 11, 10, 11], "type": "Point", "coordinates": [10, 11]} +SELECT st_astext(st_geomfromgeojson('{"type": "MultiLineString","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. +SELECT st_astext(st_geomfromgeojson('{"type": "Polygon","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. +SELECT st_astext(st_geomfromgeojson('{"type": "MultiPolygon","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. # # End of 10.2 tests # diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 5b239f09172..d2118fb56fe 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1428,7 +1428,7 @@ Warnings: Note 1305 FUNCTION test.test_function does not exist drop view if exists v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' create table test (col1 varchar(30)); create function test_function() returns varchar(30) begin diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index f43e6ce18af..5a9f2fae1e0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3159,3 +3159,51 @@ pk 2 3 DROP TABLE t1; +# +# MDEV-13994: Bad join results with orderby_uses_equalities=on +# +CREATE TABLE books ( +id int(16) NOT NULL AUTO_INCREMENT, +library_id int(16) NOT NULL DEFAULT 0, +wings_id int(12) NOT NULL DEFAULT 0, +scheduled_for_removal int(1) DEFAULT 0, +PRIMARY KEY (id), +KEY library_idx (library_id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); +CREATE TABLE wings ( +id int(11) NOT NULL AUTO_INCREMENT, +department_id int(11) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO wings VALUES (505,11745),(707,11768); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='orderby_uses_equalities=off'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +SET optimizer_switch='orderby_uses_equalities=on'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +explain extended SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary; Using filesort +1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 +2 MATERIALIZED books ref library_idx library_idx 4 const 1 100.00 Using where +Warnings: +Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` +set optimizer_switch= @save_optimizer_switch; +DROP TABLE books, wings; diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 4f59a2f8c20..3ff1f92e94a 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -48,3 +48,76 @@ where key1<3 or key2<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using sort_union(key1,key2); Using where drop table t0, t1; +# +# MDEV-14071: wrong results with orderby_uses_equalities=on +# (duplicate of MDEV-13994) +# +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), +(381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1); +INSERT INTO t1 VALUES +(77,4,0),(86,7,0),(96,6,0),(96,7,0),(99,9,0),(99,10,0),(99,11,0),(104,4,0), +(106,5,0),(148,6,0),(177,6,0),(181,5,0),(188,8,0),(218,8,0),(253,7,0), +(268,4,0),(338,4,0),(409,7,0),(466,8,0),(469,8,0),(498,8,0),(656,8,0); +INSERT INTO t2 VALUES +(127,7),(188,8),(188,9),(206,6),(218,8),(218,9),(292,7),(338,4),(338,5), +(375,6),(381,5),(409,7),(409,8),(466,8),(466,9),(469,8),(469,9),(498,8), +(498,9),(656,8),(656,9); +INSERT INTO t3 VALUES +(4,'four'),(5,'five'),(6,'six'),(7,'seven'),(8,'eight'),(9,'nine'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='orderby_uses_equalities=off'; +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SET optimizer_switch='orderby_uses_equalities=on'; +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 9644a8afe8d..bc8dd162481 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -415,7 +415,7 @@ select @@profiling; drop table if exists t1, t2, t3; drop view if exists v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' drop function if exists f1; set session profiling = OFF; set global profiling_history_size= @start_value; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 4cb5bb79982..f1a62d88111 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3388,7 +3388,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` bigint(20) DEFAULT NULL + `c1` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = b'10100100101'; @a @a = b'10100100101' @@ -3478,7 +3478,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` bigint(20) DEFAULT NULL + `c1` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = 2010; @a @a = 2010 @@ -4992,3 +4992,85 @@ ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NE EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger DROP PROCEDURE p1; +# +# MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) +# +SET NAMES utf8; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; +CHARSET(CONCAT(5,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; +COERCIBILITY(?) +5 +# +# MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL +# +CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; diff --git a/mysql-test/r/signal.result b/mysql-test/r/signal.result index 671df4b7f17..09493f0da30 100644 --- a/mysql-test/r/signal.result +++ b/mysql-test/r/signal.result @@ -1715,7 +1715,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1740,7 +1740,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1789,7 +1789,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1814,7 +1814,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1863,7 +1863,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1888,7 +1888,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1931,7 +1931,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to a not found -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1953,7 +1953,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to an error -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2004,7 +2004,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to a not found -Note 4092 At line 10 in test.test_resignal +Note 4093 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2030,7 +2030,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to an error -Note 4092 At line 10 in test.test_resignal +Note 4093 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2073,7 +2073,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to a not found -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2095,7 +2095,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to an error -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ # # More complex cases @@ -2142,7 +2142,7 @@ ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 9999 Hi, I am a useless error message -Note 4092 At line 7 in test.peter_p2 +Note 4093 At line 7 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ CREATE PROCEDURE peter_p1 () @@ -2198,16 +2198,16 @@ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4092 At line 8 in test.peter_p1 +Note 4093 At line 8 in test.peter_p1 ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4092 At line 8 in test.peter_p1 +Note 4093 At line 8 in test.peter_p1 Error 9999 Hi, I am a useless error message -Note 4092 At line 10 in test.peter_p2 +Note 4093 At line 10 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ drop procedure if exists peter_p3 $$ @@ -2225,7 +2225,7 @@ show warnings $$ Level Code Message Error 1 Original Error 2 Original -Note 4092 At line 4 in test.peter_p3 +Note 4093 At line 4 in test.peter_p3 drop procedure peter_p3 $$ drop table t_warn; drop table t_cursor; diff --git a/mysql-test/r/signal_demo3.result b/mysql-test/r/signal_demo3.result index a98d587937c..1d597aaf71c 100644 --- a/mysql-test/r/signal_demo3.result +++ b/mysql-test/r/signal_demo3.result @@ -79,23 +79,23 @@ show warnings; Level Code Message Error 1051 Unknown table 'demo.oops_it_is_not_here' Error 1644 Oops in proc_9 -Note 4092 At line 4 in demo.proc_9 +Note 4093 At line 4 in demo.proc_9 Error 1644 Oops in proc_8 -Note 4092 At line 4 in demo.proc_8 +Note 4093 At line 4 in demo.proc_8 Error 1644 Oops in proc_7 -Note 4092 At line 4 in demo.proc_7 +Note 4093 At line 4 in demo.proc_7 Error 1644 Oops in proc_6 -Note 4092 At line 4 in demo.proc_6 +Note 4093 At line 4 in demo.proc_6 Error 1644 Oops in proc_5 -Note 4092 At line 4 in demo.proc_5 +Note 4093 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 5; SELECT @@session.max_error_count; @@session.max_error_count @@ -104,11 +104,11 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 7; SELECT @@session.max_error_count; @@session.max_error_count @@ -117,13 +117,13 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 9; SELECT @@session.max_error_count; @@session.max_error_count @@ -132,15 +132,15 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_5 +Note 4093 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 drop database demo; SET @@global.max_error_count = @start_global_value; SELECT @@global.max_error_count; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index f0bc1874850..40643a97765 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1990,8 +1990,8 @@ Warning 1264 Out of range value for column 'a' at row 1 Note 1292 Truncated incorrect INTEGER value: '222222 ' Warning 1264 Out of range value for column 'b' at row 1 Error 1048 Column 'c' cannot be null -Note 4092 At line 6 in test.t1_bi -Note 4092 At line 2 in test.p1 +Note 4093 At line 6 in test.t1_bi +Note 4093 At line 2 in test.p1 DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/r/sp-group.result b/mysql-test/r/sp-group.result index 535e67046d8..800d83f1f74 100644 --- a/mysql-test/r/sp-group.result +++ b/mysql-test/r/sp-group.result @@ -3,7 +3,7 @@ Warnings: Note 1051 Unknown table 'test.t1' drop view if exists view_t1; Warnings: -Note 4090 Unknown VIEW: 'test.view_t1' +Note 4091 Unknown VIEW: 'test.view_t1' SET sql_mode=ONLY_FULL_GROUP_BY; CREATE TABLE t1 ( pk INT, diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index f05f5105aa7..3891423f53e 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -3211,7 +3211,7 @@ drop procedure bug10961| DROP PROCEDURE IF EXISTS bug6866| DROP VIEW IF EXISTS tv| Warnings: -Note 4090 Unknown VIEW: 'test.tv' +Note 4091 Unknown VIEW: 'test.tv' DROP TABLE IF EXISTS tt1,tt2,tt3| Warnings: Note 1051 Unknown table 'test.tt1' @@ -7823,7 +7823,7 @@ ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show warnings; Level Code Message Error 1062 Duplicate entry '2' for key 'PRIMARY' -Note 4092 At line 5 in test.p1 +Note 4093 At line 5 in test.p1 select * from t1; id 1 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 6eead303c7a..d7fb05c9847 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5166,7 +5166,7 @@ CREATE TABLE t4 (i4 INT); INSERT INTO t4 VALUES (1),(2); DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); CREATE VIEW v2 AS select v1_field1 from t4 join v1; prepare my_stmt from "select v1_field1 from v2"; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 5d805ac572a..d808479e8ef 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -353,7 +353,7 @@ ERROR 23000: Duplicate entry '11' for key 'a' SHOW WARNINGS; Level Code Message -Note 4092 At line 4 in test.f1 +Note 4093 At line 4 in test.f1 Error 1062 Duplicate entry '11' for key 'a' DROP TABLE t1; diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 5a27cb65b3d..93fd45dc9f9 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -4314,7 +4314,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7566,7 +7566,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21312,7 +21312,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index a2af9082c72..1b167b611df 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -4315,7 +4315,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7567,7 +7567,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21314,7 +21314,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index 2c86e6c3e73..f3666db164b 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -4477,7 +4477,7 @@ CREATE PROCEDURE sp1() for:BEGIN SELECT @x; END// -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for:BEGIN +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':BEGIN SELECT @x; END' at line 2 DROP PROCEDURE IF EXISTS sp1; diff --git a/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result new file mode 100644 index 00000000000..474ebf33bbd --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result @@ -0,0 +1,60 @@ +set sql_mode=''; +set innodb_strict_mode=0; +CREATE TABLE `v` ( +`id` int(11) unsigned NOT NULL AUTO_INCREMENT, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT v values (1); +CREATE TABLE `vp` ( +`id` int(11) unsigned NOT NULL AUTO_INCREMENT, +`v_id` int(10) unsigned NOT NULL, +`p_id` int(10) unsigned NOT NULL, +`ppp` varchar(255) NOT NULL, +PRIMARY KEY (`id`), +UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`), +KEY `FK_vp_v` (`v_id`), +CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT vp VALUES (12, 1, 100, 'text12'); +INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`); +Warnings: +Warning 1364 Field 'v_id' doesn't have a default value +Warning 1364 Field 'p_id' doesn't have a default value +SELECT * FROM vp; +id v_id p_id ppp +12 1 100 test12-2 +DROP TABLE vp, v; +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); +CREATE TABLE t2 ( +i int not null primary key, +vi int not null, +m int, +UNIQUE KEY (vi), +CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +Warnings: +Warning 1364 Field 'vi' doesn't have a default value +SELECT * FROM t2; +i vi m +1 1 3 +DROP TABLE t2,t1; +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); +CREATE TABLE t2 ( +i int not null primary key, +vi int not null, +m int, +KEY (vi), +CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +Warnings: +Warning 1364 Field 'vi' doesn't have a default value +SELECT * FROM t2; +i vi m +1 1 3 +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test new file mode 100644 index 00000000000..cc80198d24a --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test @@ -0,0 +1,63 @@ +--source include/have_innodb.inc + +# +# MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail +# +set sql_mode=''; +set innodb_strict_mode=0; + +CREATE TABLE `v` ( + `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT v values (1); + +CREATE TABLE `vp` ( + `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + `v_id` int(10) unsigned NOT NULL, + `p_id` int(10) unsigned NOT NULL, + `ppp` varchar(255) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`), + KEY `FK_vp_v` (`v_id`), + CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT vp VALUES (12, 1, 100, 'text12'); +INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`); +SELECT * FROM vp; +DROP TABLE vp, v; + +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); + +CREATE TABLE t2 ( + i int not null primary key, + vi int not null, + m int, + UNIQUE KEY (vi), + CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +SELECT * FROM t2; + +DROP TABLE t2,t1; + +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); + +CREATE TABLE t2 ( + i int not null primary key, + vi int not null, + m int, + KEY (vi), + CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +SELECT * FROM t2; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_view.result b/mysql-test/suite/rpl/r/rpl_create_drop_view.result index ebbe9efc9df..436aa7bc236 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_view.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_view.result @@ -99,7 +99,7 @@ DROP VIEW v1; ERROR 42S02: Unknown VIEW: 'test.v1' DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' # Syncing slave with master connection slave; SELECT * FROM v1; diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index 2849e5b7ae3..fbd81cb7146 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -128,7 +128,7 @@ show warnings; Level Code Message Error 1062 Duplicate entry '20' for key 'a' Warning 1196 Some non-transactional changed tables couldn't be rolled back -Note 4092 At line 4 in mysqltest1.foo4 +Note 4093 At line 4 in mysqltest1.foo4 select * from t2; a 20 @@ -291,7 +291,7 @@ end| do fn1(100); Warnings: Error 1062 Duplicate entry '100' for key 'a' -Note 4092 At line 3 in mysqltest1.fn1 +Note 4093 At line 3 in mysqltest1.fn1 Warning 1196 Some non-transactional changed tables couldn't be rolled back select fn1(20); ERROR 23000: Duplicate entry '20' for key 'a' diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 0400843c98a..a1789f02718 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -212,7 +212,7 @@ ERROR 42S02: 'test.t1' is not a SEQUENCE drop table t1; alter sequence if exists t1 minvalue=100; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' alter sequence t1 minvalue=100; ERROR 42S02: Table 'test.t1' doesn't exist create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 0a44dfe8931..b9394da6fcd 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -165,7 +165,7 @@ drop sequence t1; ERROR 42S02: 'test.t1' is not a SEQUENCE drop sequence if exists t1; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' create sequence t1 start with 10 maxvalue=9; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 minvalue= 100 maxvalue=10; @@ -377,7 +377,7 @@ key key1 (next_not_cached_value) ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys) drop sequence if exists t1; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' create sequence t1; create sequence t2; create table t3 (a int) engine=myisam; @@ -387,8 +387,8 @@ CREATE SEQUENCE s1; drop sequence s1; drop sequence if exists t1,t2,t3,t4; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t3' -Note 4089 Unknown SEQUENCE: 'test.t4' +Note 4090 Unknown SEQUENCE: 'test.t3' +Note 4090 Unknown SEQUENCE: 'test.t4' drop table if exists t1,t2,t3; Warnings: Note 1051 Unknown table 'test.t1' @@ -414,9 +414,9 @@ CREATE TABLE t2 (a int); CREATE SEQUENCE s1; drop sequence if exists t1,t2,s1,s2; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' -Note 4089 Unknown SEQUENCE: 'test.t2' -Note 4089 Unknown SEQUENCE: 'test.s2' +Note 4090 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t2' +Note 4090 Unknown SEQUENCE: 'test.s2' drop table if exists t1,t2; CREATE TEMPORARY SEQUENCE s1; DROP SEQUENCE s1; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 742e8f6e4d7..03e6e298fa8 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -790,3 +790,54 @@ SHOW CREATE VIEW cte_test; SELECT * FROM cte_test; DROP VIEW cte_test; + +--echo # +--echo # MDEV-13453: privileges checking for CTE +--echo # + +create database db; +use db; +create table t1 (i int); +insert into t1 + values (3), (7), (1), (4), (2), (3), (1); + +create table t2 (a int, b int); +insert into t2 + values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); + +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; + +--connect (con1,localhost,foo,,) +use db; +with cte as (select * from t1 where i < 4) + select * from cte; +with cte as (select * from t1 where i < 4 group by i) + select * from cte; +with cte as (select * from t1 where i < 4) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +with cte as (select * from t1 where i < 4 group by i) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; + +--error ER_COLUMNACCESS_DENIED_ERROR +with cte as (select b from t2 where a < 4) + select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +with cte as (select a from t2 where a < 4) + select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; + +--connection default +revoke SELECT on db.t1 from foo@localhost; + +--connection con1 + +--error ER_TABLEACCESS_DENIED_ERROR +with cte as (select * from t1 where i < 4) + select * from cte; + +# Cleanup +--disconnect con1 + +--connection default +drop database db; +drop user foo@localhost; diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 47ed0c3ca75..5a5d06d456d 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -9,6 +9,7 @@ select json_value('{"key1":123}', '$.key2'); select json_value('{"key1":123}', '$.key1'); select json_value('{"key1":[1,2,3]}', '$.key1'); select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); +select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z'); select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1'); @@ -377,6 +378,11 @@ SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; select json_contains_path('{"foo":"bar"}', 'one', '$[]'); +# +# MDEV-13971 crash in skip_num_constant. +# +select JSON_VALID(0x36f0c8dccd83c5eac156da); + --echo # --echo # Start of 10.3 tests --echo # diff --git a/mysql-test/t/gis-json.test b/mysql-test/t/gis-json.test index 5e695fbca9c..b91ef235fd0 100644 --- a/mysql-test/t/gis-json.test +++ b/mysql-test/t/gis-json.test @@ -40,6 +40,10 @@ SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(5.363 7.266)'),10); SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 1); SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5); +SELECT st_astext(st_geomfromgeojson('{"type": "MultiLineString","coordinates": []}')) as a; +SELECT st_astext(st_geomfromgeojson('{"type": "Polygon","coordinates": []}')) as a; +SELECT st_astext(st_geomfromgeojson('{"type": "MultiPolygon","coordinates": []}')) as a; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 8722401ccae..914911648b2 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2106,3 +2106,46 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT DISTINCT pk FROM t1 GROUP BY 'foo'; SELECT DISTINCT pk FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-13994: Bad join results with orderby_uses_equalities=on +--echo # + +CREATE TABLE books ( + id int(16) NOT NULL AUTO_INCREMENT, + library_id int(16) NOT NULL DEFAULT 0, + wings_id int(12) NOT NULL DEFAULT 0, + scheduled_for_removal int(1) DEFAULT 0, + PRIMARY KEY (id), + KEY library_idx (library_id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); + +CREATE TABLE wings ( + id int(11) NOT NULL AUTO_INCREMENT, + department_id int(11) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT INTO wings VALUES (505,11745),(707,11768); + +let $q= +SELECT wings.id as wing_id, wings.department_id FROM wings + WHERE wings.id IN ( SELECT books.wings_id FROM books + WHERE books.library_id = 8663 AND + books.scheduled_for_removal=0 ) +ORDER BY wings.id; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch='orderby_uses_equalities=off'; +eval $q; + +SET optimizer_switch='orderby_uses_equalities=on'; +eval $q; +eval explain extended $q; + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE books, wings; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 097eddd24f1..0debb777749 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -61,3 +61,50 @@ from t1 where key1<3 or key2<3; drop table t0, t1; + +--echo # +--echo # MDEV-14071: wrong results with orderby_uses_equalities=on +--echo # (duplicate of MDEV-13994) +--echo # + +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), + (381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1); +INSERT INTO t1 VALUES + (77,4,0),(86,7,0),(96,6,0),(96,7,0),(99,9,0),(99,10,0),(99,11,0),(104,4,0), + (106,5,0),(148,6,0),(177,6,0),(181,5,0),(188,8,0),(218,8,0),(253,7,0), + (268,4,0),(338,4,0),(409,7,0),(466,8,0),(469,8,0),(498,8,0),(656,8,0); + +INSERT INTO t2 VALUES + (127,7),(188,8),(188,9),(206,6),(218,8),(218,9),(292,7),(338,4),(338,5), + (375,6),(381,5),(409,7),(409,8),(466,8),(466,9),(469,8),(469,9),(498,8), + (498,9),(656,8),(656,9); +INSERT INTO t3 VALUES + (4,'four'),(5,'five'),(6,'six'),(7,'seven'),(8,'eight'),(9,'nine'); + +let $q1= +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +let $q2= +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch='orderby_uses_equalities=off'; +eval $q1; +eval $q2; + +SET optimizer_switch='orderby_uses_equalities=on'; +eval $q1; +eval $q2; + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index b61c4cf2568..a7683b5aae6 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -4476,3 +4476,56 @@ EXECUTE IMMEDIATE 'CALL p1(?)' USING DEFAULT; --error ER_SP_NOT_VAR_ARG EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) +--echo # + +SET NAMES utf8; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; + +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; + +--echo # +--echo # MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL +--echo # + +CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; |