From d689cf23be4f14e0735126b2535254c3d5cc3a62 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 10 Dec 2007 09:17:18 +0100 Subject: Bug#32770: LAST_DAY() returns a DATE, but somehow internally keeps track of the TIME. LAST_DAY() says it returns a DATE, not a DATETIME, but didn't zero the time fields. Adapted from a patch kindly supplied by Claudio Cherubino. mysql-test/r/func_time.result: show that LAST_DAY() returns only a DATE, not a DATETIME mysql-test/t/func_time.test: show that LAST_DAY() returns only a DATE, not a DATETIME sql/item_timefunc.cc: zero time-fields as we return only a DATE --- mysql-test/r/func_time.result | 3 +++ mysql-test/t/func_time.test | 7 +++++++ 2 files changed, 10 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 71234385c0d..f25f9ed9e0a 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1282,4 +1282,7 @@ DATE_ADD('20071108', INTERVAL 1 DAY) select DATE_ADD(20071108, INTERVAL 1 DAY); DATE_ADD(20071108, INTERVAL 1 DAY) 2007-11-09 +select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; +LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND +2007-12-30 23:59:59 End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index f8249b7cf7c..b0f47e0ad56 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -797,4 +797,11 @@ select DATE_ADD(20071108181000, INTERVAL 1 DAY); select DATE_ADD('20071108', INTERVAL 1 DAY); select DATE_ADD(20071108, INTERVAL 1 DAY); +# +# Bug#32770: LAST_DAY() returns a DATE, but somehow internally keeps +# track of the TIME. +# + +select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; + --echo End of 5.0 tests -- cgit v1.2.1 From 08b256f9df73fab94cc662d83b14131c59056385 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 11 Dec 2007 10:12:05 +0100 Subject: Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast. This happened because DATE-cast object did not overload get_time() method in superclass Item. The default method was inappropriate here and misinterpreted the data. Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL on NULL input, 0 otherwise. This coincides with the way DATE-columns behave. Also fixes similar bug in Date-Field now. mysql-test/r/cast.result: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise. Show that this coincides with how DATE-columns behave. mysql-test/r/type_date.result: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-fields. mysql-test/t/cast.test: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise. Show that this coincides with how DATE-columns behave. mysql-test/t/type_date.test: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-fields. sql/field.cc: Add get_time() method to DATE-field object to overload the method in Field superclass that would return spurious results. Return zero-result. sql/field.h: Add get_time() declaration to date-field class sql/item_timefunc.cc: Add get_time() method to DATE-cast object to overload the method in Item superclass that would return spurious results. Return zero-result; flag NULL if input was NULL. sql/item_timefunc.h: Add get_time() declaration to DATE-cast object. --- mysql-test/r/cast.result | 24 ++++++++++++++++++++++++ mysql-test/r/type_date.result | 5 +++++ mysql-test/t/cast.test | 22 ++++++++++++++++++++++ mysql-test/t/type_date.test | 8 ++++++++ 4 files changed, 59 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 524ff48d69e..88601eceb0a 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -414,4 +414,28 @@ NULL NULL 20070719 drop table t1; +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), +MINUTE(f1), +SECOND(f1) FROM t1; +HOUR(f1) MINUTE(f1) SECOND(f1) +0 0 0 +NULL NULL NULL +SELECT HOUR(CAST('2007-07-19' AS DATE)), +MINUTE(CAST('2007-07-19' AS DATE)), +SECOND(CAST('2007-07-19' AS DATE)); +HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE)) +0 0 0 +SELECT HOUR(CAST(NULL AS DATE)), +MINUTE(CAST(NULL AS DATE)), +SECOND(CAST(NULL AS DATE)); +HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE)) +NULL NULL NULL +SELECT HOUR(NULL), +MINUTE(NULL), +SECOND(NULL); +HOUR(NULL) MINUTE(NULL) SECOND(NULL) +NULL NULL NULL +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index bd2a43569dd..f16b873f37b 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -214,4 +214,9 @@ INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1 SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +CREATE TABLE t1 SELECT curdate() AS f1; +SELECT hour(f1), minute(f1), second(f1) FROM t1; +hour(f1) minute(f1) second(f1) +0 0 0 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 316b79efe4d..df475b49746 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), SELECT cast(date(d1) as signed) FROM t1; drop table t1; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns +# and typecasts (NULL in, NULL out). +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), + MINUTE(f1), + SECOND(f1) FROM t1; +SELECT HOUR(CAST('2007-07-19' AS DATE)), + MINUTE(CAST('2007-07-19' AS DATE)), + SECOND(CAST('2007-07-19' AS DATE)); +SELECT HOUR(CAST(NULL AS DATE)), + MINUTE(CAST(NULL AS DATE)), + SECOND(CAST(NULL AS DATE)); +SELECT HOUR(NULL), + MINUTE(NULL), + SECOND(NULL); +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 507537457d3..3f2ee4234bd 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -190,4 +190,12 @@ INSERT INTO t1 VALUES ('0000-00-00'); SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +CREATE TABLE t1 SELECT curdate() AS f1; +SELECT hour(f1), minute(f1), second(f1) FROM t1; +DROP TABLE t1; + --echo End of 5.0 tests -- cgit v1.2.1 From 94f75ffcce4daddfb709bd269fc90d1513833966 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 11 Dec 2007 20:15:03 +0100 Subject: Bug#32848: Data type conversion bug in union subselects in MySQL 5.0.38 There were two problems when inferring the correct field types resulting from UNION queries. - If the type is NULL for all corresponding fields in the UNION, the resulting type would be NULL, while the type is BINARY(0) if there is just a single SELECT NULL. - If one SELECT in the UNION uses a subselect, a temporary table is created to represent the subselect, and the result type defaults to a STRING type, hiding the fact that the type was unknown(just a NULL value). Fixed by remembering whenever a field was created from a NULL value and pass type NULL to the type coercion if that is the case, and creating a string field as result of UNION only if the type would otherwise be NULL. mysql-test/r/union.result: Bug#32848: Test result mysql-test/t/union.test: Bug#32848: Test case sql/field.cc: Bug#32848: Initialization of new field sql/field.h: Bug#32848: New member to record when a field was created from a NULL value. sql/item.cc: Bug#32848: A field created from a NULL value will submit NULL as type to the type coercion procedure. If Item_type_holder has not inferred the correct type after processing all SELECTs in a UNION, a string field is created. sql/sql_select.cc: Bug#32848: Recording when a field is created from a NULL value. --- mysql-test/r/union.result | 24 ++++++++++++++++++++++++ mysql-test/t/union.test | 24 ++++++++++++++++++++++++ 2 files changed, 48 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index efdd8195fb5..0b77459e02f 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1389,4 +1389,28 @@ select @var; 1 (select 2) union (select 1 into @var); ERROR 42000: Result consisted of more than one row +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1; +DESC t2; +Field Type Null Key Default Extra +NULL int(11) YES NULL +CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a; +DESC t3; +Field Type Null Key Default Extra +a int(11) YES NULL +CREATE TABLE t4 SELECT NULL; +DESC t4; +Field Type Null Key Default Extra +NULL binary(0) YES NULL +CREATE TABLE t5 SELECT NULL UNION SELECT NULL; +DESC t5; +Field Type Null Key Default Extra +NULL binary(0) YES NULL +CREATE TABLE t6 +SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1; +DESC t6; +Field Type Null Key Default Extra +NULL int(11) YES NULL +DROP TABLE t1, t2, t3, t4, t5, t6; End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 22f09466b1c..84616aa281d 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -877,4 +877,28 @@ DROP TABLE t1; select @var; --error 1172 (select 2) union (select 1 into @var); + +# +# Bug#32848: Data type conversion bug in union subselects in MySQL 5.0.38 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); + +CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1; +DESC t2; + +CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a; +DESC t3; + +CREATE TABLE t4 SELECT NULL; +DESC t4; + +CREATE TABLE t5 SELECT NULL UNION SELECT NULL; +DESC t5; + +CREATE TABLE t6 +SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1; +DESC t6; + +DROP TABLE t1, t2, t3, t4, t5, t6; --echo End of 5.0 tests -- cgit v1.2.1 From 93e3057ba574672c2f0bb1b0958221c0d7efc553 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Dec 2007 11:19:05 +0100 Subject: Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take subselects into account It is forbidden to use the SELECT INTO construction inside UNION statements unless on the last SELECT of the union. The parser records whether it has seen INTO or not when parsing a UNION statement. But if the INTO was legally used in an outer query, an error is thrown if UNION is seen in a subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and mitigate the error unless it collides with the UNION. mysql-test/r/union.result: Bug#32858: Test result mysql-test/t/union.test: Bug#32858: Test case sql/sql_class.cc: Bug#32858: Initializing new member sql/sql_class.h: Bug#32858: Added property nest_level to select_result class. sql/sql_yacc.yy: Bug#32858: The fix. --- mysql-test/r/union.result | 42 +++++++++++++++++++++++++++++++++ mysql-test/t/union.test | 59 +++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 101 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index efdd8195fb5..dadc6bb3558 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1389,4 +1389,46 @@ select @var; 1 (select 2) union (select 1 into @var); ERROR 42000: Result consisted of more than one row +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +SELECT a INTO @v FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 +) alias; +SELECT a INTO OUTFILE 'union.out.file' FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 WHERE 0 +) alias; +SELECT a INTO DUMPFILE 'union.out.file2' FROM ( +SELECT a FROM t1 +UNION +SELECT a FROM t1 WHERE 0 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO @v FROM t1 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO OUTFILE 'union.out.file3' FROM t1 +) alias; +SELECT a FROM ( +SELECT a FROM t1 +UNION +SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 +) alias; +SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; +ERROR HY000: Incorrect usage of UNION and INTO +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 22f09466b1c..54e00455d9a 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -877,4 +877,63 @@ DROP TABLE t1; select @var; --error 1172 (select 2) union (select 1 into @var); + +# +# Bug#32858: Erro: "Incorrect usage of UNION and INTO" does not take subselects +# into account +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); + +SELECT a INTO @v FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 +) alias; + +SELECT a INTO OUTFILE 'union.out.file' FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 WHERE 0 +) alias; + +SELECT a INTO DUMPFILE 'union.out.file2' FROM ( + SELECT a FROM t1 + UNION + SELECT a FROM t1 WHERE 0 +) alias; + +# +# INTO will not be allowed in subqueries in version 5.1 and above. +# +SELECT a FROM ( + SELECT a FROM t1 + UNION + SELECT a INTO @v FROM t1 +) alias; + +SELECT a FROM ( + SELECT a FROM t1 + UNION + SELECT a INTO OUTFILE 'union.out.file3' FROM t1 +) alias; + +SELECT a FROM ( + SELECT a FROM t1 + UNION + SELECT a INTO DUMPFILE 'union.out.file4' FROM t1 +) alias; + +SELECT a FROM t1 UNION SELECT a INTO @v FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file5' FROM t1; +SELECT a FROM t1 UNION SELECT a INTO OUTFILE 'union.out.file6' FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO @v FROM t1 UNION SELECT a FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO OUTFILE 'union.out.file7' FROM t1 UNION SELECT a FROM t1; +--error ER_WRONG_USAGE +SELECT a INTO DUMPFILE 'union.out.file8' FROM t1 UNION SELECT a FROM t1; + +DROP TABLE t1; + --echo End of 5.0 tests -- cgit v1.2.1 From 0c4b3f5784fa1af52bd978c1280180c2d659367f Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Dec 2007 12:24:20 +0100 Subject: Bug#32798: DISTINCT in GROUP_CONCAT clause fails when ordering by a column with null values For queries containing GROUP_CONCAT(DISTINCT fields ORDER BY fields), there was a limitation that the DISTINCT fields had to be the same as ORDER BY fields, owing to the fact that one single sorted tree was used for keeping track of tuples, ordering and uniqueness. Fixed by introducing a second structure to handle uniqueness so that the original structure has only to order the result. mysql-test/r/func_gconcat.result: Bug#32798: - Wrong test result turned correct after fix. - Correct test result mysql-test/t/func_gconcat.test: Bug#32798: Test case sql/item_sum.cc: Bug#32798: Implementation of fix. Dead code removal. - removed comment describing this bug - replaced body of function group_concat_key_cmp_with_distinct - removed function group_concat_key_cmp_with_distinct_and_order - Added a Unique object to maintain uniqueness of values. sql/item_sum.h: Bug#32798: Declarations and comments. --- mysql-test/r/func_gconcat.result | 63 +++++++++++++++++++++++++++++++++++++++- mysql-test/t/func_gconcat.test | 43 +++++++++++++++++++++++++++ 2 files changed, 105 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 35487c25ae3..26592e4f898 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -271,7 +271,7 @@ group_concat(distinct s1 order by s2) c,b,a select group_concat(distinct s1 order by s2) from t1; group_concat(distinct s1 order by s2) -c,b,a,c +c,b,a drop table t1; create table t1 (a int, c int); insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5); @@ -819,4 +819,65 @@ id group_concat(b.name) 1 óra,óra 2 óra,óra drop table t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3); +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b) +1,2 +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY b DESC) +2,1 +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; +GROUP_CONCAT(DISTINCT a) +1,2 +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1; +GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) +3,2 +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) +2,3 +SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1; +GROUP_CONCAT(a ORDER BY 3 - b) +2,2,1 +CREATE TABLE t2 (a INT, b INT, c INT, d INT); +INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2); +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2; +GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) +11,21,12 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2; +GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) +11,12,21 +CREATE TABLE t3 (a INT, b INT, c INT); +INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3; +GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) +11,21,32 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3; +GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) +11,32,21 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) +11,22,23 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) +11,22,32 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) +11,22,23 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) +11,22,32 +SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1; +GROUP_CONCAT(DISTINCT a ORDER BY a, b) +1,2 +SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1; +GROUP_CONCAT(DISTINCT b ORDER BY b, a) +1,2,3 +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1; +GROUP_CONCAT(DISTINCT a, b ORDER BY a) +11,23,22 +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; +GROUP_CONCAT(DISTINCT b, a ORDER BY b) +11,22,32 +DROP TABLE t1, t2, t3; End of 5.0 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index ff3ba951870..85f81520863 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -562,4 +562,47 @@ insert into t1 (id, name) values (2, " select b.id, group_concat(b.name) from t1 a, t1 b group by b.id; drop table t1; +# +# Bug#32798: DISTINCT in GROUP_CONCAT clause fails when ordering by a column +# with null values +#' +CREATE TABLE t1 (a INT, b INT); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3); + +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a) FROM t1; + +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1; + +CREATE TABLE t2 (a INT, b INT, c INT, d INT); + +# There is one duplicate in the expression list: 1,10 +# There is one duplicate in ORDER BY list, but that shouldnt matter: 1,10 +INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2); + +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2; + +CREATE TABLE t3 (a INT, b INT, c INT); + +INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); + +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3; + +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1; +SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; + +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests -- cgit v1.2.1 From 3f6073ae63f9cb738cb6f0a6a8136e1d21ba0e1b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Dec 2007 13:42:46 -0800 Subject: Fixed bug #27848. In a union without braces, the order by at the end is applied to the overall union. It therefore should not interfere with the individual select parts of the union. Fixed by changing our parser rules appropriately. mysql-test/r/union.result: Added a test case for bug #27848. mysql-test/t/union.test: Added a test case for bug #27848. --- mysql-test/r/union.result | 51 +++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/union.test | 44 ++++++++++++++++++++++++++++++++++++++++ 2 files changed, 95 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index efdd8195fb5..c7a6035aab2 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1389,4 +1389,55 @@ select @var; 1 (select 2) union (select 1 into @var); ERROR 42000: Result consisted of more than one row +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (10), (20); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (10), (50), (50); +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a; +a 1 +NULL 3 +10 1 +20 1 +50 2 +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a DESC; +a 1 +50 2 +20 1 +10 1 +NULL 3 +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a ASC LIMIT 3; +a 1 +NULL 3 +10 1 +20 1 +SELECT a,1 FROM t1 +UNION ALL +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a DESC; +a 1 +50 2 +20 1 +10 1 +10 1 +NULL 3 +SELECT a,1 FROM t1 +UNION +(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a); +ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY +SELECT a,1 FROM t1 +UNION ALL +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a +UNION +SELECT 1,1; +ERROR HY000: Incorrect usage of UNION and ORDER BY +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 22f09466b1c..0277b01e21a 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -877,4 +877,48 @@ DROP TABLE t1; select @var; --error 1172 (select 2) union (select 1 into @var); + +# +# Bug#27848: order-by of union clashes with rollup of select part +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (10), (20); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (10), (50), (50); + +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a; + +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a DESC; + +SELECT a,1 FROM t1 +UNION +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a ASC LIMIT 3; + +SELECT a,1 FROM t1 +UNION ALL +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP +ORDER BY a DESC; + +--error ER_WRONG_USAGE +SELECT a,1 FROM t1 +UNION +(SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a); + +--error ER_WRONG_USAGE +SELECT a,1 FROM t1 +UNION ALL +SELECT b, COUNT(*) FROM t2 GROUP BY b WITH ROLLUP ORDER BY a +UNION +SELECT 1,1; + +DROP TABLE t1,t2; + --echo End of 5.0 tests -- cgit v1.2.1 From 3e61561a9aaec5d4999c2f7c47880afec9b6c55f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 17 Dec 2007 18:56:39 +0200 Subject: Bug #19390: Test 'rpl_trigger' fails, might be random The checks in the test for bug #12480 were too wide and made the test to depend on the procedures and triggers present in the server. Corrected the test to check only for the procedure and trigger it creates. mysql-test/r/rpl_trigger.result: Bug #19390: corrected the test to check for its procedure only mysql-test/t/rpl_trigger.test: Bug #19390: corrected the test to check for its procedure only --- mysql-test/r/rpl_trigger.result | 12 ++++++++---- mysql-test/t/rpl_trigger.test | 12 ++++++++---- 2 files changed, 16 insertions(+), 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result index f8573eec75f..51e7c15bc12 100644 --- a/mysql-test/r/rpl_trigger.result +++ b/mysql-test/r/rpl_trigger.result @@ -88,21 +88,25 @@ select a=b && a=c from t1; a=b && a=c 1 SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; routine_name definer bug12480 root@localhost SELECT trigger_name, definer -FROM information_schema.triggers; +FROM information_schema.triggers +WHERE trigger_name = 't1_first'; trigger_name definer t1_first root@localhost --- On slave -- SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; routine_name definer bug12480 root@localhost SELECT trigger_name, definer -FROM information_schema.triggers; +FROM information_schema.triggers +WHERE trigger_name = 't1_first'; trigger_name definer t1_first root@localhost select a=b && a=c from t1; diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test index faba89e7a73..da6cea10698 100644 --- a/mysql-test/t/rpl_trigger.test +++ b/mysql-test/t/rpl_trigger.test @@ -93,10 +93,12 @@ let $time=`select a from t1`; # - dump definers on the slave; SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; SELECT trigger_name, definer -FROM information_schema.triggers; +FROM information_schema.triggers +WHERE trigger_name = 't1_first'; save_master_pos; connection slave; @@ -111,10 +113,12 @@ select "--- On slave --" as ""; # item. SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; SELECT trigger_name, definer -FROM information_schema.triggers; +FROM information_schema.triggers +WHERE trigger_name = 't1_first'; select a=b && a=c from t1; --disable_query_log -- cgit v1.2.1 From 2ae4b047a3d9c4236ebd157ef8ee4bf084d4629b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 21 Dec 2007 12:44:24 +0200 Subject: Bug #33256: CREATE ... SELECT creates obsolete table w/ Field_date instead of Field_newdate Field_date was still used in temp table creation. Fixed by using Field_newdate consistently throughout the server except when reading tables defined with older MySQL version. No test suite is possible because both Field_date and Field_newdate return the same values in all the metadata calls. mysql-test/r/type_decimal.result: Bug #33256: removed redundant warnings sql/field.h: Bug #33256: Add a constructor similar to Field_date::Field_date() sql/item.cc: Bug #33256: Use Field_newdate instead of Field_date for all temp tables and CREATE .. SELECT sql/item_sum.cc: Bug #33256: Use Field_newdate instead of Field_date for all temp tables and CREATE .. SELECT sql/item_timefunc.cc: Bug #33256: Use Field_newdate instead of Field_date for all temp tables and CREATE .. SELECT sql/item_timefunc.h: Bug #33256: Use Field_newdate instead of Field_date for all temp tables and CREATE .. SELECT --- mysql-test/r/type_decimal.result | 4 ---- 1 file changed, 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index a438755ce6b..a2ccf042165 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -786,10 +786,6 @@ from (select 1 as s,'t' as t union select null, null ) as sub1; select group_concat(t) from t1 group by week(date)/10; group_concat(t) t -Warnings: -Warning 1292 Truncated incorrect datetime value: '0000-00-00' -Warning 1292 Truncated incorrect datetime value: '0000-00-00' -Warning 1292 Truncated incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1 ( qty decimal(16,6) default NULL, -- cgit v1.2.1 From bdad41bacebf141be5dbe64e744eee106a1a80fd Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 24 Dec 2007 17:42:13 +0300 Subject: Fix for bug #33305: Test case in 'skip_grants' file need dynamic loading to be compiled in The problem was that on a statically built server an attempt to create a UDF resulted in a different, but reasonable error ("Can't open shared library" instead of "UDFs are unavailable with the --skip-grant-tables option"), which caused a failure for the test case for bug #32020. Fixed by moving the test case for bug #32020 from skip_grants.test to a separate test to ensure that it is only run when the server is built with support for dynamically loaded libraries. mysql-test/r/skip_grants.result: Moved the test case for bug #32020 to a separate test. mysql-test/t/skip_grants.test: Moved the test case for bug #32020 to a separate test. mysql-test/r/udf_skip_grants.result: Moved the test case for bug #32020 to a separate test. mysql-test/t/udf_skip_grants-master.opt: Moved the test case for bug #32020 to a separate test. mysql-test/t/udf_skip_grants.test: Moved the test case for bug #32020 to a separate test. --- mysql-test/r/skip_grants.result | 4 ---- mysql-test/r/udf_skip_grants.result | 5 +++++ mysql-test/t/skip_grants.test | 10 ---------- mysql-test/t/udf_skip_grants-master.opt | 1 + mysql-test/t/udf_skip_grants.test | 28 ++++++++++++++++++++++++++++ 5 files changed, 34 insertions(+), 14 deletions(-) create mode 100644 mysql-test/r/udf_skip_grants.result create mode 100644 mysql-test/t/udf_skip_grants-master.opt create mode 100644 mysql-test/t/udf_skip_grants.test (limited to 'mysql-test') diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 1ef35b051d6..cba46b13f19 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -70,8 +70,4 @@ count(*) select count(*) from information_schema.USER_PRIVILEGES; count(*) 0 -CREATE FUNCTION a RETURNS STRING SONAME ''; -ERROR HY000: Can't initialize function 'a'; UDFs are unavailable with the --skip-grant-tables option -DROP FUNCTION a; -ERROR 42000: FUNCTION test.a does not exist End of 5.0 tests diff --git a/mysql-test/r/udf_skip_grants.result b/mysql-test/r/udf_skip_grants.result new file mode 100644 index 00000000000..8d7081ebf6f --- /dev/null +++ b/mysql-test/r/udf_skip_grants.result @@ -0,0 +1,5 @@ +CREATE FUNCTION a RETURNS STRING SONAME ''; +ERROR HY000: Can't initialize function 'a'; UDFs are unavailable with the --skip-grant-tables option +DROP FUNCTION a; +ERROR 42000: FUNCTION test.a does not exist +End of 5.0 tests diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test index 02a381063ee..1021d629540 100644 --- a/mysql-test/t/skip_grants.test +++ b/mysql-test/t/skip_grants.test @@ -117,14 +117,4 @@ select count(*) from information_schema.SCHEMA_PRIVILEGES; select count(*) from information_schema.TABLE_PRIVILEGES; select count(*) from information_schema.USER_PRIVILEGES; -# -# Bug #32020: loading udfs while --skip-grant-tables is enabled causes out of -# memory errors -# - ---error ER_CANT_INITIALIZE_UDF -CREATE FUNCTION a RETURNS STRING SONAME ''; ---error ER_SP_DOES_NOT_EXIST -DROP FUNCTION a; - --echo End of 5.0 tests diff --git a/mysql-test/t/udf_skip_grants-master.opt b/mysql-test/t/udf_skip_grants-master.opt new file mode 100644 index 00000000000..5699a3387b8 --- /dev/null +++ b/mysql-test/t/udf_skip_grants-master.opt @@ -0,0 +1 @@ +--skip-grant-tables diff --git a/mysql-test/t/udf_skip_grants.test b/mysql-test/t/udf_skip_grants.test new file mode 100644 index 00000000000..bd9402e0d8a --- /dev/null +++ b/mysql-test/t/udf_skip_grants.test @@ -0,0 +1,28 @@ +####################### udf_skip_grants.test ########################### +# # +# Test for bug #32020 "loading udfs while --skip-grant-tables is # +# enabled causes out of memory errors" # +# # +# Creation: # +# 2007-12-24 akopytov Moved the test case for bug #32020 from # +# skip_grants.test to a separate test to ensure # +# that it is only run when the server is built # +# with support for dynamically loaded libraries # +# (see bug #33305). # +# # +######################################################################## + +-- source include/not_embedded.inc +-- source include/have_udf.inc + +# +# Bug #32020: loading udfs while --skip-grant-tables is enabled causes out of +# memory errors +# + +--error ER_CANT_INITIALIZE_UDF +CREATE FUNCTION a RETURNS STRING SONAME ''; +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION a; + +--echo End of 5.0 tests -- cgit v1.2.1 From 8845553a81a427bf34ed8466913bc197943a3af6 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 9 Jan 2008 00:40:41 +0300 Subject: Bug#33675: Usage of an uninitialized memory by filesort in a subquery caused server crash. The filesort implementation has an optimization for subquery execution which consists of reusing previously allocated buffers. In particular the call to the read_buffpek_from_file function might be skipped when a big enough buffer for buffer descriptors (buffpeks) is already allocated. Beside allocating memory for buffpeks this function fills allocated buffer with data read from disk. Skipping it might led to using an arbitrary memory as fields' data and finally to a crash. Now the read_buffpek_from_file function is always called. It allocates new buffer only when necessary, but always fill it with correct data. sql/filesort.cc: Bug#33675: Usage of an uninitialized memory by filesort in a subquery caused server crash.Now the read_buffpek_from_file function is always called. It allocates new buffer only when necessary, but always fill it with correct data. mysql-test/r/subselect.result: Added a test case for the bug#33675: Usage of an uninitialized memory by filesort in a subquery caused server crash. mysql-test/t/subselect.test: Added a test case for the bug#33675: Usage of an uninitialized memory by filesort in a subquery caused server crash. --- mysql-test/r/subselect.result | 9 +++++++++ mysql-test/t/subselect.test | 22 ++++++++++++++++++++++ 2 files changed, 31 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f6487ae3ddf..75df77b0790 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4383,4 +4383,13 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2 DROP TABLE t1,t2; +create table t1(f11 int, f12 int); +create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); +insert into t1 values(1,1),(2,2), (3, 3); +set session sort_buffer_size= 33*1024; +select count(*) from t1 where f12 = +(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); +count(*) +3 +drop table t1,t2; End of 5.0 tests. diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ea080964058..88e4f683e9e 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3230,4 +3230,26 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION DROP TABLE t1,t2; +# +# Bug#33675: Usage of an uninitialized memory by filesort in a subquery +# caused server crash. +# +create table t1(f11 int, f12 int); +create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); +insert into t1 values(1,1),(2,2), (3, 3); +let $i=10000; +--disable_query_log +--disable_warnings +while ($i) +{ + eval insert into t2 values (-1 , $i/5000 + 1, '$i'); + dec $i; +} +--enable_warnings +--enable_query_log +set session sort_buffer_size= 33*1024; +select count(*) from t1 where f12 = +(select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); + +drop table t1,t2; --echo End of 5.0 tests. -- cgit v1.2.1 From 06b68454eadd20cbc2af5b2bfa8389e85120c0a3 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 9 Jan 2008 16:49:13 +0200 Subject: Bug #33133: Views are not transparent When resolving references we need to take into consideration the view "fields" and allow qualified access to them. Fixed by extending the reference resolution to process view fields correctly. mysql-test/r/func_group.result: Bug #33133: test case mysql-test/t/func_group.test: Bug #33133: test case sql/sql_base.cc: Bug #33133: allow qualified alias refs to view fields --- mysql-test/r/func_group.result | 12 ++++++++++++ mysql-test/t/func_group.test | 15 +++++++++++++++ 2 files changed, 27 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 1e130877088..4785ca9919d 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1407,4 +1407,16 @@ SELECT COUNT(*), a FROM t1; COUNT(*) a 4 1 DROP TABLE t1; +set SQL_MODE=ONLY_FULL_GROUP_BY; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE VIEW v1 AS SELECT a,(a + 1) AS y FROM t1; +EXPLAIN EXTENDED SELECT y FROM v1 GROUP BY v1.y; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +Warnings: +Note 1003 select (`test`.`t1`.`a` + 1) AS `y` from `test`.`t1` group by (`test`.`t1`.`a` + 1) +DROP VIEW v1; +DROP TABLE t1; +SET SQL_MODE=DEFAULT; End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 25cb13a2f75..75a380c733f 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -901,5 +901,20 @@ SELECT COUNT(*), a FROM t1; DROP TABLE t1; +# +# Bug #33133: Views are not transparent +# + +set SQL_MODE=ONLY_FULL_GROUP_BY; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE VIEW v1 AS SELECT a,(a + 1) AS y FROM t1; +EXPLAIN EXTENDED SELECT y FROM v1 GROUP BY v1.y; + +DROP VIEW v1; +DROP TABLE t1; +SET SQL_MODE=DEFAULT; + ### --echo End of 5.0 tests -- cgit v1.2.1