diff options
Diffstat (limited to 'mysql-test/r')
48 files changed, 2177 insertions, 4927 deletions
diff --git a/mysql-test/r/alter_table-big.result b/mysql-test/r/alter_table-big.result index 9761754a02f..d6b936bd5d7 100644 --- a/mysql-test/r/alter_table-big.result +++ b/mysql-test/r/alter_table-big.result @@ -12,11 +12,11 @@ alter table t1 enable keys;; insert into t2 values (1); insert into t1 values (1, 1, 1); set session debug="-d,sleep_alter_enable_indexes"; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; insert into t2 values (1) -master-bin.000001 # Query 1 # use `test`; alter table t1 enable keys -master-bin.000001 # Query 1 # use `test`; insert into t1 values (1, 1, 1) +master-bin.000001 # Query # # use `test`; insert into t2 values (1) +master-bin.000001 # Query # # use `test`; alter table t1 enable keys +master-bin.000001 # Query # # use `test`; insert into t1 values (1, 1, 1) drop tables t1, t2; End of 5.0 tests drop table if exists t1, t2, t3; @@ -41,17 +41,17 @@ alter table t2 change c vc varchar(100) default 'Test2', rename to t1;; rename table t1 to t3; drop table t3; set session debug="-d,sleep_alter_before_main_binlog"; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; alter table t1 change i c char(10) default 'Test1' -master-bin.000001 # Query 1 # use `test`; insert into t1 values () -master-bin.000001 # Query 1 # use `test`; alter table t1 change c vc varchar(100) default 'Test2' -master-bin.000001 # Query 1 # use `test`; rename table t1 to t2 -master-bin.000001 # Query 1 # use `test`; drop table t2 -master-bin.000001 # Query 1 # use `test`; create table t1 (i int) -master-bin.000001 # Query 1 # use `test`; alter table t1 change i c char(10) default 'Test3', rename to t2 -master-bin.000001 # Query 1 # use `test`; insert into t2 values () -master-bin.000001 # Query 1 # use `test`; alter table t2 change c vc varchar(100) default 'Test2', rename to t1 -master-bin.000001 # Query 1 # use `test`; rename table t1 to t3 -master-bin.000001 # Query 1 # use `test`; drop table t3 +master-bin.000001 # Query # # use `test`; alter table t1 change i c char(10) default 'Test1' +master-bin.000001 # Query # # use `test`; insert into t1 values () +master-bin.000001 # Query # # use `test`; alter table t1 change c vc varchar(100) default 'Test2' +master-bin.000001 # Query # # use `test`; rename table t1 to t2 +master-bin.000001 # Query # # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t1 (i int) +master-bin.000001 # Query # # use `test`; alter table t1 change i c char(10) default 'Test3', rename to t2 +master-bin.000001 # Query # # use `test`; insert into t2 values () +master-bin.000001 # Query # # use `test`; alter table t2 change c vc varchar(100) default 'Test2', rename to t1 +master-bin.000001 # Query # # use `test`; rename table t1 to t3 +master-bin.000001 # Query # # use `test`; drop table t3 End of 5.1 tests diff --git a/mysql-test/r/bug39022.result b/mysql-test/r/bug39022.result index 5963709aa2a..75899ed686b 100644 --- a/mysql-test/r/bug39022.result +++ b/mysql-test/r/bug39022.result @@ -12,7 +12,7 @@ INSERT INTO t2 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), START TRANSACTION; # in thread2 REPLACE INTO t2 VALUES (-17); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; d # in thread1 REPLACE INTO t1(a,b) VALUES (67,20); @@ -21,10 +21,10 @@ COMMIT; START TRANSACTION; REPLACE INTO t1(a,b) VALUES (65,-50); REPLACE INTO t2 VALUES (-91); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; # in thread1 # should not crash -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # in thread2 d diff --git a/mysql-test/r/create-big.result b/mysql-test/r/create-big.result index eb57bf59084..d062b59a008 100644 --- a/mysql-test/r/create-big.result +++ b/mysql-test/r/create-big.result @@ -175,12 +175,12 @@ t2 CREATE TABLE `t2` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t2; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; insert into t1 values (1) -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; insert into t1 values (1) +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t1 +master-bin.000001 # Query # # use `test`; drop table t2 create table t1 (i int); set session debug="-d,sleep_create_like_before_check_if_exists:+d,sleep_create_like_before_copy"; create table t2 like t1;; @@ -197,11 +197,11 @@ reset master; create table t2 like t1;; drop table t1; drop table t2; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t1 +master-bin.000001 # Query # # use `test`; drop table t2 create table t1 (i int); set session debug="-d,sleep_create_like_before_copy:+d,sleep_create_like_before_ha_create"; reset master; @@ -213,16 +213,16 @@ drop table t2; create table t2 like t1;; drop table t1; drop table t2; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; insert into t2 values (1) -master-bin.000001 # Query 1 # use `test`; drop table t2 -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; insert into t2 values (1) +master-bin.000001 # Query # # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t1 +master-bin.000001 # Query # # use `test`; drop table t2 create table t1 (i int); set session debug="-d,sleep_create_like_before_ha_create:+d,sleep_create_like_before_binlogging"; reset master; @@ -234,14 +234,14 @@ drop table t2; create table t2 like t1;; drop table t1; drop table t2; -show binlog events in 'master-bin.000001' from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; insert into t2 values (1) -master-bin.000001 # Query 1 # use `test`; drop table t2 -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 -master-bin.000001 # Query 1 # use `test`; create table t2 like t1 -master-bin.000001 # Query 1 # use `test`; drop table t1 -master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; insert into t2 values (1) +master-bin.000001 # Query # # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t2 +master-bin.000001 # Query # # use `test`; create table t2 like t1 +master-bin.000001 # Query # # use `test`; drop table t1 +master-bin.000001 # Query # # use `test`; drop table t2 set session debug="-d,sleep_create_like_before_binlogging"; diff --git a/mysql-test/r/ctype_cp932_binlog_stm.result b/mysql-test/r/ctype_cp932_binlog_stm.result index 044885d1ea7..8854a835e25 100644 --- a/mysql-test/r/ctype_cp932_binlog_stm.result +++ b/mysql-test/r/ctype_cp932_binlog_stm.result @@ -29,20 +29,20 @@ HEX(s1) HEX(s2) d 466F6F2773206120426172 ED40ED41ED42 47.93 DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 370| +show binlog events from <binlog_start>| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 370 Query 1 536 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, +master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, s2 CHAR(50) CHARACTER SET cp932, d DECIMAL(10,2)) -master-bin.000001 536 Query 1 785 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `bug18293`(IN ins1 CHAR(50), +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `bug18293`(IN ins1 CHAR(50), IN ins2 CHAR(50) CHARACTER SET cp932, IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 785 Query 1 1049 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'), NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'), NAME_CONST('ind',47.93)) -master-bin.000001 1049 Query 1 1138 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1138 Query 1 1217 use `test`; DROP TABLE t4 +master-bin.000001 # Query # # use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'), NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'), NAME_CONST('ind',47.93)) +master-bin.000001 # Query # # use `test`; DROP PROCEDURE bug18293 +master-bin.000001 # Query # # use `test`; DROP TABLE t4 End of 5.0 tests SHOW BINLOG EVENTS FROM 365; ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 77b2071494d..36025cbfb35 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -349,4 +349,13 @@ END | DELETE IGNORE FROM t1; ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. DROP TABLE t1; +# +# Bug #53450: Crash/assertion +# "virtual int ha_myisam::index_first(uchar*)") at assert.c:81 +# +CREATE TABLE t1 (a INT, b INT, c INT, +INDEX(a), INDEX(b), INDEX(c)); +INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); +DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result index 27e51a33112..b6b79cb596b 100644 --- a/mysql-test/r/error_simulation.result +++ b/mysql-test/r/error_simulation.result @@ -39,5 +39,49 @@ a 2 DROP TABLE t1; # +# Bug#42064: low memory crash when importing hex strings, in Item_hex_string::Item_hex_string +# +CREATE TABLE t1(a BLOB); +SET SESSION debug="+d,bug42064_simulate_oom"; +INSERT INTO t1 VALUES(""); +Got one of the listed errors +SET SESSION debug=DEFAULT; +DROP TABLE t1; +# +# Bug#41660: Sort-index_merge for non-first join table may require +# O(#scans) memory +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); +CREATE TABLE t2 (a INT, b INT, filler CHAR(100), KEY(a), KEY(b)); +INSERT INTO t2 SELECT 1000, 1000, 'filler' FROM t1 A, t1 B, t1 C; +INSERT INTO t2 VALUES (1, 1, 'data'); +# the example query uses LEFT JOIN only for the sake of being able to +# demonstrate the issue with a very small dataset. (left outer join +# disables the use of join buffering, so we get the second table +# re-scanned for every record in the outer table. if we used inner join, +# we would need to have thousands of records and/or more columns in both +# tables so that the join buffer is filled and re-scans are triggered). +SET SESSION debug = '+d,only_one_Unique_may_be_created'; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); +id select_type table type possible_keys key key_len ref rows Extra +x x x x x x x x x +x x x x x x x x x Using sort_union(a,b); Using where +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); +a a b filler +0 1 1 data +1 1 1 data +2 1 1 data +3 1 1 data +4 1 1 data +5 1 1 data +6 1 1 data +7 1 1 data +8 1 1 data +9 1 1 data +SET SESSION debug = DEFAULT; +DROP TABLE t1, t2; +# # End of 5.1 tests # diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index 7dfd10a53f8..262caea3d7f 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -82,5 +82,24 @@ DROP TABLE table_1; DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; + +Bug #50087 Interval arithmetic for Event_queue_element is not portable. + +CREATE TABLE t1(a int); +CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() - INTERVAL 1 MONTH +ENDS NOW() + INTERVAL 2 MONTH +ON COMPLETION PRESERVE +DO +INSERT INTO t1 VALUES (1); +CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() +ENDS NOW() + INTERVAL 11 MONTH +ON COMPLETION PRESERVE +DO +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +DROP EVENT e1; +DROP EVENT e2; DROP DATABASE events_test; SET GLOBAL event_scheduler=@event_scheduler; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 8f2d704b312..f46fe8daaad 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -238,4 +238,17 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1, t2; +# +# Bug #48573: difference of index selection between rpm binary and +# .tar.gz, windows vs linux.. +# +CREATE TABLE t1(c1 INT, c2 INT, c4 INT, c5 INT, KEY(c2, c5), KEY(c2, c4, c5)); +INSERT INTO t1 VALUES(4, 1, 1, 1); +INSERT INTO t1 VALUES(3, 1, 1, 1); +INSERT INTO t1 VALUES(2, 1, 1, 1); +INSERT INTO t1 VALUES(1, 1, 1, 1); +EXPLAIN SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c2,c2_2 c2 10 const,const 3 Using where +DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/flush_block_commit_notembedded.result b/mysql-test/r/flush_block_commit_notembedded.result index c7fd7a11877..a81b1ce1e3a 100644 --- a/mysql-test/r/flush_block_commit_notembedded.result +++ b/mysql-test/r/flush_block_commit_notembedded.result @@ -7,15 +7,13 @@ SET AUTOCOMMIT=0; INSERT t1 VALUES (1); # Switch to connection con2 FLUSH TABLES WITH READ LOCK; -SHOW MASTER STATUS; -File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 106 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info # Switch to connection con1 COMMIT; # Switch to connection con2 -SHOW MASTER STATUS; -File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 106 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info UNLOCK TABLES; # Switch to connection con1 DROP TABLE t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index ffdacc43735..fdeec2755ca 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -750,4 +750,24 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # +# Bug#54477: Crash on IN / CASE with NULL arguments +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +SELECT 1 IN (NULL, a) FROM t1; +1 IN (NULL, a) +1 +NULL +SELECT a IN (a, a) FROM t1 GROUP BY a WITH ROLLUP; +a IN (a, a) +1 +1 +NULL +SELECT CASE a WHEN a THEN a END FROM t1 GROUP BY a WITH ROLLUP; +CASE a WHEN a THEN a END +1 +2 +NULL +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result index 20ddc87ee78..c1f5849c091 100644 --- a/mysql-test/r/func_isnull.result +++ b/mysql-test/r/func_isnull.result @@ -5,3 +5,17 @@ flush tables; select * from t1 where isnull(to_days(mydate)); id mydate drop table t1; +# +# Bug#53933 crash when using uncacheable subquery in the having clause of outer query +# +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (0),(0); +SELECT ISNULL((SELECT GET_LOCK('Bug#53933', 0) FROM t1 GROUP BY f1)) AS f2 +FROM t1 GROUP BY f1 HAVING f2 = f2; +f2 +0 +SELECT RELEASE_LOCK('Bug#53933'); +RELEASE_LOCK('Bug#53933') +1 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index 9338a76e320..21da211160b 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -169,3 +169,17 @@ select 'andre%' like 'andreÊ%' escape 'Ê'; select _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê'; _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê' 1 +End of 4.1 tests +# +# Bug #54575: crash when joining tables with unique set column +# +CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a)); +CREATE TABLE t2(b INT PRIMARY KEY); +INSERT INTO t1 VALUES (); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +INSERT INTO t2 VALUES (1), (2), (3); +SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a; +1 +DROP TABLE t1, t2; +End of 5.1 tests diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 90503300065..645dd460735 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1790,4 +1790,24 @@ aa b COUNT( b) 1 10 1 DROP TABLE t1, t2; # +# Bug#52051: Aggregate functions incorrectly returns NULL from outer +# join query +# +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t2 VALUES (1), (2); +EXPLAIN SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +MIN(t2.a) +1 +EXPLAIN SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +MAX(t2.a) +2 +DROP TABLE t1, t2; +# # End of 5.1 tests diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 01f27a498ef..6fef66b9d93 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2767,4 +2767,17 @@ SELECT MIN( a ) FROM t1 WHERE a IS NULL; MIN( a ) NULL DROP TABLE t1; +# +# Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at +# opt_sum.cc:305 +# +CREATE TABLE t1 ( a INT, KEY (a) ); +INSERT INTO t1 VALUES (1), (2), (3); +SELECT MIN( a ) AS min_a +FROM t1 +WHERE a > 1 AND a IS NULL +ORDER BY min_a; +min_a +NULL +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index a970e20a2c0..b20b8dbb138 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -769,4 +769,97 @@ a 1 HANDLER t1 CLOSE; DROP TABLE t1; +# +# Bug #54007: assert in ha_myisam::index_next , HANDLER +# +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +HANDLER t1 READ `PRIMARY` NEXT; +a b +HANDLER t1 READ ab NEXT; +a b +HANDLER t1 READ b NEXT; +a b +HANDLER t1 READ NEXT; +a b +HANDLER t1 CLOSE; +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +1 10 +HANDLER t1 READ `PRIMARY` NEXT; +a b +2 20 +HANDLER t1 READ ab NEXT; +a b +1 10 +HANDLER t1 READ ab NEXT; +a b +2 20 +HANDLER t1 READ b NEXT; +a b +1 10 +HANDLER t1 READ b NEXT; +a b +2 20 +HANDLER t1 READ b NEXT; +a b +3 30 +HANDLER t1 READ b NEXT; +a b +4 40 +HANDLER t1 READ b NEXT; +a b +HANDLER t1 READ NEXT; +a b +4 40 +HANDLER t1 READ NEXT; +a b +3 30 +HANDLER t1 READ NEXT; +a b +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV; +a b +4 40 +HANDLER t1 READ `PRIMARY` PREV; +a b +3 30 +HANDLER t1 READ b PREV; +a b +4 40 +HANDLER t1 READ b PREV; +a b +3 30 +HANDLER t1 CLOSE; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a b +2 20 +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +a b +4 40 +3 30 +2 20 +HANDLER t1 READ b NEXT LIMIT 5; +a b +1 10 +2 20 +3 30 +4 40 +HANDLER t1 CLOSE; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 742c9f2864e..f58e92b186f 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1641,4 +1641,14 @@ COUNT(*) DROP USER nonpriv; DROP TABLE db1.t1; DROP DATABASE db1; + +Bug#54422 query with = 'variables' + +CREATE TABLE variables(f1 INT); +SELECT COLUMN_DEFAULT, TABLE_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; +COLUMN_DEFAULT TABLE_NAME +NULL variables +DROP TABLE variables; End of 5.1 tests. diff --git a/mysql-test/r/innodb-autoinc-optimize.result b/mysql-test/r/innodb-autoinc-optimize.result deleted file mode 100644 index c6da43555b2..00000000000 --- a/mysql-test/r/innodb-autoinc-optimize.result +++ /dev/null @@ -1,9 +0,0 @@ -drop table if exists t1; -create table t1(a int not null auto_increment primary key) engine=innodb; -insert into t1 set a = -1; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize note Table does not support optimize, doing recreate + analyze instead -test.t1 optimize status OK -==== clean up ==== -DROP TABLE t1; diff --git a/mysql-test/r/innodb-ucs2.result b/mysql-test/r/innodb-ucs2.result deleted file mode 100644 index b6bff7d5f42..00000000000 --- a/mysql-test/r/innodb-ucs2.result +++ /dev/null @@ -1,314 +0,0 @@ -drop table if exists t1, t2; -create table t1 ( -a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( -a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -a hex(b) hex(c) filler -1 61626364656667 61626364656667 boo -4 D0B1 D0B1 eight -4 5B 5B five -4 E880BD E880BD four -4 E880BDD0B1E880BD E880BDD0B1E880BD seven -4 E880BDE880BD E880BDE880BD six -3 71727374757677 71727374757677 three -2 696A6B696C6D6E 696A6B696C6D6E two -select a,hex(b),hex(c),filler from t2 order by filler; -a hex(b) hex(c) filler -4 05630563 05630563 email -4 0563 0563 email -4 05612020 05612020 email -4 01FC 01FC email -4 0120 0120 email -4 00640065 00640065 email -4 00E400E50068 00E400E50068 email -4 0000E400 0000E400 email -4 0000563001FC0563 0000563001FC0563 email -1 0061006200630064006500660067 0061006200630064006500660067 one -3 0071007200730074007500760077 0071007200730074007500760077 three -2 0069006A006B0069006C006D006E 0069006A006B0069006C006D006E two -drop table t1; -drop table t2; -create table t1 ( -a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( -a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -a hex(b) hex(c) filler -1 61626364656667 61626364656667 boo -4 D0B1 D0B1 eight -4 5B 5B five -4 E880BD E880BD four -4 E880BDD0B1E880BD E880BDD0B1E880BD seven -4 E880BDE880BD E880BDE880BD six -3 71727374757677 71727374757677 three -2 696A6B696C6D6E 696A6B696C6D6E two -select a,hex(b),hex(c),filler from t2 order by filler; -a hex(b) hex(c) filler -4 05630563 05630563 email -4 0563 0563 email -4 05612020 05612020 email -4 01FC 01FC email -4 0120 0120 email -4 00640065 00640065 email -4 00E400E50068 00E400E50068 email -4 0000E400 0000E400 email -4 0000563001FC0563 0000563001FC0563 email -1 0061006200630064006500660067 0061006200630064006500660067 one -3 0071007200730074007500760077 0071007200730074007500760077 three -2 0069006A006B0069006C006D006E 0069006A006B0069006C006D006E two -drop table t1; -drop table t2; -create table t1 ( -a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( -a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -a hex(b) hex(c) filler -1 61626364656667 61626364656667 boo -4 D0B1 D0B1 eight -4 5B 5B five -4 E880BD E880BD four -4 E880BDD0B1E880BD E880BDD0B1E880BD seven -4 E880BDE880BD E880BDE880BD six -3 71727374757677 71727374757677 three -2 696A6B696C6D6E 696A6B696C6D6E two -select a,hex(b),hex(c),filler from t2 order by filler; -a hex(b) hex(c) filler -4 0120 0120 email -4 01FC 01FC email -4 0563 0563 email -4 0000563001FC0563 0000563001FC0563 email -4 0000E400 0000E400 email -4 00640065 00640065 email -4 00E400E50068 00E400E50068 email -4 05612020 05612020 email -4 05630563 05630563 email -1 0061006200630064006500660067 0061006200630064006500660067 one -3 0071007200730074007500760077 0071007200730074007500760077 three -2 0069006A006B0069006C006D006E 0069006A006B0069006C006D006E two -drop table t1; -drop table t2; -create table t1 ( -a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( -a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -a hex(b) hex(c) filler -1 61626364656667 61626364656667 boo -4 D0B1 D0B1 eight -4 5B 5B five -4 E880BD E880BD four -3 71727374757677 71727374757677 three -2 696A6B696C6D6E 696A6B696C6D6E two -select a,hex(b),hex(c),filler from t2 order by filler; -a hex(b) hex(c) filler -4 0000E400 0000E400 email -4 00640065 00640065 email -4 00E400E50068 00E400E50068 email -4 0120 0120 email -4 01FC 01FC email -4 05612020 05612020 email -4 0563 0563 email -1 61626364656667 61626364656667 one -3 71727374757677 71727374757677 three -2 696A6B696C6D6E 696A6B696C6D6E two -drop table t1; -drop table t2; -commit; -CREATE TABLE t1 ( -ind enum('0','1','2') NOT NULL default '0', -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( -ind enum('0','1','2') NOT NULL default '0', -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; -INSERT INTO t1 VALUES ('1', ''),('2', ''); -INSERT INTO t2 VALUES ('1', ''),('2', ''); -SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; -hex(ind) hex(string1) -31 -32 -SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; -hex(ind) hex(string1) -0031 -0032 -drop table t1,t2; -CREATE TABLE t1 ( -ind set('0','1','2') NOT NULL default '0', -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( -ind set('0','1','2') NOT NULL default '0', -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; -INSERT INTO t1 VALUES ('1', ''),('2', ''); -INSERT INTO t2 VALUES ('1', ''),('2', ''); -SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; -hex(ind) hex(string1) -31 -32 -SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; -hex(ind) hex(string1) -0031 -0032 -drop table t1,t2; -CREATE TABLE t1 ( -ind bit not null, -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( -ind bit not null, -string1 varchar(250) NOT NULL, -PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; -insert into t1 values(0,''),(1,''); -insert into t2 values(0,''),(1,''); -select hex(ind),hex(string1) from t1 order by string1; -hex(ind) hex(string1) -0 -1 -select hex(ind),hex(string1) from t2 order by string1; -hex(ind) hex(string1) -0 -1 -drop table t1,t2; -create table t2 ( -a int, b char(10), filler char(10), primary key(a, b(2)) -) character set utf8 engine = innodb; -insert into t2 values (1,'abcdefg','one'); -insert into t2 values (2,'ijkilmn','two'); -insert into t2 values (3, 'qrstuvw','three'); -update t2 set a=5, filler='booo' where a=1; -drop table t2; -create table t2 ( -a int, b char(10), filler char(10), primary key(a, b(2)) -) character set ucs2 engine = innodb; -insert into t2 values (1,'abcdefg','one'); -insert into t2 values (2,'ijkilmn','two'); -insert into t2 values (3, 'qrstuvw','three'); -update t2 set a=5, filler='booo' where a=1; -drop table t2; -create table t1(a int not null, b char(110),primary key(a,b(100))) engine=innodb default charset=utf8; -insert into t1 values(1,'abcdefg'),(2,'defghijk'); -insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); -insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); -select a,hex(b) from t1 order by b; -a hex(b) -1 61626364656667 -2 6465666768696A6B -6 D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1 -7 D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2 -update t1 set b = 'three' where a = 6; -drop table t1; -create table t1(a int not null, b text(110),primary key(a,b(100))) engine=innodb default charset=utf8; -insert into t1 values(1,'abcdefg'),(2,'defghijk'); -insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); -insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); -select a,hex(b) from t1 order by b; -a hex(b) -1 61626364656667 -2 6465666768696A6B -6 D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1 -7 D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2 -update t1 set b = 'three' where a = 6; -drop table t1; -End of 5.0 tests diff --git a/mysql-test/r/innodb_autoinc_lock_mode_zero.result b/mysql-test/r/innodb_autoinc_lock_mode_zero.result deleted file mode 100644 index 3d016684338..00000000000 --- a/mysql-test/r/innodb_autoinc_lock_mode_zero.result +++ /dev/null @@ -1,39 +0,0 @@ -drop table if exists t1; -CREATE TABLE t1 ( -id int(11) NOT NULL auto_increment, -ggid varchar(32) binary DEFAULT '' NOT NULL, -email varchar(64) DEFAULT '' NOT NULL, -passwd varchar(32) binary DEFAULT '' NOT NULL, -PRIMARY KEY (id), -UNIQUE ggid (ggid) -) ENGINE=innodb; -insert into t1 (ggid,passwd) values ('test1','xxx'); -insert into t1 (ggid,passwd) values ('test2','yyy'); -insert into t1 (ggid,passwd) values ('test2','this will fail'); -ERROR 23000: Duplicate entry 'test2' for key 'ggid' -insert into t1 (ggid,id) values ('this will fail',1); -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -select * from t1 where ggid='test1'; -id ggid email passwd -1 test1 xxx -select * from t1 where passwd='xxx'; -id ggid email passwd -1 test1 xxx -select * from t1 where id=2; -id ggid email passwd -2 test2 yyy -replace into t1 (ggid,id) values ('this will work',1); -replace into t1 (ggid,passwd) values ('test2','this will work'); -update t1 set id=100,ggid='test2' where id=1; -ERROR 23000: Duplicate entry 'test2' for key 'ggid' -select * from t1; -id ggid email passwd -1 this will work -3 test2 this will work -select * from t1 where id=1; -id ggid email passwd -1 this will work -select * from t1 where id=999; -id ggid email passwd -drop table t1; -End of tests diff --git a/mysql-test/r/innodb_bug30919.result b/mysql-test/r/innodb_bug30919.result deleted file mode 100644 index 42aa4ff302b..00000000000 --- a/mysql-test/r/innodb_bug30919.result +++ /dev/null @@ -1,1043 +0,0 @@ -use test; -CREATE TABLE test.part_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, -dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, -fkid MEDIUMINT, filler VARCHAR(255), -PRIMARY KEY(id)) ENGINE='innodb' -PARTITION BY RANGE(id) -SUBPARTITION BY hash(id) subpartitions 2 -(PARTITION pa3 values less than (42), -PARTITION pa6 values less than (60), -PARTITION pa7 values less than (70), -PARTITION pa8 values less than (80), -PARTITION pa9 values less than (90), -PARTITION pa10 values less than (100), -PARTITION pa11 values less than MAXVALUE); -CREATE PROCEDURE test.proc_part() -BEGIN -DECLARE ins_count INT DEFAULT 1000; -DECLARE del_count INT; -DECLARE cur_user VARCHAR(255); -DECLARE local_uuid VARCHAR(255); -DECLARE local_time TIMESTAMP; -SET local_time= NOW(); -SET cur_user= CURRENT_USER(); -SET local_uuid= UUID(); -WHILE ins_count > 0 DO -INSERT INTO test.part_tbl VALUES (NULL, NOW(), USER() , UUID(), -ins_count,'Going to test MBR for MySQL'); -SET ins_count = ins_count - 1; -END WHILE; -SELECT MAX(id) FROM test.part_tbl INTO del_count; -WHILE del_count > 0 DO -DELETE FROM test.part_tbl WHERE id = del_count; -select count(*) as internal_count, del_count -- these two lines are for -FROM test.part_tbl; -- debug to show the problem -SET del_count = del_count - 2; -END WHILE; -END| -CALL test.proc_part(); -internal_count del_count -999 1000 -internal_count del_count -998 998 -internal_count del_count -997 996 -internal_count del_count -996 994 -internal_count del_count -995 992 -internal_count del_count -994 990 -internal_count del_count -993 988 -internal_count del_count -992 986 -internal_count del_count -991 984 -internal_count del_count -990 982 -internal_count del_count -989 980 -internal_count del_count -988 978 -internal_count del_count -987 976 -internal_count del_count -986 974 -internal_count del_count -985 972 -internal_count del_count -984 970 -internal_count del_count -983 968 -internal_count del_count -982 966 -internal_count del_count -981 964 -internal_count del_count -980 962 -internal_count del_count -979 960 -internal_count del_count -978 958 -internal_count del_count -977 956 -internal_count del_count -976 954 -internal_count del_count -975 952 -internal_count del_count -974 950 -internal_count del_count -973 948 -internal_count del_count -972 946 -internal_count del_count -971 944 -internal_count del_count -970 942 -internal_count del_count -969 940 -internal_count del_count -968 938 -internal_count del_count -967 936 -internal_count del_count -966 934 -internal_count del_count -965 932 -internal_count del_count -964 930 -internal_count del_count -963 928 -internal_count del_count -962 926 -internal_count del_count -961 924 -internal_count del_count -960 922 -internal_count del_count -959 920 -internal_count del_count -958 918 -internal_count del_count -957 916 -internal_count del_count -956 914 -internal_count del_count -955 912 -internal_count del_count -954 910 -internal_count del_count -953 908 -internal_count del_count -952 906 -internal_count del_count -951 904 -internal_count del_count -950 902 -internal_count del_count -949 900 -internal_count del_count -948 898 -internal_count del_count -947 896 -internal_count del_count -946 894 -internal_count del_count -945 892 -internal_count del_count -944 890 -internal_count del_count -943 888 -internal_count del_count -942 886 -internal_count del_count -941 884 -internal_count del_count -940 882 -internal_count del_count -939 880 -internal_count del_count -938 878 -internal_count del_count -937 876 -internal_count del_count -936 874 -internal_count del_count -935 872 -internal_count del_count -934 870 -internal_count del_count -933 868 -internal_count del_count -932 866 -internal_count del_count -931 864 -internal_count del_count -930 862 -internal_count del_count -929 860 -internal_count del_count -928 858 -internal_count del_count -927 856 -internal_count del_count -926 854 -internal_count del_count -925 852 -internal_count del_count -924 850 -internal_count del_count -923 848 -internal_count del_count -922 846 -internal_count del_count -921 844 -internal_count del_count -920 842 -internal_count del_count -919 840 -internal_count del_count -918 838 -internal_count del_count -917 836 -internal_count del_count -916 834 -internal_count del_count -915 832 -internal_count del_count -914 830 -internal_count del_count -913 828 -internal_count del_count -912 826 -internal_count del_count -911 824 -internal_count del_count -910 822 -internal_count del_count -909 820 -internal_count del_count -908 818 -internal_count del_count -907 816 -internal_count del_count -906 814 -internal_count del_count -905 812 -internal_count del_count -904 810 -internal_count del_count -903 808 -internal_count del_count -902 806 -internal_count del_count -901 804 -internal_count del_count -900 802 -internal_count del_count -899 800 -internal_count del_count -898 798 -internal_count del_count -897 796 -internal_count del_count -896 794 -internal_count del_count -895 792 -internal_count del_count -894 790 -internal_count del_count -893 788 -internal_count del_count -892 786 -internal_count del_count -891 784 -internal_count del_count -890 782 -internal_count del_count -889 780 -internal_count del_count -888 778 -internal_count del_count -887 776 -internal_count del_count -886 774 -internal_count del_count -885 772 -internal_count del_count -884 770 -internal_count del_count -883 768 -internal_count del_count -882 766 -internal_count del_count -881 764 -internal_count del_count -880 762 -internal_count del_count -879 760 -internal_count del_count -878 758 -internal_count del_count -877 756 -internal_count del_count -876 754 -internal_count del_count -875 752 -internal_count del_count -874 750 -internal_count del_count -873 748 -internal_count del_count -872 746 -internal_count del_count -871 744 -internal_count del_count -870 742 -internal_count del_count -869 740 -internal_count del_count -868 738 -internal_count del_count -867 736 -internal_count del_count -866 734 -internal_count del_count -865 732 -internal_count del_count -864 730 -internal_count del_count -863 728 -internal_count del_count -862 726 -internal_count del_count -861 724 -internal_count del_count -860 722 -internal_count del_count -859 720 -internal_count del_count -858 718 -internal_count del_count -857 716 -internal_count del_count -856 714 -internal_count del_count -855 712 -internal_count del_count -854 710 -internal_count del_count -853 708 -internal_count del_count -852 706 -internal_count del_count -851 704 -internal_count del_count -850 702 -internal_count del_count -849 700 -internal_count del_count -848 698 -internal_count del_count -847 696 -internal_count del_count -846 694 -internal_count del_count -845 692 -internal_count del_count -844 690 -internal_count del_count -843 688 -internal_count del_count -842 686 -internal_count del_count -841 684 -internal_count del_count -840 682 -internal_count del_count -839 680 -internal_count del_count -838 678 -internal_count del_count -837 676 -internal_count del_count -836 674 -internal_count del_count -835 672 -internal_count del_count -834 670 -internal_count del_count -833 668 -internal_count del_count -832 666 -internal_count del_count -831 664 -internal_count del_count -830 662 -internal_count del_count -829 660 -internal_count del_count -828 658 -internal_count del_count -827 656 -internal_count del_count -826 654 -internal_count del_count -825 652 -internal_count del_count -824 650 -internal_count del_count -823 648 -internal_count del_count -822 646 -internal_count del_count -821 644 -internal_count del_count -820 642 -internal_count del_count -819 640 -internal_count del_count -818 638 -internal_count del_count -817 636 -internal_count del_count -816 634 -internal_count del_count -815 632 -internal_count del_count -814 630 -internal_count del_count -813 628 -internal_count del_count -812 626 -internal_count del_count -811 624 -internal_count del_count -810 622 -internal_count del_count -809 620 -internal_count del_count -808 618 -internal_count del_count -807 616 -internal_count del_count -806 614 -internal_count del_count -805 612 -internal_count del_count -804 610 -internal_count del_count -803 608 -internal_count del_count -802 606 -internal_count del_count -801 604 -internal_count del_count -800 602 -internal_count del_count -799 600 -internal_count del_count -798 598 -internal_count del_count -797 596 -internal_count del_count -796 594 -internal_count del_count -795 592 -internal_count del_count -794 590 -internal_count del_count -793 588 -internal_count del_count -792 586 -internal_count del_count -791 584 -internal_count del_count -790 582 -internal_count del_count -789 580 -internal_count del_count -788 578 -internal_count del_count -787 576 -internal_count del_count -786 574 -internal_count del_count -785 572 -internal_count del_count -784 570 -internal_count del_count -783 568 -internal_count del_count -782 566 -internal_count del_count -781 564 -internal_count del_count -780 562 -internal_count del_count -779 560 -internal_count del_count -778 558 -internal_count del_count -777 556 -internal_count del_count -776 554 -internal_count del_count -775 552 -internal_count del_count -774 550 -internal_count del_count -773 548 -internal_count del_count -772 546 -internal_count del_count -771 544 -internal_count del_count -770 542 -internal_count del_count -769 540 -internal_count del_count -768 538 -internal_count del_count -767 536 -internal_count del_count -766 534 -internal_count del_count -765 532 -internal_count del_count -764 530 -internal_count del_count -763 528 -internal_count del_count -762 526 -internal_count del_count -761 524 -internal_count del_count -760 522 -internal_count del_count -759 520 -internal_count del_count -758 518 -internal_count del_count -757 516 -internal_count del_count -756 514 -internal_count del_count -755 512 -internal_count del_count -754 510 -internal_count del_count -753 508 -internal_count del_count -752 506 -internal_count del_count -751 504 -internal_count del_count -750 502 -internal_count del_count -749 500 -internal_count del_count -748 498 -internal_count del_count -747 496 -internal_count del_count -746 494 -internal_count del_count -745 492 -internal_count del_count -744 490 -internal_count del_count -743 488 -internal_count del_count -742 486 -internal_count del_count -741 484 -internal_count del_count -740 482 -internal_count del_count -739 480 -internal_count del_count -738 478 -internal_count del_count -737 476 -internal_count del_count -736 474 -internal_count del_count -735 472 -internal_count del_count -734 470 -internal_count del_count -733 468 -internal_count del_count -732 466 -internal_count del_count -731 464 -internal_count del_count -730 462 -internal_count del_count -729 460 -internal_count del_count -728 458 -internal_count del_count -727 456 -internal_count del_count -726 454 -internal_count del_count -725 452 -internal_count del_count -724 450 -internal_count del_count -723 448 -internal_count del_count -722 446 -internal_count del_count -721 444 -internal_count del_count -720 442 -internal_count del_count -719 440 -internal_count del_count -718 438 -internal_count del_count -717 436 -internal_count del_count -716 434 -internal_count del_count -715 432 -internal_count del_count -714 430 -internal_count del_count -713 428 -internal_count del_count -712 426 -internal_count del_count -711 424 -internal_count del_count -710 422 -internal_count del_count -709 420 -internal_count del_count -708 418 -internal_count del_count -707 416 -internal_count del_count -706 414 -internal_count del_count -705 412 -internal_count del_count -704 410 -internal_count del_count -703 408 -internal_count del_count -702 406 -internal_count del_count -701 404 -internal_count del_count -700 402 -internal_count del_count -699 400 -internal_count del_count -698 398 -internal_count del_count -697 396 -internal_count del_count -696 394 -internal_count del_count -695 392 -internal_count del_count -694 390 -internal_count del_count -693 388 -internal_count del_count -692 386 -internal_count del_count -691 384 -internal_count del_count -690 382 -internal_count del_count -689 380 -internal_count del_count -688 378 -internal_count del_count -687 376 -internal_count del_count -686 374 -internal_count del_count -685 372 -internal_count del_count -684 370 -internal_count del_count -683 368 -internal_count del_count -682 366 -internal_count del_count -681 364 -internal_count del_count -680 362 -internal_count del_count -679 360 -internal_count del_count -678 358 -internal_count del_count -677 356 -internal_count del_count -676 354 -internal_count del_count -675 352 -internal_count del_count -674 350 -internal_count del_count -673 348 -internal_count del_count -672 346 -internal_count del_count -671 344 -internal_count del_count -670 342 -internal_count del_count -669 340 -internal_count del_count -668 338 -internal_count del_count -667 336 -internal_count del_count -666 334 -internal_count del_count -665 332 -internal_count del_count -664 330 -internal_count del_count -663 328 -internal_count del_count -662 326 -internal_count del_count -661 324 -internal_count del_count -660 322 -internal_count del_count -659 320 -internal_count del_count -658 318 -internal_count del_count -657 316 -internal_count del_count -656 314 -internal_count del_count -655 312 -internal_count del_count -654 310 -internal_count del_count -653 308 -internal_count del_count -652 306 -internal_count del_count -651 304 -internal_count del_count -650 302 -internal_count del_count -649 300 -internal_count del_count -648 298 -internal_count del_count -647 296 -internal_count del_count -646 294 -internal_count del_count -645 292 -internal_count del_count -644 290 -internal_count del_count -643 288 -internal_count del_count -642 286 -internal_count del_count -641 284 -internal_count del_count -640 282 -internal_count del_count -639 280 -internal_count del_count -638 278 -internal_count del_count -637 276 -internal_count del_count -636 274 -internal_count del_count -635 272 -internal_count del_count -634 270 -internal_count del_count -633 268 -internal_count del_count -632 266 -internal_count del_count -631 264 -internal_count del_count -630 262 -internal_count del_count -629 260 -internal_count del_count -628 258 -internal_count del_count -627 256 -internal_count del_count -626 254 -internal_count del_count -625 252 -internal_count del_count -624 250 -internal_count del_count -623 248 -internal_count del_count -622 246 -internal_count del_count -621 244 -internal_count del_count -620 242 -internal_count del_count -619 240 -internal_count del_count -618 238 -internal_count del_count -617 236 -internal_count del_count -616 234 -internal_count del_count -615 232 -internal_count del_count -614 230 -internal_count del_count -613 228 -internal_count del_count -612 226 -internal_count del_count -611 224 -internal_count del_count -610 222 -internal_count del_count -609 220 -internal_count del_count -608 218 -internal_count del_count -607 216 -internal_count del_count -606 214 -internal_count del_count -605 212 -internal_count del_count -604 210 -internal_count del_count -603 208 -internal_count del_count -602 206 -internal_count del_count -601 204 -internal_count del_count -600 202 -internal_count del_count -599 200 -internal_count del_count -598 198 -internal_count del_count -597 196 -internal_count del_count -596 194 -internal_count del_count -595 192 -internal_count del_count -594 190 -internal_count del_count -593 188 -internal_count del_count -592 186 -internal_count del_count -591 184 -internal_count del_count -590 182 -internal_count del_count -589 180 -internal_count del_count -588 178 -internal_count del_count -587 176 -internal_count del_count -586 174 -internal_count del_count -585 172 -internal_count del_count -584 170 -internal_count del_count -583 168 -internal_count del_count -582 166 -internal_count del_count -581 164 -internal_count del_count -580 162 -internal_count del_count -579 160 -internal_count del_count -578 158 -internal_count del_count -577 156 -internal_count del_count -576 154 -internal_count del_count -575 152 -internal_count del_count -574 150 -internal_count del_count -573 148 -internal_count del_count -572 146 -internal_count del_count -571 144 -internal_count del_count -570 142 -internal_count del_count -569 140 -internal_count del_count -568 138 -internal_count del_count -567 136 -internal_count del_count -566 134 -internal_count del_count -565 132 -internal_count del_count -564 130 -internal_count del_count -563 128 -internal_count del_count -562 126 -internal_count del_count -561 124 -internal_count del_count -560 122 -internal_count del_count -559 120 -internal_count del_count -558 118 -internal_count del_count -557 116 -internal_count del_count -556 114 -internal_count del_count -555 112 -internal_count del_count -554 110 -internal_count del_count -553 108 -internal_count del_count -552 106 -internal_count del_count -551 104 -internal_count del_count -550 102 -internal_count del_count -549 100 -internal_count del_count -548 98 -internal_count del_count -547 96 -internal_count del_count -546 94 -internal_count del_count -545 92 -internal_count del_count -544 90 -internal_count del_count -543 88 -internal_count del_count -542 86 -internal_count del_count -541 84 -internal_count del_count -540 82 -internal_count del_count -539 80 -internal_count del_count -538 78 -internal_count del_count -537 76 -internal_count del_count -536 74 -internal_count del_count -535 72 -internal_count del_count -534 70 -internal_count del_count -533 68 -internal_count del_count -532 66 -internal_count del_count -531 64 -internal_count del_count -530 62 -internal_count del_count -529 60 -internal_count del_count -528 58 -internal_count del_count -527 56 -internal_count del_count -526 54 -internal_count del_count -525 52 -internal_count del_count -524 50 -internal_count del_count -523 48 -internal_count del_count -522 46 -internal_count del_count -521 44 -internal_count del_count -520 42 -internal_count del_count -519 40 -internal_count del_count -518 38 -internal_count del_count -517 36 -internal_count del_count -516 34 -internal_count del_count -515 32 -internal_count del_count -514 30 -internal_count del_count -513 28 -internal_count del_count -512 26 -internal_count del_count -511 24 -internal_count del_count -510 22 -internal_count del_count -509 20 -internal_count del_count -508 18 -internal_count del_count -507 16 -internal_count del_count -506 14 -internal_count del_count -505 12 -internal_count del_count -504 10 -internal_count del_count -503 8 -internal_count del_count -502 6 -internal_count del_count -501 4 -internal_count del_count -500 2 -select count(*) as Part from test.part_tbl; -Part -500 -DROP PROCEDURE test.proc_part; -DROP TABLE test.part_tbl; diff --git a/mysql-test/r/innodb_bug42419.result b/mysql-test/r/innodb_bug42419.result deleted file mode 100644 index f304bb634cb..00000000000 --- a/mysql-test/r/innodb_bug42419.result +++ /dev/null @@ -1,17 +0,0 @@ -CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = InnoDB; -INSERT INTO t1 VALUES (1,1),(2,2),(3,3); -COMMIT; -SET AUTOCOMMIT = 0; -CREATE TEMPORARY TABLE t1_tmp ( b INT ); -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 3; -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 2; -SET AUTOCOMMIT = 0; -CREATE TEMPORARY TABLE t2_tmp ( a int, new_a int ); -INSERT INTO t2_tmp VALUES (1,51),(2,52),(3,53); -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 1; -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 2; -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -Reap the server message for connection user2 UPDATE t1 ... -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 3; -DROP TABLE t1; diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result deleted file mode 100644 index c6c775afc9f..00000000000 --- a/mysql-test/r/innodb_gis.result +++ /dev/null @@ -1,589 +0,0 @@ -SET storage_engine=innodb; -DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; -CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT); -CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING); -CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON); -CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT); -CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING); -CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON); -CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION); -CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY); -SHOW CREATE TABLE gis_point; -Table Create Table -gis_point CREATE TABLE `gis_point` ( - `fid` int(11) NOT NULL AUTO_INCREMENT, - `g` point DEFAULT NULL, - PRIMARY KEY (`fid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SHOW FIELDS FROM gis_point; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g point YES NULL -SHOW FIELDS FROM gis_line; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g linestring YES NULL -SHOW FIELDS FROM gis_polygon; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g polygon YES NULL -SHOW FIELDS FROM gis_multi_point; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g multipoint YES NULL -SHOW FIELDS FROM gis_multi_line; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g multilinestring YES NULL -SHOW FIELDS FROM gis_multi_polygon; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g multipolygon YES NULL -SHOW FIELDS FROM gis_geometrycollection; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g geometrycollection YES NULL -SHOW FIELDS FROM gis_geometry; -Field Type Null Key Default Extra -fid int(11) NO PRI NULL auto_increment -g geometry YES NULL -INSERT INTO gis_point VALUES -(101, PointFromText('POINT(10 10)')), -(102, PointFromText('POINT(20 10)')), -(103, PointFromText('POINT(20 20)')), -(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); -INSERT INTO gis_line VALUES -(105, LineFromText('LINESTRING(0 0,0 10,10 0)')), -(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), -(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10)))); -INSERT INTO gis_polygon VALUES -(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), -(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), -(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))); -INSERT INTO gis_multi_point VALUES -(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), -(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), -(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10)))); -INSERT INTO gis_multi_line VALUES -(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), -(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), -(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))); -INSERT INTO gis_multi_polygon VALUES -(117, MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), -(118, MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), -(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))); -INSERT INTO gis_geometrycollection VALUES -(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), -(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))); -INSERT into gis_geometry SELECT * FROM gis_point; -INSERT into gis_geometry SELECT * FROM gis_line; -INSERT into gis_geometry SELECT * FROM gis_polygon; -INSERT into gis_geometry SELECT * FROM gis_multi_point; -INSERT into gis_geometry SELECT * FROM gis_multi_line; -INSERT into gis_geometry SELECT * FROM gis_multi_polygon; -INSERT into gis_geometry SELECT * FROM gis_geometrycollection; -SELECT fid, AsText(g) FROM gis_point ORDER by fid; -fid AsText(g) -101 POINT(10 10) -102 POINT(20 10) -103 POINT(20 20) -104 POINT(10 20) -SELECT fid, AsText(g) FROM gis_line ORDER by fid; -fid AsText(g) -105 LINESTRING(0 0,0 10,10 0) -106 LINESTRING(10 10,20 10,20 20,10 20,10 10) -107 LINESTRING(10 10,40 10) -SELECT fid, AsText(g) FROM gis_polygon ORDER by fid; -fid AsText(g) -108 POLYGON((10 10,20 10,20 20,10 20,10 10)) -109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) -110 POLYGON((0 0,30 0,30 30,0 0)) -SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid; -fid AsText(g) -111 MULTIPOINT(0 0,10 10,10 20,20 20) -112 MULTIPOINT(1 1,11 11,11 21,21 21) -113 MULTIPOINT(3 6,4 10) -SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid; -fid AsText(g) -114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) -115 MULTILINESTRING((10 48,10 21,10 0)) -116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) -SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid; -fid AsText(g) -117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) -118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) -119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) -SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid; -fid AsText(g) -120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) -121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) -SELECT fid, AsText(g) FROM gis_geometry ORDER by fid; -fid AsText(g) -101 POINT(10 10) -102 POINT(20 10) -103 POINT(20 20) -104 POINT(10 20) -105 LINESTRING(0 0,0 10,10 0) -106 LINESTRING(10 10,20 10,20 20,10 20,10 10) -107 LINESTRING(10 10,40 10) -108 POLYGON((10 10,20 10,20 20,10 20,10 10)) -109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) -110 POLYGON((0 0,30 0,30 30,0 0)) -111 MULTIPOINT(0 0,10 10,10 20,20 20) -112 MULTIPOINT(1 1,11 11,11 21,21 21) -113 MULTIPOINT(3 6,4 10) -114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) -115 MULTILINESTRING((10 48,10 21,10 0)) -116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) -117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) -118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) -119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) -120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) -121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) -SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid; -fid Dimension(g) -101 0 -102 0 -103 0 -104 0 -105 1 -106 1 -107 1 -108 2 -109 2 -110 2 -111 0 -112 0 -113 0 -114 1 -115 1 -116 1 -117 2 -118 2 -119 2 -120 1 -121 1 -SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid; -fid GeometryType(g) -101 POINT -102 POINT -103 POINT -104 POINT -105 LINESTRING -106 LINESTRING -107 LINESTRING -108 POLYGON -109 POLYGON -110 POLYGON -111 MULTIPOINT -112 MULTIPOINT -113 MULTIPOINT -114 MULTILINESTRING -115 MULTILINESTRING -116 MULTILINESTRING -117 MULTIPOLYGON -118 MULTIPOLYGON -119 MULTIPOLYGON -120 GEOMETRYCOLLECTION -121 GEOMETRYCOLLECTION -SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid; -fid IsEmpty(g) -101 0 -102 0 -103 0 -104 0 -105 0 -106 0 -107 0 -108 0 -109 0 -110 0 -111 0 -112 0 -113 0 -114 0 -115 0 -116 0 -117 0 -118 0 -119 0 -120 0 -121 0 -SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid; -fid AsText(Envelope(g)) -101 POLYGON((10 10,10 10,10 10,10 10,10 10)) -102 POLYGON((20 10,20 10,20 10,20 10,20 10)) -103 POLYGON((20 20,20 20,20 20,20 20,20 20)) -104 POLYGON((10 20,10 20,10 20,10 20,10 20)) -105 POLYGON((0 0,10 0,10 10,0 10,0 0)) -106 POLYGON((10 10,20 10,20 20,10 20,10 10)) -107 POLYGON((10 10,40 10,40 10,10 10,10 10)) -108 POLYGON((10 10,20 10,20 20,10 20,10 10)) -109 POLYGON((0 0,50 0,50 50,0 50,0 0)) -110 POLYGON((0 0,30 0,30 30,0 30,0 0)) -111 POLYGON((0 0,20 0,20 20,0 20,0 0)) -112 POLYGON((1 1,21 1,21 21,1 21,1 1)) -113 POLYGON((3 6,4 6,4 10,3 10,3 6)) -114 POLYGON((10 0,16 0,16 48,10 48,10 0)) -115 POLYGON((10 0,10 0,10 48,10 48,10 0)) -116 POLYGON((1 2,21 2,21 8,1 8,1 2)) -117 POLYGON((28 0,84 0,84 42,28 42,28 0)) -118 POLYGON((28 0,84 0,84 42,28 42,28 0)) -119 POLYGON((0 0,3 0,3 3,0 3,0 0)) -120 POLYGON((0 0,10 0,10 10,0 10,0 0)) -121 POLYGON((3 6,44 6,44 9,3 9,3 6)) -explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00 -Warnings: -Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry` -SELECT fid, X(g) FROM gis_point ORDER by fid; -fid X(g) -101 10 -102 20 -103 20 -104 10 -SELECT fid, Y(g) FROM gis_point ORDER by fid; -fid Y(g) -101 10 -102 10 -103 20 -104 20 -explain extended select X(g),Y(g) FROM gis_point; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00 -Warnings: -Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point` -SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid; -fid AsText(StartPoint(g)) -105 POINT(0 0) -106 POINT(10 10) -107 POINT(10 10) -SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid; -fid AsText(EndPoint(g)) -105 POINT(10 0) -106 POINT(10 10) -107 POINT(40 10) -SELECT fid, GLength(g) FROM gis_line ORDER by fid; -fid GLength(g) -105 24.142135623731 -106 40 -107 30 -SELECT fid, NumPoints(g) FROM gis_line ORDER by fid; -fid NumPoints(g) -105 3 -106 5 -107 2 -SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid; -fid AsText(PointN(g, 2)) -105 POINT(0 10) -106 POINT(20 10) -107 POINT(40 10) -SELECT fid, IsClosed(g) FROM gis_line ORDER by fid; -fid IsClosed(g) -105 0 -106 1 -107 0 -explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00 -Warnings: -Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS `GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line` -SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid; -fid AsText(Centroid(g)) -108 POINT(15 15) -109 POINT(25.4166666666667 25.4166666666667) -110 POINT(20 10) -SELECT fid, Area(g) FROM gis_polygon ORDER by fid; -fid Area(g) -108 100 -109 2400 -110 450 -SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid; -fid AsText(ExteriorRing(g)) -108 LINESTRING(10 10,20 10,20 20,10 20,10 10) -109 LINESTRING(0 0,50 0,50 50,0 50,0 0) -110 LINESTRING(0 0,30 0,30 30,0 0) -SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid; -fid NumInteriorRings(g) -108 0 -109 1 -110 0 -SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid; -fid AsText(InteriorRingN(g, 1)) -108 NULL -109 LINESTRING(10 10,20 10,20 20,10 20,10 10) -110 NULL -explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00 -Warnings: -Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon` -SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid; -fid IsClosed(g) -114 0 -115 0 -116 0 -SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid; -fid AsText(Centroid(g)) -117 POINT(55.5885277530424 17.426536064114) -118 POINT(55.5885277530424 17.426536064114) -119 POINT(2 2) -SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid; -fid Area(g) -117 1684.5 -118 1684.5 -119 4.5 -SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid; -fid NumGeometries(g) -111 4 -112 4 -113 2 -SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid; -fid NumGeometries(g) -114 2 -115 1 -116 2 -SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid; -fid NumGeometries(g) -117 2 -118 2 -119 1 -SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid; -fid NumGeometries(g) -120 2 -121 2 -explain extended SELECT fid, NumGeometries(g) from gis_multi_point; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 -Warnings: -Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point` -SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid; -fid AsText(GeometryN(g, 2)) -111 POINT(10 10) -112 POINT(11 11) -113 POINT(4 10) -SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid; -fid AsText(GeometryN(g, 2)) -114 LINESTRING(16 0,16 23,16 48) -115 NULL -116 LINESTRING(2 5,5 8,21 7) -SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid; -fid AsText(GeometryN(g, 2)) -117 POLYGON((59 18,67 18,67 13,59 13,59 18)) -118 POLYGON((59 18,67 18,67 13,59 13,59 18)) -119 NULL -SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid; -fid AsText(GeometryN(g, 2)) -120 LINESTRING(0 0,10 10) -121 LINESTRING(3 6,7 9) -SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid; -fid AsText(GeometryN(g, 1)) -120 POINT(0 0) -121 POINT(44 6) -explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 -Warnings: -Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point` -SELECT g1.fid as first, g2.fid as second, -Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, -Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, -Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r -FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; -first second w c o e d t i r -120 120 1 1 0 1 0 0 1 0 -120 121 0 0 1 0 0 0 1 0 -121 120 0 0 1 0 0 0 1 0 -121 121 1 1 0 1 0 0 1 0 -explain extended SELECT g1.fid as first, g2.fid as second, -Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, -Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, -Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r -FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer -Warnings: -Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` -DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; -CREATE TABLE t1 ( -a INTEGER PRIMARY KEY AUTO_INCREMENT, -gp point, -ln linestring, -pg polygon, -mp multipoint, -mln multilinestring, -mpg multipolygon, -gc geometrycollection, -gm geometry -); -SHOW FIELDS FROM t1; -Field Type Null Key Default Extra -a int(11) NO PRI NULL auto_increment -gp point YES NULL -ln linestring YES NULL -pg polygon YES NULL -mp multipoint YES NULL -mln multilinestring YES NULL -mpg multipolygon YES NULL -gc geometrycollection YES NULL -gm geometry YES NULL -ALTER TABLE t1 ADD fid INT; -SHOW FIELDS FROM t1; -Field Type Null Key Default Extra -a int(11) NO PRI NULL auto_increment -gp point YES NULL -ln linestring YES NULL -pg polygon YES NULL -mp multipoint YES NULL -mln multilinestring YES NULL -mpg multipolygon YES NULL -gc geometrycollection YES NULL -gm geometry YES NULL -fid int(11) YES NULL -DROP TABLE t1; -create table t1 (pk integer primary key auto_increment, a geometry not null); -insert into t1 (a) values (GeomFromText('Point(1 2)')); -insert into t1 (a) values ('Garbage'); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -insert IGNORE into t1 (a) values ('Garbage'); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -drop table t1; -create table t1 (pk integer primary key auto_increment, fl geometry not null); -insert into t1 (fl) values (1); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -insert into t1 (fl) values (1.11); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -insert into t1 (fl) values ("qwerty"); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -insert into t1 (fl) values (pointfromtext('point(1,1)')); -ERROR 23000: Column 'fl' cannot be null -drop table t1; -End of 4.1 tests -CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); -INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); -INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); -INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); -INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); -INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); -INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); -INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); -INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); -INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); -INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); -INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); -INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); -INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); -INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); -INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrcontains -center,small -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrdisjoint -down3,left3,right3,up3 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrequal -center -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrintersect -big,center,down,down2,left,left2,right,right2,small,up,up2 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbroverlaps -down,left,right,up -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrtouches -down2,left2,right2,up2 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -mbrwithin -big,center -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -contains -center,small -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -disjoint -down3,left3,right3,up3 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -equals -center -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -intersect -big,center,down,down2,left,left2,right,right2,small,up,up2 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -overlaps -down,left,right,up -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -touches -down2,left2,right2,up2 -SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; -within -big,center -SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); -SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); -SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); -SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); -SET @point1 = GeomFromText('POLYGON ((0 0))'); -SET @point2 = GeomFromText('POLYGON ((-2 0))'); -SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name; -overlaps -SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name; -overlaps -SELECT Overlaps(@horiz1, @vert1) FROM DUAL; -Overlaps(@horiz1, @vert1) -0 -SELECT Overlaps(@horiz1, @horiz2) FROM DUAL; -Overlaps(@horiz1, @horiz2) -1 -SELECT Overlaps(@horiz1, @horiz3) FROM DUAL; -Overlaps(@horiz1, @horiz3) -0 -SELECT Overlaps(@horiz1, @point1) FROM DUAL; -Overlaps(@horiz1, @point1) -0 -SELECT Overlaps(@horiz1, @point2) FROM DUAL; -Overlaps(@horiz1, @point2) -0 -DROP TABLE t1; -End of 5.0 tests -CREATE TABLE t1 (p POINT); -CREATE TABLE t2 (p POINT, INDEX(p)); -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); -SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); -COUNT(*) -1 -EXPLAIN -SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref p p 28 const 1 Using where -SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); -COUNT(*) -1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); -EXPLAIN -SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); -COUNT(*) -2 -EXPLAIN -SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref p p 28 const 1 Using where -SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); -COUNT(*) -2 -EXPLAIN -SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); -COUNT(*) -2 -DROP TABLE t1, t2; -End of 5.0 tests -create table t1 (g geometry not null, spatial gk(g)) engine=innodb; -ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/r/innodb_lock_wait_timeout_1.result b/mysql-test/r/innodb_lock_wait_timeout_1.result deleted file mode 100644 index bd8760b8f79..00000000000 --- a/mysql-test/r/innodb_lock_wait_timeout_1.result +++ /dev/null @@ -1,375 +0,0 @@ -# -# Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout -# without error -# -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB; -INSERT INTO t1 (a,b) VALUES (1070109,99); -CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB; -INSERT INTO t2 (b,a) VALUES (7,1070109); -SELECT * FROM t1; -a b -1070109 99 -BEGIN; -SELECT b FROM t2 WHERE b=7 FOR UPDATE; -b -7 -BEGIN; -SELECT b FROM t2 WHERE b=7 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -SELECT * FROM t1; -a b -1070109 99 -DROP TABLE t2, t1; -# End of 5.0 tests -# -# Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT -# FOR UPDATE -# -drop table if exists t1; -create table t1 (a int primary key auto_increment, -b int, index(b)) engine=innodb; -insert into t1 (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -set autocommit=0; -begin; -select * from t1 where b=5 for update; -a b -5 5 -insert ignore into t1 (b) select a as b from t1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -# Cleanup -# -commit; -set autocommit=default; -drop table t1; -# -# Bug #37183 insert ignore into .. select ... hangs -# after deadlock was encountered -# -create table t1(id int primary key,v int)engine=innodb; -insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); -create table t2 like t1; -begin; -update t1 set v=id*2 where id=1; -begin; -update t1 set v=id*2 where id=2; -update t1 set v=id*2 where id=2; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -insert ignore into t2 select * from t1 where id=1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -rollback; -rollback; -drop table t1, t2; -# -# Bug#41756 Strange error messages about locks from InnoDB -# -drop table if exists t1; -# In the default transaction isolation mode, and/or with -# innodb_locks_unsafe_for_binlog=OFF, handler::unlock_row() -# in InnoDB does nothing. -# Thus in order to reproduce the condition that led to the -# warning, one needs to relax isolation by either -# setting a weaker tx_isolation value, or by turning on -# the unsafe replication switch. -# For testing purposes, choose to tweak the isolation level, -# since it's settable at runtime, unlike -# innodb_locks_unsafe_for_binlog, which is -# only a command-line switch. -# -set @@session.tx_isolation="read-committed"; -# Prepare data. We need a table with a unique index, -# for join_read_key to be used. The other column -# allows to control what passes WHERE clause filter. -create table t1 (a int primary key, b int) engine=innodb; -# Let's make sure t1 has sufficient amount of rows -# to exclude JT_ALL access method when reading it, -# i.e. make sure that JT_EQ_REF(a) is always preferred. -insert into t1 values (1,1), (2,null), (3,1), (4,1), -(5,1), (6,1), (7,1), (8,1), (9,1), (10,1), -(11,1), (12,1), (13,1), (14,1), (15,1), -(16,1), (17,1), (18,1), (19,1), (20,1); -# -# Demonstrate that for the SELECT statement -# used later in the test JT_EQ_REF access method is used. -# -explain -select 1 from t1 natural join (select 2 as a, 1 as b union all -select 2 as a, 2 as b) as t2 for update; -id 1 -select_type PRIMARY -table <derived2> -type ALL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows 2 -Extra -id 1 -select_type PRIMARY -table t1 -type eq_ref -possible_keys PRIMARY -key PRIMARY -key_len 4 -ref t2.a -rows 1 -Extra Using where -id 2 -select_type DERIVED -table NULL -type NULL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra No tables used -id 3 -select_type UNION -table NULL -type NULL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra No tables used -id NULL -select_type UNION RESULT -table <union2,3> -type ALL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra -# -# Demonstrate that the reported SELECT statement -# no longer produces warnings. -# -select 1 from t1 natural join (select 2 as a, 1 as b union all -select 2 as a, 2 as b) as t2 for update; -1 -commit; -# -# Demonstrate that due to lack of inter-sweep "reset" function, -# we keep some non-matching records locked, even though we know -# we could unlock them. -# To do that, show that if there is only one distinct value -# for a in t2 (a=2), we will keep record (2,null) in t1 locked. -# But if we add another value for "a" to t2, say 6, -# join_read_key cache will be pruned at least once, -# and thus record (2, null) in t1 will get unlocked. -# -begin; -select 1 from t1 natural join (select 2 as a, 1 as b union all -select 2 as a, 2 as b) as t2 for update; -1 -# -# Switching to connection con1 -# We should be able to delete all records from t1 except (2, null), -# since they were not locked. -begin; -# Delete in series of 3 records so that full scan -# is not used and we're not blocked on record (2,null) -delete from t1 where a in (1,3,4); -delete from t1 where a in (5,6,7); -delete from t1 where a in (8,9,10); -delete from t1 where a in (11,12,13); -delete from t1 where a in (14,15,16); -delete from t1 where a in (17,18); -delete from t1 where a in (19,20); -# -# Record (2, null) is locked. This is actually unnecessary, -# because the previous select returned no rows. -# Just demonstrate the effect. -# -delete from t1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -rollback; -# -# Switching to connection default -# -# Show that the original contents of t1 is intact: -select * from t1; -a b -1 1 -2 NULL -3 1 -4 1 -5 1 -6 1 -7 1 -8 1 -9 1 -10 1 -11 1 -12 1 -13 1 -14 1 -15 1 -16 1 -17 1 -18 1 -19 1 -20 1 -commit; -# -# Have a one more record in t2 to show that -# if join_read_key cache is purned, the current -# row under the cursor is unlocked (provided, this row didn't -# match the partial WHERE clause, of course). -# Sic: the result of this test dependent on the order of retrieval -# of records --echo # from the derived table, if ! -# We use DELETE to disable the JOIN CACHE. This DELETE modifies no -# records. It also should leave no InnoDB row locks. -# -begin; -delete t1.* from t1 natural join (select 2 as a, 2 as b union all -select 0 as a, 0 as b) as t2; -# Demonstrate that nothing was deleted form t1 -select * from t1; -a b -1 1 -2 NULL -3 1 -4 1 -5 1 -6 1 -7 1 -8 1 -9 1 -10 1 -11 1 -12 1 -13 1 -14 1 -15 1 -16 1 -17 1 -18 1 -19 1 -20 1 -# -# Switching to connection con1 -begin; -# Since there is another distinct record in the derived table -# the previous matching record in t1 -- (2,null) -- was unlocked. -delete from t1; -# We will need the contents of the table again. -rollback; -select * from t1; -a b -1 1 -2 NULL -3 1 -4 1 -5 1 -6 1 -7 1 -8 1 -9 1 -10 1 -11 1 -12 1 -13 1 -14 1 -15 1 -16 1 -17 1 -18 1 -19 1 -20 1 -commit; -# -# Switching to connection default -rollback; -begin; -# -# Before this patch, we could wrongly unlock a record -# that was cached and later used in a join. Demonstrate that -# this is no longer the case. -# Sic: this test is also order-dependent (i.e. the -# the bug would show up only if the first record in the union -# is retreived and processed first. -# -# Verify that JT_EQ_REF is used. -explain -select 1 from t1 natural join (select 3 as a, 2 as b union all -select 3 as a, 1 as b) as t2 for update; -id 1 -select_type PRIMARY -table <derived2> -type ALL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows 2 -Extra -id 1 -select_type PRIMARY -table t1 -type eq_ref -possible_keys PRIMARY -key PRIMARY -key_len 4 -ref t2.a -rows 1 -Extra Using where -id 2 -select_type DERIVED -table NULL -type NULL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra No tables used -id 3 -select_type UNION -table NULL -type NULL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra No tables used -id NULL -select_type UNION RESULT -table <union2,3> -type ALL -possible_keys NULL -key NULL -key_len NULL -ref NULL -rows NULL -Extra -# Lock the record. -select 1 from t1 natural join (select 3 as a, 2 as b union all -select 3 as a, 1 as b) as t2 for update; -1 -1 -# Switching to connection con1 -# -# We should not be able to delete record (3,1) from t1, -# (previously it was possible). -# -delete from t1 where a=3; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -# Switching to connection default -commit; -set @@session.tx_isolation=default; -drop table t1; -# -# End of 5.1 tests -# diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result deleted file mode 100644 index 7a19d913231..00000000000 --- a/mysql-test/r/innodb_mysql.result +++ /dev/null @@ -1,2389 +0,0 @@ -set global innodb_support_xa=default; -set session innodb_support_xa=default; -SET SESSION STORAGE_ENGINE = InnoDB; -drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; -drop procedure if exists p1; -create table t1 ( -c_id int(11) not null default '0', -org_id int(11) default null, -unique key contacts$c_id (c_id), -key contacts$org_id (org_id) -); -insert into t1 values -(2,null),(120,null),(141,null),(218,7), (128,1), -(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), -(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); -create table t2 ( -slai_id int(11) not null default '0', -owner_tbl int(11) default null, -owner_id int(11) default null, -sla_id int(11) default null, -inc_web int(11) default null, -inc_email int(11) default null, -inc_chat int(11) default null, -inc_csr int(11) default null, -inc_total int(11) default null, -time_billed int(11) default null, -activedate timestamp null default null, -expiredate timestamp null default null, -state int(11) default null, -sla_set int(11) default null, -unique key t2$slai_id (slai_id), -key t2$owner_id (owner_id), -key t2$sla_id (sla_id) -); -insert into t2(slai_id, owner_tbl, owner_id, sla_id) values -(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), -(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); -flush tables; -select si.slai_id -from t1 c join t2 si on -((si.owner_tbl = 3 and si.owner_id = c.org_id) or -( si.owner_tbl = 2 and si.owner_id = c.c_id)) -where -c.c_id = 218 and expiredate is null; -slai_id -12 -select * from t1 where org_id is null; -c_id org_id -2 NULL -120 NULL -141 NULL -select si.slai_id -from t1 c join t2 si on -((si.owner_tbl = 3 and si.owner_id = c.org_id) or -( si.owner_tbl = 2 and si.owner_id = c.c_id)) -where -c.c_id = 218 and expiredate is null; -slai_id -12 -drop table t1, t2; -CREATE TABLE t1 (a int, b int, KEY b (b)); -CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)); -CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), -UNIQUE KEY b (b,c), KEY a (a,b,c)); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; -INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; -INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); -INSERT INTO t2 SELECT a + 1, b FROM t2; -DELETE FROM t2 WHERE a = 1 AND b < 2; -INSERT INTO t3 VALUES (1,1,1),(2,1,2); -INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; -INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; -SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE -t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) -ORDER BY t1.b LIMIT 2; -b a -1 1 -2 2 -SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE -t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) -ORDER BY t1.b LIMIT 5; -b a -1 1 -2 2 -2 2 -3 3 -3 3 -DROP TABLE t1, t2, t3; -CREATE TABLE `t1` (`id1` INT) ; -INSERT INTO `t1` (`id1`) VALUES (1),(5),(2); -CREATE TABLE `t2` ( -`id1` INT, -`id2` INT NOT NULL, -`id3` INT, -`id4` INT NOT NULL, -UNIQUE (`id2`,`id4`), -KEY (`id1`) -); -INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES -(1,1,1,0), -(1,1,2,1), -(5,1,2,2), -(6,1,2,3), -(1,2,2,2), -(1,2,1,1); -SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2); -id1 -2 -DROP TABLE t1, t2; -create table t1 (c1 int) engine=innodb; -handler t1 open; -handler t1 read first; -c1 -Before and after comparison -0 -drop table t1; -CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) -ENGINE=INNODB CHARACTER SET UTF8; -INSERT INTO t1 (c1) VALUES ('1a'); -SELECT * FROM t1; -c1 cnt -1a 1 -INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; -SELECT * FROM t1; -c1 cnt -1a 2 -DROP TABLE t1; -CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) -ENGINE=INNODB CHARACTER SET UTF8; -INSERT INTO t1 (c1) VALUES ('1a'); -SELECT * FROM t1; -c1 cnt -1a 1 -INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; -SELECT * FROM t1; -c1 cnt -1a 2 -DROP TABLE t1; -CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) -ENGINE=INNODB CHARACTER SET UTF8; -INSERT INTO t1 (c1) VALUES ('1a'); -SELECT * FROM t1; -c1 cnt -1a 1 -INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; -SELECT * FROM t1; -c1 cnt -1a 2 -DROP TABLE t1; -CREATE TABLE t1 ( -a1 decimal(10,0) DEFAULT NULL, -a2 blob, -a3 time DEFAULT NULL, -a4 blob, -a5 char(175) DEFAULT NULL, -a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', -a7 tinyblob, -INDEX idx (a6,a7(239),a5) -) ENGINE=InnoDB; -EXPLAIN SELECT a4 FROM t1 WHERE -a6=NULL AND -a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE -t.a6=t.a6 AND t1.a6=NULL AND -t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -DROP TABLE t1; -create table t1m (a int) engine = MEMORY; -create table t1i (a int); -create table t2m (a int) engine = MEMORY; -create table t2i (a int); -insert into t2m values (5); -insert into t2i values (5); -select min(a) from t1i; -min(a) -NULL -select min(7) from t1i; -min(7) -NULL -select min(7) from DUAL; -min(7) -7 -explain select min(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer -select min(7) from t2i join t1i; -min(7) -NULL -select max(a) from t1i; -max(a) -NULL -select max(7) from t1i; -max(7) -NULL -select max(7) from DUAL; -max(7) -7 -explain select max(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer -select max(7) from t2i join t1i; -max(7) -NULL -select 1, min(a) from t1i where a=99; -1 min(a) -1 NULL -select 1, min(a) from t1i where 1=99; -1 min(a) -1 NULL -select 1, min(1) from t1i where a=99; -1 min(1) -1 NULL -select 1, min(1) from t1i where 1=99; -1 min(1) -1 NULL -select 1, max(a) from t1i where a=99; -1 max(a) -1 NULL -select 1, max(a) from t1i where 1=99; -1 max(a) -1 NULL -select 1, max(1) from t1i where a=99; -1 max(1) -1 NULL -select 1, max(1) from t1i where 1=99; -1 max(1) -1 NULL -explain select count(*), min(7), max(7) from t1m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t1m, t1i; -count(*) min(7) max(7) -0 NULL NULL -explain select count(*), min(7), max(7) from t1m, t2i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t1m, t2i; -count(*) min(7) max(7) -0 NULL NULL -explain select count(*), min(7), max(7) from t2m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2m system NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t2m, t1i; -count(*) min(7) max(7) -0 NULL NULL -drop table t1m, t1i, t2m, t2i; -create table t1 ( -a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' -) ENGINE = MEMORY; -insert into t1 (a1, a2, b, c, d) values -('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), -('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), -('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), -('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), -('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), -('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), -('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), -('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), -('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), -('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), -('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), -('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), -('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), -('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), -('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), -('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), -('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), -('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), -('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), -('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), -('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), -('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), -('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), -('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), -('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), -('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), -('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), -('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), -('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), -('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), -('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), -('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); -create table t4 ( -pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' -); -insert into t4 (a1, a2, b, c, d, dummy) select * from t1; -create index idx12672_0 on t4 (a1); -create index idx12672_1 on t4 (a1,a2,b,c); -create index idx12672_2 on t4 (a1,a2,b); -analyze table t4; -Table Op Msg_type Msg_text -test.t4 analyze status OK -select distinct a1 from t4 where pk_col not in (1,2,3,4); -a1 -a -b -c -d -drop table t1,t4; -DROP TABLE IF EXISTS t2, t1; -CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB; -CREATE TABLE t2 ( -i INT NOT NULL, -FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION -) ENGINE= InnoDB; -INSERT INTO t1 VALUES (1); -INSERT INTO t2 VALUES (1); -DELETE IGNORE FROM t1 WHERE i = 1; -Warnings: -Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION) -SELECT * FROM t1, t2; -i i -1 1 -DROP TABLE t2, t1; -End of 4.1 tests. -create table t1 ( -a varchar(30), b varchar(30), primary key(a), key(b) -); -select distinct a from t1; -a -drop table t1; -create table t1(a int, key(a)); -insert into t1 values(1); -select a, count(a) from t1 group by a with rollup; -a count(a) -1 1 -NULL 1 -drop table t1; -create table t1 (f1 int, f2 char(1), primary key(f1,f2)); -insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); -alter table t1 drop primary key, add primary key (f2, f1); -explain select distinct f1 a, f1 b from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary -explain select distinct f1, f2 from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary -drop table t1; -CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), -INDEX (name)); -CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); -ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); -INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); -INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); -EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id -WHERE t1.name LIKE 'A%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index -EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id -WHERE t1.name LIKE 'A%' OR FALSE; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where -DROP TABLE t1,t2; -CREATE TABLE t1 ( -id int NOT NULL, -name varchar(20) NOT NULL, -dept varchar(20) NOT NULL, -age tinyint(3) unsigned NOT NULL, -PRIMARY KEY (id), -INDEX (name,dept) -) ENGINE=InnoDB; -INSERT INTO t1(id, dept, age, name) VALUES -(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), -(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), -(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), -(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); -EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by -SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -name dept -rs5 cs10 -rs5 cs9 -DELETE FROM t1; -# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). -EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by -SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -name dept -DROP TABLE t1; -drop table if exists t1; -show variables like 'innodb_rollback_on_timeout'; -Variable_name Value -innodb_rollback_on_timeout OFF -create table t1 (a int unsigned not null primary key) engine = innodb; -insert into t1 values (1); -commit; -begin work; -insert into t1 values (2); -select * from t1; -a -1 -2 -begin work; -insert into t1 values (5); -select * from t1; -a -1 -5 -insert into t1 values (2); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -a -1 -5 -commit; -select * from t1; -a -1 -2 -commit; -select * from t1; -a -1 -2 -5 -drop table t1; -set @save_qcache_size=@@global.query_cache_size; -set @save_qcache_type=@@global.query_cache_type; -set global query_cache_size=10*1024*1024; -set global query_cache_type=1; -drop table if exists `test`; -Warnings: -Note 1051 Unknown table 'test' -CREATE TABLE `test` (`test1` varchar(3) NOT NULL, -`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) -ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); -select * from test; -test1 test2 -tes 5678 -INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') -ON DUPLICATE KEY UPDATE `test2` = '1234'; -select * from test; -test1 test2 -tes 1234 -flush tables; -select * from test; -test1 test2 -tes 1234 -drop table test; -set global query_cache_type=@save_qcache_type; -set global query_cache_size=@save_qcache_size; -drop table if exists t1; -show variables like 'innodb_rollback_on_timeout'; -Variable_name Value -innodb_rollback_on_timeout OFF -create table t1 (a int unsigned not null primary key) engine = innodb; -insert into t1 values (1); -commit; -begin work; -insert into t1 values (2); -select * from t1; -a -1 -2 -begin work; -insert into t1 values (5); -select * from t1; -a -1 -5 -insert into t1 values (2); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -a -1 -5 -commit; -select * from t1; -a -1 -2 -commit; -select * from t1; -a -1 -2 -5 -drop table t1; -create table t1( -id int auto_increment, -c char(1) not null, -counter int not null default 1, -primary key (id), -unique key (c) -) engine=innodb; -insert into t1 (id, c) values -(NULL, 'a'), -(NULL, 'a') -on duplicate key update id = values(id), counter = counter + 1; -select * from t1; -id c counter -2 a 2 -insert into t1 (id, c) values -(NULL, 'b') -on duplicate key update id = values(id), counter = counter + 1; -select * from t1; -id c counter -2 a 2 -3 b 1 -truncate table t1; -insert into t1 (id, c) values (NULL, 'a'); -select * from t1; -id c counter -1 a 1 -insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b') -on duplicate key update id = values(id), c = values(c), counter = counter + 1; -select * from t1; -id c counter -1 a 1 -3 b 2 -insert into t1 (id, c) values (NULL, 'a') -on duplicate key update id = values(id), c = values(c), counter = counter + 1; -select * from t1; -id c counter -3 b 2 -4 a 2 -drop table t1; -CREATE TABLE t1( -id int AUTO_INCREMENT PRIMARY KEY, -stat_id int NOT NULL, -acct_id int DEFAULT NULL, -INDEX idx1 (stat_id, acct_id), -INDEX idx2 (acct_id) -) ENGINE=MyISAM; -CREATE TABLE t2( -id int AUTO_INCREMENT PRIMARY KEY, -stat_id int NOT NULL, -acct_id int DEFAULT NULL, -INDEX idx1 (stat_id, acct_id), -INDEX idx2 (acct_id) -) ENGINE=InnoDB; -INSERT INTO t1(stat_id,acct_id) VALUES -(1,759), (2,831), (3,785), (4,854), (1,921), -(1,553), (2,589), (3,743), (2,827), (2,545), -(4,779), (4,783), (1,597), (1,785), (4,832), -(1,741), (1,833), (3,788), (2,973), (1,907); -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; -UPDATE t1 SET acct_id=785 -WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SELECT COUNT(*) FROM t1; -COUNT(*) -40960 -SELECT COUNT(*) FROM t1 WHERE acct_id=785; -COUNT(*) -8702 -EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index -INSERT INTO t2 SELECT * FROM t1; -OPTIMIZE TABLE t2; -Table Op Msg_type Msg_text -test.t2 optimize note Table does not support optimize, doing recreate + analyze instead -test.t2 optimize status OK -EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index -DROP TABLE t1,t2; -create table t1(a int) engine=innodb; -alter table t1 comment '123'; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123' -drop table t1; -CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; -INSERT INTO t1 VALUES ('uk'),('bg'); -SELECT * FROM t1 WHERE a = 'uk'; -a -uk -DELETE FROM t1 WHERE a = 'uk'; -SELECT * FROM t1 WHERE a = 'uk'; -a -UPDATE t1 SET a = 'us' WHERE a = 'uk'; -SELECT * FROM t1 WHERE a = 'uk'; -a -CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; -INSERT INTO t2 VALUES ('uk'),('bg'); -SELECT * FROM t2 WHERE a = 'uk'; -a -uk -DELETE FROM t2 WHERE a = 'uk'; -SELECT * FROM t2 WHERE a = 'uk'; -a -INSERT INTO t2 VALUES ('uk'); -UPDATE t2 SET a = 'us' WHERE a = 'uk'; -SELECT * FROM t2 WHERE a = 'uk'; -a -CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; -INSERT INTO t3 VALUES ('uk'),('bg'); -SELECT * FROM t3 WHERE a = 'uk'; -a -uk -DELETE FROM t3 WHERE a = 'uk'; -SELECT * FROM t3 WHERE a = 'uk'; -a -INSERT INTO t3 VALUES ('uk'); -UPDATE t3 SET a = 'us' WHERE a = 'uk'; -SELECT * FROM t3 WHERE a = 'uk'; -a -DROP TABLE t1,t2,t3; -create table t1 (a int) engine=innodb; -select * from bug29807; -ERROR 42S02: Table 'test.bug29807' doesn't exist -drop table t1; -drop table bug29807; -ERROR 42S02: Unknown table 'bug29807' -create table bug29807 (a int); -drop table bug29807; -CREATE TABLE t1 (a INT) ENGINE=InnoDB; -CREATE TABLE t2 (a INT) ENGINE=InnoDB; -switch to connection c1 -SET AUTOCOMMIT=0; -INSERT INTO t2 VALUES (1); -switch to connection c2 -SET AUTOCOMMIT=0; -LOCK TABLES t1 READ, t2 READ; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -switch to connection c1 -COMMIT; -INSERT INTO t1 VALUES (1); -switch to connection default -SET AUTOCOMMIT=default; -DROP TABLE t1,t2; -CREATE TABLE t1 ( -id int NOT NULL auto_increment PRIMARY KEY, -b int NOT NULL, -c datetime NOT NULL, -INDEX idx_b(b), -INDEX idx_c(c) -) ENGINE=InnoDB; -CREATE TABLE t2 ( -b int NOT NULL auto_increment PRIMARY KEY, -c datetime NOT NULL -) ENGINE= MyISAM; -INSERT INTO t2(c) VALUES ('2007-01-01'); -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t2(c) SELECT c FROM t2; -INSERT INTO t1(b,c) SELECT b,c FROM t2; -UPDATE t2 SET c='2007-01-02'; -INSERT INTO t1(b,c) SELECT b,c FROM t2; -UPDATE t2 SET c='2007-01-03'; -INSERT INTO t1(b,c) SELECT b,c FROM t2; -set @@sort_buffer_size=8192; -Warnings: -Warning 1292 Truncated incorrect sort_buffer_size value: '8192' -SELECT COUNT(*) FROM t1; -COUNT(*) -3072 -EXPLAIN -SELECT COUNT(*) FROM t1 -WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where -SELECT COUNT(*) FROM t1 -WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -COUNT(*) -3072 -EXPLAIN -SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) -WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 8,4 NULL # Using sort_union(idx_c,idx_b); Using where -SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) -WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -COUNT(*) -3072 -set @@sort_buffer_size=default; -DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b int); -insert into t1 values (1,1),(1,2); -CREATE TABLE t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -drop table if exists t2; -Warnings: -Note 1051 Unknown table 't2' -CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -drop table if exists t2; -Warnings: -Note 1051 Unknown table 't2' -CREATE TABLE t2 (a int, b int, primary key (a)); -BEGIN; -INSERT INTO t2 values(100,100); -CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -100 100 -ROLLBACK; -SELECT * from t2; -a b -100 100 -TRUNCATE table t2; -INSERT INTO t2 select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -drop table t2; -CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); -BEGIN; -INSERT INTO t2 values(100,100); -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -100 100 -COMMIT; -BEGIN; -INSERT INTO t2 values(101,101); -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -100 100 -101 101 -ROLLBACK; -SELECT * from t2; -a b -100 100 -TRUNCATE table t2; -INSERT INTO t2 select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -drop table t1,t2; -create table t1(f1 varchar(800) binary not null, key(f1)) -character set utf8 collate utf8_general_ci; -Warnings: -Warning 1071 Specified key was too long; max key length is 767 bytes -insert into t1 values('aaa'); -drop table t1; -CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; -INSERT INTO t1 VALUES ( 1 , 1 , 1); -INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; -EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 5 NULL 128 -EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort -DROP TABLE t1; -drop table if exists t1; -show variables like 'innodb_rollback_on_timeout'; -Variable_name Value -innodb_rollback_on_timeout OFF -create table t1 (a int unsigned not null primary key) engine = innodb; -insert into t1 values (1); -commit; -begin work; -insert into t1 values (2); -select * from t1; -a -1 -2 -begin work; -insert into t1 values (5); -select * from t1; -a -1 -5 -insert into t1 values (2); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -a -1 -5 -commit; -select * from t1; -a -1 -2 -commit; -select * from t1; -a -1 -2 -5 -drop table t1; -drop table if exists t1; -create table t1 (a int) engine=innodb; -alter table t1 alter a set default 1; -drop table t1; - -Bug#24918 drop table and lock / inconsistent between -perm and temp tables - -Check transactional tables under LOCK TABLES - -drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, -t24918_access; -create table t24918_access (id int); -create table t24918 (id int) engine=myisam; -create temporary table t24918_tmp (id int) engine=myisam; -create table t24918_trans (id int) engine=innodb; -create temporary table t24918_trans_tmp (id int) engine=innodb; -lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write; -drop table t24918; -select * from t24918_access; -ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES -drop table t24918_trans; -select * from t24918_access; -ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES -drop table t24918_trans_tmp; -select * from t24918_access; -ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES -drop table t24918_tmp; -select * from t24918_access; -ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES -unlock tables; -drop table t24918_access; -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2); -INSERT INTO t1 SELECT a + 8, 2 FROM t1; -INSERT INTO t1 SELECT a + 16, 1 FROM t1; -EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a; -id 1 -select_type SIMPLE -table t1 -type ref -possible_keys bkey -key bkey -key_len 5 -ref const -rows 16 -Extra Using where; Using index -SELECT * FROM t1 WHERE b=2 ORDER BY a; -a b -1 2 -2 2 -3 2 -4 2 -5 2 -6 2 -7 2 -8 2 -9 2 -10 2 -11 2 -12 2 -13 2 -14 2 -15 2 -16 2 -EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; -id 1 -select_type SIMPLE -table t1 -type range -possible_keys bkey -key bkey -key_len 5 -ref NULL -rows 16 -Extra Using where; Using index; Using filesort -SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; -a b -1 2 -2 2 -3 2 -4 2 -5 2 -6 2 -7 2 -8 2 -9 2 -10 2 -11 2 -12 2 -13 2 -14 2 -15 2 -16 2 -17 1 -18 1 -19 1 -20 1 -21 1 -22 1 -23 1 -24 1 -25 1 -26 1 -27 1 -28 1 -29 1 -30 1 -31 1 -32 1 -EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; -id 1 -select_type SIMPLE -table t1 -type range -possible_keys bkey -key bkey -key_len 5 -ref NULL -rows 16 -Extra Using where; Using index -SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; -a b -17 1 -18 1 -19 1 -20 1 -21 1 -22 1 -23 1 -24 1 -25 1 -26 1 -27 1 -28 1 -29 1 -30 1 -31 1 -32 1 -1 2 -2 2 -3 2 -4 2 -5 2 -6 2 -7 2 -8 2 -9 2 -10 2 -11 2 -12 2 -13 2 -14 2 -15 2 -16 2 -CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c)) -ENGINE=InnoDB; -INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1); -INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2; -INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2; -EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; -id 1 -select_type SIMPLE -table t2 -type ref -possible_keys bkey -key bkey -key_len 5 -ref const -rows 8 -Extra Using where; Using index; Using filesort -SELECT * FROM t2 WHERE b=1 ORDER BY a; -a b c -1 1 1 -2 1 1 -3 1 1 -4 1 1 -5 1 1 -6 1 1 -7 1 1 -8 1 1 -9 1 1 -10 1 1 -11 1 1 -12 1 1 -13 1 1 -14 1 1 -15 1 1 -16 1 1 -EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; -id 1 -select_type SIMPLE -table t2 -type ref -possible_keys bkey -key bkey -key_len 10 -ref const,const -rows 8 -Extra Using where; Using index -SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; -a b c -1 1 1 -2 1 1 -3 1 1 -4 1 1 -5 1 1 -6 1 1 -7 1 1 -8 1 1 -9 1 1 -10 1 1 -11 1 1 -12 1 1 -13 1 1 -14 1 1 -15 1 1 -16 1 1 -EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; -id 1 -select_type SIMPLE -table t2 -type ref -possible_keys bkey -key bkey -key_len 10 -ref const,const -rows 8 -Extra Using where; Using index -SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; -a b c -1 1 1 -2 1 1 -3 1 1 -4 1 1 -5 1 1 -6 1 1 -7 1 1 -8 1 1 -9 1 1 -10 1 1 -11 1 1 -12 1 1 -13 1 1 -14 1 1 -15 1 1 -16 1 1 -EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; -id 1 -select_type SIMPLE -table t2 -type ref -possible_keys bkey -key bkey -key_len 10 -ref const,const -rows 8 -Extra Using where; Using index -SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; -a b c -1 1 1 -2 1 1 -3 1 1 -4 1 1 -5 1 1 -6 1 1 -7 1 1 -8 1 1 -9 1 1 -10 1 1 -11 1 1 -12 1 1 -13 1 1 -14 1 1 -15 1 1 -16 1 1 -DROP TABLE t1,t2; -CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); -INSERT INTO t1 SELECT a + 8 FROM t1; -INSERT INTO t1 SELECT a + 16 FROM t1; -CREATE PROCEDURE p1 () -BEGIN -DECLARE i INT DEFAULT 50; -DECLARE cnt INT; -START TRANSACTION; -ALTER TABLE t1 ENGINE=InnoDB; -COMMIT; -START TRANSACTION; -WHILE (i > 0) DO -SET i = i - 1; -SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE; -END WHILE; -COMMIT; -END;| -CALL p1(); -CALL p1(); -CALL p1(); -DROP PROCEDURE p1; -DROP TABLE t1; -create table t1(a text) engine=innodb default charset=utf8; -insert into t1 values('aaa'); -alter table t1 add index(a(1024)); -Warnings: -Warning 1071 Specified key was too long; max key length is 767 bytes -Warning 1071 Specified key was too long; max key length is 767 bytes -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` text, - KEY `a` (`a`(255)) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 -drop table t1; -CREATE TABLE t1 ( -a INT, -b INT, -KEY (b) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); -START TRANSACTION; -SELECT * FROM t1 WHERE b=20 FOR UPDATE; -a b -2 20 -START TRANSACTION; -SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; -a b -1 10 -2 10 -ROLLBACK; -ROLLBACK; -DROP TABLE t1; -CREATE TABLE t1( -a INT, -b INT NOT NULL, -c INT NOT NULL, -d INT, -UNIQUE KEY (c,b) -) engine=innodb; -INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); -EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort -SELECT c,b,d FROM t1 GROUP BY c,b,d; -c b d -1 1 50 -3 1 4 -3 2 40 -EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; -c b d -1 1 50 -3 1 4 -3 2 40 -EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort -SELECT c,b,d FROM t1 ORDER BY c,b,d; -c b d -1 1 50 -3 1 4 -3 2 40 -EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 8 NULL 3 -SELECT c,b,d FROM t1 GROUP BY c,b; -c b d -1 1 50 -3 1 4 -3 2 40 -EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 8 NULL 3 Using index -SELECT c,b FROM t1 GROUP BY c,b; -c b -1 1 -3 1 -3 2 -DROP TABLE t1; -CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB; -INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2); -EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; -id 1 -select_type SIMPLE -table t1 -type ref -possible_keys b -key b -key_len 5 -ref const -rows 1 -Extra Using where; Using index -SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; -a b -2 2 -3 2 -EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; -id 1 -select_type SIMPLE -table t1 -type ref -possible_keys b -key b -key_len 5 -ref const -rows 1 -Extra Using where; Using index -SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; -a b -3 2 -2 2 -EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC; -id 1 -select_type SIMPLE -table t1 -type index -possible_keys NULL -key b -key_len 5 -ref NULL -rows 3 -Extra Using index -SELECT * FROM t1 ORDER BY b ASC, a ASC; -a b -1 1 -2 2 -3 2 -EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC; -id 1 -select_type SIMPLE -table t1 -type index -possible_keys NULL -key b -key_len 5 -ref NULL -rows 3 -Extra Using index -SELECT * FROM t1 ORDER BY b DESC, a DESC; -a b -3 2 -2 2 -1 1 -EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC; -id 1 -select_type SIMPLE -table t1 -type index -possible_keys NULL -key b -key_len 5 -ref NULL -rows 3 -Extra Using index; Using filesort -SELECT * FROM t1 ORDER BY b ASC, a DESC; -a b -1 1 -3 2 -2 2 -EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC; -id 1 -select_type SIMPLE -table t1 -type index -possible_keys NULL -key b -key_len 5 -ref NULL -rows 3 -Extra Using index; Using filesort -SELECT * FROM t1 ORDER BY b DESC, a ASC; -a b -2 2 -3 2 -1 1 -DROP TABLE t1; - -# -# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table. -# - -# - prepare; - -DROP TABLE IF EXISTS t1; - -CREATE TABLE t1(c INT) -ENGINE = InnoDB -ROW_FORMAT = COMPACT; - -# - initial check; - -SELECT table_schema, table_name, row_format -FROM INFORMATION_SCHEMA.TABLES -WHERE table_schema = DATABASE() AND table_name = 't1'; -table_schema table_name row_format -test t1 Compact - -# - change ROW_FORMAT and check; - -ALTER TABLE t1 ROW_FORMAT = REDUNDANT; - -SELECT table_schema, table_name, row_format -FROM INFORMATION_SCHEMA.TABLES -WHERE table_schema = DATABASE() AND table_name = 't1'; -table_schema table_name row_format -test t1 Redundant - -# - that's it, cleanup. - -DROP TABLE t1; -create table t1(a char(10) not null, unique key aa(a(1)), -b char(4) not null, unique key bb(b(4))) engine=innodb; -desc t1; -Field Type Null Key Default Extra -a char(10) NO UNI NULL -b char(4) NO PRI NULL -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` char(10) NOT NULL, - `b` char(4) NOT NULL, - UNIQUE KEY `bb` (`b`), - UNIQUE KEY `aa` (`a`(1)) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -drop table t1; -CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; -INSERT INTO t1 VALUES -(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); -EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort -SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; -id type d -191 member 1 -NULL member 3 -NULL member 4 -DROP TABLE t1; -set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; -set global innodb_autoextend_increment=8; -set global innodb_autoextend_increment=@my_innodb_autoextend_increment; -set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; -set global innodb_commit_concurrency=0; -set global innodb_commit_concurrency=@my_innodb_commit_concurrency; -CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) -ENGINE=InnoDB; -INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); -INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; -EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index t1_b PRIMARY 4 NULL 8 Using where -SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; -a b c -8 1 1 -7 1 1 -6 1 1 -5 1 1 -4 1 1 -DROP TABLE t1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; -CREATE INDEX i1 on t1 (a(3)); -SELECT * FROM t1 WHERE a = 'abcde'; -a -DROP TABLE t1; -# -# BUG #26288: savepoint are not deleted on comit, if the transaction -# was otherwise empty -# -BEGIN; -SAVEPOINT s1; -COMMIT; -RELEASE SAVEPOINT s1; -ERROR 42000: SAVEPOINT s1 does not exist -BEGIN; -SAVEPOINT s2; -COMMIT; -ROLLBACK TO SAVEPOINT s2; -ERROR 42000: SAVEPOINT s2 does not exist -BEGIN; -SAVEPOINT s3; -ROLLBACK; -RELEASE SAVEPOINT s3; -ERROR 42000: SAVEPOINT s3 does not exist -BEGIN; -SAVEPOINT s4; -ROLLBACK; -ROLLBACK TO SAVEPOINT s4; -ERROR 42000: SAVEPOINT s4 does not exist -CREATE TABLE t1 (f1 INTEGER PRIMARY KEY COMMENT 'My ID#', f2 INTEGER DEFAULT NULL, f3 CHAR(10) DEFAULT 'My ID#', CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=INNODB; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `f1` int(11) NOT NULL COMMENT 'My ID#', - `f2` int(11) DEFAULT NULL, - `f3` char(10) DEFAULT 'My ID#', - PRIMARY KEY (`f1`), - KEY `f2_ref` (`f2`), - CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t1; -# -# Bug #36995: valgrind error in remove_const during subquery executions -# -create table t1 (a bit(1) not null,b int) engine=myisam; -create table t2 (c int) engine=innodb; -explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 -DROP TABLE t1,t2; -End of 5.0 tests -CREATE TABLE `t2` ( -`k` int(11) NOT NULL auto_increment, -`a` int(11) default NULL, -`c` int(11) default NULL, -PRIMARY KEY (`k`), -UNIQUE KEY `idx_1` (`a`) -); -insert into t2 ( a ) values ( 6 ) on duplicate key update c = -ifnull( c, -0 ) + 1; -insert into t2 ( a ) values ( 7 ) on duplicate key update c = -ifnull( c, -0 ) + 1; -select last_insert_id(); -last_insert_id() -2 -select * from t2; -k a c -1 6 NULL -2 7 NULL -insert into t2 ( a ) values ( 6 ) on duplicate key update c = -ifnull( c, -0 ) + 1; -select last_insert_id(); -last_insert_id() -2 -select last_insert_id(0); -last_insert_id(0) -0 -insert into t2 ( a ) values ( 6 ) on duplicate key update c = -ifnull( c, -0 ) + 1; -select last_insert_id(); -last_insert_id() -0 -select * from t2; -k a c -1 6 2 -2 7 NULL -insert ignore into t2 values (null,6,1),(10,8,1); -select last_insert_id(); -last_insert_id() -0 -insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1); -select last_insert_id(); -last_insert_id() -11 -select * from t2; -k a c -1 6 2 -2 7 NULL -10 8 1 -11 15 1 -12 20 1 -insert into t2 ( a ) values ( 6 ) on duplicate key update c = -ifnull( c, -0 ) + 1, k=last_insert_id(k); -select last_insert_id(); -last_insert_id() -1 -select * from t2; -k a c -1 6 3 -2 7 NULL -10 8 1 -11 15 1 -12 20 1 -drop table t2; -drop table if exists t1, t2; -create table t1 (i int); -alter table t1 modify i int default 1; -alter table t1 modify i int default 2, rename t2; -lock table t2 write; -alter table t2 modify i int default 3; -unlock tables; -lock table t2 write; -alter table t2 modify i int default 4, rename t1; -unlock tables; -drop table t1; -drop table if exists t1; -create table t1 (i int); -insert into t1 values (); -lock table t1 write; -alter table t1 modify i int default 1; -insert into t1 values (); -select * from t1; -i -NULL -1 -alter table t1 change i c char(10) default "Two"; -insert into t1 values (); -select * from t1; -c -NULL -1 -Two -unlock tables; -select * from t1; -c -NULL -1 -Two -drop tables t1; -create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT -CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); -insert into t1(f1) values(1); -select @a:=f2 from t1; -@a:=f2 -# -update t1 set f1=1; -select @b:=f2 from t1; -@b:=f2 -# -select if(@a=@b,"ok","wrong"); -if(@a=@b,"ok","wrong") -ok -insert into t1(f1) values (1) on duplicate key update f1="1"; -select @b:=f2 from t1; -@b:=f2 -# -select if(@a=@b,"ok","wrong"); -if(@a=@b,"ok","wrong") -ok -insert into t1(f1) select f1 from t1 on duplicate key update f1="1"; -select @b:=f2 from t1; -@b:=f2 -# -select if(@a=@b,"ok","wrong"); -if(@a=@b,"ok","wrong") -ok -drop table t1; -SET SESSION AUTOCOMMIT = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -set binlog_format=mixed; -# Switch to connection con1 -CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) -ENGINE = InnoDB; -INSERT INTO t1 VALUES (1,2); -# 1. test for locking: -BEGIN; -UPDATE t1 SET b = 12 WHERE a = 1; -affected rows: 1 -info: Rows matched: 1 Changed: 1 Warnings: 0 -SELECT * FROM t1; -a b -1 12 -# Switch to connection con2 -UPDATE t1 SET b = 21 WHERE a = 1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -# Switch to connection con1 -SELECT * FROM t1; -a b -1 12 -ROLLBACK; -# 2. test for serialized update: -CREATE TABLE t2 (a INT); -TRUNCATE t1; -INSERT INTO t1 VALUES (1,'init'); -CREATE PROCEDURE p1() -BEGIN -UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; -INSERT INTO t2 VALUES (); -END| -BEGIN; -UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; -affected rows: 1 -info: Rows matched: 1 Changed: 1 Warnings: 0 -SELECT * FROM t1; -a b -1 init+con1 -# Switch to connection con2 -CALL p1;; -# Switch to connection con1 -SELECT * FROM t1; -a b -1 init+con1 -COMMIT; -SELECT * FROM t1; -a b -1 init+con1 -# Switch to connection con2 -SELECT * FROM t1; -a b -1 init+con1+con2 -# Switch to connection con1 -# 3. test for updated key column: -TRUNCATE t1; -TRUNCATE t2; -INSERT INTO t1 VALUES (1,'init'); -BEGIN; -UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; -affected rows: 1 -info: Rows matched: 1 Changed: 1 Warnings: 0 -SELECT * FROM t1; -a b -2 init+con1 -# Switch to connection con2 -CALL p1;; -# Switch to connection con1 -SELECT * FROM t1; -a b -2 init+con1 -COMMIT; -SELECT * FROM t1; -a b -2 init+con1 -# Switch to connection con2 -SELECT * FROM t1; -a b -2 init+con1 -DROP PROCEDURE p1; -DROP TABLE t1, t2; -CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION, -CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; -ALTER TABLE t2 DROP FOREIGN KEY c2; -DROP TABLE t2; -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; -ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match -CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), -CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION, -CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION, -FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION, -FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c` int(11) NOT NULL, - `d` int(11) NOT NULL, - PRIMARY KEY (`c`,`d`), - CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION, - CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, - CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, - CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -DROP TABLE t2; -DROP TABLE t1; -create table t1 (a int auto_increment primary key) engine=innodb; -alter table t1 order by a; -Warnings: -Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1' -drop table t1; -CREATE TABLE t1 -(vid integer NOT NULL, -tid integer NOT NULL, -idx integer NOT NULL, -name varchar(128) NOT NULL, -type varchar(128) NULL, -PRIMARY KEY(idx, vid, tid), -UNIQUE(vid, tid, name) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES -(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL), -(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL), -(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL), -(4,1,3,'pk',NULL),(5,1,3,'c2',NULL), -(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); -EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where -SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; -vid tid idx name type -3 1 4 c_extra NULL -3 1 3 c2 NULL -3 1 2 c1 NULL -3 1 1 pk NULL -DROP TABLE t1; -# -# Bug #44290: explain crashes for subquery with distinct in -# SQL_SELECT::test_quick_select -# (reproduced only with InnoDB tables) -# -CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3)) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -1 -1 -EXPLAIN -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 10 NULL 5 -DROP TABLE t1; -CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -1 -1 -EXPLAIN -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 18 NULL 5 -DROP TABLE t1; -CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), -KEY (c3), KEY (c2, c3)) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -1 -1 -EXPLAIN -SELECT 1 FROM (SELECT COUNT(DISTINCT c1) -FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 14 NULL 5 -DROP TABLE t1; -End of 5.1 tests -drop table if exists t1, t2, t3; -create table t1(a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; -insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; -this must use key 'a', not PRIMARY: -explain select a from t2 where a=b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index -drop table t1, t2; -SET SESSION BINLOG_FORMAT=STATEMENT; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; -@@session.sql_log_bin 1 -@@session.binlog_format STATEMENT -@@session.tx_isolation READ-COMMITTED -CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; -INSERT INTO t1 VALUES(1); -DROP TABLE t1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; -CREATE INDEX i1 on t1 (a(3)); -SELECT * FROM t1 WHERE a = 'abcde'; -a -DROP TABLE t1; -CREATE TABLE foo (a int, b int, c char(10), -PRIMARY KEY (c(3)), -KEY b (b) -) engine=innodb; -CREATE TABLE foo2 (a int, b int, c char(10), -PRIMARY KEY (c), -KEY b (b) -) engine=innodb; -CREATE TABLE bar (a int, b int, c char(10), -PRIMARY KEY (c(3)), -KEY b (b) -) engine=myisam; -INSERT INTO foo VALUES -(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'), -(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'); -INSERT INTO bar SELECT * FROM foo; -INSERT INTO foo2 SELECT * FROM foo; -EXPLAIN SELECT c FROM bar WHERE b>2;; -id 1 -select_type SIMPLE -table bar -type ALL -possible_keys b -key NULL -key_len NULL -ref NULL -rows 6 -Extra Using where -EXPLAIN SELECT c FROM foo WHERE b>2;; -id 1 -select_type SIMPLE -table foo -type ALL -possible_keys b -key NULL -key_len NULL -ref NULL -rows 6 -Extra Using where -EXPLAIN SELECT c FROM foo2 WHERE b>2;; -id 1 -select_type SIMPLE -table foo2 -type range -possible_keys b -key b -key_len 5 -ref NULL -rows 3 -Extra Using where; Using index -EXPLAIN SELECT c FROM bar WHERE c>2;; -id 1 -select_type SIMPLE -table bar -type ALL -possible_keys PRIMARY -key NULL -key_len NULL -ref NULL -rows 6 -Extra Using where -EXPLAIN SELECT c FROM foo WHERE c>2;; -id 1 -select_type SIMPLE -table foo -type ALL -possible_keys PRIMARY -key NULL -key_len NULL -ref NULL -rows 6 -Extra Using where -EXPLAIN SELECT c FROM foo2 WHERE c>2;; -id 1 -select_type SIMPLE -table foo2 -type index -possible_keys PRIMARY -key b -key_len 5 -ref NULL -rows 6 -Extra Using where; Using index -DROP TABLE foo, bar, foo2; -DROP TABLE IF EXISTS t1,t3,t2; -DROP FUNCTION IF EXISTS f1; -CREATE FUNCTION f1() RETURNS VARCHAR(250) -BEGIN -return 'hhhhhhh' ; -END| -CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB; -BEGIN WORK; -CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB; -CREATE TEMPORARY TABLE t3 LIKE t2; -INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL); -SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl'); -PREPARE stmt1 FROM @stmt; -SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2'); -PREPARE stmt3 FROM @stmt; -EXECUTE stmt1; -COMMIT; -DEALLOCATE PREPARE stmt1; -DEALLOCATE PREPARE stmt3; -DROP TABLE t1,t3,t2; -DROP FUNCTION f1; -DROP TABLE IF EXISTS t1,t2; -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), -FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB; -INSERT INTO t1 VALUES (1),(2); -INSERT INTO t2 VALUES (3,2); -SET AUTOCOMMIT = 0; -START TRANSACTION; -TRUNCATE TABLE t1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) -SELECT * FROM t1; -id -1 -2 -COMMIT; -SELECT * FROM t1; -id -1 -2 -START TRANSACTION; -TRUNCATE TABLE t1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) -SELECT * FROM t1; -id -1 -2 -ROLLBACK; -SELECT * FROM t1; -id -1 -2 -SET AUTOCOMMIT = 1; -START TRANSACTION; -SELECT * FROM t1; -id -1 -2 -COMMIT; -TRUNCATE TABLE t1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)) -SELECT * FROM t1; -id -1 -2 -DELETE FROM t2 WHERE id = 3; -START TRANSACTION; -SELECT * FROM t1; -id -1 -2 -TRUNCATE TABLE t1; -ROLLBACK; -SELECT * FROM t1; -id -TRUNCATE TABLE t2; -DROP TABLE t2; -DROP TABLE t1; -# -# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 -# -CREATE TABLE t1 ( -id INT UNSIGNED NOT NULL AUTO_INCREMENT, -PRIMARY KEY (id) -) ENGINE=InnoDB; -CREATE TABLE t2 ( -id INT UNSIGNED NOT NULL AUTO_INCREMENT, -aid INT UNSIGNED NOT NULL, -PRIMARY KEY (id), -FOREIGN KEY (aid) REFERENCES t1 (id) -) ENGINE=InnoDB; -CREATE TABLE t3 ( -bid INT UNSIGNED NOT NULL, -FOREIGN KEY (bid) REFERENCES t2 (id) -) ENGINE=InnoDB; -CREATE TABLE t4 ( -a INT -) ENGINE=InnoDB; -CREATE TABLE t5 ( -a INT -) ENGINE=InnoDB; -INSERT INTO t1 (id) VALUES (1); -INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1); -INSERT INTO t3 (bid) VALUES (1); -INSERT INTO t4 VALUES (1),(2),(3),(4),(5); -INSERT INTO t5 VALUES (1); -DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a; -DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`)) -DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`)) -DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; -DROP TABLE t3; -DROP TABLE t2; -DROP TABLE t1; -DROP TABLES t4,t5; -# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 -# Testing for any side effects of IGNORE on AFTER DELETE triggers used with -# transactional tables. -# -CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB; -CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, -FOREIGN KEY (t1i) REFERENCES t1(i)) -ENGINE=InnoDB; -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN -SET @b:='EXECUTED TRIGGER'; -INSERT INTO t2 VALUES (@b); -SET @a:= error_happens_here; -END|| -SET @b:=""; -SET @a:=""; -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 SELECT * FROM t1; -** An error in a trigger causes rollback of the statement. -DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -ERROR 42S22: Unknown column 'error_happens_here' in 'field list' -SELECT @a,@b; -@a @b - EXECUTED TRIGGER -SELECT * FROM t2; -a -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; -i i -1 1 -2 2 -3 3 -4 4 -** Same happens with the IGNORE option -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -ERROR 42S22: Unknown column 'error_happens_here' in 'field list' -SELECT * FROM t2; -a -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; -i i -1 1 -2 2 -3 3 -4 4 -** -** The following is an attempt to demonstrate -** error handling inside a row iteration. -** -DROP TRIGGER trg; -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; -TRUNCATE TABLE t3; -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 VALUES (1),(2),(3),(4); -INSERT INTO t4 VALUES (3,3),(4,4); -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN -SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR)); -INSERT INTO t2 VALUES (@b); -END|| -** DELETE is prevented by foreign key constrains but errors are silenced. -** The AFTER trigger isn't fired. -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -** Tables are modified by best effort: -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; -i i -3 3 -4 4 -** The AFTER trigger was only executed on successful rows: -SELECT * FROM t2; -a -EXECUTED TRIGGER FOR ROW 1 -EXECUTED TRIGGER FOR ROW 2 -DROP TRIGGER trg; -** -** Induce an error midway through an AFTER-trigger -** -TRUNCATE TABLE t4; -TRUNCATE TABLE t1; -TRUNCATE TABLE t3; -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 VALUES (1),(2),(3),(4); -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN -SET @a:= @a+1; -IF @a > 2 THEN -INSERT INTO t4 VALUES (5,5); -END IF; -END|| -SET @a:=0; -** Errors in the trigger causes the statement to abort. -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`t1i`) REFERENCES `t1` (`i`)) -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; -i i -1 1 -2 2 -3 3 -4 4 -SELECT * FROM t4; -i t1i -DROP TRIGGER trg; -DROP TABLE t4; -DROP TABLE t1; -DROP TABLE t2; -DROP TABLE t3; -CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB; -CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; -CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB; -CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6); -INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106); -INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10 -WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b; -SELECT * FROM t2; -a b -1 1 -2 12 -3 13 -4 14 -5 5 -UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10 -WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100; -SELECT * FROM t4; -a b -1 1 -2 12 -3 13 -4 14 -5 5 -DROP TABLE t1, t2, t3, t4; -# -# Bug#44886: SIGSEGV in test_if_skip_sort_order() - -# uninitialized variable used as subscript -# -CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c)) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,1,1,0); -CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB; -INSERT INTO t2 VALUES (1,1,2); -CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM; -INSERT INTO t3 VALUES (1, 1); -SELECT * FROM t1, t2, t3 -WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2 -GROUP BY t1.b; -a b c d a b e a b -1 1 1 0 1 1 2 1 1 -DROP TABLE t1, t2, t3; -# -# Bug #45828: Optimizer won't use partial primary key if another -# index can prevent filesort -# -CREATE TABLE `t1` ( -c1 int NOT NULL, -c2 int NOT NULL, -c3 int NOT NULL, -PRIMARY KEY (c1,c2), -KEY (c3) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES (5,2,1246276747); -INSERT INTO t1 VALUES (2,1,1246281721); -INSERT INTO t1 VALUES (7,3,1246281756); -INSERT INTO t1 VALUES (4,2,1246282139); -INSERT INTO t1 VALUES (3,1,1246282230); -INSERT INTO t1 VALUES (1,0,1246282712); -INSERT INTO t1 VALUES (8,3,1246282765); -INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1; -INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1; -INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1; -INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1; -INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1; -INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1; -SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -c1 c2 c3 -EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort -EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort -CREATE TABLE t2 ( -c1 int NOT NULL, -c2 int NOT NULL, -c3 int NOT NULL, -KEY (c1,c2), -KEY (c3) -) ENGINE=InnoDB; -explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort -DROP TABLE t1,t2; -# -# 36259: Optimizing with ORDER BY -# -CREATE TABLE t1 ( -a INT NOT NULL AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL, -d VARCHAR(5), -e INT NOT NULL, -PRIMARY KEY (a), KEY i2 (b,c,d) -) ENGINE=InnoDB; -INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2); -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort -EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort -EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using where -DROP TABLE t1; -# -# Bug #47963: Wrong results when index is used -# -CREATE TABLE t1( -a VARCHAR(5) NOT NULL, -b VARCHAR(5) NOT NULL, -c DATETIME NOT NULL, -KEY (c) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00'); -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; -a b c -TEST TEST 2009-10-09 00:00:00 -SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -a b c -EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND -c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -DROP TABLE t1; -# -# Bug #46175: NULL read_view and consistent read assertion -# -CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; -CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; -INSERT INTO t1 VALUES (),(); -INSERT INTO t2 VALUES (),(); -CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 -WHERE b =(SELECT a FROM t1 LIMIT 1); -CREATE PROCEDURE p1(num INT) -BEGIN -DECLARE i INT DEFAULT 0; -REPEAT -SHOW CREATE VIEW v1; -SET i:=i+1; -UNTIL i>num END REPEAT; -END| -# Should not crash -# Should not crash -DROP PROCEDURE p1; -DROP VIEW v1; -DROP TABLE t1,t2; -# -# Bug #49324: more valgrind errors in test_if_skip_sort_order -# -CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; -#should not cause valgrind warnings -SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; -1 -DROP TABLE t1; -# -# Bug#50843: Filesort used instead of clustered index led to -# performance degradation. -# -create table t1(f1 int not null primary key, f2 int) engine=innodb; -create table t2(f1 int not null, key (f1)) engine=innodb; -insert into t1 values (1,1),(2,2),(3,3); -insert into t2 values (1),(2),(3); -explain select t1.* from t1 left join t2 using(f1) group by t1.f1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index -drop table t1,t2; -# -# -# Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may -# corrupt definition at engine -# -CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) -ENGINE=InnoDB; -ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); -SHOW INDEXES FROM t1;; -Table t1 -Non_unique 0 -Key_name k -Seq_in_index 1 -Column_name a -Collation A -Cardinality 0 -Sub_part NULL -Packed NULL -Null -Index_type BTREE -Comment -Table t1 -Non_unique 0 -Key_name k -Seq_in_index 2 -Column_name b -Collation A -Cardinality 0 -Sub_part NULL -Packed NULL -Null -Index_type BTREE -Comment -DROP TABLE t1; -# -# Bug #53334: wrong result for outer join with impossible ON condition -# (see the same test case for MyISAM in join.test) -# -create table t1 (id int primary key); -create table t2 (id int); -insert into t1 values (75); -insert into t1 values (79); -insert into t1 values (78); -insert into t1 values (77); -replace into t1 values (76); -replace into t1 values (76); -insert into t1 values (104); -insert into t1 values (103); -insert into t1 values (102); -insert into t1 values (101); -insert into t1 values (105); -insert into t1 values (106); -insert into t1 values (107); -insert into t2 values (107),(75),(1000); -select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 -where t2.id=75 and t1.id is null; -id id -NULL 75 -explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 -where t2.id=75 and t1.id is null; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -drop table t1,t2; -# -# Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when -# JOINed during an UPDATE -# -CREATE TABLE t1 (d INT) ENGINE=InnoDB; -CREATE TABLE t2 (a INT, b INT, -c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB; -set up our data elements -INSERT INTO t1 (d) VALUES (1); -INSERT INTO t2 (a,b) VALUES (1,1); -SELECT SECOND(c) INTO @bug47453 FROM t2; -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; -SECOND(c)-@bug47453 -0 -UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; -SECOND(c)-@bug47453 -0 -SELECT SLEEP(1); -SLEEP(1) -0 -UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; -#should be 0 -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; -SECOND(c)-@bug47453 -0 -DROP TABLE t1, t2; -End of 5.1 tests diff --git a/mysql-test/r/innodb_mysql_lock2.result b/mysql-test/r/innodb_mysql_lock2.result new file mode 100644 index 00000000000..79606ea8bdc --- /dev/null +++ b/mysql-test/r/innodb_mysql_lock2.result @@ -0,0 +1,601 @@ +# +# Test how do we handle locking in various cases when +# we read data from InnoDB tables. +# +# In fact by performing this test we check two things: +# 1) That SQL-layer correctly determine type of thr_lock.c +# lock to be acquired/passed to InnoDB engine. +# 2) That InnoDB engine correctly interprets this lock +# type and takes necessary row locks or does not +# take them if they are not necessary. +# +# This test makes sense only in REPEATABLE-READ mode as +# in SERIALIZABLE mode all statements that read data take +# shared lock on them to enforce its semantics. +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +# Prepare playground by creating tables, views, +# routines and triggers used in tests. +drop table if exists t0, t1, t2, t3, t4, t5, te; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +create table t1 (i int primary key) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key) engine=innodb; +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key) engine=innodb; +insert into t3 values (1), (2), (3); +create table t4 (l int primary key) engine=innodb; +insert into t4 values (1); +create table t5 (l int primary key) engine=innodb; +insert into t5 values (1); +create table te(e int primary key); +insert into te values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +create function f1() returns int +begin +declare j int; +select i from t1 where i = 1 into j; +return j; +end| +create function f2() returns int +begin +declare k int; +select i from t1 where i = 1 into k; +insert into t2 values (k + 5); +return 0; +end| +create function f3() returns int +begin +return (select i from t1 where i = 3); +end| +create function f4() returns int +begin +if (select i from t1 where i = 3) then +return 1; +else +return 0; +end if; +end| +create function f5() returns int +begin +insert into t2 values ((select i from t1 where i = 1) + 5); +return 0; +end| +create function f6() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +return k; +end| +create function f7() returns int +begin +declare k int; +select j from v2 where j = 1 into k; +return k; +end| +create function f8() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +insert into t2 values (k+5); +return k; +end| +create function f9() returns int +begin +update v2 set j=j+10 where j=1; +return 1; +end| +create function f10() returns int +begin +return f1(); +end| +create function f11() returns int +begin +declare k int; +set k= f1(); +insert into t2 values (k+5); +return k; +end| +create function f12(p int) returns int +begin +insert into t2 values (p); +return p; +end| +create function f13(p int) returns int +begin +return p; +end| +create procedure p2(inout p int) +begin +select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin +declare k int; +call p2(k); +insert into t2 values (k+5); +return k; +end| +create function f15() returns int +begin +declare k int; +call p2(k); +return k; +end| +create trigger t4_bi before insert on t4 for each row +begin +declare k int; +select i from t1 where i=1 into k; +set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin +if (select i from t1 where i=1) then +set new.l= 2; +end if; +end| +# Trigger below uses insertion of duplicate key in 'te' +# table as a way to abort delete operation. +create trigger t4_bd before delete on t4 for each row +begin +if !(select i from v1 where i=1) then +insert into te values (1); +end if; +end| +create trigger t5_bi before insert on t5 for each row +begin +set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin +declare j int; +call p2(j); +set new.l= j + 1; +end| +# +# Set common variables to be used by scripts called below. +# +# +# 1. Statements that read tables and do not use subqueries. +# +# +# 1.1 Simple SELECT statement. +# +# No locks are necessary as this statement won't be written +# to the binary log and InnoDB supports snapshots. +Success: 'select * from t1' doesn't take row locks on 't1'. +# +# 1.2 Multi-UPDATE statement. +# +# Has to take shared locks on rows in the table being read as this +# statement will be written to the binary log and therefore should +# be serialized with concurrent statements. +Success: 'update t2, t1 set j= j - 1 where i = j' takes shared row locks on 't1'. +# +# 1.3 Multi-DELETE statement. +# +# The above is true for this statement as well. +Success: 'delete t2 from t1, t2 where i = j' takes shared row locks on 't1'. +# +# 1.4 DESCRIBE statement. +# +# This statement does not really read data from the +# target table and thus does not take any lock on it. +# We check this for completeness of coverage. +Success: 'describe t1' doesn't take row locks on 't1'. +# +# 1.5 SHOW statements. +# +# The above is true for SHOW statements as well. +Success: 'show create table t1' doesn't take row locks on 't1'. +Success: 'show keys from t1' doesn't take row locks on 't1'. +# +# 2. Statements which read tables through subqueries. +# +# +# 2.1 CALL with a subquery. +# +# A strong lock is not necessary as this statement is not +# written to the binary log as a whole (it is written +# statement-by-statement) and thanks to MVCC we can always get +# versions of rows prior to the update that has locked them. +# But in practice InnoDB does locking reads for all statements +# other than SELECT (unless it is a READ-COMITTED mode or +# innodb_locks_unsafe_for_binlog is ON). +Success: 'call p1((select i + 5 from t1 where i = 1))' takes shared row locks on 't1'. +# +# 2.2 CREATE TABLE with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'create table t0 engine=innodb select * from t1' takes shared row locks on 't1'. +drop table t0; +Success: 'create table t0 engine=innodb select j from t2 where j in (select i from t1)' takes shared row locks on 't1'. +drop table t0; +# +# 2.3 DELETE with a subquery. +# +# The above is true for this statement as well. +Success: 'delete from t2 where j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.4 MULTI-DELETE with a subquery. +# +# Same is true for this statement as well. +Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.5 DO with a subquery. +# +# In theory should not take row locks as it is not logged. +# In practice InnoDB takes shared row locks. +Success: 'do (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.6 INSERT with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'insert into t2 select i+5 from t1' takes shared row locks on 't1'. +Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. +# +# 2.7 LOAD DATA with a subquery. +# +# The above is true for this statement as well. +Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.8 REPLACE with a subquery. +# +# Same is true for this statement as well. +Success: 'replace into t2 select i+5 from t1' takes shared row locks on 't1'. +Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. +# +# 2.9 SELECT with a subquery. +# +# Locks are not necessary as this statement is not written +# to the binary log and thanks to MVCC we can always get +# versions of rows prior to the update that has locked them. +# +# Also serves as a test case for bug #46947 "Embedded SELECT +# without FOR UPDATE is causing a lock". +Success: 'select * from t2 where j in (select i from t1)' doesn't take row locks on 't1'. +# +# 2.10 SET with a subquery. +# +# In theory should not require locking as it is not written +# to the binary log. In practice InnoDB acquires shared row +# locks. +Success: 'set @a:= (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.11 SHOW with a subquery. +# +# Similarly to the previous case, in theory should not require locking +# as it is not written to the binary log. In practice InnoDB +# acquires shared row locks. +Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' takes shared row locks on 't1'. +Success: 'show columns from t2 where (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.12 UPDATE with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'update t2 set j= j-10 where j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.13 MULTI-UPDATE with a subquery. +# +# Same is true for this statement as well. +Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' takes shared row locks on 't1'. +# +# 3. Statements which read tables through a view. +# +# +# 3.1 SELECT statement which uses some table through a view. +# +# Since this statement is not written to the binary log +# and old version of rows are accessible thanks to MVCC, +# no locking is necessary. +Success: 'select * from v1' doesn't take row locks on 't1'. +Success: 'select * from v2' doesn't take row locks on 't1'. +Success: 'select * from t2 where j in (select i from v1)' doesn't take row locks on 't1'. +Success: 'select * from t3 where k in (select j from v2)' doesn't take row locks on 't1'. +# +# 3.2 Statements which modify a table and use views. +# +# Since such statements are going to be written to the binary +# log they need to be serialized against concurrent statements +# and therefore should take shared row locks on data read. +Success: 'update t2 set j= j-10 where j in (select i from v1)' takes shared row locks on 't1'. +Success: 'update t3 set k= k-10 where k in (select j from v2)' takes shared row locks on 't1'. +Success: 'update t2, v1 set j= j-10 where j = i' takes shared row locks on 't1'. +Success: 'update v2 set j= j-10 where j = 3' takes shared row locks on 't1'. +# +# 4. Statements which read tables through stored functions. +# +# +# 4.1 SELECT/SET with a stored function which does not +# modify data and uses SELECT in its turn. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f1()' doesn't take row locks on 't1'. +Success: 'set @a:= f1()' doesn't take row locks on 't1'. +# +# 4.2 INSERT (or other statement which modifies data) with +# a stored function which does not modify data and uses +# SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore it should take row locks on the data +# it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'insert into t2 values (f1() + 5)' doesn't take row locks on 't1'. +# +# 4.3 SELECT/SET with a stored function which +# reads and modifies data. +# +# Since a call to such function is written to the binary log, +# it should be serialized with concurrent statements affecting +# the data it uses. Hence, row locks on the data read +# should be taken. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'select f2()' doesn't take row locks on 't1'. +Success: 'set @a:= f2()' doesn't take row locks on 't1'. +# +# 4.4. SELECT/SET with a stored function which does not +# modify data and reads a table through subselect +# in a control construct. +# +# Again, in theory a call to this function won't get to the +# binary log and thus no locking is needed. But in practice +# we don't detect this fact early enough (get_lock_type_for_table()) +# to avoid taking row locks. +Success: 'select f3()' takes shared row locks on 't1'. +Success: 'set @a:= f3()' takes shared row locks on 't1'. +Success: 'select f4()' takes shared row locks on 't1'. +Success: 'set @a:= f4()' takes shared row locks on 't1'. +# +# 4.5. INSERT (or other statement which modifies data) with +# a stored function which does not modify data and reads +# the table through a subselect in one of its control +# constructs. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take row locks on the data +# it reads. +Success: 'insert into t2 values (f3() + 5)' takes shared row locks on 't1'. +Success: 'insert into t2 values (f4() + 6)' takes shared row locks on 't1'. +# +# 4.6 SELECT/SET which uses a stored function with +# DML which reads a table via a subquery. +# +# Since call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence reads should take row locks. +Success: 'select f5()' takes shared row locks on 't1'. +Success: 'set @a:= f5()' takes shared row locks on 't1'. +# +# 4.7 SELECT/SET which uses a stored function which +# doesn't modify data and reads tables through +# a view. +# +# Once again, in theory, calls to such functions won't +# get into the binary log and thus don't need row +# locks. In practice this fact is discovered +# too late to have any effect. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken +# in case of simple SELECT. +Success: 'select f6()' doesn't take row locks on 't1'. +Success: 'set @a:= f6()' doesn't take row locks on 't1'. +Success: 'select f7()' takes shared row locks on 't1'. +Success: 'set @a:= f7()' takes shared row locks on 't1'. +# +# 4.8 INSERT which uses stored function which +# doesn't modify data and reads a table +# through a view. +# +# Since such statement is written to the binary log and +# should be serialized with concurrent statements affecting +# the data it uses. Therefore it should take row locks on +# the rows it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken +# in case of simple SELECT. +Success: 'insert into t3 values (f6() + 5)' doesn't take row locks on 't1'. +Success: 'insert into t3 values (f7() + 5)' takes shared row locks on 't1'. +# +# 4.9 SELECT which uses a stored function which +# modifies data and reads tables through a view. +# +# Since a call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence, reads should take row locks. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken +# in case of simple SELECT. +Success: 'select f8()' doesn't take row locks on 't1'. +Success: 'select f9()' takes shared row locks on 't1'. +# +# 4.10 SELECT which uses stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f10()' doesn't take row locks on 't1'. +# +# 4.11 INSERT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Since such statement is written to the binary log, it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take row locks on data it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'insert into t2 values (f10() + 5)' doesn't take row locks on 't1'. +# +# 4.12 SELECT which uses a stored function which modifies +# data and reads a table indirectly, by calling another +# function. +# +# Since a call to such function is written to the binary log +# it should be serialized from concurrent statements. +# Hence, reads should take row locks. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'select f11()' doesn't take row locks on 't1'. +# +# 4.13 SELECT that reads a table through a subquery passed +# as a parameter to a stored function which modifies +# data. +# +# Even though a call to this function is written to the +# binary log, values of its parameters are written as literals. +# So there is no need to acquire row locks on rows used in +# the subquery. +# But due to the fact that in 5.1 for prelocked statements +# THD::in_lock_tables is set to TRUE we acquire strong locks +# (see also bug#44613 "SELECT statement inside FUNCTION takes +# a shared lock" [sic!!!]). +Success: 'select f12((select i+10 from t1 where i=1))' takes shared row locks on 't1'. +# +# 4.14 INSERT that reads a table via a subquery passed +# as a parameter to a stored function which doesn't +# modify data. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take row locks on the data it reads. +Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' takes shared row locks on 't1'. +# +# 5. Statements that read tables through stored procedures. +# +# +# 5.1 CALL statement which reads a table via SELECT. +# +# Since neither this statement nor its components are +# written to the binary log, there is no need to take +# row locks on the data it reads. +Success: 'call p2(@a)' doesn't take row locks on 't1'. +# +# 5.2 Function that modifies data and uses CALL, +# which reads a table through SELECT. +# +# Since a call to such function is written to the binary +# log, it should be serialized with concurrent statements. +# Hence, in this case reads should take row locks on data. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'select f14()' doesn't take row locks on 't1'. +# +# 5.3 SELECT that calls a function that doesn't modify data and +# uses a CALL statement that reads a table via SELECT. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f15()' doesn't take row locks on 't1'. +# +# 5.4 INSERT which calls function which doesn't modify data and +# uses CALL statement which reads table through SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take row locks on data it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'insert into t2 values (f15()+5)' doesn't take row locks on 't1'. +# +# 6. Statements that use triggers. +# +# +# 6.1 Statement invoking a trigger that reads table via SELECT. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore, it should take row locks on the data +# it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'insert into t4 values (2)' doesn't take row locks on 't1'. +# +# 6.2 Statement invoking a trigger that reads table through +# a subquery in a control construct. +# +# The above is true for this statement as well. +Success: 'update t4 set l= 2 where l = 1' takes shared row locks on 't1'. +# +# 6.3 Statement invoking a trigger that reads a table through +# a view. +# +# And for this statement. +Success: 'delete from t4 where l = 1' takes shared row locks on 't1'. +# +# 6.4 Statement invoking a trigger that reads a table through +# a stored function. +# +# And for this statement. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'insert into t5 values (2)' doesn't take row locks on 't1'. +# +# 6.5 Statement invoking a trigger that reads a table through +# stored procedure. +# +# And for this statement. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" no lock is taken. +Success: 'update t5 set l= 2 where l = 1' doesn't take row locks on 't1'. +# Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5, te; diff --git a/mysql-test/r/innodb_mysql_rbk.result b/mysql-test/r/innodb_mysql_rbk.result deleted file mode 100644 index 21ac4295325..00000000000 --- a/mysql-test/r/innodb_mysql_rbk.result +++ /dev/null @@ -1,21 +0,0 @@ -CREATE TABLE t1(a INT, b INT NOT NULL, PRIMARY KEY (a)) ENGINE=innodb -DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); -START TRANSACTION; -SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; -a b -3 3 -START TRANSACTION; -UPDATE t1 SET b=b+12 WHERE a > 2 ORDER BY a; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -ROLLBACK; -START TRANSACTION; -SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; -a b -3 3 -START TRANSACTION; -UPDATE t1 SET b=10 WHERE a > 1 ORDER BY a; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -SELECT * FROM t1 WHERE b = 10; -a b -DROP TABLE t1; diff --git a/mysql-test/r/innodb_notembedded.result b/mysql-test/r/innodb_notembedded.result deleted file mode 100644 index af332aba38a..00000000000 --- a/mysql-test/r/innodb_notembedded.result +++ /dev/null @@ -1,23 +0,0 @@ -drop table if exists t1; -SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; -SET GLOBAL log_bin_trust_function_creators = 1; -create table t1 (col1 integer primary key, col2 integer) engine=innodb; -insert t1 values (1,100); -create function f1 () returns integer begin -declare var1 int; -select col2 into var1 from t1 where col1=1 for update; -return var1; -end| -start transaction; -select f1(); -f1() -100 -update t1 set col2=0 where col1=1; -select * from t1; -col1 col2 -1 100 -rollback; -rollback; -drop table t1; -drop function f1; -SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; diff --git a/mysql-test/r/innodb_timeout_rollback.result b/mysql-test/r/innodb_timeout_rollback.result deleted file mode 100644 index e2da6ba8af7..00000000000 --- a/mysql-test/r/innodb_timeout_rollback.result +++ /dev/null @@ -1,36 +0,0 @@ -drop table if exists t1; -show variables like 'innodb_rollback_on_timeout'; -Variable_name Value -innodb_rollback_on_timeout ON -create table t1 (a int unsigned not null primary key) engine = innodb; -insert into t1 values (1); -commit; -begin work; -insert into t1 values (2); -select * from t1; -a -1 -2 -begin work; -insert into t1 values (5); -select * from t1; -a -1 -5 -insert into t1 values (2); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -a -1 -commit; -select * from t1; -a -1 -2 -commit; -select * from t1; -a -1 -2 -drop table t1; -End of 5.0 tests diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 4543446e807..8e438934b23 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1308,4 +1308,93 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); f1 f2 f3 f1 f2 1 NULL 3 NULL NULL DROP TABLE t1, t2; +# +# Bug#52357: Assertion failed: join->best_read in greedy_search +# optimizer_search_depth=0 +# +CREATE TABLE t1( a INT ); +INSERT INTO t1 VALUES (1),(2); +SET optimizer_search_depth = 0; +# Should not core dump on query preparation +EXPLAIN +SELECT 1 +FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1 +LEFT OUTER JOIN t1 tt5 ON 1 +LEFT OUTER JOIN t1 tt6 ON 1 +LEFT OUTER JOIN t1 tt7 ON 1 +LEFT OUTER JOIN t1 tt8 ON 1 +RIGHT OUTER JOIN t1 tt2 ON 1 +RIGHT OUTER JOIN t1 tt1 ON 1 +STRAIGHT_JOIN t1 tt9 ON 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tt1 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt2 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt3 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt4 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt5 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt6 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt7 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt8 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer +SET optimizer_search_depth = DEFAULT; +DROP TABLE t1; +# +# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result +# +CREATE TABLE t1 (f1 INT NOT NULL); +INSERT INTO t1 VALUES (9),(0); +CREATE TABLE t2 (f1 INT NOT NULL); +INSERT INTO t2 VALUES +(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); +SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; +COUNT(*) +476 +EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where +1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer +1 SIMPLE TA1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1, t2; +# +# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 +# +CREATE TABLE t1(f1 INT, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 +LEFT JOIN t1 AS jt2 +RIGHT JOIN t1 AS jt3 +JOIN t1 AS jt4 ON 1 +LEFT JOIN t1 AS jt5 ON 1 +ON 1 +RIGHT JOIN t1 AS jt6 ON jt6.f1 +ON 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index +Warnings: +Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1 +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 +RIGHT JOIN t1 AS jt2 +RIGHT JOIN t1 AS jt3 +JOIN t1 AS jt4 ON 1 +LEFT JOIN t1 AS jt5 ON 1 +ON 1 +RIGHT JOIN t1 AS jt6 ON jt6.f1 +ON 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index +Warnings: +Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index b3487d376a1..665e80b8ba2 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -496,4 +496,11 @@ SELECT * FROM t1; col0 test DROP TABLE t1; +# +# Bug #52512 : Assertion `! is_set()' in +# Diagnostics_area::set_ok_status on LOAD DATA +# +CREATE TABLE t1 (id INT NOT NULL); +LOAD DATA LOCAL INFILE 'tb.txt' INTO TABLE t1; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result new file mode 100644 index 00000000000..752f278a2b4 --- /dev/null +++ b/mysql-test/r/lock_sync.result @@ -0,0 +1,631 @@ +# +# Test how we handle locking in various cases when +# we read data from MyISAM tables. +# +# In this test we mostly check that the SQL-layer correctly +# determines the type of thr_lock.c lock for a table being +# read. +# I.e. that it disallows concurrent inserts when the statement +# is going to be written to the binary log and therefore +# should be serialized, and allows concurrent inserts when +# such serialization is not necessary (e.g. when +# the statement is not written to binary log). +# +# Force concurrent inserts to be performed even if the table +# has gaps. This allows to simplify clean up in scripts +# used below (instead of backing up table being inserted +# into and then restoring it from backup at the end of the +# script we can simply delete rows which were inserted). +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 2; +select @@global.concurrent_insert; +@@global.concurrent_insert +2 +# Prepare playground by creating tables, views, +# routines and triggers used in tests. +drop table if exists t0, t1, t2, t3, t4, t5, te; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +create table t1 (i int primary key); +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key); +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key); +insert into t3 values (1), (2), (3); +create table t4 (l int primary key); +insert into t4 values (1); +create table t5 (l int primary key); +insert into t5 values (1); +create table te(e int primary key); +insert into te values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +create function f1() returns int +begin +declare j int; +select i from t1 where i = 1 into j; +return j; +end| +create function f2() returns int +begin +declare k int; +select i from t1 where i = 1 into k; +insert into t2 values (k + 5); +return 0; +end| +create function f3() returns int +begin +return (select i from t1 where i = 3); +end| +create function f4() returns int +begin +if (select i from t1 where i = 3) then +return 1; +else +return 0; +end if; +end| +create function f5() returns int +begin +insert into t2 values ((select i from t1 where i = 1) + 5); +return 0; +end| +create function f6() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +return k; +end| +create function f7() returns int +begin +declare k int; +select j from v2 where j = 1 into k; +return k; +end| +create function f8() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +insert into t2 values (k+5); +return k; +end| +create function f9() returns int +begin +update v2 set j=j+10 where j=1; +return 1; +end| +create function f10() returns int +begin +return f1(); +end| +create function f11() returns int +begin +declare k int; +set k= f1(); +insert into t2 values (k+5); +return k; +end| +create function f12(p int) returns int +begin +insert into t2 values (p); +return p; +end| +create function f13(p int) returns int +begin +return p; +end| +create procedure p2(inout p int) +begin +select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin +declare k int; +call p2(k); +insert into t2 values (k+5); +return k; +end| +create function f15() returns int +begin +declare k int; +call p2(k); +return k; +end| +create trigger t4_bi before insert on t4 for each row +begin +declare k int; +select i from t1 where i=1 into k; +set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin +if (select i from t1 where i=1) then +set new.l= 2; +end if; +end| +# Trigger below uses insertion of duplicate key in 'te' +# table as a way to abort delete operation. +create trigger t4_bd before delete on t4 for each row +begin +if !(select i from v1 where i=1) then +insert into te values (1); +end if; +end| +create trigger t5_bi before insert on t5 for each row +begin +set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin +declare j int; +call p2(j); +set new.l= j + 1; +end| +# +# Set common variables to be used by the scripts +# called below. +# +# Switch to connection 'con1'. +# Cache all functions used in the tests below so statements +# calling them won't need to open and lock mysql.proc table +# and we can assume that each statement locks its tables +# once during its execution. +show create procedure p1; +show create procedure p2; +show create function f1; +show create function f2; +show create function f3; +show create function f4; +show create function f5; +show create function f6; +show create function f7; +show create function f8; +show create function f9; +show create function f10; +show create function f11; +show create function f12; +show create function f13; +show create function f14; +show create function f15; +# Switch back to connection 'default'. +# +# 1. Statements that read tables and do not use subqueries. +# +# +# 1.1 Simple SELECT statement. +# +# No locks are necessary as this statement won't be written +# to the binary log and thanks to how MyISAM works SELECT +# will see version of the table prior to concurrent insert. +Success: 'select * from t1' allows concurrent inserts into 't1'. +# +# 1.2 Multi-UPDATE statement. +# +# Has to take shared locks on rows in the table being read as this +# statement will be written to the binary log and therefore should +# be serialized with concurrent statements. +Success: 'update t2, t1 set j= j - 1 where i = j' doesn't allow concurrent inserts into 't1'. +# +# 1.3 Multi-DELETE statement. +# +# The above is true for this statement as well. +Success: 'delete t2 from t1, t2 where i = j' doesn't allow concurrent inserts into 't1'. +# +# 1.4 DESCRIBE statement. +# +# This statement does not really read data from the +# target table and thus does not take any lock on it. +# We check this for completeness of coverage. +lock table t1 write; +# Switching to connection 'con1'. +# This statement should not be blocked. +describe t1; +# Switching to connection 'default'. +unlock tables; +# +# 1.5 SHOW statements. +# +# The above is true for SHOW statements as well. +lock table t1 write; +# Switching to connection 'con1'. +# These statements should not be blocked. +show keys from t1; +# Switching to connection 'default'. +unlock tables; +# +# 2. Statements which read tables through subqueries. +# +# +# 2.1 CALL with a subquery. +# +# In theory strong lock is not necessary as this statement +# is not written to the binary log as a whole (it is written +# statement-by-statement). But in practice in 5.1 for +# almost everything except SELECT we take strong lock. +Success: 'call p1((select i + 5 from t1 where i = 1))' doesn't allow concurrent inserts into 't1'. +# +# 2.2 CREATE TABLE with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'create table t0 select * from t1' doesn't allow concurrent inserts into 't1'. +drop table t0; +Success: 'create table t0 select j from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +drop table t0; +# +# 2.3 DELETE with a subquery. +# +# The above is true for this statement as well. +Success: 'delete from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.4 MULTI-DELETE with a subquery. +# +# Same is true for this statement as well. +Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.5 DO with a subquery. +# +# In theory strong lock is not necessary as it is not logged. +# But in practice in 5.1 for almost everything except SELECT +# we take strong lock. +Success: 'do (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +# +# 2.6 INSERT with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent inserts. +Success: 'insert into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'. +Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'. +# +# 2.7 LOAD DATA with a subquery. +# +# The above is true for this statement as well. +Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +# +# 2.8 REPLACE with a subquery. +# +# Same is true for this statement as well. +Success: 'replace into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'. +Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'. +# +# 2.9 SELECT with a subquery. +# +# Strong locks are not necessary as this statement is not written +# to the binary log and thanks to how MyISAM works this statement +# sees a version of the table prior to the concurrent insert. +Success: 'select * from t2 where j in (select i from t1)' allows concurrent inserts into 't1'. +# +# 2.10 SET with a subquery. +# +# In theory the same is true for this statement as well. +# But in practice in 5.1 we acquire strong lock in this +# case as well. +Success: 'set @a:= (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +# +# 2.11 SHOW with a subquery. +# +# The same is true for this statement too. +Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +Success: 'show columns from t2 where (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +# +# 2.12 UPDATE with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent inserts. +Success: 'update t2 set j= j-10 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.13 MULTI-UPDATE with a subquery. +# +# Same is true for this statement as well. +Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 3. Statements which read tables through a view. +# +# +# 3.1 SELECT statement which uses some table through a view. +# +# Since this statement is not written to the binary log and +# an old version of the table is accessible thanks to how MyISAM +# handles concurrent insert, no locking is necessary. +Success: 'select * from v1' allows concurrent inserts into 't1'. +Success: 'select * from v2' allows concurrent inserts into 't1'. +Success: 'select * from t2 where j in (select i from v1)' allows concurrent inserts into 't1'. +Success: 'select * from t3 where k in (select j from v2)' allows concurrent inserts into 't1'. +# +# 3.2 Statements which modify a table and use views. +# +# Since such statements are going to be written to the binary +# log they need to be serialized against concurrent statements +# and therefore should take strong locks on the data read. +Success: 'update t2 set j= j-10 where j in (select i from v1)' doesn't allow concurrent inserts into 't1'. +Success: 'update t3 set k= k-10 where k in (select j from v2)' doesn't allow concurrent inserts into 't1'. +Success: 'update t2, v1 set j= j-10 where j = i' doesn't allow concurrent inserts into 't1'. +Success: 'update v2 set j= j-10 where j = 3' doesn't allow concurrent inserts into 't1'. +# +# 4. Statements which read tables through stored functions. +# +# +# 4.1 SELECT/SET with a stored function which does not +# modify data and uses SELECT in its turn. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f1()' allows concurrent inserts into 't1'. +Success: 'set @a:= f1()' allows concurrent inserts into 't1'. +# +# 4.2 INSERT (or other statement which modifies data) with +# a stored function which does not modify data and uses +# SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore it should take strong lock on the data +# it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'insert into t2 values (f1() + 5)' allows concurrent inserts into 't1'. +# +# 4.3 SELECT/SET with a stored function which +# reads and modifies data. +# +# Since a call to such function is written to the binary log, +# it should be serialized with concurrent statements affecting +# the data it uses. Hence, a strong lock on the data read +# should be taken. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'select f2()' allows concurrent inserts into 't1'. +Success: 'set @a:= f2()' allows concurrent inserts into 't1'. +# +# 4.4. SELECT/SET with a stored function which does not +# modify data and reads a table through subselect +# in a control construct. +# +# Again, in theory a call to this function won't get to the +# binary log and thus no strong lock is needed. But in practice +# we don't detect this fact early enough (get_lock_type_for_table()) +# to avoid taking a strong lock. +Success: 'select f3()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f3()' doesn't allow concurrent inserts into 't1'. +Success: 'select f4()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f4()' doesn't allow concurrent inserts into 't1'. +# +# 4.5. INSERT (or other statement which modifies data) with +# a stored function which does not modify data and reads +# the table through a subselect in one of its control +# constructs. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take a strong lock on the data +# it reads. +Success: 'insert into t2 values (f3() + 5)' doesn't allow concurrent inserts into 't1'. +Success: 'insert into t2 values (f4() + 6)' doesn't allow concurrent inserts into 't1'. +# +# 4.6 SELECT/SET which uses a stored function with +# DML which reads a table via a subquery. +# +# Since call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence reads should take a strong lock. +Success: 'select f5()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f5()' doesn't allow concurrent inserts into 't1'. +# +# 4.7 SELECT/SET which uses a stored function which +# doesn't modify data and reads tables through +# a view. +# +# Once again, in theory, calls to such functions won't +# get into the binary log and thus don't need strong +# locks. In practice this fact is discovered +# too late to have any effect. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken +# in case when simple SELECT is used. +Success: 'select f6()' allows concurrent inserts into 't1'. +Success: 'set @a:= f6()' allows concurrent inserts into 't1'. +Success: 'select f7()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f7()' doesn't allow concurrent inserts into 't1'. +# +# 4.8 INSERT which uses stored function which +# doesn't modify data and reads a table +# through a view. +# +# Since such statement is written to the binary log and +# should be serialized with concurrent statements affecting +# the data it uses. Therefore it should take a strong lock on +# the table it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken +# in case when simple SELECT is used. +Success: 'insert into t3 values (f6() + 5)' allows concurrent inserts into 't1'. +Success: 'insert into t3 values (f7() + 5)' doesn't allow concurrent inserts into 't1'. +# +# 4.9 SELECT which uses a stored function which +# modifies data and reads tables through a view. +# +# Since a call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence, reads should take strong locks. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken +# in case when simple SELECT is used. +Success: 'select f8()' allows concurrent inserts into 't1'. +Success: 'select f9()' doesn't allow concurrent inserts into 't1'. +# +# 4.10 SELECT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f10()' allows concurrent inserts into 't1'. +# +# 4.11 INSERT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Since such statement is written to the binary log, it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take strong locks on data it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'insert into t2 values (f10() + 5)' allows concurrent inserts into 't1'. +# +# 4.12 SELECT which uses a stored function which modifies +# data and reads a table indirectly, by calling another +# function. +# +# Since a call to such function is written to the binary log +# it should be serialized from concurrent statements. +# Hence, read should take a strong lock. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'select f11()' allows concurrent inserts into 't1'. +# +# 4.13 SELECT that reads a table through a subquery passed +# as a parameter to a stored function which modifies +# data. +# +# Even though a call to this function is written to the +# binary log, values of its parameters are written as literals. +# So there is no need to acquire strong locks for tables used in +# the subquery. +Success: 'select f12((select i+10 from t1 where i=1))' allows concurrent inserts into 't1'. +# +# 4.14 INSERT that reads a table via a subquery passed +# as a parameter to a stored function which doesn't +# modify data. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take strong locks on the data it reads. +Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' doesn't allow concurrent inserts into 't1'. +# +# 5. Statements that read tables through stored procedures. +# +# +# 5.1 CALL statement which reads a table via SELECT. +# +# Since neither this statement nor its components are +# written to the binary log, there is no need to take +# strong locks on the data it reads. +Success: 'call p2(@a)' allows concurrent inserts into 't1'. +# +# 5.2 Function that modifies data and uses CALL, +# which reads a table through SELECT. +# +# Since a call to such function is written to the binary +# log, it should be serialized with concurrent statements. +# Hence, in this case reads should take strong locks on data. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'select f14()' allows concurrent inserts into 't1'. +# +# 5.3 SELECT that calls a function that doesn't modify data and +# uses a CALL statement that reads a table via SELECT. +# +# Calls to such functions won't get into the binary log and +# thus don't need to acquire strong locks. +# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +# used stored functions may lead to broken SBR" strong locks +# are taken (we accepted it as a trade-off for this fix). +Success: 'select f15()' allows concurrent inserts into 't1'. +# +# 5.4 INSERT which calls function which doesn't modify data and +# uses CALL statement which reads table through SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take strong locks on data it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'insert into t2 values (f15()+5)' allows concurrent inserts into 't1'. +# +# 6. Statements that use triggers. +# +# +# 6.1 Statement invoking a trigger that reads table via SELECT. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore, it should take strong locks on the data +# it reads. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'insert into t4 values (2)' allows concurrent inserts into 't1'. +# +# 6.2 Statement invoking a trigger that reads table through +# a subquery in a control construct. +# +# The above is true for this statement as well. +Success: 'update t4 set l= 2 where l = 1' doesn't allow concurrent inserts into 't1'. +# +# 6.3 Statement invoking a trigger that reads a table through +# a view. +# +# And for this statement. +Success: 'delete from t4 where l = 1' doesn't allow concurrent inserts into 't1'. +# +# 6.4 Statement invoking a trigger that reads a table through +# a stored function. +# +# And for this statement. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'insert into t5 values (2)' allows concurrent inserts into 't1'. +# +# 6.5 Statement invoking a trigger that reads a table through +# stored procedure. +# +# And for this statement. +# But due to bug #53921 "Wrong locks for SELECTs used stored +# functions may lead to broken SBR" weak locks are taken. +Success: 'update t5 set l= 2 where l = 1' allows concurrent inserts into 't1'. +# Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5, te; +set @@global.concurrent_insert= @old_concurrent_insert; diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 4ce678e37aa..654f9d127d3 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -308,8 +308,41 @@ SET @@global.general_log = @old_general_log; SET @@global.general_log_file = @old_general_log_file; SET @@global.slow_query_log = @old_slow_query_log; SET @@global.slow_query_log_file = @old_slow_query_log_file; +# +# Bug #49756 Rows_examined is always 0 in the slow query log +# for update statements +# +SET @old_log_output = @@global.log_output; +SET GLOBAL log_output = "TABLE"; +SET GLOBAL slow_query_log = ON; +SET GLOBAL long_query_time = 0.001; +TRUNCATE TABLE mysql.slow_log; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, PRIMARY KEY (b)); +INSERT INTO t2 VALUES (3),(4); +INSERT INTO t1 VALUES (1+sleep(.01)),(2); +INSERT INTO t1 SELECT b+sleep(.01) from t2; +UPDATE t1 SET a=a+sleep(.01) WHERE a>2; +UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; +UPDATE t2 set b=b+sleep(.01) limit 1; +UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); +DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; +SELECT rows_examined,sql_text FROM mysql.slow_log; +rows_examined sql_text +0 INSERT INTO t1 VALUES (1+sleep(.01)),(2) +2 INSERT INTO t1 SELECT b+sleep(.01) from t2 +4 UPDATE t1 SET a=a+sleep(.01) WHERE a>2 +8 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC +2 UPDATE t2 set b=b+sleep(.01) limit 1 +4 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2) +6 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2 +DROP TABLE t1,t2; +TRUNCATE TABLE mysql.slow_log; +# end of bug#49756 End of 5.1 tests # Close connection con1 +SET GLOBAL long_query_time = DEFAULT; +SET GLOBAL log_output = @old_log_output; SET global general_log = @old_general_log; SET global general_log_file = @old_general_log_file; SET global slow_query_log = @old_slow_query_log; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 04bf7720c43..ae72f416c79 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -602,9 +602,6 @@ select * from t2 /* must be (3,1), (4,4) */; a b 3 1 4 4 -show master status /* there must be the UPDATE query event */; -File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 206 delete from t1; delete from t2; insert into t1 values (1,2),(3,4),(4,4); @@ -612,9 +609,6 @@ insert into t2 values (1,2),(3,4),(4,4); reset master; UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; ERROR 23000: Duplicate entry '4' for key 'PRIMARY' -show master status /* there must be the UPDATE query event */; -File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 221 drop table t1, t2; set @@session.binlog_format= @sav_binlog_format; drop table if exists t1, t2, t3; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ad77d04d583..ccf60ae5fd9 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4561,5 +4561,20 @@ a b c SET NAMES default; DROP TABLE t1, t2; # +# Bug #53088: mysqldump with -T & --default-character-set set +# truncates text/blob to 766 chars +# +# Also see outfile_loaddata.test +# +CREATE TABLE t1 (a BLOB) CHARSET latin1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (REPEAT('.', 800)); +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET latin1; +# should be 800 +SELECT LENGTH(a) FROM t2; +LENGTH(a) +800 +DROP TABLE t1, t2; +# # End of 5.1 tests # diff --git a/mysql-test/r/mysqlhotcopy_archive.result b/mysql-test/r/mysqlhotcopy_archive.result new file mode 100644 index 00000000000..bea78597336 --- /dev/null +++ b/mysql-test/r/mysqlhotcopy_archive.result @@ -0,0 +1,118 @@ +DROP DATABASE IF EXISTS hotcopy_test; +CREATE DATABASE hotcopy_test; +USE hotcopy_test; +CREATE TABLE t1 (c1 int, c2 varchar(20)) ENGINE=archive; +CREATE TABLE t2 (c1 int, c2 varchar(20)) ENGINE=archive; +CREATE TABLE t3 (c1 int, c2 varchar(20)) ENGINE=archive; +INSERT INTO t1 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +INSERT INTO t2 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +INSERT INTO t3 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +USE hotcopy_save; +SELECT * FROM t1; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t2; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t3; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +USE hotcopy_test; +DROP TABLE t2; +db.opt +t1.ARZ +t1.frm +t3.ARZ +t3.frm +FLUSH TABLES; +SELECT * FROM t1; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t2; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t3; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +USE hotcopy_test; +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +DROP DATABASE hotcopy_save; +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +DROP DATABASE hotcopy_save; +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +db.opt +t1.ARZ +t1.frm +t2.ARZ +t2.frm +t3.ARZ +t3.frm +DROP DATABASE hotcopy_test_cpy; +DROP DATABASE hotcopy_test; +DROP DATABASE hotcopy_save; +DROP DATABASE hotcopy_save_old; diff --git a/mysql-test/r/mysqlhotcopy_myisam.result b/mysql-test/r/mysqlhotcopy_myisam.result new file mode 100644 index 00000000000..52aeffce5cf --- /dev/null +++ b/mysql-test/r/mysqlhotcopy_myisam.result @@ -0,0 +1,164 @@ +DROP DATABASE IF EXISTS hotcopy_test; +CREATE DATABASE hotcopy_test; +USE hotcopy_test; +CREATE TABLE t1 (c1 int, c2 varchar(20)) ENGINE=MyISAM; +CREATE TABLE t2 (c1 int, c2 varchar(20)) ENGINE=MyISAM; +CREATE TABLE t3 (c1 int, c2 varchar(20)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +INSERT INTO t2 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +INSERT INTO t3 VALUES (1,'aaaaaaaaaaaaaaaaaaaa'),(2, 'bbbbbbbbbbbbbbbbbbbbbbb'); +Warnings: +Warning 1265 Data truncated for column 'c2' at row 2 +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +USE hotcopy_save; +SELECT * FROM t1; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t2; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t3; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +USE hotcopy_test; +DELETE FROM t1; +SELECT * FROM t1; +c1 c2 +USE hotcopy_save; +SELECT * FROM t1; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t2; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t3; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +USE hotcopy_test; +DROP TABLE t2; +db.opt +t1.MYD +t1.MYI +t1.frm +t3.MYD +t3.MYI +t3.frm +FLUSH TABLES; +SELECT * FROM t1; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t2; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +SELECT * FROM t3; +c1 c2 +1 aaaaaaaaaaaaaaaaaaaa +2 bbbbbbbbbbbbbbbbbbbb +USE hotcopy_test; +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +DROP DATABASE hotcopy_save; +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +DROP DATABASE hotcopy_save; +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +db.opt +t1.MYD +t1.MYI +t1.frm +t2.MYD +t2.MYI +t2.frm +t3.MYD +t3.MYI +t3.frm +DROP DATABASE hotcopy_test_cpy; +DROP DATABASE hotcopy_test; +DROP DATABASE hotcopy_save; +DROP DATABASE hotcopy_save_old; diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result index 453e3adb54c..36a72fd84ce 100644 --- a/mysql-test/r/outfile_loaddata.result +++ b/mysql-test/r/outfile_loaddata.result @@ -239,4 +239,24 @@ a b c 2 NULL NULL SET NAMES default; DROP TABLE t1, t2; +# +# Bug #53088: mysqldump with -T & --default-character-set set +# truncates text/blob to 766 chars +# +# Also see mysqldump.test +# +CREATE TABLE t1 (a BLOB) CHARSET latin1; +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (REPEAT('.', 800)); +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' CHARACTER SET latin1 FROM t1; +# should be greater than 800 +SELECT LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt')); +LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt')) +801 +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' INTO TABLE t2; +# should be 800 +SELECT LENGTH(a) FROM t2; +LENGTH(a) +800 +DROP TABLE t1, t2; # End of 5.1 tests. diff --git a/mysql-test/r/partition_error.result b/mysql-test/r/partition_error.result index 6ebf033adb7..16428fba4d9 100644 --- a/mysql-test/r/partition_error.result +++ b/mysql-test/r/partition_error.result @@ -1,5 +1,29 @@ drop table if exists t1; # +# Bug#49161: Out of memory; restart server and try again (needed 2 bytes) +# +CREATE TABLE t1 (a INT) PARTITION BY HASH (a); +FLUSH TABLES; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check Error Failed to read from the .par file +test.t1 check Error Incorrect information in file: './test/t1.frm' +test.t1 check error Corrupt +SELECT * FROM t1; +ERROR HY000: Failed to read from the .par file +# Note that it is currently impossible to drop a partitioned table +# without the .par file +DROP TABLE t1; +ERROR 42S02: Unknown table 't1' +# +# Bug#49477: Assertion `0' failed in ha_partition.cc:5530 +# with temporary table and partitions +# +CREATE TABLE t1 (a INT) PARTITION BY HASH(a); +CREATE TEMPORARY TABLE tmp_t1 LIKE t1; +ERROR HY000: Cannot create temporary table with partitions +DROP TABLE t1; +# # Bug#50392: insert_id is not reset for partitioned tables # auto_increment on duplicate entry CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 64e00521cd2..ad8aec7383a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1653,4 +1653,48 @@ a b 0 0 1 1 DROP TABLE t1; +# +# Bug#50939: Loose Index Scan unduly relies on engine to remember range +# endpoints +# +CREATE TABLE t1 ( +a INT, +b INT, +KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; +CREATE TABLE t2 ( +a INT, +b INT, +KEY ( a, b ) +); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; +INSERT INTO t2 SELECT * FROM t1; +# plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +a MAX(b) +10 10 +# Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +Variable_name Value +Handler_read_key 4 +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +a MAX(b) +10 10 +# Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +Variable_name Value +Handler_read_key 4 +DROP TABLE t1, t2; End of 5.1 tests diff --git a/mysql-test/r/renamedb.result b/mysql-test/r/renamedb.result index ff8f89592fc..e77aca0d0b7 100644 --- a/mysql-test/r/renamedb.result +++ b/mysql-test/r/renamedb.result @@ -7,6 +7,6 @@ ERROR HY000: Incorrect usage of ALTER DATABASE UPGRADE DATA DIRECTORY NAME and n ALTER DATABASE `#mysql51#not-yet` UPGRADE DATA DIRECTORY NAME; ERROR HY000: Incorrect usage of ALTER DATABASE UPGRADE DATA DIRECTORY NAME and name ALTER DATABASE `#mysql50#` UPGRADE DATA DIRECTORY NAME; -ERROR HY000: Incorrect usage of ALTER DATABASE UPGRADE DATA DIRECTORY NAME and name +ERROR 42000: Incorrect database name '#mysql50#' ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME; ERROR 42000: Unknown database '#mysql50#upgrade-me' diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result index 564fb3626df..e6af4e312a3 100644 --- a/mysql-test/r/schema.result +++ b/mysql-test/r/schema.result @@ -11,3 +11,22 @@ mtr mysql test drop schema foo; +# +# Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER +# +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +INSERT INTO db1.t1 VALUES (1), (2); +# Connection con1 +HANDLER db1.t1 OPEN; +# Connection default +# Sending: +DROP DATABASE db1; +# Connection con2 +# Waiting for 'DROP DATABASE db1' to sync in. +# Connection con1 +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +# Connection default +# Reaping: DROP DATABASE db1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 6353ae23013..c1aa5704f56 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4781,4 +4781,19 @@ a b c SELECT * FROM t1 WHERE 102 < c; a b c DROP TABLE t1; +# +# Bug #54459: Assertion failed: param.sort_length, +# file .\filesort.cc, line 149 (part II) +# +CREATE TABLE t1(a ENUM('') NOT NULL); +INSERT INTO t1 VALUES (), (), (); +EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +1 +1 +1 +1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/sp_trans_log.result b/mysql-test/r/sp_trans_log.result index 7a6173b89e2..7b7d05617ab 100644 --- a/mysql-test/r/sp_trans_log.result +++ b/mysql-test/r/sp_trans_log.result @@ -14,13 +14,13 @@ end| reset master| insert into t2 values (bug23333(),1)| ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -show binlog events from 106 /* with fixes for #23333 will show there is the query */| +show binlog events from <binlog_start>| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # # -master-bin.000001 # Table_map 1 # # -master-bin.000001 # Table_map 1 # # -master-bin.000001 # Write_rows 1 # # -master-bin.000001 # Query 1 # # +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK select count(*),@a from t1 /* must be 1,1 */| count(*) @a 1 1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ed5f8e9e75f..cbb6149a148 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4714,3 +4714,20 @@ t1_id total_amount DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; +# +# Bug #52711: Segfault when doing EXPLAIN SELECT with +# union...order by (select... where...) +# +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1),(2); +# Should not crash +EXPLAIN +SELECT * FROM t2 UNION SELECT * FROM t2 +ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); +# Should not crash +SELECT * FROM t2 UNION SELECT * FROM t2 +ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); +DROP TABLE t1,t2; +End of 5.1 tests diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index e4b90196c2d..86712bebfa1 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -138,3 +138,13 @@ CAST(c AS TIME) 00:00:00 DROP TABLE t1; End of 5.0 tests +# +# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values +# +CREATE TABLE t1(f1 TIME); +INSERT INTO t1 VALUES ('23:38:57'); +SELECT TIMESTAMP(f1,'1') FROM t1; +TIMESTAMP(f1,'1') +NULL +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/upgrade.result b/mysql-test/r/upgrade.result index 034242079b1..da2f55b5bb1 100644 --- a/mysql-test/r/upgrade.result +++ b/mysql-test/r/upgrade.result @@ -112,3 +112,31 @@ select * from `a-b-c`.v1; f1 drop database `a-b-c`; use test; +# End of 5.0 tests +# +# Bug #53804: serious flaws in the alter database .. upgrade data +# directory name command +# +ALTER DATABASE `#mysql50#:` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Unknown database '#mysql50#:' +ALTER DATABASE `#mysql50#.` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#.' +ALTER DATABASE `#mysql50#../` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#../' +ALTER DATABASE `#mysql50#../..` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#../..' +ALTER DATABASE `#mysql50#../../` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#../../' +ALTER DATABASE `#mysql50#./blablabla` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#./blablabla' +ALTER DATABASE `#mysql50#../blablabla` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#../blablabla' +ALTER DATABASE `#mysql50#/` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#/' +ALTER DATABASE `#mysql50#/.` UPGRADE DATA DIRECTORY NAME; +ERROR 42000: Incorrect database name '#mysql50#/.' +USE `#mysql50#.`; +ERROR 42000: Incorrect database name '#mysql50#.' +USE `#mysql50#../blablabla`; +ERROR 42000: Incorrect database name '#mysql50#../blablabla' +# End of 5.1 tests diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 54506a9a4a2..832c679f8d5 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -576,7 +576,7 @@ set global table_open_cache=100; set storage_engine=myisam; set global thread_cache_size=100; set timestamp=1, timestamp=default; -set tmp_table_size=100; +set tmp_table_size=1024; set tx_isolation="READ-COMMITTED"; set wait_timeout=100; set log_warnings=1; @@ -1017,6 +1017,13 @@ hostname # # Test 'myisam_mmap_size' option is not dynamic SET @@myisam_mmap_size= 500M; ERROR HY000: Variable 'myisam_mmap_size' is a read only variable +# +# Bug #52315: utc_date() crashes when system time > year 2037 +# +SET TIMESTAMP=2*1024*1024*1024; +#Should not crash +SELECT UTC_DATE(); +SET TIMESTAMP=DEFAULT; End of 5.0 tests set join_buffer_size=1; Warnings: diff --git a/mysql-test/r/variables_debug.result b/mysql-test/r/variables_debug.result index 85eaf34b033..d578fa957fc 100644 --- a/mysql-test/r/variables_debug.result +++ b/mysql-test/r/variables_debug.result @@ -1,3 +1,4 @@ +SET @old_debug = @@GLOBAL.debug; set debug= 'T'; select @@debug; @@debug @@ -22,4 +23,5 @@ SET GLOBAL debug=''; SELECT @@global.debug; @@global.debug +SET GLOBAL debug=@old_debug; End of 5.1 tests |