diff options
Diffstat (limited to 'mysql-test/suite')
28 files changed, 381 insertions, 39 deletions
diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test index 0139b9fb817..1bd69365f68 100644 --- a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test +++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test @@ -30,8 +30,10 @@ insert into t8 values (1, 'publicmessage'); insert into t9 values (1, 'pugliccompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc'); --echo # should list tables t1-t6 +--sorted_result SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 AND NAME LIKE 'enctests%'; --echo # should list tables t7-t9 +--sorted_result SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 and NAME LIKE 'enctests%'; --let $MYSQLD_DATADIR=`select @@datadir` diff --git a/mysql-test/suite/encryption/t/innodb_encryption_is.test b/mysql-test/suite/encryption/t/innodb_encryption_is.test index 52574aa2b9d..9e15cebb1da 100644 --- a/mysql-test/suite/encryption/t/innodb_encryption_is.test +++ b/mysql-test/suite/encryption/t/innodb_encryption_is.test @@ -9,6 +9,7 @@ INSERT INTO t2 VALUES ('foobar'); # # MDEV-9640: Add used key_id to INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION # +--sorted_result SELECT NAME, ENCRYPTION_SCHEME, MIN_KEY_VERSION, CURRENT_KEY_VERSION, CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result index 1cd2a62cb56..1534f93b436 100644 --- a/mysql-test/suite/engines/iuds/r/update_delete_number.result +++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result @@ -739,8 +739,21 @@ c1 c2 c3 c1 c2 c3 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +DELETE FROM t1,t2 using t1,t2 +where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +SELECT * FROM t1; +c1 c2 c3 +1 127 3 +254 127 1 +3 NULL 5 +SELECT * FROM t2; +c1 c2 c3 +-1 NULL 5 +127 255 1 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 INT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -981,7 +994,6 @@ create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; -ERROR HY000: Table 'mt1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table mt1,mt2; CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY (`id`)); CREATE TABLE IF NOT EXISTS `mt2` (`ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tsmt1` text, PRIMARY KEY (`ID`), KEY `IX_ParId_mt2` (`ParId`), FOREIGN KEY (`ParId`) REFERENCES `mt1` (`id`)); @@ -1852,7 +1864,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -2599,7 +2610,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -3346,7 +3356,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); @@ -4093,7 +4102,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; c1 c2 c3 c1 c2 c3 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); -ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT); CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT); INSERT INTO t3 VALUES(200,126,1),(250,-127,2); diff --git a/mysql-test/suite/engines/iuds/t/update_delete_number.test b/mysql-test/suite/engines/iuds/t/update_delete_number.test index ce3f90140b2..4347d0606ae 100644 --- a/mysql-test/suite/engines/iuds/t/update_delete_number.test +++ b/mysql-test/suite/engines/iuds/t/update_delete_number.test @@ -285,8 +285,18 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); +# After the patch for MDEV-28883 this should not report +# ER_UPDATE_TABLE_USED anymore +DELETE FROM t1,t2 using t1,t2 + where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; # eq-ref join CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); @@ -496,7 +506,7 @@ drop table mt1, mt2, mt3; create table mt1 (col1 int); create table mt2 (col1 int); update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1; --- error ER_UPDATE_TABLE_USED +# -- error ER_UPDATE_TABLE_USED delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1; drop table mt1,mt2; @@ -865,7 +875,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1166,7 +1175,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1467,7 +1475,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join @@ -1768,7 +1775,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; --sorted_result SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; ---error ER_UPDATE_TABLE_USED DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); # eq-ref join diff --git a/mysql-test/suite/funcs_1/r/is_collations.result b/mysql-test/suite/funcs_1/r/is_collations.result index 979b477b8bc..6007742a2a1 100644 --- a/mysql-test/suite/funcs_1/r/is_collations.result +++ b/mysql-test/suite/funcs_1/r/is_collations.result @@ -66,7 +66,7 @@ INSERT INTO information_schema.collations (collation_name,character_set_name,id,is_default,is_compiled,sortlen) VALUES ( 'cp1251_bin', 'cp1251',50, '', '',0); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' -UPDATE information_schema.collations SET description = 'just updated'; +UPDATE information_schema.collations SET collation_name = 'just updated'; Got one of the listed errors DELETE FROM information_schema.collations WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' diff --git a/mysql-test/suite/funcs_1/t/is_collations.test b/mysql-test/suite/funcs_1/t/is_collations.test index db34a7b77b1..aa199b512bc 100644 --- a/mysql-test/suite/funcs_1/t/is_collations.test +++ b/mysql-test/suite/funcs_1/t/is_collations.test @@ -83,7 +83,7 @@ INSERT INTO information_schema.collations VALUES ( 'cp1251_bin', 'cp1251',50, '', '',0); --error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE -UPDATE information_schema.collations SET description = 'just updated'; +UPDATE information_schema.collations SET collation_name = 'just updated'; --error ER_DBACCESS_DENIED_ERROR DELETE FROM information_schema.collations WHERE table_name = 't1'; diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result index 5be25e62ae2..dfe916bc6cd 100644 --- a/mysql-test/suite/innodb/r/innodb-system-table-view.result +++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result @@ -77,6 +77,9 @@ test/t_dynamic test/t_dynamic 33 5 Dynamic 0 test/t_redundant test/t_redundant 0 5 Redundant 0 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t_redundant DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd test/t_compact DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd test/t_compressed DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index ede62417652..b51a7fb3f6d 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -182,7 +182,7 @@ compress_pages_page_decompressed compression 0 NULL NULL NULL 0 NULL NULL NULL N compress_pages_page_compression_error compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of page compression errors compress_pages_encrypted compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of pages encrypted compress_pages_decrypted compression 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of pages decrypted -index_page_splits index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of index page splits +index_page_splits index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of index page splits index_page_merge_attempts index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of index page merge attempts index_page_merge_successful index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of successful index page merges index_page_reorg_attempts index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of index page reorganization attempts diff --git a/mysql-test/suite/innodb/r/innodb_status_variables.result b/mysql-test/suite/innodb/r/innodb_status_variables.result index dcd8ff4d619..2eeed4f169a 100644 --- a/mysql-test/suite/innodb/r/innodb_status_variables.result +++ b/mysql-test/suite/innodb/r/innodb_status_variables.result @@ -23,6 +23,7 @@ INNODB_BUFFER_POOL_PAGES_OLD INNODB_BUFFER_POOL_PAGES_TOTAL INNODB_BUFFER_POOL_PAGES_LRU_FLUSHED INNODB_BUFFER_POOL_PAGES_LRU_FREED +INNODB_BUFFER_POOL_PAGES_SPLIT INNODB_BUFFER_POOL_READ_AHEAD_RND INNODB_BUFFER_POOL_READ_AHEAD INNODB_BUFFER_POOL_READ_AHEAD_EVICTED diff --git a/mysql-test/suite/innodb/r/insert_into_empty.result b/mysql-test/suite/innodb/r/insert_into_empty.result index eedfb681929..d17f24bd7d5 100644 --- a/mysql-test/suite/innodb/r/insert_into_empty.result +++ b/mysql-test/suite/innodb/r/insert_into_empty.result @@ -214,6 +214,21 @@ WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; TABLE_ROWS AVG_ROW_LENGTH>0 3 1 DROP TABLE t1; +# +# MDEV-29975 InnoDB fails to release savepoint during bulk insert +# +CREATE TABLE t (c INT KEY) ENGINE=InnoDB; +begin; +INSERT INTO t VALUES (0,0); +ERROR 21S01: Column count doesn't match value count at row 1 +SAVEPOINT a; +INSERT INTO t VALUES (0),(0); +ERROR HY000: Got error 1 "Operation not permitted" during COMMIT +SAVEPOINT a; +commit; +SELECT * FROM t; +c +DROP TABLE t; # End of 10.6 tests # # MDEV-26947 UNIQUE column checks fail in InnoDB resulting diff --git a/mysql-test/suite/innodb/r/online_table_rebuild.result b/mysql-test/suite/innodb/r/online_table_rebuild.result index d4bddbc5305..46d9780decb 100644 --- a/mysql-test/suite/innodb/r/online_table_rebuild.result +++ b/mysql-test/suite/innodb/r/online_table_rebuild.result @@ -43,5 +43,25 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`f1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; +# +# MDEV-30183 Assertion `!memcmp(rec_trx_id, old_pk_trx_id->data, +# 6 + 7)' failed in row_log_table_apply_update +# +set @old_sql_mode = @@sql_mode; +set @@sql_mode=""; +CREATE TABLE t1(col_int int, col_varchar varchar(500))ENGINE=InnoDB; +INSERT INTO t1(col_int) values(2560); +set debug_sync="row_log_table_apply1_before SIGNAL con1_begin WAIT_FOR con1_commit"; +ALTER TABLE t1 ADD PRIMARY KEY ( col_varchar); +connection con1; +SET DEBUG_SYNC="now WAIT_FOR con1_begin"; +UPDATE t1 SET col_int = 2178; +INSERT INTO t1(col_int) VALUES(3016); +UPDATE t1 set col_int=2802; +SET DEBUG_SYNC="now SIGNAL con1_commit"; +connection default; +ERROR 23000: Duplicate entry '' for key 'PRIMARY' +DROP TABLE t1; +SET @@sql_mode = @old_sql_mode; disconnect con1; SET DEBUG_SYNC=reset; diff --git a/mysql-test/suite/innodb/r/undo_truncate.result b/mysql-test/suite/innodb/r/undo_truncate.result index b7729020aca..c46dccfbba9 100644 --- a/mysql-test/suite/innodb/r/undo_truncate.result +++ b/mysql-test/suite/innodb/r/undo_truncate.result @@ -2,6 +2,11 @@ SET GLOBAL innodb_fast_shutdown=0; # restart: --innodb_undo_tablespaces=2 SET GLOBAL innodb_undo_log_truncate = 0; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 create table t1(keyc int primary key, c char(100)) engine = innodb; create table t2(keyc int primary key, c char(100)) engine = innodb; connect con1,localhost,root,,; diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test index ee32a2d7cac..8f7eb3a6c1f 100644 --- a/mysql-test/suite/innodb/t/insert_into_empty.test +++ b/mysql-test/suite/innodb/t/insert_into_empty.test @@ -235,6 +235,20 @@ SELECT TABLE_ROWS, AVG_ROW_LENGTH>0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; DROP TABLE t1; +--echo # +--echo # MDEV-29975 InnoDB fails to release savepoint during bulk insert +--echo # +CREATE TABLE t (c INT KEY) ENGINE=InnoDB; +begin; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES (0,0); +SAVEPOINT a; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t VALUES (0),(0); +SAVEPOINT a; +commit; +SELECT * FROM t; +DROP TABLE t; --echo # End of 10.6 tests --echo # diff --git a/mysql-test/suite/innodb/t/online_table_rebuild.test b/mysql-test/suite/innodb/t/online_table_rebuild.test index 1d34738703c..02e9639eae2 100644 --- a/mysql-test/suite/innodb/t/online_table_rebuild.test +++ b/mysql-test/suite/innodb/t/online_table_rebuild.test @@ -59,5 +59,29 @@ connection default; reap; SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # MDEV-30183 Assertion `!memcmp(rec_trx_id, old_pk_trx_id->data, +--echo # 6 + 7)' failed in row_log_table_apply_update +--echo # +set @old_sql_mode = @@sql_mode; +set @@sql_mode=""; +CREATE TABLE t1(col_int int, col_varchar varchar(500))ENGINE=InnoDB; +INSERT INTO t1(col_int) values(2560); +set debug_sync="row_log_table_apply1_before SIGNAL con1_begin WAIT_FOR con1_commit"; +send ALTER TABLE t1 ADD PRIMARY KEY ( col_varchar); + +connection con1; +SET DEBUG_SYNC="now WAIT_FOR con1_begin"; +UPDATE t1 SET col_int = 2178; +INSERT INTO t1(col_int) VALUES(3016); +UPDATE t1 set col_int=2802; +SET DEBUG_SYNC="now SIGNAL con1_commit"; + +connection default; +--error ER_DUP_ENTRY +reap; +DROP TABLE t1; +SET @@sql_mode = @old_sql_mode; disconnect con1; SET DEBUG_SYNC=reset; diff --git a/mysql-test/suite/innodb/t/undo_truncate.opt b/mysql-test/suite/innodb/t/undo_truncate.opt index 1459ec5db74..5e331e832fd 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.opt +++ b/mysql-test/suite/innodb/t/undo_truncate.opt @@ -1,2 +1,3 @@ --innodb-buffer-pool-size=24M --innodb-immediate-scrub-data-uncompressed=ON +--loose-innodb-sys-tablespaces diff --git a/mysql-test/suite/innodb/t/undo_truncate.test b/mysql-test/suite/innodb/t/undo_truncate.test index a5e0a52f798..eeffb6622c1 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.test +++ b/mysql-test/suite/innodb/t/undo_truncate.test @@ -19,6 +19,9 @@ let $restart_parameters="--innodb_undo_tablespaces=2"; SET GLOBAL innodb_undo_log_truncate = 0; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; +LET $MYSQLD_DATADIR = `select @@datadir`; +LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`; +--source suite/innodb/include/show_i_s_tablespaces.inc #----------------------------------------------------------------------------- # # Perform DML action using multiple clients and multiple undo tablespace. diff --git a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff index 0ba8a299ab9..c901bf6dbe0 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff @@ -7,7 +7,7 @@ # Test 3) Query some information_shema tables that are dependent upon # the page size. SELECT t.name table_name, t.n_cols, t.flag table_flags, -@@ -36,13 +36,13 @@ +@@ -36,7 +36,7 @@ table_name n_cols table_flags index_name root_page type n_fields merge_threshold test/t1 5 0 PRIMARY 3 3 1 50 test/t2 5 1 PRIMARY 3 3 1 50 @@ -16,6 +16,8 @@ test/t4 5 33 PRIMARY 3 3 1 50 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +@@ -45,7 +45,7 @@ + innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t1.ibd test/t2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t2.ibd -test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd @@ -23,7 +25,7 @@ test/t4 DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 DROP TABLE t1, t2, t3, t4; -@@ -52,141 +52,90 @@ +@@ -55,141 +55,90 @@ SET SESSION innodb_strict_mode = ON; CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), @@ -211,7 +213,7 @@ ALTER TABLE t1 KEY_BLOCK_SIZE=4; SHOW WARNINGS; Level Code Message -@@ -218,15 +167,21 @@ +@@ -221,15 +170,21 @@ DROP TABLE t1; SET SESSION innodb_strict_mode = OFF; CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; @@ -233,7 +235,7 @@ SELECT table_name, row_format, create_options FROM information_schema.tables WHERE table_name = 't1'; table_name row_format create_options -@@ -272,6 +227,7 @@ +@@ -275,6 +230,7 @@ ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -241,7 +243,7 @@ Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table `test`.`t4` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -@@ -279,107 +235,13 @@ +@@ -282,107 +238,13 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -350,7 +352,7 @@ # Test 8) Test creating a table that could lead to undo log overflow. CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob, h blob,i blob,j blob,k blob,l blob,m blob,n blob, -@@ -394,10 +256,6 @@ +@@ -397,10 +259,6 @@ UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b, k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b; CREATE INDEX t1a ON t1 (a(767)); @@ -361,7 +363,7 @@ UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c, k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c; CREATE INDEX t1f ON t1 (f(767)); -@@ -412,37 +270,15 @@ +@@ -415,37 +273,15 @@ COMMIT; CREATE INDEX t1g ON t1 (g(767)); UPDATE t1 SET g=@e; @@ -403,7 +405,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( -@@ -468,28 +304,12 @@ +@@ -471,28 +307,12 @@ `t` blob DEFAULT NULL, `u` blob DEFAULT NULL, KEY `t1a` (`a`(767)), @@ -434,7 +436,7 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DROP TABLE t1; # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -@@ -574,27 +394,25 @@ +@@ -577,27 +397,25 @@ DROP TABLE t1; CREATE TABLE t1( c text NOT NULL, d text NOT NULL, diff --git a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff index 0d215684e2f..90b2a1f7c48 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff @@ -7,7 +7,7 @@ # Test 3) Query some information_shema tables that are dependent upon # the page size. SELECT t.name table_name, t.n_cols, t.flag table_flags, -@@ -36,13 +36,13 @@ +@@ -36,7 +36,7 @@ table_name n_cols table_flags index_name root_page type n_fields merge_threshold test/t1 5 0 PRIMARY 3 3 1 50 test/t2 5 1 PRIMARY 3 3 1 50 @@ -16,6 +16,8 @@ test/t4 5 33 PRIMARY 3 3 1 50 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +@@ -45,7 +45,7 @@ + innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t1.ibd test/t2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t2.ibd -test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd @@ -23,7 +25,7 @@ test/t4 DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 DROP TABLE t1, t2, t3, t4; -@@ -54,133 +54,97 @@ +@@ -57,133 +57,97 @@ c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), @@ -199,7 +201,7 @@ SHOW WARNINGS; Level Code Message SELECT table_name, row_format, create_options -@@ -218,8 +182,11 @@ +@@ -221,8 +185,11 @@ DROP TABLE t1; SET SESSION innodb_strict_mode = OFF; CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; @@ -211,7 +213,7 @@ SELECT table_name, row_format, create_options FROM information_schema.tables WHERE table_name = 't1'; table_name row_format create_options -@@ -279,107 +246,13 @@ +@@ -282,107 +249,13 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -320,7 +322,7 @@ # Test 8) Test creating a table that could lead to undo log overflow. CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob, h blob,i blob,j blob,k blob,l blob,m blob,n blob, -@@ -394,10 +267,6 @@ +@@ -397,10 +270,6 @@ UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b, k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b; CREATE INDEX t1a ON t1 (a(767)); @@ -331,7 +333,7 @@ UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c, k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c; CREATE INDEX t1f ON t1 (f(767)); -@@ -412,30 +281,6 @@ +@@ -415,30 +284,6 @@ COMMIT; CREATE INDEX t1g ON t1 (g(767)); UPDATE t1 SET g=@e; @@ -362,7 +364,7 @@ CREATE INDEX t1t ON t1 (t(767)); BEGIN; UPDATE t1 SET t=@e; -@@ -468,24 +313,8 @@ +@@ -471,24 +316,8 @@ `t` blob DEFAULT NULL, `u` blob DEFAULT NULL, KEY `t1a` (`a`(767)), @@ -387,7 +389,7 @@ KEY `t1t` (`t`(767)), KEY `t1u` (`u`(767)), KEY `t1ut` (`u`(767),`t`(767)), -@@ -577,14 +406,14 @@ +@@ -580,14 +409,14 @@ PRIMARY KEY (c(767),d(767))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; Warnings: @@ -404,7 +406,7 @@ DROP TABLE t1; CREATE TABLE t1( c text NOT NULL, d text NOT NULL, -@@ -594,7 +423,7 @@ +@@ -597,7 +426,7 @@ CREATE TABLE t1(c text, PRIMARY KEY (c(440))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; Warnings: diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result index 71e8e5461d3..c8d405e912a 100644 --- a/mysql-test/suite/innodb_zip/r/page_size.result +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -40,6 +40,9 @@ test/t3 5 41 PRIMARY 3 3 1 50 test/t4 5 33 PRIMARY 3 3 1 50 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t1.ibd test/t2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t2.ibd test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd diff --git a/mysql-test/suite/innodb_zip/r/restart.result b/mysql-test/suite/innodb_zip/r/restart.result index b57fe1e89e6..133cf020d55 100644 --- a/mysql-test/suite/innodb_zip/r/restart.result +++ b/mysql-test/suite/innodb_zip/r/restart.result @@ -210,6 +210,9 @@ test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0 test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t1_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t1_restart.ibd test/t2_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t2_restart.ibd test/t3_restart DEFAULT 2048 MYSQLD_DATADIR/test/t3_restart.ibd @@ -393,6 +396,9 @@ test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0 test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t1_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t1_restart.ibd test/t2_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t2_restart.ibd test/t3_restart DEFAULT 2048 MYSQLD_DATADIR/test/t3_restart.ibd @@ -417,6 +423,9 @@ ALTER TABLE t6_restart TRUNCATE PARTITION p2; ALTER TABLE t7_restart TRUNCATE PARTITION p1; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd test/t6_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd test/t6_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd @@ -520,6 +529,9 @@ t7_restart#p#p1#sp#s3.ibd # restart === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd test/t6_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd test/t6_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd @@ -622,6 +634,9 @@ RENAME TABLE t6_restart TO t66_restart; RENAME TABLE t7_restart TO t77_restart; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd test/t66_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd test/t66_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd @@ -718,6 +733,9 @@ t77_restart#p#p1#sp#s3.ibd # restart === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd test/t66_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd test/t66_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd @@ -853,6 +871,9 @@ t77_restart#p#p1#sp#s3.ibd # restart === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t4_restart.ibd test/t66_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd test/t66_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd @@ -992,6 +1013,9 @@ t77_restart.par # restart === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd test/t66_restart#p#p0 DEFAULT 2048 MYSQLD_DATADIR/test/t66_restart#p#p0.ibd test/t66_restart#p#p1 DEFAULT 2048 MYSQLD_DATADIR/test/t66_restart#p#p1.ibd diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 900348d8a13..ed385f86a31 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -1146,3 +1146,104 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp # # End of 10.9 tests # +# +# MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites +# +# Multi-update with JSON_TABLE +create table t1 ( name varchar(10), +color varchar(10), +price decimal(8,2), +instock BOOLEAN); +insert into t1 values ("Laptop", "black", 20000, 1); +insert into t1 values ("Jacket", "brown", 5000, 1); +insert into t1 values ("Jeans", "blue", 5000, 1); +select * from t1; +name color price instock +Laptop black 20000.00 1 +Jacket brown 5000.00 1 +Jeans blue 5000.00 1 +set @json=' +[ + {"name":"Laptop", "color":"black", "price":"1000", "ordered":"3"}, + {"name":"Jeans", "color":"blue", "ordered":"0"}, + {"name":"Phone", "color":"red", "ordered":"0"} +]'; +select * from json_table(@json, '$[*]' +columns( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' ) +) as jt; +name color price ordered +Laptop black 1000.00 3 +Jeans blue NULL 0 +Phone red NULL 0 +explain update t1, JSON_TABLE(@json,'$[*]' +COLUMNS ( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' + )) AS jt1 +SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE jt1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where +update t1, JSON_TABLE(@json,'$[*]' +COLUMNS ( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' + )) AS jt1 +SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=2; +select * from t1; +name color price instock +Laptop black 20000.00 1 +Jacket brown 5000.00 1 +Jeans blue 5000.00 1 +explain update t1 +SET t1.instock=2 where t1.name in ( +select jt1.name from json_table(@json, '$[*]' +columns( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' ) +) as jt1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY jt1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1) +update t1 +SET t1.instock=2 where t1.name in ( +select jt1.name from json_table(@json, '$[*]' +columns( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' ) +) as jt1); +select * from t1; +name color price instock +Laptop black 20000.00 2 +Jacket brown 5000.00 1 +Jeans blue 5000.00 2 +update t1, JSON_TABLE(@json,'$[*]' +COLUMNS ( +name varchar(10) path '$.name', +color varchar(10) path '$.color', +price decimal(8,2) path '$.price', +ordered boolean path '$.ordered' + )) AS jt1 +SET t1.instock=0, jt1.ordered=1 where t1.name=jt1.name; +ERROR HY000: The target table jt1 of the UPDATE is not updatable +select * from t1; +name color price instock +Laptop black 20000.00 2 +Jacket brown 5000.00 1 +Jeans blue 5000.00 2 +drop table t1; +# +# End of 11.0 tests +# diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 982922ff595..5a8fe984c79 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -997,3 +997,97 @@ COLUMNS --echo # --echo # End of 10.9 tests --echo # + +--echo # +--echo # MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites +--echo # + +--echo # Multi-update with JSON_TABLE + +create table t1 ( name varchar(10), + color varchar(10), + price decimal(8,2), + instock BOOLEAN); + +insert into t1 values ("Laptop", "black", 20000, 1); +insert into t1 values ("Jacket", "brown", 5000, 1); +insert into t1 values ("Jeans", "blue", 5000, 1); + +select * from t1; + +set @json=' +[ + {"name":"Laptop", "color":"black", "price":"1000", "ordered":"3"}, + {"name":"Jeans", "color":"blue", "ordered":"0"}, + {"name":"Phone", "color":"red", "ordered":"0"} +]'; + +select * from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) +) as jt; + +explain update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=3; + +update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=2; + +select * from t1; + +explain update t1 + SET t1.instock=2 where t1.name in ( + select jt1.name from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) + ) as jt1); + + +update t1 + SET t1.instock=2 where t1.name in ( + select jt1.name from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) + ) as jt1); + +select * from t1; + + +-- error ER_NON_UPDATABLE_TABLE +update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0, jt1.ordered=1 where t1.name=jt1.name; + +select * from t1; + +drop table t1; + +--echo # +--echo # End of 11.0 tests +--echo # diff --git a/mysql-test/suite/parts/t/engine_defined_part_attributes.test b/mysql-test/suite/parts/t/engine_defined_part_attributes.test index 22aec9286e9..1245066300c 100644 --- a/mysql-test/suite/parts/t/engine_defined_part_attributes.test +++ b/mysql-test/suite/parts/t/engine_defined_part_attributes.test @@ -181,16 +181,19 @@ CREATE TABLE `t12` ( ( pt1 PAGE_COMPRESSED=0 ); +--sorted_result SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; ALTER TABLE `t12` ADD PARTITION ( PARTITION pt2 PAGE_COMPRESSED=1 ); +--sorted_result SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; ALTER TABLE `t12` ADD PARTITION ( PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3 ); +--sorted_result SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%'; DROP TABLE `t12`; @@ -203,6 +206,7 @@ CREATE TABLE `t13` ( PARTITION pt3 VALUES LESS THAN MAXVALUE ); SHOW CREATE TABLE `t13`; +--sorted_result SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%'; ALTER TABLE `t13` PARTITION BY RANGE(id) ( @@ -211,6 +215,7 @@ ALTER TABLE `t13` PARTITION BY RANGE(id) ( PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0 ); SHOW CREATE TABLE `t13`; +--sorted_result SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%'; DROP TABLE `t13`; diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result index f726b4c07cf..004b9976f51 100644 --- a/mysql-test/suite/period/r/update.result +++ b/mysql-test/suite/period/r/update.result @@ -229,8 +229,8 @@ update t for portion of apptime from @s to g() set t.id= t.id + 5; ERROR HY000: Expression in FOR PORTION OF must be constant # success update t for portion of apptime from @s to h() set t.id= t.id + 5; -# select value is cached update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5; +ERROR HY000: Expression in FOR PORTION OF must be constant # auto_inrement field is updated create or replace table t (id int primary key auto_increment, x int, s date, e date, period for apptime(s, e)); diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test index 3f4dd2bdc68..fd67dc328c1 100644 --- a/mysql-test/suite/period/t/update.test +++ b/mysql-test/suite/period/t/update.test @@ -123,7 +123,7 @@ update t for portion of apptime from @s to g() set t.id= t.id + 5; --echo # success update t for portion of apptime from @s to h() set t.id= t.id + 5; ---echo # select value is cached +--error ER_NOT_CONSTANT_EXPRESSION update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5; --echo # auto_inrement field is updated diff --git a/mysql-test/suite/plugins/r/locales.result b/mysql-test/suite/plugins/r/locales.result index e906d27c21e..a6848fb8486 100644 --- a/mysql-test/suite/plugins/r/locales.result +++ b/mysql-test/suite/plugins/r/locales.result @@ -112,6 +112,7 @@ ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOU 108 zh_HK Chinese - Hong Kong SAR 3 3 . , english 109 el_GR Greek - Greece 11 9 , . greek 110 rm_CH Romansh - Switzerland 9 9 , ' english +111 ka_GE Georgian - Georgia 10 9 , georgian show locales; Id Name Description Error_Message_Language 0 en_US English - United States english @@ -225,6 +226,7 @@ Id Name Description Error_Message_Language 108 zh_HK Chinese - Hong Kong SAR english 109 el_GR Greek - Greece greek 110 rm_CH Romansh - Switzerland english +111 ka_GE Georgian - Georgia georgian show locales like '%spanish%'; 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 'like '%spanish%'' at line 1 show locales where description like '%spanish%'; diff --git a/mysql-test/suite/sys_vars/r/lc_time_names_basic.result b/mysql-test/suite/sys_vars/r/lc_time_names_basic.result index 7d98f44f27b..c1896a0cd73 100644 --- a/mysql-test/suite/sys_vars/r/lc_time_names_basic.result +++ b/mysql-test/suite/sys_vars/r/lc_time_names_basic.result @@ -1024,7 +1024,11 @@ SELECT @@lc_time_names; @@lc_time_names rm_CH SET @@lc_time_names = 111; -ERROR HY000: Unknown locale: '111' +SELECT @@lc_time_names; +@@lc_time_names +ka_GE +SET @@lc_time_names = 112; +ERROR HY000: Unknown locale: '112' '#--------------------FN_DYNVARS_060_10-------------------------#' SET @@lc_time_names = en_EN; ERROR HY000: Unknown locale: 'en_EN' diff --git a/mysql-test/suite/sys_vars/t/lc_time_names_basic.test b/mysql-test/suite/sys_vars/t/lc_time_names_basic.test index 7f748bec2c0..7e634eda996 100644 --- a/mysql-test/suite/sys_vars/t/lc_time_names_basic.test +++ b/mysql-test/suite/sys_vars/t/lc_time_names_basic.test @@ -621,8 +621,10 @@ SET @@lc_time_names = 109; SELECT @@lc_time_names; SET @@lc_time_names = 110; SELECT @@lc_time_names; ---Error ER_UNKNOWN_LOCALE SET @@lc_time_names = 111; +SELECT @@lc_time_names; +--Error ER_UNKNOWN_LOCALE +SET @@lc_time_names = 112; --echo '#--------------------FN_DYNVARS_060_10-------------------------#' ############################################################################# |