diff options
30 files changed, 560 insertions, 52 deletions
diff --git a/CMakeLists.txt b/CMakeLists.txt index f5a1c0cfaba..890ee2676e8 100755 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -154,6 +154,11 @@ IF(EMBED_MANIFESTS) # Disable automatic manifest generation. STRING(REPLACE "/MANIFEST" "/MANIFEST:NO" CMAKE_EXE_LINKER_FLAGS ${CMAKE_EXE_LINKER_FLAGS}) + # Explicitly disable it since it is the default for newer versions of VS + STRING(REGEX MATCH "MANIFEST:NO" tmp_manifest ${CMAKE_EXE_LINKER_FLAGS}) + IF(NOT tmp_manifest) + SET(CMAKE_EXE_LINKER_FLAGS "${CMAKE_EXE_LINKER_FLAGS} /MANIFEST:NO") + ENDIF(tmp_manifest) # Set the processor architecture. IF(CMAKE_GENERATOR MATCHES "Visual Studio 8 2005 Win64") SET(PROCESSOR_ARCH "X64") diff --git a/configure.in b/configure.in index e788419eb4f..989aa978690 100644 --- a/configure.in +++ b/configure.in @@ -7,7 +7,7 @@ AC_INIT(sql/mysqld.cc) AC_CANONICAL_SYSTEM # The Docs Makefile.am parses this line! # remember to also change ndb version below and update version.c in ndb -AM_INIT_AUTOMAKE(mysql, 5.0.42) +AM_INIT_AUTOMAKE(mysql, 5.0.44) AM_CONFIG_HEADER(config.h) PROTOCOL_VERSION=10 @@ -23,7 +23,7 @@ NDB_SHARED_LIB_VERSION=$NDB_SHARED_LIB_MAJOR_VERSION:0:0 # ndb version NDB_VERSION_MAJOR=5 NDB_VERSION_MINOR=0 -NDB_VERSION_BUILD=42 +NDB_VERSION_BUILD=44 NDB_VERSION_STATUS="" # Set all version vars based on $VERSION. How do we do this more elegant ? diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 541a15561e2..f18d1c9b583 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -352,3 +352,13 @@ select c1 mod 50 as result from t1; result 6 drop table t1; +select cast(19999999999999999999 as signed); +cast(19999999999999999999 as signed) +9223372036854775807 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' +select cast(-19999999999999999999 as signed); +cast(-19999999999999999999 as signed) +-9223372036854775808 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 71419b5b2c3..20df776ec1b 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -737,4 +737,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(b)) ONE.1,TWO.2,ONE.3 DROP TABLE t1; +CREATE TABLE t1( a VARCHAR( 10 ), b INT ); +INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), +( repeat( 'b', 10 ), 2); +SET group_concat_max_len = 20; +SELECT GROUP_CONCAT( a ) FROM t1; +GROUP_CONCAT( a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +GROUP_CONCAT( DISTINCT a ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +GROUP_CONCAT( a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +GROUP_CONCAT( DISTINCT a ORDER BY b ) +aaaaaaaaaa,bbbbbbbbb +Warnings: +Warning 1260 1 line(s) were cut by GROUP_CONCAT() +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index f245d272ede..e5720cc1ee0 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1330,4 +1330,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; a average 1 32768.5000 DROP TABLE t1; +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2); +EXPLAIN SELECT MIN(a), MIN(b) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +SELECT MIN(a), MIN(b) FROM t1; +MIN(a) MIN(b) +NULL 1 +CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); +INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); +EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref a a 5 const 2 Using where +SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +MIN(b) MIN(c) +3 2 +CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2; +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(a), MIN(b) FROM t3 where a = 2; +MIN(a) MIN(b) +2 NULL +CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2; +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(a), MIN(b) FROM t4 where a = 2; +MIN(a) MIN(b) +2 NULL +SELECT MIN(b), min(c) FROM t4 where a = 2; +MIN(b) min(c) +NULL 2 +CREATE TABLE t5( a INT, b INT, KEY( a, b) ); +INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 ); +EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +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(a), MIN(b) FROM t5 WHERE a = 1; +MIN(a) MIN(b) +1 1 +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; +MIN(a) MIN(b) +1 2 +DROP TABLE t1, t2, t3, t4, t5; End of 5.0 tests diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 48e01d8dd6f..45cb116f08b 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -144,6 +144,27 @@ 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; End of 4.1 tests create table t1m (a int) engine=myisam; create table t1i (a int) engine=innodb; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 96abff8d7f6..8a10a52ee65 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1114,6 +1114,28 @@ execute stmt; show create table t1; drop table t1; deallocate prepare stmt; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); +PREPARE st1 FROM +'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; +EXECUTE st1; +a +1 +2 +3 +11 +12 +13 +EXECUTE st1; +a +1 +2 +3 +11 +12 +13 +DEALLOCATE PREPARE st1; +DROP TABLE t1; End of 4.1 tests. create table t1 (a varchar(20)); insert into t1 values ('foo'); diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index b112c6bece6..f3eb40b3d70 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -1161,3 +1161,44 @@ CALL p1(); v_text abc|def DROP PROCEDURE p1; +DROP PROCEDURE IF EXISTS bug27415_text_test| +DROP PROCEDURE IF EXISTS bug27415_text_test2| +CREATE PROCEDURE bug27415_text_test(entity_id_str_in text) +BEGIN +DECLARE str_remainder text; +SET str_remainder = entity_id_str_in; +select 'before substr', str_remainder; +SET str_remainder = SUBSTRING(str_remainder, 3); +select 'after substr', str_remainder; +END| +CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text) +BEGIN +DECLARE str_remainder text; +DECLARE str_remainder2 text; +SET str_remainder2 = entity_id_str_in; +select 'before substr', str_remainder2; +SET str_remainder = SUBSTRING(str_remainder2, 3); +select 'after substr', str_remainder; +END| +CALL bug27415_text_test('a,b,c')| +before substr str_remainder +before substr a,b,c +after substr str_remainder +after substr b,c +CALL bug27415_text_test('a,b,c')| +before substr str_remainder +before substr a,b,c +after substr str_remainder +after substr b,c +CALL bug27415_text_test2('a,b,c')| +before substr str_remainder2 +before substr a,b,c +after substr str_remainder +after substr b,c +CALL bug27415_text_test('a,b,c')| +before substr str_remainder +before substr a,b,c +after substr str_remainder +after substr b,c +DROP PROCEDURE bug27415_text_test| +DROP PROCEDURE bug27415_text_test2| diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result index f09645703ba..c74909e7e8f 100644 --- a/mysql-test/r/sp_trans.result +++ b/mysql-test/r/sp_trans.result @@ -530,8 +530,6 @@ count(*) drop table t3, t4| drop procedure bug14210| set @@session.max_heap_table_size=default| -drop function if exists bug23333| -drop table if exists t1,t2| CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| insert into t2 values (1,1)| @@ -551,3 +549,4 @@ Log_name Pos Event_type Server_id End_log_pos Info select count(*),@a from t1 /* must be 1,1 */| count(*) @a 1 1 +drop table t1, t2| diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index e2e3dd67af5..a3ebd5c86fd 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -3,7 +3,7 @@ set @@sql_mode='ansi,traditional'; select @@sql_mode; @@sql_mode REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER -DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (col1 date); INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29'); INSERT INTO t1 VALUES('0000-10-31'); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 2e82d948edb..92a4a6f3f5a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4041,4 +4041,34 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; +CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); +INSERT INTO t1 VALUES +(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); +CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); +INSERT INTO t2 VALUES (7), (5), (1), (3); +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +id st +3 FL +1 GA +7 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) +GROUP BY id; +id st +1 GA +3 FL +7 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +id st +2 GA +4 FL +SELECT id, st FROM t1 +WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) +GROUP BY id; +id st +2 GA +4 FL +DROP TABLE t1,t2; End of 5.0 tests. diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index d2a611d2413..ba02f19712a 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -381,3 +381,33 @@ call test27759(); a b a_then_b b_then_a c_then_a 2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00 drop procedure test27759; +create table t1 (f1 date); +insert into t1 values (curdate()); +select left(f1,10) = curdate() from t1; +left(f1,10) = curdate() +1 +drop table t1; +create table t1(f1 date); +insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); +set @bug28261=''; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +Warnings: +Warning 1292 Incorrect date value: '' for column 'f1' at row 1 +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +if(@bug28261 = f1, '', @bug28261:= f1) +2001-01-01 +2002-02-02 +2001-01-01 +2002-02-02 +drop table t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 6c1229db83f..9a5fb11229d 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -288,3 +288,9 @@ insert into t1 values (10000002383263201056); select c1 mod 50 as result from t1; drop table t1; +# +# Bug #8663 cant use bgint unsigned as input to cast +# + +select cast(19999999999999999999 as signed); +select cast(-19999999999999999999 as signed); diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 0dd82864520..7771f216f69 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; DROP TABLE t1; +# +# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated. +# +CREATE TABLE t1( a VARCHAR( 10 ), b INT ); +INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), + ( repeat( 'b', 10 ), 2); +SET group_concat_max_len = 20; +SELECT GROUP_CONCAT( a ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; +SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; +SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; +SET group_concat_max_len = DEFAULT; +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 654bb8bb75d..2293ac71454 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -827,4 +827,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; DROP TABLE t1; +# +# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ +# results to NULL +# +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2); +EXPLAIN SELECT MIN(a), MIN(b) FROM t1; +SELECT MIN(a), MIN(b) FROM t1; + +CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); +INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); +EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; + +CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2; +SELECT MIN(a), MIN(b) FROM t3 where a = 2; + +CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(b), min(c) FROM t4 where a = 2; + +CREATE TABLE t5( a INT, b INT, KEY( a, b) ); +INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 ); +EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; + +DROP TABLE t1, t2, t3, t4, t5; + +### --echo End of 5.0 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 90f9047291c..d9e50add8bf 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -169,6 +169,31 @@ INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; DROP TABLE t1; +# +# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table +# + +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'; + +EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE +t.a6=t.a6 AND t1.a6=NULL AND +t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; + +DROP TABLE t1; + --echo End of 4.1 tests # # Bug #12882 min/max inconsistent on empty table diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 6c93ae25045..5b7adc40755 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1194,6 +1194,24 @@ show create table t1; --enable_warnings drop table t1; deallocate prepare stmt; +# + +# +# Bug #27937: crash on the second execution for prepared statement +# from UNION with ORDER BY an expression containing RAND() +# + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); + +PREPARE st1 FROM + '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; + +EXECUTE st1; +EXECUTE st1; + +DEALLOCATE PREPARE st1; +DROP TABLE t1; --echo End of 4.1 tests. diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index 0014dc1f6af..2cc68b054d5 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -1367,4 +1367,48 @@ CALL p1(); DROP PROCEDURE p1; +# +# Bug #27415 Text Variables in stored procedures +# If the SP varible was also referenced on the right side +# the result was corrupted. +# +DELIMITER |; + +--disable_warnings +DROP PROCEDURE IF EXISTS bug27415_text_test| +DROP PROCEDURE IF EXISTS bug27415_text_test2| +--enable_warnings + +CREATE PROCEDURE bug27415_text_test(entity_id_str_in text) +BEGIN + DECLARE str_remainder text; + + SET str_remainder = entity_id_str_in; + + select 'before substr', str_remainder; + SET str_remainder = SUBSTRING(str_remainder, 3); + select 'after substr', str_remainder; +END| + +CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text) +BEGIN + DECLARE str_remainder text; + DECLARE str_remainder2 text; + + SET str_remainder2 = entity_id_str_in; + select 'before substr', str_remainder2; + SET str_remainder = SUBSTRING(str_remainder2, 3); + select 'after substr', str_remainder; +END| + +CALL bug27415_text_test('a,b,c')| +CALL bug27415_text_test('a,b,c')| +CALL bug27415_text_test2('a,b,c')| +CALL bug27415_text_test('a,b,c')| + +DROP PROCEDURE bug27415_text_test| +DROP PROCEDURE bug27415_text_test2| + +DELIMITER ;| + # End of 5.0 tests. diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index d9b34c303ae..8eccaafcf0e 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -559,12 +559,8 @@ set @@session.max_heap_table_size=default| # breaks stmt-based binlog # Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() # ---disable_warnings -drop function if exists bug23333| -drop table if exists t1,t2| ---enable_warnings - CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| - CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| +CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| insert into t2 values (1,1)| @@ -583,6 +579,7 @@ insert into t2 values (bug23333(),1)| --replace_column 2 # 5 # 6 # show binlog events from 98 /* with fixes for #23333 will show there is the query */| select count(*),@a from t1 /* must be 1,1 */| +drop table t1, t2| # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index faca9e7b080..fc663853174 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -7,7 +7,7 @@ set @@sql_mode='ansi,traditional'; select @@sql_mode; --disable_warnings -DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1, t2; --enable_warnings # Test INSERT with DATE diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 64de2ada93c..5840e434b64 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2882,4 +2882,28 @@ INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); DROP TABLE t1; +# +# Bug #28377: grouping query with a correlated subquery in WHERE condition +# + +CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); +INSERT INTO t1 VALUES + (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); +CREATE TABLE t2 (id int NOT NULL, INDEX idx(id)); +INSERT INTO t2 VALUES (7), (5), (1), (3); + +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) + GROUP BY id; + +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); +SELECT id, st FROM t1 + WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) + GROUP BY id; + +DROP TABLE t1,t2; + --echo End of 5.0 tests. diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 60a5d976d30..d420afbde37 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -250,3 +250,24 @@ END;| DELIMITER ;| call test27759(); drop procedure test27759; + +# +# Bug#28208: Wrong result of a non-const STRING function with a const +# DATETIME function. +# +create table t1 (f1 date); +insert into t1 values (curdate()); +select left(f1,10) = curdate() from t1; +drop table t1; + +# +# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function +# is involved. +# +create table t1(f1 date); +insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); +set @bug28261=''; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +drop table t1; diff --git a/scripts/make_win_bin_dist b/scripts/make_win_bin_dist index 87d35a0850b..30127b0043f 100755 --- a/scripts/make_win_bin_dist +++ b/scripts/make_win_bin_dist @@ -39,7 +39,7 @@ The "package-base-name" argument should be something like mysql-noinstall-5.0.25-win32 (or winx64) -and will be the name of the directory of the unpacked ZIP (stripping +and will become the name of the directory of the unpacked ZIP (stripping away the "noinstall" part of the ZIP file name if any) and the base for the resulting package name. @@ -51,6 +51,7 @@ Options are --no-embedded Don't pack the embedded server even if built --debug Pack the debug binaries and give error if not built. + The default is to pack them if they are built. --no-debug Don't pack the debug binaries even if built @@ -58,10 +59,10 @@ Options are want to replace the normal binaries with debug versions, i.e. no separate "debug" directories. - --exe-suffix=SUF Add a suffix to the "mysqld" binary. + --exe-suffix=SUF Add a suffix to the filename part of the "mysqld" binary. As you might want to include files of directories from other builds -(like a "mysqld-max.exe" server), you can instruct this script do copy +(like a "mysqld-max.exe" server), you can instruct this script to copy them in for you. This is the "copy-def" arguments, and they are of the form @@ -172,10 +173,10 @@ else BASENAME="mysqld" # New style CMake build fi -if [ x"$PACK_DEBUG" = "" -a -f "sql/debug/$BASENAME.exe" -o \ - x"$PACK_DEBUG" = "yes" ] ; then +if [ x"$PACK_DEBUG" = x"" -a -f "sql/debug/$BASENAME.exe" -o \ + x"$PACK_DEBUG" = x"yes" ] ; then cp sql/debug/$BASENAME.exe $DESTDIR/bin/mysqld-debug.exe - cp sql/debug/$BASENAME.pdb $DESTDIR/bin/mysqld-debug.pdb + cp sql/debug/$BASENAME.pdb $DESTDIR/bin/mysqld-debug.pdb || true cp sql/debug/$BASENAME.map $DESTDIR/bin/mysqld-debug.map || true fi @@ -221,8 +222,8 @@ copy_embedded() cp libmysqld/$TARGET/libmysqld.exp $DESTDIR/Embedded/DLL/release/ cp libmysqld/$TARGET/libmysqld.lib $DESTDIR/Embedded/DLL/release/ - if [ x"$PACK_DEBUG" = "" -a -f "libmysqld/debug/libmysqld.lib" -o \ - x"$PACK_DEBUG" = "yes" ] ; then + if [ x"$PACK_DEBUG" = x"" -a -f "libmysqld/debug/libmysqld.lib" -o \ + x"$PACK_DEBUG" = x"yes" ] ; then mkdir -p $DESTDIR/Embedded/DLL/debug cp libmysqld/debug/libmysqld.dll $DESTDIR/Embedded/DLL/debug/ cp libmysqld/debug/libmysqld.exp $DESTDIR/Embedded/DLL/debug/ @@ -230,10 +231,10 @@ copy_embedded() fi } -if [ x"$PACK_EMBEDDED" = "" -a \ +if [ x"$PACK_EMBEDDED" = x"" -a \ -f "libmysqld/$TARGET/mysqlserver.lib" -a \ -f "libmysqld/$TARGET/libmysqld.lib" -o \ - x"$PACK_EMBEDDED" = "yes" ] ; then + x"$PACK_EMBEDDED" = x"yes" ] ; then copy_embedded fi @@ -271,8 +272,8 @@ cp libmysql/$TARGET/libmysql.dll \ strings/$TARGET/strings.lib \ zlib/$TARGET/zlib.lib $DESTDIR/lib/opt/ -if [ x"$PACK_DEBUG" = "" -a -f "libmysql/debug/libmysql.lib" -o \ - x"$PACK_DEBUG" = "yes" ] ; then +if [ x"$PACK_DEBUG" = x"" -a -f "libmysql/debug/libmysql.lib" -o \ + x"$PACK_DEBUG" = x"yes" ] ; then mkdir -p $DESTDIR/lib/debug cp libmysql/debug/libmysql.dll \ libmysql/debug/libmysql.lib \ @@ -329,12 +330,11 @@ fi # ---------------------------------------------------------------------- # Copy what could be usable in the "scripts" directory. Currently -# only SQL files, others are bourne shell scripts or Perl scripts +# only SQL files, others are Bourne shell scripts or Perl scripts # not really usable on Windows. # # But to be nice to the few Cygwin users we might have in 5.0 we -# continue to copy the stuff, but don't include it include it in -# the WiX install. +# continue to copy the stuff, but don't include it in the WiX install. # ---------------------------------------------------------------------- mkdir -p $DESTDIR/scripts @@ -360,7 +360,7 @@ fi cp -pR sql-bench $DESTDIR/ rm -f $DESTDIR/sql-bench/*.sh $DESTDIR/sql-bench/Makefile* -# The SQL initiation code is really expected to be in "share" +# The SQL initialisation code is really expected to be in "share" mv $DESTDIR/scripts/*.sql $DESTDIR/share/ || true # ---------------------------------------------------------------------- @@ -381,7 +381,7 @@ for arg do done # ---------------------------------------------------------------------- -# Finally creat the ZIP archive +# Finally create the ZIP archive # ---------------------------------------------------------------------- rm -f $NOINST_NAME.zip diff --git a/sql/item.cc b/sql/item.cc index 4f7d1cb56c0..92ea35072f9 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3522,7 +3522,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) prev_subselect_item->const_item_cache= 0; set_field(*from_field); if (!last_checked_context->select_lex->having_fix_field && - select->group_list.elements) + select->group_list.elements && + (place == SELECT_LIST || place == IN_HAVING)) { Item_outer_ref *rf; /* diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 92a6f5c06da..0de9ef3e9ad 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -637,7 +637,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value) if (cmp_type != CMP_DATE_DFLT) { - if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item()) + /* + Do not cache GET_USER_VAR() function as its const_item() may return TRUE + for the current thread but it still may change during the execution. + */ + if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() && + (str_arg->type() != Item::FUNC_ITEM || + ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC)) { THD *thd= current_thd; ulonglong value; @@ -665,7 +671,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg, Item_result type) { enum enum_date_cmp_type cmp_type; - ulonglong const_value; + ulonglong const_value= (ulonglong)-1; a= a1; b= a2; @@ -678,8 +684,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg, a_cache= 0; b_cache= 0; - if (cmp_type != CMP_DATE_WITH_DATE && - ((*b)->const_item() || (*a)->const_item())) + if (const_value != (ulonglong)-1) { Item_cache_int *cache= new Item_cache_int(); /* Mark the cache as non-const to prevent re-caching. */ @@ -785,7 +790,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME; value= get_date_from_str(thd, str, t_type, warn_item->name, &error); } - if (item->const_item() && cache_arg) + /* + Do not cache GET_USER_VAR() function as its const_item() may return TRUE + for the current thread but it still may change during the execution. + */ + if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM || + ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC)) { Item_cache_int *cache= new Item_cache_int(); /* Mark the cache as non-const to prevent re-caching. */ diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 4579ecd48ae..d6b31d43389 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3339,6 +3339,10 @@ String* Item_func_group_concat::val_str(String* str) DBUG_ASSERT(fixed == 1); if (null_value) return 0; + if (!result.length() && tree) + /* Tree is used for sorting as in ORDER BY */ + tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this, + left_root_right); if (count_cut_values && !warning) { /* @@ -3350,11 +3354,6 @@ String* Item_func_group_concat::val_str(String* str) ER_CUT_VALUE_GROUP_CONCAT, ER(ER_CUT_VALUE_GROUP_CONCAT)); } - if (result.length()) - return &result; - if (tree) - tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this, - left_root_right); return &result; } diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 9222e15ff91..b9de54dbf5c 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -206,12 +206,68 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) if (!ref.key_length) error= table->file->index_first(table->record[0]); - else - error= table->file->index_read(table->record[0],key_buff, - ref.key_length, - range_fl & NEAR_MIN ? - HA_READ_AFTER_KEY : - HA_READ_KEY_OR_NEXT); + else + { + /* + Use index to replace MIN/MAX functions with their values + according to the following rules: + + 1) Insert the minimum non-null values where the WHERE clause still + matches, or + 2) a NULL value if there are only NULL values for key_part_k. + 3) Fail, producing a row of nulls + + Implementation: Read the smallest value using the search key. If + the interval is open, read the next value after the search + key. If read fails, and we're looking for a MIN() value for a + nullable column, test if there is an exact match for the key. + */ + if (!(range_fl & NEAR_MIN)) + /* + Closed interval: Either The MIN argument is non-nullable, or + we have a >= predicate for the MIN argument. + */ + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, + HA_READ_KEY_OR_NEXT); + else + { + /* + Open interval: There are two cases: + 1) We have only MIN() and the argument column is nullable, or + 2) there is a > predicate on it, nullability is irrelevant. + We need to scan the next bigger record first. + */ + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, HA_READ_AFTER_KEY); + /* + If the found record is outside the group formed by the search + prefix, or there is no such record at all, check if all + records in that group have NULL in the MIN argument + column. If that is the case return that NULL. + + Check if case 1 from above holds. If it does, we should read + the skipped tuple. + */ + if (ref.key_buff[prefix_len] == 1 && + /* + Last keypart (i.e. the argument to MIN) is set to NULL by + find_key_for_maxmin only if all other keyparts are bound + to constants in a conjunction of equalities. Hence, we + can detect this by checking only if the last keypart is + NULL. + */ + (error == HA_ERR_KEY_NOT_FOUND || + key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len))) + { + DBUG_ASSERT(item_field->field->real_maybe_null()); + error= table->file->index_read(table->record[0], ref.key_buff, + ref.key_length, + HA_READ_KEY_EXACT); + } + } + } + /* Verify that the read tuple indeed matches the search key */ if (!error && reckey_in_range(0, &ref, item_field->field, conds, range_fl, prefix_len)) error= HA_ERR_KEY_NOT_FOUND; @@ -739,14 +795,24 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, if (!max_fl && key_part_used == key_part_to_use && part->null_bit) { /* - SELECT MIN(key_part2) FROM t1 WHERE key_part1=const - If key_part2 may be NULL, then we want to find the first row - that is not null + The query is on this form: + + SELECT MIN(key_part_k) + FROM t1 + WHERE key_part_1 = const and ... and key_part_k-1 = const + + If key_part_k is nullable, we want to find the first matching row + where key_part_k is not null. The key buffer is now {const, ..., + NULL}. This will be passed to the handler along with a flag + indicating open interval. If a tuple is read that does not match + these search criteria, an attempt will be made to read an exact + match for the key buffer. */ + /* Set the first byte of key_part_k to 1, that means NULL */ ref->key_buff[ref->key_length]= 1; ref->key_length+= part->store_length; *range_fl&= ~NO_MIN_RANGE; - *range_fl|= NEAR_MIN; // > NULL + *range_fl|= NEAR_MIN; // Open interval } /* The following test is false when the key in the key tree is diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 967322600a7..ea59cbbe9f2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -976,6 +976,12 @@ JOIN::optimize() } } + if (conds &&!outer_join && const_table_map != found_const_table_map && + (select_options & SELECT_DESCRIBE) && + select_lex->master_unit() == &thd->lex->unit) // upper level SELECT + { + conds=new Item_int((longlong) 0,1); // Always false + } if (make_join_select(this, select, conds)) { zero_result_cause= diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7fb75732012..373b03d45e6 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -147,8 +147,16 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg) fake_select_lex->table_list.link_in_list((byte *)&result_table_list, (byte **) &result_table_list.next_local); - fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table= + fake_select_lex->context.table_list= + fake_select_lex->context.first_name_resolution_table= fake_select_lex->get_table_list(); + if (!fake_select_lex->first_execution) + { + for (ORDER *order= (ORDER *) global_parameters->order_list.first; + order; + order= order->next) + order->item= &order->item_ptr; + } for (ORDER *order= (ORDER *)global_parameters->order_list.first; order; order=order->next) diff --git a/strings/decimal.c b/strings/decimal.c index 65db68b1b59..1ae75167794 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -1083,7 +1083,11 @@ int decimal2longlong(decimal_t *from, longlong *to) x=x*DIG_BASE - *buf++; if (unlikely(y < (LONGLONG_MIN/DIG_BASE) || x > y)) { - *to= from->sign ? y : -y; + /* + the decimal is bigger than any possible integer + return border integer depending on the sign + */ + *to= from->sign ? LONGLONG_MIN : LONGLONG_MAX; return E_DEC_OVERFLOW; } } |