diff options
Diffstat (limited to 'mysql-test/r')
67 files changed, 1834 insertions, 91 deletions
diff --git a/mysql-test/r/bug46080.result b/mysql-test/r/bug46080.result new file mode 100644 index 00000000000..18c7c22829a --- /dev/null +++ b/mysql-test/r/bug46080.result @@ -0,0 +1,14 @@ +# +# Bug #46080: group_concat(... order by) crashes server when +# sort_buffer_size cannot allocate +# +CREATE TABLE t1(a CHAR(255)); +INSERT INTO t1 VALUES ('a'); +SET @@SESSION.sort_buffer_size=5*16*1000000; +SET @@SESSION.max_heap_table_size=5*1000000; +# Must not crash. +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +DROP TABLE t1; +SET @@SESSION.sort_buffer_size=default; +SET @@SESSION.max_heap_table_size=default; +End of 5.0 tests diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index c1af92c5f8d..dd61396e485 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -439,3 +439,16 @@ HOUR(NULL) MINUTE(NULL) SECOND(NULL) NULL NULL NULL DROP TABLE t1; End of 5.0 tests +# +# Bug #44766: valgrind error when using convert() in a subquery +# +CREATE TABLE t1(a tinyint); +INSERT INTO t1 VALUES (127); +SELECT 1 FROM +( +SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1 +) AS s LIMIT 1; +1 +1 +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index de80dba47c1..cabd4c29c1d 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -766,15 +766,11 @@ call p_verify_status_increment(2, 2, 2, 2); SUCCESS savepoint a; -call p_verify_status_increment(0, 0, 0, 0); +call p_verify_status_increment(1, 0, 1, 0); SUCCESS insert t1 set a=4; -# Binlog does not register itself this time for other than the 1st -# statement of the transaction with MIXED/STATEMENT binlog_format. -# It needs registering with the ROW format. Therefore 1,0,2,2 are -# the correct arguments to this test after bug#40221 fixed. -call p_verify_status_increment(1, 0, 2, 2); +call p_verify_status_increment(2, 2, 2, 2); SUCCESS release savepoint a; diff --git a/mysql-test/r/concurrent_innodb_safelog.result b/mysql-test/r/concurrent_innodb_safelog.result index e6adaac1068..24a84afb9ce 100644 --- a/mysql-test/r/concurrent_innodb_safelog.result +++ b/mysql-test/r/concurrent_innodb_safelog.result @@ -785,6 +785,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Cleanup +** connection thread2 ** connection default drop table t1; drop user mysqltest@localhost; diff --git a/mysql-test/r/concurrent_innodb_unsafelog.result b/mysql-test/r/concurrent_innodb_unsafelog.result index e9c53d4cfa0..35fc2d89cfe 100644 --- a/mysql-test/r/concurrent_innodb_unsafelog.result +++ b/mysql-test/r/concurrent_innodb_unsafelog.result @@ -781,6 +781,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Cleanup +** connection thread2 ** connection default drop table t1; drop user mysqltest@localhost; diff --git a/mysql-test/r/consistent_snapshot.result b/mysql-test/r/consistent_snapshot.result index 694c996a58e..3a0227b1a1a 100644 --- a/mysql-test/r/consistent_snapshot.result +++ b/mysql-test/r/consistent_snapshot.result @@ -1,6 +1,9 @@ DROP TABLE IF EXISTS t1; # Establish connection con1 (user=root) # Establish connection con2 (user=root) +### Test 1: +### - While a consistent snapshot transaction is executed, +### no external inserts should be visible to the transaction. # Switch to connection con1 CREATE TABLE t1 (a INT) ENGINE=innodb; START TRANSACTION WITH CONSISTENT SNAPSHOT; @@ -10,6 +13,9 @@ INSERT INTO t1 VALUES(1); SELECT * FROM t1; a COMMIT; +### Test 2: +### - For any non-consistent snapshot transaction, external +### committed inserts should be visible to the transaction. DELETE FROM t1; START TRANSACTION; # Switch to connection con2 @@ -19,5 +25,18 @@ SELECT * FROM t1; a 1 COMMIT; +### Test 3: +### - Bug#44664: valgrind warning for COMMIT_AND_CHAIN and ROLLBACK_AND_CHAIN +### Chaining a transaction does not retain consistency level. +START TRANSACTION WITH CONSISTENT SNAPSHOT; +DELETE FROM t1; +COMMIT WORK AND CHAIN; +# Switch to connection con2 +INSERT INTO t1 VALUES(1); +# Switch to connection con1 +SELECT * FROM t1; +a +1 +COMMIT; # Switch to connection default + close connections con1 and con2 DROP TABLE t1; diff --git a/mysql-test/r/ctype_cp932_binlog_row.result b/mysql-test/r/ctype_cp932_binlog_row.result index 0370b7a1cf6..cbac6b14669 100644 --- a/mysql-test/r/ctype_cp932_binlog_row.result +++ b/mysql-test/r/ctype_cp932_binlog_row.result @@ -9,10 +9,10 @@ EXECUTE stmt1 USING @var1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 # Query # # use `test`; BEGIN +master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # use `test`; COMMIT +master-bin.000001 # Query # # COMMIT SELECT HEX(f1) FROM t1; HEX(f1) 8300 diff --git a/mysql-test/r/ctype_cp932_binlog_stm.result b/mysql-test/r/ctype_cp932_binlog_stm.result index 0cd2d395ebc..044885d1ea7 100644 --- a/mysql-test/r/ctype_cp932_binlog_stm.result +++ b/mysql-test/r/ctype_cp932_binlog_stm.result @@ -9,7 +9,7 @@ EXECUTE stmt1 USING @var1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES('ƒ\0') +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(0x8300) SELECT HEX(f1) FROM t1; HEX(f1) 8300 @@ -29,21 +29,29 @@ HEX(s1) HEX(s2) d 466F6F2773206120426172 ED40ED41ED42 47.93 DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 369| +SHOW BINLOG EVENTS FROM 370| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 369 Query 1 535 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, +master-bin.000001 370 Query 1 536 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, s2 CHAR(50) CHARACTER SET cp932, d DECIMAL(10,2)) -master-bin.000001 535 Query 1 784 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `bug18293`(IN ins1 CHAR(50), +master-bin.000001 536 Query 1 785 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 784 Query 1 1048 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 1048 Query 1 1137 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1137 Query 1 1216 use `test`; DROP TABLE t4 +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 End of 5.0 tests -SHOW BINLOG EVENTS FROM 364; +SHOW BINLOG EVENTS FROM 365; ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error +Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. +CREATE TABLE t1 (a varchar(16)) character set cp932; +INSERT INTO t1 VALUES (0x8372835E),(0x8352835E); +SELECT hex(a), hex(lower(a)), hex(upper(a)) FROM t1 ORDER BY binary(a); +hex(a) hex(lower(a)) hex(upper(a)) +8352835E 8352835E 8352835E +8372835E 8372835E 8372835E +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/ctype_gbk_binlog.result b/mysql-test/r/ctype_gbk_binlog.result new file mode 100644 index 00000000000..a49e170ff19 --- /dev/null +++ b/mysql-test/r/ctype_gbk_binlog.result @@ -0,0 +1,26 @@ +SET NAMES gbk; +CREATE TABLE t1 ( +f1 BLOB +) ENGINE=MyISAM DEFAULT CHARSET=gbk; +CREATE PROCEDURE p1(IN val BLOB) +BEGIN +SET @tval = val; +SET @sql_cmd = CONCAT_WS(' ', 'insert into t1(f1) values(?)'); +PREPARE stmt FROM @sql_cmd; +EXECUTE stmt USING @tval; +DEALLOCATE PREPARE stmt; +END| +SET @`tcontent`:=_binary 0x50434B000900000000000000E9000000 COLLATE `binary`/*!*/; +CALL p1(@`tcontent`); +FLUSH LOGS; +DROP PROCEDURE p1; +RENAME TABLE t1 to t2; +SELECT hex(f1) FROM t2; +hex(f1) +50434B000900000000000000E9000000 +SELECT hex(f1) FROM t1; +hex(f1) +50434B000900000000000000E9000000 +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result index 5dc9ea35cc3..711921eb526 100644 --- a/mysql-test/r/ctype_ldml.result +++ b/mysql-test/r/ctype_ldml.result @@ -40,6 +40,14 @@ abcd abcd efgh efgh ijkl ijkl DROP TABLE t1; +# +# Bug#43827 Server closes connections and restarts +# +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); +INSERT INTO t1 SELECT REPEAT('a',11); +Warnings: +Warning 1265 Data truncated for column 'c1' at row 1 +DROP TABLE t1; show collation like 'ucs2_vn_ci'; Collation Charset Id Default Compiled Sortlen ucs2_vn_ci ucs2 242 8 diff --git a/mysql-test/r/ctype_sjis.result b/mysql-test/r/ctype_sjis.result index 91d6ebd9795..1469e335f23 100644 --- a/mysql-test/r/ctype_sjis.result +++ b/mysql-test/r/ctype_sjis.result @@ -209,3 +209,13 @@ SET NAMES sjis; SELECT HEX('²“‘@Œ\') FROM DUAL; HEX('²“‘@Œ\') 8DB2939181408C5C +# Start of 5.1 tests +Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. +CREATE TABLE t1 (a varchar(16)) character set sjis; +INSERT INTO t1 VALUES (0x8372835E),(0x8352835E); +SELECT hex(a), hex(lower(a)), hex(upper(a)) FROM t1 ORDER BY binary(a); +hex(a) hex(lower(a)) hex(upper(a)) +8352835E 8352835E 8352835E +8372835E 8372835E 8372835E +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/ddl_i18n_koi8r.result b/mysql-test/r/ddl_i18n_koi8r.result index af3a0899181..fe24c17a1c5 100644 --- a/mysql-test/r/ddl_i18n_koi8r.result +++ b/mysql-test/r/ddl_i18n_koi8r.result @@ -2829,7 +2829,11 @@ t2 CREATE TABLE `t2` ( `col1` varchar(10) COLLATE cp1251_general_cs DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs +---> connection: con2 + +---> connection: con3 + ---> connection: default -use test| -DROP DATABASE mysqltest1| -DROP DATABASE mysqltest2| +USE test; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; diff --git a/mysql-test/r/ddl_i18n_utf8.result b/mysql-test/r/ddl_i18n_utf8.result index 10c2afcadc1..cf4272bf90c 100644 --- a/mysql-test/r/ddl_i18n_utf8.result +++ b/mysql-test/r/ddl_i18n_utf8.result @@ -2829,7 +2829,11 @@ t2 CREATE TABLE `t2` ( `col1` varchar(10) COLLATE cp1251_general_cs DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs +---> connection: con2 + +---> connection: con3 + ---> connection: default -use test| -DROP DATABASE mysqltest1| -DROP DATABASE mysqltest2| +USE test; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 306c51fb8cf..80f04ffd455 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -383,3 +383,21 @@ select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID; ID DATA FID drop table t1, t2; drop user mysqltest_1; +# End of 4.1 tests +SELECT 0 FROM +(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05, +(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10, +(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15, +(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20, +(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25, +(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30, +(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35, +(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40, +(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45, +(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50, +(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55, +(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60, +(SELECT 0) t61; +0 +0 +# End of 5.0 tests diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index f71bbd175e3..e0324af8cfd 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -629,21 +629,21 @@ SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE fruit_name = 'APPLE'; SELECT @v5, @v6, @v7, @v8; @v5 @v6 @v7 @v8 -3 PEAR 3 PEAR +2 APPLE 2 APPLE SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 WHERE fruit_name = 'APPLE'; SELECT @v5, @v6, @v7, @v8, @v9, @v10; @v5 @v6 @v7 @v8 @v9 @v10 -3 PEAR 3 PEAR 5 PEARAPPLE +2 APPLE 2 APPLE 4 APPLEAPPLE SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO @v13, @v14 FROM t1 WHERE fruit_name = 'APPLE'; SELECT @v11, @v12, @v13, @v14; @v11 @v12 @v13 @v14 -6 PEARPEAR 6 PEARPEAR +4 APPLEAPPLE 4 APPLEAPPLE SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE'; SELECT @v15, @v16; @v15 @v16 -6 PEARPEAR +4 APPLEAPPLE SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = 'APPLE'; SELECT @v17, @v18; diff --git a/mysql-test/r/events_stress.result b/mysql-test/r/events_stress.result index 17eb32b36b7..9b9f3caaff6 100644 --- a/mysql-test/r/events_stress.result +++ b/mysql-test/r/events_stress.result @@ -63,3 +63,4 @@ DROP TABLE fill_it1; DROP TABLE fill_it2; DROP TABLE fill_it3; DROP DATABASE events_test; +SET GLOBAL event_scheduler=off; diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 8e14b7695ee..b4e61d0e4fc 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -117,4 +117,13 @@ Warnings: Error 1259 ZLIB: Input data corrupted Error 1259 ZLIB: Input data corrupted drop table t1; +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 VALUES (1), (1111), (11111); +SELECT UNCOMPRESS(c1), UNCOMPRESSED_LENGTH(c1) FROM t1; +UNCOMPRESS(c1) UNCOMPRESSED_LENGTH(c1) +NULL NULL +NULL NULL +NULL 825307441 +EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_concat.result b/mysql-test/r/func_concat.result index 7e7c163716e..75b4888fbb2 100644 --- a/mysql-test/r/func_concat.result +++ b/mysql-test/r/func_concat.result @@ -89,3 +89,34 @@ c1 c2 First DROP TABLE t1; # End of 5.0 tests +# +# Bug #44743: Join in combination with concat does not always work +# +CREATE TABLE t1 ( +a VARCHAR(100) NOT NULL DEFAULT '0', +b VARCHAR(2) NOT NULL DEFAULT '', +c VARCHAR(2) NOT NULL DEFAULT '', +d TEXT NOT NULL, +PRIMARY KEY (a, b, c), +KEY (a) +) DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES ('gui_A', 'a', 'b', 'str1'), +('gui_AB', 'a', 'b', 'str2'), ('gui_ABC', 'a', 'b', 'str3'); +CREATE TABLE t2 ( +a VARCHAR(100) NOT NULL DEFAULT '', +PRIMARY KEY (a) +) DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES ('A'), ('AB'), ('ABC'); +SELECT CONCAT('gui_', t2.a), t1.d FROM t2 +LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; +CONCAT('gui_', t2.a) d +gui_A str1 +gui_AB str2 +gui_ABC str3 +EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 +LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL PRIMARY 102 NULL 3 Using index +1 SIMPLE t1 eq_ref PRIMARY,a PRIMARY 318 func,const,const 1 +DROP TABLE t1, t2; +# End of 5.1 tests diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index 25b921681c5..c2f369b3941 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -95,3 +95,14 @@ Note 1003 select password('idkfa ') AS `password('idkfa ')`,old_password('idkfa' select encrypt('1234','_.'); encrypt('1234','_.') # +# +# Bug #44767: invalid memory reads in password() and old_password() +# functions +# +CREATE TABLE t1(c1 MEDIUMBLOB); +INSERT INTO t1 VALUES (REPEAT('a', 1024)); +SELECT OLD_PASSWORD(c1), PASSWORD(c1) FROM t1; +OLD_PASSWORD(c1) PASSWORD(c1) +77023ffe214c04ff *82E58A2C08AAFE72C8EB523069CD8ADB33F78F58 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 1e967b668c5..88a822a2fa6 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -587,4 +587,25 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; CASE c1 WHEN c1 + 1 THEN 1 END ABS(AVG(c0)) NULL 1.0000 DROP TABLE t1; +CREATE TABLE t1(a TEXT, b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); +INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); +INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); +SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); +SUM( DISTINCT a ) +SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); +SUM( DISTINCT b ) +1 +SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); +SUM( DISTINCT c ) +1 +SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); +SUM( DISTINCT d ) +1.00 +SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); +SUM( DISTINCT e ) +1 +SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN +((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); +SUM( DISTINCT e ) +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index c3d2db2d553..fd7ef72409e 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -437,6 +437,13 @@ a ROUND(a) -1e+16 -10000000000000002 1e+16 10000000000000002 DROP TABLE t1; +CREATE TABLE t1(f1 LONGTEXT) engine=myisam; +INSERT INTO t1 VALUES ('a'); +SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a'; +1 +SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a'; +1 +DROP TABLE t1; End of 5.0 tests SELECT 1e308 + 1e308; 1e308 + 1e308 @@ -456,4 +463,23 @@ NULL SELECT POW(10, 309); POW(10, 309) NULL +# +# Bug #44768: SIGFPE crash when selecting rand from a view +# containing null +# +CREATE OR REPLACE VIEW v1 AS SELECT NULL AS a; +SELECT RAND(a) FROM v1; +RAND(a) +0.155220427694936 +DROP VIEW v1; +SELECT RAND(a) FROM (SELECT NULL AS a) b; +RAND(a) +0.155220427694936 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (NULL); +SELECT RAND(i) FROM t1; +RAND(i) +0.155220427694936 +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index ecdc35ac4cd..14ebc8203ec 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -146,3 +146,16 @@ NULL 0 0 drop table t1; +CREATE TABLE t1( a SET('a', 'b', 'c') ); +CREATE TABLE t2( a SET('a', 'b', 'c') ); +INSERT INTO t1 VALUES ('d'); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +INSERT INTO t2 VALUES (''); +SELECT CONVERT( a USING latin1 ) FROM t1; +CONVERT( a USING latin1 ) + +SELECT CONVERT( a USING latin1 ) FROM t2; +CONVERT( a USING latin1 ) + +DROP TABLE t1, t2; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 25cbf2470ed..a0c3935fde0 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2525,6 +2525,15 @@ SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i F h i 31.12.2008 AAAAAA, aaaaaa DROP TABLE t1; +# +# BUG#44774: load_file function produces valgrind warnings +# +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES ('aaaaaaaa'); +SELECT LOAD_FILE(a) FROM t1; +LOAD_FILE(a) +NULL +DROP TABLE t1; End of 5.0 tests drop table if exists t1; create table t1(f1 tinyint default null)engine=myisam; diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 494b7a36532..a3708d06a1c 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -984,4 +984,52 @@ f4 geometry YES NULL f5 datetime YES NULL drop view v1; drop table t1; +SELECT MultiPoint(12345,''); +MultiPoint(12345,'') +NULL +SELECT MultiPoint(123451,''); +MultiPoint(123451,'') +NULL +SELECT MultiPoint(1234512,''); +MultiPoint(1234512,'') +NULL +SELECT MultiPoint(12345123,''); +MultiPoint(12345123,'') +NULL +SELECT MultiLineString(12345,''); +MultiLineString(12345,'') +NULL +SELECT MultiLineString(123451,''); +MultiLineString(123451,'') +NULL +SELECT MultiLineString(1234512,''); +MultiLineString(1234512,'') +NULL +SELECT MultiLineString(12345123,''); +MultiLineString(12345123,'') +NULL +SELECT LineString(12345,''); +LineString(12345,'') +NULL +SELECT LineString(123451,''); +LineString(123451,'') +NULL +SELECT LineString(1234512,''); +LineString(1234512,'') +NULL +SELECT LineString(12345123,''); +LineString(12345123,'') +NULL +SELECT Polygon(12345,''); +Polygon(12345,'') +NULL +SELECT Polygon(123451,''); +Polygon(123451,'') +NULL +SELECT Polygon(1234512,''); +Polygon(1234512,'') +NULL +SELECT Polygon(12345123,''); +Polygon(12345123,'') +NULL End of 5.1 tests diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index de80a83d538..a677d71b266 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1358,3 +1358,58 @@ DROP USER 'userbug33464'@'localhost'; USE test; DROP DATABASE dbbug33464; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; +CREATE USER user1; +CREATE USER user2; +GRANT CREATE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ON db1.* TO 'user2'@'%'; +GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; +FLUSH PRIVILEGES; +SHOW GRANTS FOR 'user1'@'localhost'; +Grants for user1@localhost +GRANT USAGE ON *.* TO 'user1'@'localhost' +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' +** Connect as user1 and create a procedure. +** The creation will imply implicitly assigned +** EXECUTE and ALTER ROUTINE privileges to +** the current user user1@localhost. +SELECT @@GLOBAL.sql_mode; +@@GLOBAL.sql_mode + +SELECT @@SESSION.sql_mode; +@@SESSION.sql_mode + +CREATE DATABASE db1; +CREATE PROCEDURE db1.proc1(p1 INT) +BEGIN +SET @x = 0; +REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; +END ;|| +** Connect as user2 and create a procedure. +** Implicitly assignment of privileges will +** fail because the user2@localhost is an +** unknown user. +CREATE PROCEDURE db1.proc2(p1 INT) +BEGIN +SET @x = 0; +REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; +END ;|| +Warnings: +Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges +SHOW GRANTS FOR 'user1'@'localhost'; +Grants for user1@localhost +GRANT USAGE ON *.* TO 'user1'@'localhost' +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc1` TO 'user1'@'localhost' +SHOW GRANTS FOR 'user2'; +Grants for user2@% +GRANT USAGE ON *.* TO 'user2'@'%' +GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' +DROP PROCEDURE db1.proc1; +DROP PROCEDURE db1.proc2; +REVOKE ALL ON db1.* FROM 'user1'@'localhost'; +REVOKE ALL ON db1.* FROM 'user2'@'%'; +DROP USER 'user1'; +DROP USER 'user1'@'localhost'; +DROP USER 'user2'; +DROP DATABASE db1; diff --git a/mysql-test/r/grant_cache_no_prot.result b/mysql-test/r/grant_cache_no_prot.result index cb9acaf540d..32bb9cce90e 100644 --- a/mysql-test/r/grant_cache_no_prot.result +++ b/mysql-test/r/grant_cache_no_prot.result @@ -206,7 +206,8 @@ Qcache_hits 8 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 8 ------ switch to connection default and close connections ----- +----- close connections ----- +----- switch to connection default ----- set names binary; delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); diff --git a/mysql-test/r/grant_cache_ps_prot.result b/mysql-test/r/grant_cache_ps_prot.result index cf1450f3b75..281468ee2e1 100644 --- a/mysql-test/r/grant_cache_ps_prot.result +++ b/mysql-test/r/grant_cache_ps_prot.result @@ -206,7 +206,8 @@ Qcache_hits 8 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 5 ------ switch to connection default and close connections ----- +----- close connections ----- +----- switch to connection default ----- set names binary; delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index b17884c4f7a..27448d3e949 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2462,4 +2462,43 @@ c 1 2 DROP TABLE t1; +# +# Bug #45386: Wrong query result with MIN function in field list, +# WHERE and GROUP BY clause +# +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; +# test MIN +#should use range with index for group by +EXPLAIN +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +a MIN(b) +2 1 +# test MAX +#should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +a MAX(b) +2 0 +# test 3 ranges and use the middle one +INSERT INTO t SELECT a, 2 FROM t; +#should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t range NULL a 10 NULL 9 Using where; Using index for group-by +#should return 1 row +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +a MAX(b) +2 1 +DROP TABLE t; End of 5.0 tests diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 9db03855c01..7ad0f212d99 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -336,4 +336,11 @@ a b NULL NULL NULL 1 drop table t1; +# +# bug#39918 - memory (heap) engine crashing while executing self join with delete +# +CREATE TABLE t1(a INT, KEY USING BTREE (a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(1); +DELETE a1 FROM t1 AS a1, t1 AS a2 WHERE a1.a=a2.a; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 8a935d87457..c639b20de91 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -557,6 +557,30 @@ a 1 2 drop table t0, t1, t2, t3; +# +# BUG#44810: index merge and order by with low sort_buffer_size +# crashes server! +# +CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B)); +INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128)); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +SET SESSION sort_buffer_size=1; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '1' +EXPLAIN +SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' +ORDER BY a,b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 131,131 NULL 64 Using sort_union(a,b); Using where; Using filesort +SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' +ORDER BY a,b; +SET SESSION sort_buffer_size=DEFAULT; +DROP TABLE t1; End of 5.0 tests #---------------- ROR-index_merge tests ----------------------- SET SESSION STORAGE_ENGINE = MyISAM; diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 475839569c7..6305f8cd47a 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -61,7 +61,7 @@ begin select table_name from information_schema.key_column_usage order by table_name; end| -create table t1 +create table t1 (f1 int(10) unsigned not null, f2 varchar(100) not null, primary key (f1), unique key (f2)); @@ -203,15 +203,15 @@ View Create View character_set_client collation_connection v2 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1` latin1 latin1_swedish_ci show create view testdb_1.v1; ERROR 42000: SHOW VIEW command denied to user 'testdb_2'@'localhost' for table 'v1' -select table_name from information_schema.columns a +select table_name from information_schema.columns a where a.table_name = 'v2'; table_name v2 -select view_definition from information_schema.views a +select view_definition from information_schema.views a where a.table_name = 'v2'; view_definition select `v1`.`f1` AS `f1` from `testdb_1`.`v1` -select view_definition from information_schema.views a +select view_definition from information_schema.views a where a.table_name = 'testdb_1.v1'; view_definition select * from v2; diff --git a/mysql-test/r/init_file.result b/mysql-test/r/init_file.result index 8e014815a9c..43ed908ad01 100644 --- a/mysql-test/r/init_file.result +++ b/mysql-test/r/init_file.result @@ -4,6 +4,7 @@ SELECT * INTO @Y FROM init_file.startup limit 1,1; SELECT YEAR(@X)-YEAR(@Y); YEAR(@X)-YEAR(@Y) 0 +DROP DATABASE init_file; ok end of 4.1 tests select * from t1; @@ -19,3 +20,5 @@ y 3 11 13 +drop table t1, t2; +call mtr.force_restart(); diff --git a/mysql-test/r/innodb_bug21704.result b/mysql-test/r/innodb_bug21704.result new file mode 100644 index 00000000000..b8e0b15d50d --- /dev/null +++ b/mysql-test/r/innodb_bug21704.result @@ -0,0 +1,55 @@ +# +# Bug#21704: Renaming column does not update FK definition. +# + +# Test that it's not possible to rename columns participating in a +# foreign key (either in the referencing or referenced table). + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT ENGINE=INNODB; +CREATE TABLE t2 (a INT PRIMARY KEY, b INT, +CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(a)) +ROW_FORMAT=COMPACT ENGINE=INNODB; +CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY(b), C INT, +CONSTRAINT fk2 FOREIGN KEY (b) REFERENCES t3 (a)) +ROW_FORMAT=COMPACT ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +INSERT INTO t3 VALUES (1,1,1),(2,2,2),(3,3,3); + +# Test renaming the column in the referenced table. + +ALTER TABLE t1 CHANGE a c INT; +ERROR HY000: Error on rename of '#sql-temporary' to './test/t1' (errno: 150) +# Ensure that online column rename works. +ALTER TABLE t1 CHANGE b c INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 + +# Test renaming the column in the referencing table + +ALTER TABLE t2 CHANGE a c INT; +ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150) +# Ensure that online column rename works. +ALTER TABLE t2 CHANGE b c INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 + +# Test with self-referential constraints + +ALTER TABLE t3 CHANGE a d INT; +ERROR HY000: Error on rename of '#sql-temporary' to './test/t3' (errno: 150) +ALTER TABLE t3 CHANGE b d INT; +ERROR HY000: Error on rename of '#sql-temporary' to './test/t3' (errno: 150) +# Ensure that online column rename works. +ALTER TABLE t3 CHANGE c d INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 + +# Cleanup. + +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/r/innodb_bug40565.result b/mysql-test/r/innodb_bug40565.result new file mode 100644 index 00000000000..21e923d9336 --- /dev/null +++ b/mysql-test/r/innodb_bug40565.result @@ -0,0 +1,9 @@ +create table bug40565(value decimal(4,2)) engine=innodb; +insert into bug40565 values (1), (null); +update bug40565 set value=NULL; +affected rows: 1 +info: Rows matched: 2 Changed: 1 Warnings: 0 +update bug40565 set value=NULL; +affected rows: 0 +info: Rows matched: 2 Changed: 0 Warnings: 0 +drop table bug40565; diff --git a/mysql-test/r/innodb_bug42101-nonzero.result b/mysql-test/r/innodb_bug42101-nonzero.result new file mode 100644 index 00000000000..277dfffdd35 --- /dev/null +++ b/mysql-test/r/innodb_bug42101-nonzero.result @@ -0,0 +1,26 @@ +set global innodb_commit_concurrency=0; +ERROR HY000: Incorrect arguments to SET +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +1 +set global innodb_commit_concurrency=1; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +1 +set global innodb_commit_concurrency=42; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +42 +set global innodb_commit_concurrency=DEFAULT; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +1 +set global innodb_commit_concurrency=0; +ERROR HY000: Incorrect arguments to SET +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +1 +set global innodb_commit_concurrency=1; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +1 diff --git a/mysql-test/r/innodb_bug42101.result b/mysql-test/r/innodb_bug42101.result new file mode 100644 index 00000000000..805097ffe9d --- /dev/null +++ b/mysql-test/r/innodb_bug42101.result @@ -0,0 +1,22 @@ +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +0 +set global innodb_commit_concurrency=1; +ERROR HY000: Incorrect arguments to SET +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +0 +set global innodb_commit_concurrency=42; +ERROR HY000: Incorrect arguments to SET +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +0 +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +0 +set global innodb_commit_concurrency=DEFAULT; +select @@innodb_commit_concurrency; +@@innodb_commit_concurrency +0 diff --git a/mysql-test/r/innodb_bug45357.result b/mysql-test/r/innodb_bug45357.result new file mode 100644 index 00000000000..7adeff2062f --- /dev/null +++ b/mysql-test/r/innodb_bug45357.result @@ -0,0 +1,7 @@ +set session transaction isolation level read committed; +create table bug45357(a int, b int,key(b))engine=innodb; +insert into bug45357 values (25170,6122); +update bug45357 set a=1 where b=30131; +delete from bug45357 where b < 20996; +delete from bug45357 where b < 7001; +drop table bug45357; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 191a8578d4c..3ff5f04b6c6 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1408,6 +1408,30 @@ 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, @@ -1677,10 +1701,10 @@ INSERT INTO t1 VALUES (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 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; +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 vid PRIMARY 12 NULL 16 Using where -SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; +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 @@ -1706,6 +1730,35 @@ 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); @@ -2040,4 +2093,119 @@ 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; End of 5.1 tests diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 780e91ea73f..2f2cc6334a9 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -765,6 +765,11 @@ f1 f2 2 2 10 10 DROP TABLE t1, t2; +CREATE TABLE t1 ( a INT KEY, b INT ); +INSERT INTO t1 VALUES ( 0, 1 ); +INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2; +ERROR 23000: Duplicate entry '0' for key 'PRIMARY' +DROP TABLE t1; SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); CREATE TABLE t2 (d VARCHAR(10)); diff --git a/mysql-test/r/log_tables_debug.result b/mysql-test/r/log_tables_debug.result new file mode 100644 index 00000000000..daedb8c103a --- /dev/null +++ b/mysql-test/r/log_tables_debug.result @@ -0,0 +1,24 @@ +SET @old_general_log= @@global.general_log; +SET @old_general_log_file= @@global.general_log_file; +SET @old_slow_query_log= @@global.slow_query_log; +SET @old_slow_query_log_file= @@global.slow_query_log_file; +# +# Bug#45387 Information about statement id for prepared +# statements missed from general log +# +SET @@global.general_log = ON; +SET @@global.general_log_file = 'bug45387_general.log'; +SET SESSION debug='+d,reset_log_last_time'; +FLUSH LOGS; +SET @@global.general_log = @old_general_log; +SET @@global.general_log_file = @old_general_log_file; +SET SESSION debug='-d'; +Bug#45387: ID match. +End of 5.1 tests +# +# Cleanup +# +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; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index f53b328d14e..bf9108459d7 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2127,4 +2127,18 @@ SELECT * FROM m1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP VIEW v1; DROP TABLE m1, t1; +# +# Bug #45796: invalid memory reads and writes when altering merge and +# base tables +# +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1); +ALTER TABLE m1 ADD INDEX idx_c1(c1); +SELECT * FROM m1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +ALTER TABLE t1 ADD INDEX idx_c1(c1); +SELECT * FROM m1; +c1 +DROP TABLE m1; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/mysql-bug45236.result b/mysql-test/r/mysql-bug45236.result new file mode 100644 index 00000000000..cefcb1d314c --- /dev/null +++ b/mysql-test/r/mysql-bug45236.result @@ -0,0 +1,8 @@ +DROP TABLE IF EXISTS t1; +SET @old_max_allowed_packet= @@global.max_allowed_packet; +SET @@global.max_allowed_packet = 1024 * 1024 + 1024; +CREATE TABLE t1(data LONGBLOB); +INSERT INTO t1 SELECT CONCAT(REPEAT('1', 1024*1024 - 27), +"\'\r dummydb dummyhost"); +DROP TABLE t1; +SET @@global.max_allowed_packet = @old_max_allowed_packet; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 5054c3aa76f..e704f81e187 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -192,6 +192,13 @@ delimiter 1 1 1 +COUNT (*) +1 +COUNT (*) +1 +COUNT (*) +1 +ERROR 2005 (HY000) at line 1: Unknown MySQL server host 'invalid_hostname' (errno) End of 5.0 tests WARNING: --server-arg option not supported in this configuration. Warning (Code 1286): Unknown table engine 'nonexistent' @@ -200,4 +207,5 @@ Warning (Code 1286): Unknown table engine 'nonexistent2' Warning (Code 1266): Using storage engine MyISAM for table 't2' Error (Code 1050): Table 't2' already exists drop tables t1, t2; +<TABLE BORDER=1><TR><TH><</TH></TR><TR><TD>< & ></TD></TR></TABLE> End of tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index b55a96b6f30..295a2f41d40 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -471,4 +471,7 @@ IS NOT NULL 1 *** Unsigned server_id 4294967295 is found: 1 *** SET @@global.server_id= 1; +RESET MASTER; +FLUSH LOGS; +End of 5.0 tests End of 5.1 tests diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index c97131563cb..2f8e0ce5ec1 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -3563,9 +3563,6 @@ grant REPLICATION CLIENT on *.* to mysqltest_1@localhost; drop table t1; drop user mysqltest_1@localhost; # -# Bug#21527 mysqldump incorrectly tries to LOCK TABLES on the -# information_schema database. -# # Bug#21424 mysqldump failing to export/import views # create database mysqldump_myDB; @@ -3605,6 +3602,39 @@ drop user myDB_User@localhost; drop database mysqldump_myDB; use test; # +# Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the +# information_schema database. +# +# Bug #33762: mysqldump can not dump INFORMATION_SCHEMA +# +DROP TABLE IF EXISTS `TABLES`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TEMPORARY TABLE `TABLES` ( + `TABLE_CATALOG` varchar(512) DEFAULT NULL, + `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '', + `ENGINE` varchar(64) DEFAULT NULL, + `VERSION` bigint(21) unsigned DEFAULT NULL, + `ROW_FORMAT` varchar(10) DEFAULT NULL, + `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, + `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL, + `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, + `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, + `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, + `DATA_FREE` bigint(21) unsigned DEFAULT NULL, + `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL, + `CREATE_TIME` datetime DEFAULT NULL, + `UPDATE_TIME` datetime DEFAULT NULL, + `CHECK_TIME` datetime DEFAULT NULL, + `TABLE_COLLATION` varchar(32) DEFAULT NULL, + `CHECKSUM` bigint(21) unsigned DEFAULT NULL, + `CREATE_OPTIONS` varchar(255) DEFAULT NULL, + `TABLE_COMMENT` varchar(80) NOT NULL DEFAULT '' +) ENGINE=MEMORY DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +# # Bug#19745 mysqldump --xml produces invalid xml # DROP TABLE IF EXISTS t1; @@ -4006,6 +4036,181 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +create table t1 (a text , b text); +create table t2 (a text , b text); +insert t1 values ("Duck, Duck", "goose"); +insert t1 values ("Duck, Duck", "pidgeon"); +insert t2 values ("We the people", "in order to perform"); +insert t2 values ("a more perfect", "union"); +select * from t1; +a b +Duck, Duck goose +Duck, Duck pidgeon +select * from t2; +a b +We the people in order to perform +a more perfect union +test.t1: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 +test.t2: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 +select * from t1; +a b +Duck, Duck goose +Duck, Duck pidgeon +Duck, Duck goose +Duck, Duck pidgeon +select * from t2; +a b +We the people in order to perform +a more perfect union +We the people in order to perform +a more perfect union +create table words(a varchar(255)); +create table words2(b varchar(255)); +select * from t1; +a b +Duck, Duck goose +Duck, Duck pidgeon +Duck, Duck goose +Duck, Duck pidgeon +Duck, Duck goose +Duck, Duck pidgeon +select * from t2; +a b +We the people in order to perform +a more perfect union +We the people in order to perform +a more perfect union +We the people in order to perform +a more perfect union +select * from words; +a +Aarhus +Aaron +Ababa +aback +abaft +abandon +abandoned +abandoning +abandonment +abandons +Aarhus +Aaron +Ababa +aback +abaft +abandon +abandoned +abandoning +abandonment +abandons +abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration +select * from words2; +b +abase +abased +abasement +abasements +abases +abash +abashed +abashes +abashing +abasing +abate +abated +abatement +abatements +abater +abates +abating +Abba +abbe +abbey +abbeys +abbot +abbots +Abbott +abbreviate +abbreviated +abbreviates +abbreviating +abbreviation +abbreviations +Abby +abdomen +abdomens +abdominal +abduct +abducted +abduction +abductions +abductor +abductors +abducts +Abe +abed +Abel +Abelian +Abelson +Aberdeen +Abernathy +aberrant +aberration +drop table words; +mysql-import: Error: 1146, Table 'test.words' doesn't exist, when using table: words +drop table t1; +drop table t2; +drop table words2; # # Bug#16853 mysqldump doesn't show events # @@ -4226,6 +4431,57 @@ DROP DATABASE mysqldump_test_db; # -- End of test case for Bug#32538. SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT; + +Bug #34861 - mysqldump with --tab gives weird output for triggers. + +CREATE TABLE t1 (f1 INT); +CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW SET @f1 = 1; +CREATE PROCEDURE pr1 () SELECT "Meow"; +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT "Meow"; + +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test ev1 root@localhost SYSTEM ONE TIME 2030-01-01 00:00:00 NULL NULL NULL NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; +name body +pr1 SELECT "Meow" + +dump table; if anything goes to stdout, it ends up here: --------------- + +drop everything +DROP EVENT ev1; +DROP TRIGGER tr1; +DROP TABLE t1; +DROP PROCEDURE pr1; + +reload table; this should restore table and trigger +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; +name body + +reload db; this should restore routines and events +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test ev1 root@localhost SYSTEM ONE TIME 2030-01-01 00:00:00 NULL NULL NULL NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; +name body +pr1 SELECT "Meow" + +cleanup +DROP EVENT IF EXISTS ev1; +DROP PROCEDURE IF EXISTS pr1; +DROP TRIGGER IF EXISTS tr1; +DROP TABLE IF EXISTS t1; # # End of 5.1 tests # diff --git a/mysql-test/r/mysqldump_restore.result b/mysql-test/r/mysqldump_restore.result new file mode 100644 index 00000000000..16698251913 --- /dev/null +++ b/mysql-test/r/mysqldump_restore.result @@ -0,0 +1,110 @@ +# Set concurrent_insert = 0 to prevent random errors +# will reset to original value at the end of the test +SET @old_concurrent_insert = @@global.concurrent_insert; +SET @@global.concurrent_insert = 0; +# Pre-test cleanup +DROP TABLE IF EXISTS t1; +# Begin tests +# +# Bug#2005 Long decimal comparison bug. +# +CREATE TABLE t1 (a DECIMAL(64, 20)); +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), +("0987654321098765432109876543210987654321"); +# Begin testing mysqldump output + restore +# Create 'original table name - <table>_orig +SET @orig_table_name = CONCAT('test.t1', '_orig'); +# Rename original table +ALTER TABLE test.t1 RENAME to test.t1_orig; +# Recreate table from mysqldump output +# Compare original and recreated tables +# Recreated table: test.t1 +# Original table: test.t1_orig +Comparing tables test.t1 and test.t1_orig +# Cleanup +DROP TABLE test.t1, test.t1_orig; +# +# Bug#3361 mysqldump quotes DECIMAL values inconsistently +# +CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT); +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES'; +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); +ERROR 42S22: Unknown column '1.2345' in 'field list' +SET SQL_MODE=@OLD_SQL_MODE; +# Begin testing mysqldump output + restore +# Create 'original table name - <table>_orig +SET @orig_table_name = CONCAT('test.t1', '_orig'); +# Rename original table +ALTER TABLE test.t1 RENAME to test.t1_orig; +# Recreate table from mysqldump output +# Compare original and recreated tables +# Recreated table: test.t1 +# Original table: test.t1_orig +Comparing tables test.t1 and test.t1_orig +# Cleanup +DROP TABLE test.t1, test.t1_orig; +# +# Bug#1994 mysqldump does not correctly dump UCS2 data +# Bug#4261 mysqldump 10.7 (mysql 4.1.2) --skip-extended-insert drops NULL from inserts +# +CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; +INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); +# Begin testing mysqldump output + restore +# Create 'original table name - <table>_orig +SET @orig_table_name = CONCAT('test.t1', '_orig'); +# Rename original table +ALTER TABLE test.t1 RENAME to test.t1_orig; +# Recreate table from mysqldump output +# Compare original and recreated tables +# Recreated table: test.t1 +# Original table: test.t1_orig +Comparing tables test.t1 and test.t1_orig +# Cleanup +DROP TABLE test.t1, test.t1_orig; +# +# WL#2319 Exclude Tables from dump +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (4),(5),(6); +# Begin testing mysqldump output + restore +# Create 'original table name - <table>_orig +SET @orig_table_name = CONCAT('test.t2', '_orig'); +# Rename original table +ALTER TABLE test.t2 RENAME to test.t2_orig; +# Recreate table from mysqldump output +# Compare original and recreated tables +# Recreated table: test.t2 +# Original table: test.t2_orig +Comparing tables test.t2 and test.t2_orig +# Cleanup +DROP TABLE test.t2, test.t2_orig; +DROP TABLE t1; +# +# Bug#8830 mysqldump --skip-extended-insert causes --hex-blob to dump wrong values +# +CREATE TABLE t1 (`b` blob); +INSERT INTO `t1` VALUES (0x602010000280100005E71A); +# Begin testing mysqldump output + restore +# Create 'original table name - <table>_orig +SET @orig_table_name = CONCAT('test.t1', '_orig'); +# Rename original table +ALTER TABLE test.t1 RENAME to test.t1_orig; +# Recreate table from mysqldump output +# Compare original and recreated tables +# Recreated table: test.t1 +# Original table: test.t1_orig +Comparing tables test.t1 and test.t1_orig +# Cleanup +DROP TABLE test.t1, test.t1_orig; +# End tests +# Cleanup +# Reset concurrent_insert to its original value +SET @@global.concurrent_insert = @old_concurrent_insert; +# remove mysqldumpfile diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index a9c20e34517..52a1734ea54 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -545,6 +545,8 @@ mysqltest: At line 1: Failed to open file 'non_existing_file' mysqltest: At line 1: Missing required argument 'filename' to command 'file_exists' mysqltest: At line 1: Missing required argument 'from_file' to command 'copy_file' mysqltest: At line 1: Missing required argument 'to_file' to command 'copy_file' +mysqltest: At line 1: Missing required argument 'from_file' to command 'move_file' +mysqltest: At line 1: Missing required argument 'to_file' to command 'move_file' mysqltest: At line 1: Missing required argument 'mode' to command 'chmod' mysqltest: At line 1: You must write a 4 digit octal number for mode mysqltest: At line 1: You must write a 4 digit octal number for mode @@ -697,6 +699,7 @@ statement="SHOW COLUMNS FROM t1" row_number=1, column_name="Type", Value=int(11) statement=SHOW COLUMNS FROM t1 row_number=1, column_name=Default, Value=NULL value= ->A B<- value= 1 +value= 2 mysqltest: At line 1: query_get_value - argument list started with '(' must be ended with ')' mysqltest: At line 1: Missing required argument 'query' to command 'query_get_value' mysqltest: At line 1: Missing required argument 'column name' to command 'query_get_value' diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result index c408c14b716..b0dd3acd662 100644 --- a/mysql-test/r/openssl_1.result +++ b/mysql-test/r/openssl_1.result @@ -202,4 +202,10 @@ Ssl_cipher RC4-SHA select 'is still running; no cipher request crashed the server' as result from dual; result is still running; no cipher request crashed the server +GRANT SELECT ON test.* TO bug42158@localhost REQUIRE X509; +FLUSH PRIVILEGES; +SHOW STATUS LIKE 'Ssl_cipher'; +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA +DROP USER bug42158@localhost; End of 5.1 tests diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index f69ba522a9c..8e3fbde1ea8 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1924,5 +1924,72 @@ EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1; +CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) ) +PARTITION BY KEY (a, b) PARTITIONS 3 +; +INSERT INTO t1 VALUES +(17, 1, -8), +(3, 1, -7), +(23, 1, -6), +(22, 1, -5), +(11, 1, -4), +(21, 1, -3), +(19, 1, -2), +(30, 1, -1), +(20, 1, 1), +(16, 1, 2), +(18, 1, 3), +(9, 1, 4), +(15, 1, 5), +(28, 1, 6), +(29, 1, 7), +(25, 1, 8), +(10, 1, 9), +(13, 1, 10), +(27, 1, 11), +(24, 1, 12), +(12, 1, 13), +(26, 1, 14), +(14, 1, 15) +; +SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; +b c +1 -8 +1 -7 +1 -6 +1 -5 +1 -4 +1 -3 +1 -2 +1 -1 +1 1 +1 2 +1 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 +EXPLAIN +SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range bc bc 10 NULL 7 Using where; Using index for group-by +DROP TABLE t1; +# +# Bug #45807: crash accessing partitioned table and sql_mode +# contains ONLY_FULL_GROUP_BY +# +SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM +PARTITION BY HASH(id) PARTITIONS 2; +DROP TABLE t1; +SET SESSION SQL_MODE=DEFAULT; End of 5.1 tests SET @@global.general_log= @old_general_log; diff --git a/mysql-test/r/query_cache_debug.result b/mysql-test/r/query_cache_debug.result index b03a71d3fec..eb59e62c8ba 100644 --- a/mysql-test/r/query_cache_debug.result +++ b/mysql-test/r/query_cache_debug.result @@ -71,3 +71,111 @@ DROP TABLE t1,t2; SET GLOBAL concurrent_insert= DEFAULT; SET GLOBAL query_cache_size= DEFAULT; SET GLOBAL query_cache_type= DEFAULT; +# +# Bug43758 Query cache can lock up threads in 'freeing items' state +# +FLUSH STATUS; +SET GLOBAL query_cache_type=DEMAND; +SET GLOBAL query_cache_size= 1024*768; +DROP TABLE IF EXISTS t1,t2,t3,t4,t5; +CREATE TABLE t1 (a VARCHAR(100)); +CREATE TABLE t2 (a VARCHAR(100)); +CREATE TABLE t3 (a VARCHAR(100)); +CREATE TABLE t4 (a VARCHAR(100)); +CREATE TABLE t5 (a VARCHAR(100)); +INSERT INTO t1 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t2 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t3 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t4 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t5 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +=================================== Connection thd1 +** +** Load Query Cache with a result set and one table. +** +SELECT SQL_CACHE * FROM t1; +a +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +************************************************************************* +** We want to accomplish the following state: +** - Query cache status: TABLE_FLUSH_IN_PROGRESS +** - THD1: invalidate_table_internal (iterating query blocks) +** - THD2: query_cache_insert (cond_wait) +** - THD3: query_cache_insert (cond_wait) +** - No thread should be holding the structure_guard_mutex. +** +** First step is to place a DELETE-statement on the debug hook just +** before the mutex lock in invalidate_table_internal. +** This will allow new result sets to be written into the QC. +** +SET SESSION debug='+d,wait_in_query_cache_invalidate1'; +SET SESSION debug='+d,wait_in_query_cache_invalidate2'; +DELETE FROM t1 WHERE a like '%a%';; +=================================== Connection default +** Assert that the expect process status is obtained. +** +=================================== Connection thd2 +** On THD2: Insert a result into the cache. This attempt will be blocked +** because of a debug hook placed just before the mutex lock after which +** the first part of the result set is written. +SET SESSION debug='+d,wait_in_query_cache_insert'; +SELECT SQL_CACHE * FROM t2 UNION SELECT * FROM t3; +=================================== Connection thd3 +** On THD3: Insert another result into the cache and block on the same +** debug hook. +SET SESSION debug='+d,wait_in_query_cache_insert'; +SELECT SQL_CACHE * FROM t4 UNION SELECT * FROM t5;; +=================================== Connection default +** Assert that the two SELECT-stmt threads to reach the hook. +** +** +** Signal the DELETE thread, THD1, to continue. It will enter the mutex +** lock and set query cache status to TABLE_FLUSH_IN_PROGRESS and then +** unlock the mutex before stopping on the next debug hook. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate1' LIMIT 1 INTO @flush_thread_id; +KILL QUERY @flush_thread_id; +** Assert that we reach the next debug hook. +** +** Signal the remaining debug hooks blocking THD2 and THD3. +** The threads will grab the guard mutex enter the wait condition and +** and finally release the mutex. The threads will continue to wait +** until a broadcast signal reaches them causing both threads to +** come alive and check the condition. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id ASC LIMIT 1 INTO @thread_id; +KILL QUERY @thread_id; +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id DESC LIMIT 1 INTO @thread_id; +KILL QUERY @thread_id; +** +** Finally signal the DELETE statement on THD1 one last time. +** The stmt will complete the query cache invalidation and return +** cache status to NO_FLUSH_IN_PROGRESS. On the status change +** One signal will be sent to the thread group waiting for executing +** invalidations and a broadcast signal will be sent to the thread +** group holding result set writers. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2' LIMIT 1 INTO @flush_thread_id; +KILL QUERY @flush_thread_id; +** +************************************************************************* +** No tables should be locked +=================================== Connection thd2 +a +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +DELETE FROM t1; +DELETE FROM t2; +DELETE FROM t3; +=================================== Connection thd3 +a +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +DELETE FROM t4; +DELETE FROM t5; +=================================== Connection thd1 +** Done. +SET GLOBAL query_cache_size= 0; +# Restore defaults +RESET QUERY CACHE; +FLUSH STATUS; +DROP TABLE t1,t2,t3,t4,t5; +SET GLOBAL query_cache_size= DEFAULT; +SET GLOBAL query_cache_type= DEFAULT; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 09c7d1b329d..50b5c3c13fb 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4457,4 +4457,83 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select '0' AS `a`,'0' AS `b`,'0' AS `c` from `test`.`t1` where 1 DROP TABLE t1; +# +# Bug#45266: Uninitialized variable lead to an empty result. +# +drop table if exists A,AA,B,BB; +CREATE TABLE `A` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`date_key` date NOT NULL, +`date_nokey` date NOT NULL, +`datetime_key` datetime NOT NULL, +`int_nokey` int(11) NOT NULL, +`time_key` time NOT NULL, +`time_nokey` time NOT NULL, +PRIMARY KEY (`pk`), +KEY `date_key` (`date_key`), +KEY `time_key` (`time_key`), +KEY `datetime_key` (`datetime_key`) +); +CREATE TABLE `AA` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`int_nokey` int(11) NOT NULL, +`time_key` time NOT NULL, +KEY `time_key` (`time_key`), +PRIMARY KEY (`pk`) +); +CREATE TABLE `B` ( +`date_nokey` date NOT NULL, +`date_key` date NOT NULL, +`time_key` time NOT NULL, +`datetime_nokey` datetime NOT NULL, +`varchar_key` varchar(1) NOT NULL, +KEY `date_key` (`date_key`), +KEY `time_key` (`time_key`), +KEY `varchar_key` (`varchar_key`) +); +INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x'); +CREATE TABLE `BB` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`int_nokey` int(11) NOT NULL, +`date_key` date NOT NULL, +`varchar_nokey` varchar(1) NOT NULL, +`date_nokey` date NOT NULL, +PRIMARY KEY (`pk`), +KEY `date_key` (`date_key`) +); +INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); +SELECT table1 . `pk` AS field1 +FROM +(BB AS table1 INNER JOIN +(AA AS table2 STRAIGHT_JOIN A AS table3 +ON ( table3 . `date_key` = table2 . `pk` )) +ON ( table3 . `datetime_key` = table2 . `int_nokey` )) +WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) +GROUP BY field1 ; +field1 +SELECT table3 .`date_key` field1 +FROM +B table1 LEFT JOIN B table3 JOIN +(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) +ON table6 .`int_nokey` ON table6 .`date_key` + WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; +field1 +NULL +SELECT table4 . `time_nokey` AS field1 FROM +(AA AS table1 CROSS JOIN +(AA AS table2 STRAIGHT_JOIN +(B AS table3 STRAIGHT_JOIN A AS table4 +ON ( table4 . `date_key` = table3 . `time_key` )) +ON ( table4 . `pk` = table3 . `date_nokey` )) +ON ( table4 . `time_key` = table3 . `datetime_nokey` )) +WHERE ( table4 . `time_key` < table1 . `time_key` AND +table1 . `int_nokey` != 'f') +GROUP BY field1 ORDER BY field1 , field1; +field1 +SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; +field2 +00:05:48 +15:13:38 +drop table A,AA,B,BB; +#end of test for bug#45266 End of 5.1 tests diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 35d61ce757d..17ab2b79043 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1660,3 +1660,13 @@ declare continue handler for sqlstate '00000' set @x=0; end$$ ERROR 42000: Bad SQLSTATE: '00000' LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT MAX (a) FROM t1 WHERE b = 999999; +ERROR 42000: FUNCTION test.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual +SELECT AVG (a) FROM t1 WHERE b = 999999; +AVG (a) +NULL +SELECT non_existent (a) FROM t1 WHERE b = 999999; +ERROR 42000: FUNCTION test.non_existent does not exist +DROP TABLE t1; diff --git a/mysql-test/r/sp-fib.result b/mysql-test/r/sp-fib.result new file mode 100644 index 00000000000..a26e104c1e8 --- /dev/null +++ b/mysql-test/r/sp-fib.result @@ -0,0 +1,33 @@ +drop table if exists t3; +create table t3 ( f bigint unsigned not null ); +drop procedure if exists fib; +create procedure fib(n int unsigned) +begin +if n > 1 then +begin +declare x, y bigint unsigned; +declare c cursor for select f from t3 order by f desc limit 2; +open c; +fetch c into y; +fetch c into x; +insert into t3 values (x+y); +call fib(n-1); +## Close the cursor AFTER the recursion to ensure that the stack +## frame is somewhat intact. +close c; +end; +end if; +end| +set @@max_sp_recursion_depth= 20| +insert into t3 values (0), (1)| +call fib(4)| +select * from t3 order by f asc| +f +0 +1 +1 +2 +3 +drop table t3| +drop procedure fib| +set @@max_sp_recursion_depth= 0| diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9574841bc35..3ad556b8c30 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1337,52 +1337,6 @@ drop procedure opp| drop procedure ip| show procedure status where name like '%p%' and db='test'| Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation -drop table if exists t3| -create table t3 ( f bigint unsigned not null )| -drop procedure if exists fib| -create procedure fib(n int unsigned) -begin -if n > 1 then -begin -declare x, y bigint unsigned; -declare c cursor for select f from t3 order by f desc limit 2; -open c; -fetch c into y; -fetch c into x; -close c; -insert into t3 values (x+y); -call fib(n-1); -end; -end if; -end| -set @@max_sp_recursion_depth= 20| -insert into t3 values (0), (1)| -call fib(3)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -truncate table t3| -insert into t3 values (0), (1)| -call fib(10)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -3 -5 -8 -13 -21 -34 -55 -drop table t3| -drop procedure fib| -set @@max_sp_recursion_depth= 0| drop procedure if exists bar| create procedure bar(x char(16), y int) comment "111111111111" sql security invoker diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index d15efc6d7d7..c6641e673ee 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -233,4 +233,19 @@ rl_acquirer old drop procedure p1; drop table t1; set session low_priority_updates=default; +INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, +Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, +Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, +Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, +Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, +Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, +max_updates, max_connections, max_user_connections) +VALUES('%', 'mysqltest_1', password(''), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', +'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '', +'', '', '', '0', '0', '0', '0'); +FLUSH PRIVILEGES; +CREATE PROCEDURE p1(i INT) BEGIN END; +DROP PROCEDURE p1; +DELETE FROM mysql.user WHERE User='mysqltest_1'; +FLUSH PRIVILEGES; set @@global.concurrent_insert= @old_concurrent_insert; diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 401340f204c..0b0d5a38d0b 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -506,3 +506,24 @@ mysqltest_32753@localhost set session sql_mode=@OLD_SQL_MODE; flush privileges; drop user mysqltest_32753@localhost; +DROP TABLE IF EXISTS t1,t2; +CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD'; +CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD'; +CREATE TABLE t1 (f1 BIGINT); +CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20)); +GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; +GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; +SET @OLD_SQL_MODE = @@SESSION.SQL_MODE; +SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'; +DROP USER 'user_PCTFL'@'localhost'; +SET SESSION SQL_MODE = @OLD_SQL_MODE; +DROP USER 'user_no_PCTFL'@'localhost'; +FLUSH PRIVILEGES; +SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv +SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host Db User Table_name Grantor Timestamp Table_priv Column_priv +SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host Db User Table_name Column_name Timestamp Column_priv +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index ca815540c29..ce3acba9b8a 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -1,13 +1,15 @@ set @old_concurrent_insert= @@global.concurrent_insert; set @@global.concurrent_insert= 0; +SET @old_log_output = @@global.log_output; +SET GLOBAL LOG_OUTPUT = 'FILE'; flush status; show status like 'Table_lock%'; Variable_name Value -Table_locks_immediate 1 +Table_locks_immediate 0 Table_locks_waited 0 select * from information_schema.session_status where variable_name like 'Table_lock%'; VARIABLE_NAME VARIABLE_VALUE -TABLE_LOCKS_IMMEDIATE 2 +TABLE_LOCKS_IMMEDIATE 0 TABLE_LOCKS_WAITED 0 # Switched to connection: con1 set sql_log_bin=0; @@ -154,7 +156,7 @@ Variable_name Value Com_show_status 3 show status like 'hand%write%'; Variable_name Value -Handler_write 5 +Handler_write 0 show status like '%tmp%'; Variable_name Value Created_tmp_disk_tables 0 @@ -162,7 +164,7 @@ Created_tmp_files 0 Created_tmp_tables 0 show status like 'hand%write%'; Variable_name Value -Handler_write 7 +Handler_write 0 show status like '%tmp%'; Variable_name Value Created_tmp_disk_tables 0 @@ -237,3 +239,4 @@ SELECT 9; DROP PROCEDURE p1; DROP FUNCTION f1; set @@global.concurrent_insert= @old_concurrent_insert; +SET GLOBAL log_output = @old_log_output; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index e0f361a0f4f..f055b40116a 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -849,6 +849,25 @@ ROW(1,2) = (SELECT 1, 1) ROW(1,2) IN (SELECT 1, 1) SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); ROW(1,2) = (SELECT 1, 2) ROW(1,2) IN (SELECT 1, 2) 1 1 +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,1,1); +EXPLAIN EXTENDED +SELECT c FROM +( SELECT +(SELECT COUNT(a) FROM +(SELECT COUNT(b) FROM t1) AS x GROUP BY c +) FROM t1 GROUP BY b +) AS y; +ERROR 42S22: Unknown column 'c' in 'field list' +SHOW WARNINGS; +Level Code Message +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 +Error 1054 Unknown column 'c' in 'field list' +Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM +(SELECT COUNT(b) FROM t1) AS x GROUP BY c +)` from `test`.`t1` group by `test`.`t1`.`b`) `y` +DROP TABLE t1; End of 5.0 tests create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 23f15f618f2..4476735735c 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2073,4 +2073,18 @@ select @a, @b; drop trigger trg1; drop trigger trg2; drop table t1, t2; +CREATE TABLE t1 ( a INT, b INT ); +CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT ); +INSERT INTO t1 (a) VALUES (1); +CREATE TRIGGER tr1 +BEFORE INSERT ON t2 +FOR EACH ROW +BEGIN +UPDATE a_nonextisting_table SET a = 1; +END// +CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1; +ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist +SELECT * FROM t2; +a b +DROP TABLE t1, t2; End of 5.1 tests. diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result index 1e13bff03b1..335e6910a3a 100644 --- a/mysql-test/r/trigger_notembedded.result +++ b/mysql-test/r/trigger_notembedded.result @@ -462,4 +462,18 @@ unlock tables; select * from t1; i drop table t1; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY; +CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW +INSERT INTO db1.t1 VALUES('Some very sensitive data goes here'); +CREATE USER 'no_rights'@'localhost'; +REVOKE ALL ON *.* FROM 'no_rights'@'localhost'; +FLUSH PRIVILEGES; +SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS +WHERE trigger_schema = 'db1'; +trigger_name +SHOW CREATE TRIGGER db1.trg; +ERROR 42000: Access denied; you need the TRIGGER privilege for this operation +DROP USER 'no_rights'@'localhost'; +DROP DATABASE db1; End of 5.1 tests. diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index f2b08d1c6b7..748aadee4fb 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1524,10 +1524,10 @@ Warnings: Warning 1264 Out of range value for column 'f1' at row 1 DESC t1; Field Type Null Key Default Extra -f1 decimal(59,30) NO 0.000000000000000000000000000000 +f1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT f1 FROM t1; f1 -99999999999999999999999999999.999999999999999999999999999999 +99999999999999999999999999999999999.999999999999999999999999999999 DROP TABLE t1; select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000); @@ -1577,3 +1577,56 @@ Error 1264 Out of range value for column 'cast(-13.4 as decimal(2,1))' at row 1 select cast(98.6 as decimal(2,0)); cast(98.6 as decimal(2,0)) 99 +# +# Bug #45262: Bad effects with CREATE TABLE and DECIMAL +# +CREATE TABLE t1 SELECT .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +Warnings: +Note 1265 Data truncated for column 'my_col' at row 1 +DESCRIBE t1; +Field Type Null Key Default Extra +my_col decimal(30,30) NO 0.000000000000000000000000000000 +SELECT my_col FROM t1; +my_col +0.123456789123456789123456789123 +DROP TABLE t1; +CREATE TABLE t1 SELECT 1 + .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +Warnings: +Note 1265 Data truncated for column 'my_col' at row 1 +DESCRIBE t1; +Field Type Null Key Default Extra +my_col decimal(65,30) NO 0.000000000000000000000000000000 +SELECT my_col FROM t1; +my_col +1.123456789123456789123456789123 +DROP TABLE t1; +CREATE TABLE t1 SELECT 1 * .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +Warnings: +Note 1265 Data truncated for column 'my_col' at row 1 +DESCRIBE t1; +Field Type Null Key Default Extra +my_col decimal(65,30) NO 0.000000000000000000000000000000 +SELECT my_col FROM t1; +my_col +0.123456789123456789123456789123 +DROP TABLE t1; +CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +Warnings: +Note 1265 Data truncated for column 'my_col' at row 1 +DESCRIBE t1; +Field Type Null Key Default Extra +my_col decimal(65,4) YES NULL +SELECT my_col FROM t1; +my_col +8.1000 +DROP TABLE t1; +CREATE TABLE t1 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +Warnings: +Note 1265 Data truncated for column 'my_col' at row 1 +DESCRIBE t1; +Field Type Null Key Default Extra +my_col decimal(65,30) YES NULL +SELECT my_col FROM t1; +my_col +0.012345687012345687012345687012 +DROP TABLE t1; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index d80a3973555..e4b90196c2d 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -128,3 +128,13 @@ SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2; sum(f3) 3 drop table t1; +# +# Bug #44792: valgrind warning when casting from time to time +# +CREATE TABLE t1 (c TIME); +INSERT INTO t1 VALUES ('0:00:00'); +SELECT CAST(c AS TIME) FROM t1; +CAST(c AS TIME) +00:00:00 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 23a7724984c..44a3812725a 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1574,4 +1574,17 @@ SHOW FIELDS FROM t2; Field Type Null Key Default Extra d double(9,6) YES NULL DROP TABLE t1, t2; +CREATE TABLE t1(a INT); +EXPLAIN EXTENDED +SELECT a FROM t1 +UNION +SELECT a FROM t1 +ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNION t1 system NULL NULL NULL NULL 0 0.00 const row not found +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a` +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 8961a935006..28da1dae931 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -399,6 +399,17 @@ select @lastid != id, @lastid, @lastid := id from t1; 0 3 3 1 3 4 drop table t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20); +SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; +SELECT @a, @b; +@a @b +2 3 +SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; +a b +2 3 +DROP TABLE t1; +End of 5.0 tests CREATE TABLE t1 (i INT); CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10; INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index f27d0b9fdd5..c1cd1840df8 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1436,7 +1436,7 @@ Warnings: Warning 1292 Truncated incorrect auto_increment_offset value: '0' select @@storage_engine; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def @@storage_engine 253 6 6 N 1 31 8 +def @@storage_engine 253 6 6 Y 0 31 8 @@storage_engine MyISAM SET @old_server_id = @@GLOBAL.server_id; @@ -1467,4 +1467,24 @@ SELECT @@GLOBAL.server_id; @@GLOBAL.server_id 0 SET GLOBAL server_id = @old_server_id; +SELECT @@GLOBAL.INIT_FILE, @@GLOBAL.INIT_FILE IS NULL; +@@GLOBAL.INIT_FILE @@GLOBAL.INIT_FILE IS NULL +NULL 1 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (); +SET @bug42778= @@sql_safe_updates; +SET @@sql_safe_updates= 0; +DELETE FROM t1 ORDER BY (@@GLOBAL.INIT_FILE) ASC LIMIT 10; +SET @@sql_safe_updates= @bug42778; +DROP TABLE t1; +# +# BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback +# +SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; +# Set the max_binlog_cache_size to size more than 4GB. +SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; +SELECT @@GLOBAL.max_binlog_cache_size; +@@GLOBAL.max_binlog_cache_size +5368709120 +SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; End of 5.1 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 0905fc0109b..2dc448a29d8 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3700,6 +3700,136 @@ ERROR 42000: Key 'c2' doesn't exist in table 'v1' DROP VIEW v1; DROP TABLE t1; # ----------------------------------------------------------------- +# -- Bug#40825: Error 1356 while selecting from a view +# -- with a "HAVING" clause though query works +# ----------------------------------------------------------------- + +CREATE TABLE t1 (c INT); + +CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having `view_column` latin1 latin1_swedish_ci +SELECT * FROM v1; +view_column + +DROP VIEW v1; +DROP TABLE t1; + +# -- End of test case for Bug#40825 + +# +# Bug #45806 crash when replacing into a view with a join! +# +CREATE TABLE t1(a INT UNIQUE); +CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a; +INSERT INTO t1 VALUES (1), (2); +REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c; +SELECT * FROM v1; +a +1 +2 +1 +2 +REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c; +SELECT * FROM v1; +a +1 +2 +3 +1 +2 +3 +1 +2 +3 +DELETE FROM t1 WHERE a=3; +INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c +ON DUPLICATE KEY UPDATE `v1`.`a`= 1; +SELECT * FROM v1; +a +1 +2 +1 +2 +CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a; +REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c; +SELECT * FROM v2; +a +1 +2 +1 +2 +1 +2 +1 +2 +REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c; +SELECT * FROM v2; +a +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c +ON DUPLICATE KEY UPDATE `v2`.`a`= 1; +SELECT * FROM v2; +a +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +1 +2 +3 +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t1; +# -- End of test case for Bug#45806 +# ----------------------------------------------------------------- # -- End of 5.0 tests. # ----------------------------------------------------------------- DROP DATABASE IF EXISTS `d-1`; @@ -3817,6 +3947,14 @@ call p(); call p(); drop view a; drop procedure p; +# +# Bug #44860: ALTER TABLE on view crashes server +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT a FROM t1; +ALTER TABLE v1; +DROP VIEW v1; +DROP TABLE t1; # ----------------------------------------------------------------- # -- End of 5.1 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index 592cf07522b..a597806d897 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -75,3 +75,17 @@ xa rollback 'a','c'; xa start 'a','c'; drop table t1; End of 5.0 tests +xa start 'a'; +xa end 'a'; +xa rollback 'a'; +xa start 'a'; +xa end 'a'; +xa rollback 'a'; +xa start 'a'; +xa end 'a'; +xa prepare 'a'; +xa commit 'a'; +xa start 'a'; +xa end 'a'; +xa prepare 'a'; +xa commit 'a'; |