From b8ab82a6294228c65f1ac2a3b3efa82a5abc1dc8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 1 Oct 2006 16:36:26 +0500 Subject: bug #21790 (UNKNOWN ERROR message in geometry) We issued UNKNOWN ERROR initially in this place and forgot to fix it when we implemented informative error message for this mysql-test/r/gis-rtree.result: test result mysql-test/t/gis-rtree.test: test case sql/handler.cc: let's issue informative error message here --- mysql-test/r/gis-rtree.result | 8 ++++++++ mysql-test/t/gis-rtree.test | 12 ++++++++++++ 2 files changed, 20 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index b283d64395d..f872838f6f3 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -862,3 +862,11 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 163f2806ad2..091811b05b3 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -232,3 +232,15 @@ CHECK TABLE t1 EXTENDED; DROP TABLE t1; # End of 4.1 tests + +# +# bug #21790 (UNKNOWN ERROR on NULLs in RTree) +# +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +--error 1048 +INSERT INTO t1(foo) VALUES (NULL); +--error 1416 +INSERT INTO t1() VALUES (); +--error 1416 +INSERT INTO t1(foo) VALUES (''); +DROP TABLE t1; -- cgit v1.2.1 From d649efbb07af288b6b58b4bd01a04d34c7a7f467 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Oct 2006 23:05:53 -0700 Subject: Fixed bug #23478. If elements a not top-level IN subquery were accessed by an index and the subquery result set included a NULL value then the quantified predicate that contained the subquery was evaluated to NULL when it should return a non-null value. mysql-test/r/subselect.result: Added a test case for bug #23478. mysql-test/t/subselect.test: Added a test case for bug #23478. --- mysql-test/r/subselect.result | 15 +++++++++++++++ mysql-test/t/subselect.test | 17 +++++++++++++++++ 2 files changed, 32 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ad847b5f156..28fbfc86657 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2982,3 +2982,18 @@ field1 field2 1 1 1 3 DROP TABLE t1, t2; +CREATE TABLE t1(a int, INDEX (a)); +INSERT INTO t1 VALUES (1), (3), (5), (7); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES (1),(2),(3); +EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index +SELECT a, a IN (SELECT a FROM t1) FROM t2; +a a IN (SELECT a FROM t1) +1 1 +2 NULL +3 1 +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6defa8b16a5..ac035c72d18 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1948,4 +1948,21 @@ SELECT field1, field2 DROP TABLE t1, t2; +# +# Bug #23478: not top-level IN subquery returning a non-empty result set +# with possible NULL values by index access from the outer query +# + +CREATE TABLE t1(a int, INDEX (a)); +INSERT INTO t1 VALUES (1), (3), (5), (7); +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES (1),(2),(3); + +EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; +SELECT a, a IN (SELECT a FROM t1) FROM t2; + +DROP TABLE t1,t2; + # End of 4.1 tests -- cgit v1.2.1 From 4ddb48c619c5fb703a846f5dd2659a4854a8c0d7 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 Oct 2006 15:02:51 +0500 Subject: WL#3475 (Threads for the embedded server in mysqltest) Necessary code added to mysqltest.c. Disabled tests are available now. client/mysqltest.c: do_send_query function implemented, so now 'send' command will be run in separate thread for the embedded server. Mutex and condition added to the 'connection' struct for syncronisation purposes. Yes it'd be easier if we had pthread_join() command libmysql/libmysql.c: this isn't actually needed and causes problems in embedded server mysql-test/t/bdb-deadlock.test: test is available for the embedded server now mysql-test/t/flush.test: test is available for the embedded server now mysql-test/t/flush_block_commit.test: test is available for the embedded server now mysql-test/t/innodb-deadlock.test: test is available for the embedded server now mysql-test/t/innodb-lock.test: test is available for the embedded server now mysql-test/t/lock_multi.test: test is available for the embedded server now mysql-test/t/rename.test: test is available for the embedded server now mysql-test/t/show_check.test: test is available for the embedded server now mysql-test/t/status.test: test is available for the embedded server now --- mysql-test/t/bdb-deadlock.test | 8 -------- mysql-test/t/flush.test | 8 -------- mysql-test/t/flush_block_commit.test | 3 --- mysql-test/t/innodb-deadlock.test | 2 -- mysql-test/t/innodb-lock.test | 2 -- mysql-test/t/lock_multi.test | 8 -------- mysql-test/t/rename.test | 4 ---- mysql-test/t/show_check.test | 3 +-- mysql-test/t/status.test | 7 ------- 9 files changed, 1 insertion(+), 44 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/t/bdb-deadlock.test b/mysql-test/t/bdb-deadlock.test index 88243cfc860..b48648e0fd0 100644 --- a/mysql-test/t/bdb-deadlock.test +++ b/mysql-test/t/bdb-deadlock.test @@ -1,11 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# - --- source include/not_embedded.inc -- source include/have_bdb.inc connect (con1,localhost,root,,); diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index aedf8e85b65..8fe62ecac01 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -1,11 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# --- source include/not_embedded.inc - connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; diff --git a/mysql-test/t/flush_block_commit.test b/mysql-test/t/flush_block_commit.test index 1e7ecd2548c..0c1d2b82df6 100644 --- a/mysql-test/t/flush_block_commit.test +++ b/mysql-test/t/flush_block_commit.test @@ -3,9 +3,6 @@ # We verify that we did not introduce a deadlock. # This is intended to mimick how mysqldump and innobackup work. -# This test doesn't work with the embedded server --- source include/not_embedded.inc - # And it requires InnoDB -- source include/have_innodb.inc diff --git a/mysql-test/t/innodb-deadlock.test b/mysql-test/t/innodb-deadlock.test index 41741942963..81acfba5c93 100644 --- a/mysql-test/t/innodb-deadlock.test +++ b/mysql-test/t/innodb-deadlock.test @@ -1,6 +1,4 @@ -- source include/have_innodb.inc -# Can't test this with embedded server --- source include/not_embedded.inc connect (con1,localhost,root,,); connect (con2,localhost,root,,); diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test index 55a712fef9b..eacf7e562be 100644 --- a/mysql-test/t/innodb-lock.test +++ b/mysql-test/t/innodb-lock.test @@ -1,6 +1,4 @@ -- source include/have_innodb.inc -# Can't test this with embedded server --- source include/not_embedded.inc # # Check and select innodb lock type diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 2e40aeaccb7..32e7f4234c4 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -1,11 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# --- source include/not_embedded.inc - --disable_warnings drop table if exists t1,t2; --enable_warnings diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test index 5caecef176e..ad9921d2cf0 100644 --- a/mysql-test/t/rename.test +++ b/mysql-test/t/rename.test @@ -2,10 +2,6 @@ # Test of rename table # -# Test requires concurrent connections, which can't be tested on embedded -# server --- source include/not_embedded.inc - --disable_warnings drop table if exists t0,t1,t2,t3,t4; # Clear up from other tests (to ensure that SHOW TABLES below is right) diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index d70903adbc4..8be676d9a35 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -1,5 +1,4 @@ -# Requires use of multiple simultaneous connections, not supported with -# embedded server testing +# Uses GRANT commands that usually disabled in embedded server -- source include/not_embedded.inc # diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 7fea51c9327..df8da26df57 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,10 +1,3 @@ -# This test doesn't work with the embedded version as this code -# assumes that one query is running while we are doing queries on -# a second connection. -# This would work if mysqltest run would be threaded and handle each -# connection in a separate thread. -# ---source include/not_embedded.inc # PS causes different statistics --disable_ps_protocol -- cgit v1.2.1 From f51d0812a78ebd53be143fec274c532b94a65c12 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Oct 2006 15:26:41 +0300 Subject: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. The SQL parser was using Item::name to transfer user defined function attributes to the user defined function (udf). It was not distinguishing between user defined function call arguments and stored procedure call arguments. Setting Item::name was causing Item_ref::print() method to print the argument as quoted identifiers and caused views that reference aggregate functions as udf call arguments (and rely on Item::print() for the text of the view to store) to throw an undefined identifier error. Overloaded Item_ref::print to print aggregate functions as such when printing the references to aggregate functions taken out of context by split_sum_func2() Fixed the parser to properly detect using AS clause in stored procedure arguments as an error. Fixed printing the arguments of udf call to print properly the udf attribute. mysql-test/r/udf.result: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - test cases mysql-test/t/udf.test: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - test cases sql/item.cc: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - Don't print the refs to SUM functions as refs. sql/item_func.cc: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - print the aliases in the udf calls sql/item_func.h: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - print the aliases in the udf calls sql/sql_lex.cc: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - disable aliases for arguments in stored routine calls sql/sql_lex.h: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - disable aliases for arguments in stored routine calls sql/sql_yacc.yy: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. - disable aliases for arguments in stored routine calls - fix bison duplicate symbol warnings --- mysql-test/r/udf.result | 79 +++++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/udf.test | 44 +++++++++++++++++++++++++++ 2 files changed, 123 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 8e37cca6aa9..396f1efa1b7 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 order by 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 values (1,1),(2,2); +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN +RETURN a; +END +|| +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(a AS attr_name) FROM t1; +myfunc_int(a AS attr_name) +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` +SELECT a,c FROM v1; +a c +1 1 +2 2 +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +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 'xx) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +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 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +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 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; End of 5.0 tests. DROP FUNCTION metaphon; DROP FUNCTION myfunc_double; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 96e559f5c05..37358a292be 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -127,6 +127,50 @@ create table t1(f1 int); insert into t1 values(1),(2); explain select myfunc_int(f1) from t1 order by 1; drop table t1; + +# +# Bug #21809: Error 1356 while selecting from view with grouping though +# underlying select OK. +# +CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2); + +DELIMITER ||; +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN + RETURN a; +END +|| +DELIMITER ;|| + +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +SELECT a,c FROM v1; + +--error ER_PARSE_ERROR +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; + +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; + --echo End of 5.0 tests. # -- cgit v1.2.1 From 01a0be193d8344e1b20c366b30a7ca75a0a9bd44 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Oct 2006 20:14:39 +0500 Subject: bug #19491 (CAST do DATETIME wrong result) mysql-test/r/type_datetime.result: result fixed mysql-test/r/type_newdecimal.result: result fixed mysql-test/t/type_datetime.test: testcase mysql-test/t/type_newdecimal.test: testcase sql/field.cc: Field_new_decimal::store_time implemented sql/field.h: Field_new_decimal::store_time added sql/item.cc: auxiliary methods implemented to operate with datatimes sql/item.h: auxiliary methods declared in Item to operate with datatimes sql/item_timefunc.cc: Item_date::save_in_field old implementation removed sql/item_timefunc.h: my_decimal and save_in_field methods implemented for datetime items sql/my_decimal.cc: date2my_decimal implemented sql/my_decimal.h: date2my_decimal declared --- mysql-test/r/type_datetime.result | 12 ++++++++++++ mysql-test/r/type_newdecimal.result | 8 ++++++++ mysql-test/t/type_datetime.test | 10 ++++++++++ mysql-test/t/type_newdecimal.test | 8 ++++++++ 4 files changed, 38 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 49e4827cb97..7fc1c4f398d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -179,3 +179,15 @@ a 2006-06-06 15:55:55 DROP PREPARE s; DROP TABLE t1; +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) +20060810.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) +20060810101112.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) +20060810101112.000014 +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); +CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) +101112.098700 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 33f1ece0390..f24014da285 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1412,3 +1412,11 @@ i2 count(distinct j) 1.0 2 2.0 2 drop table t1; +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +f1 +20101112000000.000014 +20101112.000000 +drop table t1; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index cdf73bf6c89..3ad6bdc53e4 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="2006060 EXECUTE s; DROP PREPARE s; DROP TABLE t1; + + +# +# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result +# +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); + diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index de1ebd74d17..12da41dcfd5 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1108,3 +1108,11 @@ insert into t1 values (1,1), (1,2), (2,3), (2,4); select i, count(distinct j) from t1 group by i; select i+0.0 as i2, count(distinct j) from t1 group by i2; drop table t1; + + +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +drop table t1; + -- cgit v1.2.1 From f00e6bd75d24e311016cd72449ed678c0172f6e6 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 30 Oct 2006 09:52:50 +0400 Subject: Bug #8663 (cant use bigint as input to CAST) decimal->ulong conversion fixed to assign max possible ULONG if decimal is bigger Item_func_unsigned now handles DECIMAL parameter separately as we can't rely on decimal::val_int result here. mysql-test/r/type_newdecimal.result: result fixed mysql-test/t/type_newdecimal.test: testcase sql/item_func.cc: DECIMAL_RESULT should be handled separately here as it's always signed. strings/decimal.c: here we assign max possible ULONG if the decimal value is bigger --- mysql-test/r/type_newdecimal.result | 5 +++++ mysql-test/t/type_newdecimal.test | 6 ++++++ 2 files changed, 11 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 33f1ece0390..cacb945df85 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1412,3 +1412,8 @@ i2 count(distinct j) 1.0 2 2.0 2 drop table t1; +select cast(19999999999999999999 as unsigned); +cast(19999999999999999999 as unsigned) +18446744073709551615 +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index de1ebd74d17..cb85f14ee47 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1108,3 +1108,9 @@ insert into t1 values (1,1), (1,2), (2,3), (2,4); select i, count(distinct j) from t1 group by i; select i+0.0 as i2, count(distinct j) from t1 group by i2; drop table t1; + +# +# Bug #8663 (cant use bigint as input to CAST) +# +select cast(19999999999999999999 as unsigned); + -- cgit v1.2.1 From 634d3991ab818ea4afc8d98b40de75b22e0cae54 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 31 Oct 2006 11:01:27 +0200 Subject: Bug #23184: SELECT causes server crash Item::val_xxx() may be called by the server several times at execute time for a single query. Calls to val_xxx() may be very expensive and sometimes (count(distinct), sum(distinct), avg(distinct)) not possible. To avoid that problem the results of calculation for these aggregate functions are cached so that val_xxx() methods just return the calculated value for the second and subsequent calls. mysql-test/r/func_group.result: Bug #23184: SELECT causes server crash - test case mysql-test/t/func_group.test: Bug #23184: SELECT causes server crash - test case sql/item_sum.cc: Bug #23184: SELECT causes server crash - caching of the aggregate function results so no need to recalculate at val_xxx() sql/item_sum.h: Bug #23184: SELECT causes server crash - caching of the aggregate function results so no need to recalculate at val_xxx() --- mysql-test/r/func_group.result | 26 ++++++++++++++++++++++++++ mysql-test/t/func_group.test | 25 +++++++++++++++++++++++++ 2 files changed, 51 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c6117053a60..23517f7b603 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1029,3 +1029,29 @@ t1 CREATE TABLE `t1` ( `stddev(0)` double(8,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +a cnt +1 65536 +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +a sumation +1 2147516416 +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; +a average +1 32768.5000 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 079d107fad8..089f5ed9911 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -700,3 +700,28 @@ create table t1 select stddev(0); show create table t1; drop table t1; +# +# Bug #23184: SELECT causes server crash +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; + +DROP TABLE t1; + +--echo End of 5.0 tests -- cgit v1.2.1 From 48df3b96a1719141749c05e4080c57366e9d0fbe Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 31 Oct 2006 20:51:09 +0300 Subject: BUG#8804: wrong results for NULL IN (SELECT ...) Evaluate "NULL IN (SELECT ...)" in a special way: Disable pushed-down conditions and their "consequences": = Do full table scans instead of unique_[index_subquery] lookups. = Change appropriate "ref_or_null" accesses to full table scans in subquery's joins. Also cache value of NULL IN (SELECT ...) if the SELECT is not correlated wrt any upper select. mysql-test/r/subselect.result: BUG#8804: wrong results for NULL IN (SELECT ...): - Updated test results sql/item.h: BUG#8804: wrong results for NULL IN (SELECT ...): - Added comments sql/item_cmpfunc.cc: BUG#8804: wrong results for NULL IN (SELECT ...): Made Item_in_optimizer to: - cache the value of "NULL IN (uncorrelated select)" - Turn off pushed-down predicates when evaluating "NULL IN (SELECT ...)" sql/item_cmpfunc.h: BUG#8804: wrong results for NULL IN (SELECT ...): - Made Item_in_optimizer cache the value of "NULL IN (uncorrelated select)" - Added comments sql/item_subselect.cc: BUG#8804: wrong results for NULL IN (SELECT ...): - When needed, wrap the predicates we push into subquery into an Item_func_trig_cond so we're able to turn them off when evaluating NULL IN (SELECT ...). - Added code to evaluate NULL IN (SELECT ...) in a special way: = In [unique_]index_subquery, do full table scan to see if there are any rows. = For other subqueries, change ref[_or_null] to ALL if the ref[_or_null] was created from pushed-down predicate. sql/item_subselect.h: BUG#8804: wrong results for NULL IN (SELECT ...): - Added Item_subselect::is_correlated - Added comments sql/records.cc: BUG#8804: wrong results for NULL IN (SELECT ...): - Make rr_sequential() non-static sql/sql_lex.cc: BUG#8804: wrong results for NULL IN (SELECT ...): - Added st_select_lex::is_correlated and Item_subselect::is_correlated. sql/sql_lex.h: BUG#8804: wrong results for NULL IN (SELECT ...): - Added st_select_lex::is_correlated sql/sql_select.cc: BUG#8804: wrong results for NULL IN (SELECT ...): - Added KEY_FIELD::outer_ref to keep track of which ref accesses are created from predicates that were pushed down into the subquery. sql/sql_select.h: BUG#8804: wrong results for NULL IN (SELECT ...): - Added KEYUSE::outer_ref mysql-test/r/subselect3.result: New BitKeeper file ``mysql-test/r/subselect3.result'' mysql-test/t/subselect3.test: New BitKeeper file ``mysql-test/t/subselect3.test'' --- mysql-test/r/subselect.result | 10 +-- mysql-test/r/subselect3.result | 153 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect3.test | 137 ++++++++++++++++++++++++++++++++++++ 3 files changed, 295 insertions(+), 5 deletions(-) create mode 100644 mysql-test/r/subselect3.result create mode 100644 mysql-test/t/subselect3.test (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 55d48030a07..82c70e19f9c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -744,7 +744,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 AS `1` having ((`test`.`t2`.`id`) = (1)) union select 3 AS `3` having ((`test`.`t2`.`id`) = (3)))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id`,(select 1 AS `1` having trigcond(((`test`.`t2`.`id`) = (1))) union select 3 AS `3` having trigcond(((`test`.`t2`.`id`) = (3))))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -907,7 +907,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a`,(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and (((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a`,(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and trigcond((((`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`)))) having trigcond((`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -2817,19 +2817,19 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and (((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and (((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having ((`test`.`t2`.`one`) and (`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and (((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having trigcond(((`test`.`t2`.`one`) and (`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where ((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and ((`test`.`t1`.`one`) = `test`.`t2`.`one`) and ((`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where ((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and trigcond((((`test`.`t1`.`one`) = `test`.`t2`.`one`) and ((`test`.`t1`.`two`) = `test`.`t2`.`two`)))))) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having ((((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and (((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and (`test`.`t2`.`one`) and (`test`.`t2`.`two`)))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having trigcond(((((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and (((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)) and (`test`.`t2`.`one`) and (`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result new file mode 100644 index 00000000000..5ab8e448b39 --- /dev/null +++ b/mysql-test/r/subselect3.result @@ -0,0 +1,153 @@ +drop table if exists t0, t1, t2, t3, t4; +create table t1 (oref int, grp int, ie int) ; +insert into t1 (oref, grp, ie) values +(1, 1, 1), +(1, 1, 1), +(1, 2, NULL), +(2, 1, 3), +(3, 1, 4), +(3, 2, NULL); +create table t2 (oref int, a int); +insert into t2 values +(1, 1), +(2, 2), +(3, 3), +(4, NULL), +(2, NULL); +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +a oref a in (select max(ie) +from t1 where oref=t2.oref group by grp) +1 1 1 +2 2 0 +3 3 NULL +NULL 4 0 +NULL 2 NULL +explain extended +select a, oref, a in (select max(ie) +from t1 where oref=t2.oref group by grp) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,(`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond(((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) +from t1 where oref=t2.oref group by grp)` from `test`.`t2` +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where (`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having ((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`))))) +create table t3 (a int); +insert into t3 values (NULL), (NULL); +flush status; +select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +a in (select max(ie) from t1 where oref=4 group by grp) +0 +0 +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 11 +select ' ^ This must show 11' Z; +Z + ^ This must show 11 +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select (`test`.`t3`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond(((`test`.`t3`.`a`) = (max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` +drop table t1, t2, t3; +create table t1 (a int, oref int, key(a)); +insert into t1 values +(1, 1), +(1, NULL), +(2, 3), +(2, NULL), +(3, NULL); +create table t2 (a int, oref int); +insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +oref a Z +1 1 1 +2 2 0 +3 NULL NULL +4 NULL 0 +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where +Warnings: +Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,(`test`.`t2`.`a`,(((`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +oref a +1 1 +show status like '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 5 +delete from t2; +insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +flush status; +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +oref a Z +0 NULL 0 +0 NULL 0 +0 NULL 0 +0 NULL 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 29 +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; +Z +No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +drop table t1, t2; +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; +a b Z +1 1 1 +2 1 0 +NULL 1 NULL +NULL 0 0 +drop table t1, t2; +create table t1 (a int, b int, key(a)); +insert into t1 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +a oref Z +1 1 1 +NULL 1 NULL +NULL 0 0 +explain extended +select a, oref, +t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where +2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where +Warnings: +Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,(`test`.`t3`.`a`,(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((((`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond((`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` +drop table t1, t2, t3; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test new file mode 100644 index 00000000000..f7fbafdd17f --- /dev/null +++ b/mysql-test/t/subselect3.test @@ -0,0 +1,137 @@ +--disable_warnings +drop table if exists t0, t1, t2, t3, t4; +--enable_warnings + +# +# 1. Subquery with GROUP/HAVING +# +create table t1 (oref int, grp int, ie int) ; +insert into t1 (oref, grp, ie) values + (1, 1, 1), + (1, 1, 1), + (1, 2, NULL), + + (2, 1, 3), + + (3, 1, 4), + (3, 2, NULL); + +# Ok, for +# select max(ie) from t1 where oref=PARAM group by grp +# we'll have: +# 1 -> (1, NULL) matching + NULL +# 2 -> (3) non-matching +# 3 -> (3, NULL) non-matching + NULL +# 4 -> () nothing. + +create table t2 (oref int, a int); +insert into t2 values + (1, 1), + (2, 2), + (3, 3), + (4, NULL), + (2, NULL); + +# true, false, null, false, null +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must have a trigcond +explain extended +select a, oref, a in (select max(ie) + from t1 where oref=t2.oref group by grp) from t2; + +# This must not have a trigcond: +explain extended +select a, oref from t2 +where a in (select max(ie) from t1 where oref=t2.oref group by grp); + + +# Non-correlated subquery, 2 NULL evaluations +create table t3 (a int); +insert into t3 values (NULL), (NULL); +flush status; +select a in (select max(ie) from t1 where oref=4 group by grp) from t3; +show status like 'Handler_read_rnd_next'; +select ' ^ This must show 11' Z; + +# This must show trigcond: +explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; + +drop table t1, t2, t3; + +# +# 2. Subquery handled with 'index_subquery': +# +create table t1 (a int, oref int, key(a)); +insert into t1 values + (1, 1), + (1, NULL), + (2, 3), + (2, NULL), + (3, NULL); + +create table t2 (a int, oref int); +insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); + +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +# The next explain shows "using index" but that is just incorrect display +# (there is a bug filed about this). +explain extended +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; + +flush status; +select oref, a from t2 where a in (select a from t1 where oref=t2.oref); +# This will only show access to t2: +show status like '%Handler_read_rnd_next'; + +# Check that repeated NULL-scans are not cached (subq. is not correlated): +delete from t2; +insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); + +flush status; +select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; +show status like '%Handler_read%'; +select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; + +drop table t1, t2; + +# +# 3. Subquery handled with 'unique_index_subquery': +# +create table t1 (a int, b int, primary key (a)); +insert into t1 values (1,1), (3,1),(100,1); + +create table t2 (a int, b int); +insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0); + +select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ; + +drop table t1, t2; + +# +# 4. Subquery that is a join, with ref access +# +create table t1 (a int, b int, key(a)); +insert into t1 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +create table t2 like t1; +insert into t2 select * from t1; +update t2 set b=1; + +create table t3 (a int, oref int); +insert into t3 values (1, 1), (NULL,1), (NULL,0); +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +# This must have trigcond in WHERE and HAVING: +explain extended +select a, oref, + t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z +from t3; + +drop table t1, t2, t3; + -- cgit v1.2.1 From 2a7cf59fc92ce322671bb687e2941275dd23d1c5 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 31 Oct 2006 17:31:56 -0800 Subject: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Allocation of memory for the sort buffer at each evaluation of a subquery may take a significant amount of time if the buffer is rather big. With the fix we allocate the buffer at the first evaluation of the subquery and reuse it at each subsequent evaluation. mysql-test/r/subselect.result: Added a test case for bug #21727. mysql-test/t/subselect.test: Added a test case for bug #21727. sql/item_subselect.h: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added an implementation for Item_subselect::is_uncacheable() returning TRUE if the engine if the subselect is uncacheable. sql/mysql_priv.h: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added a new boolean parameter to the filesort_free_buffers procedure. If the value of this parameter is TRUE the procedure frees the sort_keys buffpek buffers. sql/records.cc: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added a new boolean parameter to the filesort_free_buffers procedure. If the value of this parameter is TRUE the procedure frees the sort_keys buffpek buffers. sql/sql_base.cc: Fixed bug #21727. Made sure that st_table::pos_in_table_list would be always initialized. sql/sql_select.cc: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added a new boolean parameter to the filesort_free_buffers procedure. If the value of this parameter is TRUE the procedure frees the sort_keys buffpek buffers. sql/sql_show.cc: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added a new boolean parameter to the filesort_free_buffers procedure. If the value of this parameter is TRUE the procedure frees the sort_keys buffpek buffers. sql/sql_table.cc: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Cleanup. sql/table.cc: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added st_table_list::in_subselect() returning for a table the subselect that contains the FROM list this table is taken from (if there is any). sql/table.h: Fixed bug #21727. This is a performance issue for queries with subqueries evaluation of which requires filesort. Added fields for sort_keys and buffpek buffers to the FILESORT_INFO structure. --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 37 +++++++++++++++++++++++++++++++++++++ 2 files changed, 53 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 57d6199675d..d05ec36a24f 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3545,3 +3545,19 @@ FROM t1 GROUP BY t1.a LIMIT 1) 2 2 DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t2 (x int auto_increment, y int, z int, +PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); +SET SESSION sort_buffer_size = 32 * 1024; +SELECT SQL_NO_CACHE COUNT(*) +FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c +FROM t1) t; +COUNT(*) +3000 +SET SESSION sort_buffer_size = 8 * 1024 * 1024; +SELECT SQL_NO_CACHE COUNT(*) +FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c +FROM t1) t; +COUNT(*) +3000 +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6d5082c360b..2f3ae3347e8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2426,3 +2426,40 @@ SELECT ( FROM t1 t2 GROUP BY t2.a; DROP TABLE t1,t2; + +# +# Bug #21727: Correlated subquery that requires filesort: +# slow with big sort_buffer_size +# + +CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t2 (x int auto_increment, y int, z int, + PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); + +disable_query_log; +let $1=3000; +while ($1) +{ + eval INSERT INTO t1(a) VALUES(RAND()*1000); + eval SELECT MAX(b) FROM t1 INTO @id; + let $2=10; + while ($2) + { + eval INSERT INTO t2(y,z) VALUES(@id,RAND()*1000); + dec $2; + } + dec $1; +} +enable_query_log; + +SET SESSION sort_buffer_size = 32 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +SET SESSION sort_buffer_size = 8 * 1024 * 1024; +SELECT SQL_NO_CACHE COUNT(*) + FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c + FROM t1) t; + +DROP TABLE t1,t2; -- cgit v1.2.1 From d922d5b6be1d45069bd87614a2688c49ef25ad8b Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 1 Nov 2006 14:22:11 +0400 Subject: test result fixed --- mysql-test/r/type_newdecimal.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index f24014da285..23e6f8266e9 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1418,5 +1418,5 @@ insert into t1 values (CAST('10:11:12' AS time)); select * from t1; f1 20101112000000.000014 -20101112.000000 +101112.000000 drop table t1; -- cgit v1.2.1 From c095f98ff7d18e2e5de1adf629147f2b199fbfaf Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 3 Nov 2006 18:48:16 +0200 Subject: Bug #22457: Column alias in ORDER BY works, but not if in an expression The parser is allocating Item_field for references by name in ORDER BY expressions. Such expressions however may point not only to Item_field in the select list (or to a table column) but also to an arbitrary Item. This causes Item_field::fix_fields to throw an error about missing column. The fix substitutes Item_field for the reference with an Item_ref when not pointing to Item_field. mysql-test/r/order_by.result: Bug #22457: Column alias in ORDER BY works, but not if in an expression - test case mysql-test/t/order_by.test: Bug #22457: Column alias in ORDER BY works, but not if in an expression - test case sql/item.cc: Bug #22457: Column alias in ORDER BY works, but not if in an expression - transform the Item_field made by the parser into Item_ref if it doesn't point to Item_field and it is in allowed context --- mysql-test/r/order_by.result | 27 +++++++++++++++++++++++++++ mysql-test/t/order_by.test | 16 ++++++++++++++++ 2 files changed, 43 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 8126e223f55..320bb89b62e 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -820,3 +820,30 @@ b a 20 1 10 2 DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; +num +3 +2 +SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); +str +test1 +test2 +SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; +num +3 +2 +SELECT a + 1 AS num FROM t1 HAVING 30 - num; +num +2 +3 +SELECT a + 1 AS num, num + 1 FROM t1; +ERROR 42S22: Unknown column 'num' in 'field list' +SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; +num (select num + 2 FROM t1 LIMIT 1) +2 4 +3 5 +SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; +ERROR 42S22: Unknown column 'num' in 'on clause' +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 1664afc70f9..a8024be7032 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -559,4 +559,20 @@ INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10); DROP TABLE t1; +# +# Bug #22457: Column alias in ORDER BY works, but not if in an expression +# + +CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); +SELECT a + 1 AS num FROM t1 ORDER BY 30 - num; +SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str); +SELECT a + 1 AS num FROM t1 GROUP BY 30 - num; +SELECT a + 1 AS num FROM t1 HAVING 30 - num; +--error 1054 +SELECT a + 1 AS num, num + 1 FROM t1; +SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; +--error 1054 +SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; +DROP TABLE t1; + # End of 4.1 tests -- cgit v1.2.1 From 7b126a91c00d7c2bca321da0de13f47220da1972 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 5 Nov 2006 22:42:23 +0400 Subject: merging mysql-test/r/gis-rtree.result: test result fixed --- mysql-test/r/gis-rtree.result | 15 ++++++++------- 1 file changed, 8 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 61a96afd8dc..95211ad9133 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -862,13 +862,6 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; -CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); -INSERT INTO t1(foo) VALUES (NULL); -ERROR 23000: Column 'foo' cannot be null -INSERT INTO t1() VALUES (); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -INSERT INTO t1(foo) VALUES (''); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); @@ -880,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); 1 1 DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; -- cgit v1.2.1 From 9f75d43ec864bfb84faffa9c1c943424f41f06db Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 6 Nov 2006 19:12:19 +0400 Subject: merging mysql-test/r/gis-rtree.result: merging fix --- mysql-test/r/gis-rtree.result | 15 ++++++++------- 1 file changed, 8 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 64b21ca988d..e8134a50496 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -862,13 +862,6 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; -CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); -INSERT INTO t1(foo) VALUES (NULL); -ERROR 23000: Column 'foo' cannot be null -INSERT INTO t1() VALUES (); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -INSERT INTO t1(foo) VALUES (''); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); @@ -880,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); 1 1 DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; -- cgit v1.2.1 From 030d080d8b4eb8154aa4da13b28c5b81cba09b03 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 6 Nov 2006 22:33:18 +0400 Subject: bug #19491 (5.0-related additional fixes) include/my_time.h: we need to use it outside the my_time.cc mysql-test/r/gis-rtree.result: result fixed sql-common/my_time.c: 'static' removed sql/field.cc: checks for invalid datetimes added --- mysql-test/r/gis-rtree.result | 15 ++++++++------- 1 file changed, 8 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 64b21ca988d..e8134a50496 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -862,13 +862,6 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; -CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); -INSERT INTO t1(foo) VALUES (NULL); -ERROR 23000: Column 'foo' cannot be null -INSERT INTO t1() VALUES (); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -INSERT INTO t1(foo) VALUES (''); -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); @@ -880,3 +873,11 @@ SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); 1 1 DROP TABLE t1; +CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); +INSERT INTO t1(foo) VALUES (NULL); +ERROR 23000: Column 'foo' cannot be null +INSERT INTO t1() VALUES (); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +INSERT INTO t1(foo) VALUES (''); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +DROP TABLE t1; -- cgit v1.2.1 From 5af4fd256321748a5ac7c8d4407f8ce977345e04 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 7 Nov 2006 18:16:17 +0200 Subject: Bug #11032: getObject() returns a String for a sub-query of type datetime - When returning metadata for scalar subqueries the actual type of the column was calculated based on the value type, which limits the actual type of a scalar subselect to the set of (currently) 3 basic types : integer, double precision or string. This is the reason that columns of types other then the basic ones (e.g. date/time) are reported as being of the corresponding basic type. Fixed by storing/returning information for the column type in addition to the result type. mysql-test/r/subselect.result: Bug #11032: getObject() returns a String for a sub-query of type datetime - test case mysql-test/t/subselect.test: Bug #11032: getObject() returns a String for a sub-query of type datetime - test case sql/item_subselect.cc: Bug #11032: getObject() returns a String for a sub-query of type datetime - store and return the field type as well in addition to result type for single row subqueries sql/item_subselect.h: Bug #11032: getObject() returns a String for a sub-query of type datetime - store and return the field type as well in addition to result type for single row subqueries --- mysql-test/r/subselect.result | 17 +++++++++++++++++ mysql-test/t/subselect.test | 16 ++++++++++++++++ 2 files changed, 33 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 28fbfc86657..a3d1bafcb0d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2997,3 +2997,20 @@ a a IN (SELECT a FROM t1) 2 NULL 3 1 DROP TABLE t1,t2; +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); +CREATE TABLE t2 AS SELECT +(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a +FROM t1 WHERE a > '2000-01-01'; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `sub_a` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ac035c72d18..11b7fcc4d8f 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1965,4 +1965,20 @@ SELECT a, a IN (SELECT a FROM t1) FROM t2; DROP TABLE t1,t2; +# +# Bug #11302: getObject() returns a String for a sub-query of type datetime +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25'); + +CREATE TABLE t2 AS SELECT + (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a + FROM t1 WHERE a > '2000-01-01'; +SHOW CREATE TABLE t2; + +CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); +SHOW CREATE TABLE t3; + +DROP TABLE t1,t2,t3; + # End of 4.1 tests -- cgit v1.2.1 From 0119e00f0dd6639c3f5a80aa67e40b0a0d670e99 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 9 Nov 2006 11:33:43 +0400 Subject: merging mysql-test/t/disabled.def: #22457 isn't fixed in 5.1 after merging from 5.0 --- mysql-test/t/disabled.def | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 8c4a76c78a9..44eec94f2db 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -44,3 +44,4 @@ rpl_multi_engine : BUG#22583 2006-09-23 lars #rpl_truncate_7ndb : BUG#21298 2006-07-27 msvensson ndb_binlog_discover : bug#21806 2006-08-24 ndb_autodiscover3 : bug#21806 +order_by : GKodinov - please fix bug #22457 in 5.1 also -- cgit v1.2.1 From 4b60bdc9c7e4cff239ec8e1839a497ff64c8cf09 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 9 Nov 2006 12:41:15 +0400 Subject: merging mysql-test/r/subselect.result: result fixed --- mysql-test/r/subselect.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0b72814797c..51d8227b9f3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3025,13 +3025,13 @@ FROM t1 WHERE a > '2000-01-01'; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `sub_a` datetime default NULL + `sub_a` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01'); SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `a` datetime default NULL + `a` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2,t3; create table t1 (df decimal(5,1)); -- cgit v1.2.1 From bffe8b8a3cab1c8a617428fae922d2b15bdb437b Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 9 Nov 2006 12:49:39 +0400 Subject: merging mysql-test/r/subselect3.result: 'filtered' column added mysql-test/r/udf.result: 'filtered' column added --- mysql-test/r/subselect3.result | 32 ++++++++++++++++---------------- mysql-test/r/udf.result | 24 ++++++++++++------------ 2 files changed, 28 insertions(+), 28 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 5ab8e448b39..10cc2729f3e 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -26,9 +26,9 @@ NULL 2 NULL explain extended select a, oref, a in (select max(ie) from t1 where oref=t2.oref group by grp) from t2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,(`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond(((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) @@ -36,9 +36,9 @@ from t1 where oref=t2.oref group by grp)` from `test`.`t2` explain extended select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where (`test`.`t2`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having ((`test`.`t2`.`a`) = (max(`test`.`t1`.`ie`))))) @@ -56,9 +56,9 @@ select ' ^ This must show 11' Z; Z ^ This must show 11 explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select (`test`.`t3`.`a`,(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond(((`test`.`t3`.`a`) = (max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3` drop table t1, t2, t3; @@ -79,9 +79,9 @@ oref a Z 4 NULL 0 explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using index; Using where Warnings: Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,(`test`.`t2`.`a`,(((`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`)))) AS `Z` from `test`.`t2` @@ -143,10 +143,10 @@ explain extended select a, oref, t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z from t3; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where -2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,(`test`.`t3`.`a`,(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((((`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond((`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 30c335a12c7..16a1c73af80 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -118,13 +118,13 @@ myfunc_int(a AS attr_name) 1 2 EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` SELECT a,c FROM v1; @@ -146,23 +146,23 @@ c 1 2 EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; -- cgit v1.2.1 From 59b45b5b0ab0bfe470919ba5175e79307928cd79 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 9 Nov 2006 16:55:42 +0200 Subject: Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs When compiling GROUP BY Item_ref instances are dereferenced in setup_copy_fields(), i.e. replaced with the corresponding Item_field (if they point to one) or Item_copy_string for the other cases. Since the Item_ref (in the Item_field case) is no longer used the information about the aliases stored in it is lost. Fixed by preserving the column, table and DB alias on dereferencing Item_ref mysql-test/r/metadata.result: Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs - test case mysql-test/t/metadata.test: Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs - test case sql/item.cc: Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs - use the table and db name to fill up the metadata for columns sql/sql_select.cc: Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs - preserve the field, table and DB name on dereferencing an Item_ref --- mysql-test/r/metadata.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/metadata.test | 19 +++++++++++++++++++ 2 files changed, 53 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 50b0b6ae294..34e961395c4 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -96,3 +96,37 @@ i 2 affected rows: 1 affected rows: 0 +create table t1 (id int(10)); +insert into t1 values (1); +CREATE VIEW v1 AS select t1.id as id from t1; +CREATE VIEW v2 AS select t1.id as renamed from t1; +CREATE VIEW v3 AS select t1.id + 12 as renamed from t1; +select * from v1 group by id limit 1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v1 group by id limit 0; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1000 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 0 Y 32768 0 63 +id +select * from v1 where id=1 group by id; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v1 id id 3 10 1 Y 32768 0 63 +id +1 +select * from v2 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 v2 id renamed 3 10 1 Y 32768 0 63 +renamed +1 +select * from v3 where renamed=1 group by renamed; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def v3 renamed 8 12 0 Y 32896 0 63 +renamed +drop table t1; +drop view v1,v2,v3; diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index 65338448555..a6ebfdc14c1 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -61,4 +61,23 @@ drop table t1;// delimiter ;// --disable_info +# +# Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs +# +--enable_metadata +create table t1 (id int(10)); +insert into t1 values (1); +CREATE VIEW v1 AS select t1.id as id from t1; +CREATE VIEW v2 AS select t1.id as renamed from t1; +CREATE VIEW v3 AS select t1.id + 12 as renamed from t1; +select * from v1 group by id limit 1; +select * from v1 group by id limit 0; +select * from v1 where id=1000 group by id; +select * from v1 where id=1 group by id; +select * from v2 where renamed=1 group by renamed; +select * from v3 where renamed=1 group by renamed; +drop table t1; +drop view v1,v2,v3; +--disable_metadata + # End of 4.1 tests -- cgit v1.2.1 From bbc5d13891e09447d399112dfa9fa1b9da929b64 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 10 Nov 2006 12:43:44 +0200 Subject: fixed bad 5.0->5.1 merge --- mysql-test/t/disabled.def | 1 - 1 file changed, 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 44eec94f2db..8c4a76c78a9 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -44,4 +44,3 @@ rpl_multi_engine : BUG#22583 2006-09-23 lars #rpl_truncate_7ndb : BUG#21298 2006-07-27 msvensson ndb_binlog_discover : bug#21806 2006-08-24 ndb_autodiscover3 : bug#21806 -order_by : GKodinov - please fix bug #22457 in 5.1 also -- cgit v1.2.1 From f53af7b8e5a8913af0625031304eb824b6330e4b Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 13 Nov 2006 12:28:55 +0200 Subject: Bug #19216: Client crashes on long SELECT The server sends a number of columns to the client. It uses a limited "fast" function for that instead of the general one. This fast function cannot send numbers larger than 2 bytes. This causes the client to expect smaller number of columns. The client writes outside of the allocated memory buffer as a result. Fixed the server to use the general function to send column count. Fixed the client to check the column count before writing column data. mysql-test/t/mysql_client.test: Bug #19216: Client crashes on long SELECT - test case sql/protocol.cc: Bug #19216: Client crashes on long SELECT - renamed the function for bether comprehention and made it local - used the right (non-local) function to transfer the column count in Protocol::send_fields sql/protocol.h: Bug #19216: Client crashes on long SELECT - made optimized net_store_length local sql-common/client.c: Bug #19216: Client crashes on long SELECT - fixed the client to check for older servers (without the fix). --- mysql-test/t/mysql_client.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/t/mysql_client.test b/mysql-test/t/mysql_client.test index b382357dacf..7bd7c762c5c 100644 --- a/mysql-test/t/mysql_client.test +++ b/mysql-test/t/mysql_client.test @@ -33,3 +33,21 @@ # --exec echo 'help' | $MYSQL > $MYSQLTEST_VARDIR/tmp/bug20328.tmp --exec echo 'help ' | $MYSQL > $MYSQLTEST_VARDIR/tmp/bug20328.tmp + +# +# Bug #19216: Client crashes on long SELECT +# +--exec echo "select" > $MYSQLTEST_VARDIR/tmp/b19216.tmp +# 3400 * 20 makes 68000 columns that is more than the max number that can fit +# in a 16 bit number. +let $i= 3400; +while ($i) +{ + --exec echo "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a'," >> $MYSQLTEST_VARDIR/tmp/b19216.tmp + dec $i; +} + +--exec echo "'b';" >> $MYSQLTEST_VARDIR/tmp/b19216.tmp +--disable_query_log +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null +--enable_query_log -- cgit v1.2.1 From 405dcd2df29e4693a0981c186d0c55b2f9a37b84 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 13 Nov 2006 15:37:04 +0200 Subject: merge 4.1->5.0 of the test suite for bug 19216 --- mysql-test/t/mysql.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index f3296e6f706..a1a4b40a3dd 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -142,6 +142,24 @@ drop table t1; --exec $MYSQL -e 'help ' > $MYSQLTEST_VARDIR/tmp/bug20328_2.result --exec diff $MYSQLTEST_VARDIR/tmp/bug20328_1.result $MYSQLTEST_VARDIR/tmp/bug20328_2.result +# +# Bug #19216: Client crashes on long SELECT +# +--exec echo "select" > $MYSQLTEST_VARDIR/tmp/b19216.tmp +# 3400 * 20 makes 68000 columns that is more than the max number that can fit +# in a 16 bit number. +let $i= 3400; +while ($i) +{ + --exec echo "'a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a','a'," >> $MYSQLTEST_VARDIR/tmp/b19216.tmp + dec $i; +} + +--exec echo "'b';" >> $MYSQLTEST_VARDIR/tmp/b19216.tmp +--disable_query_log +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/b19216.tmp >/dev/null +--enable_query_log + # # Bug #20103: Escaping with backslash does not work # -- cgit v1.2.1 From 1019dd404c273b722daa4039d42b1a920439e6b1 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 14 Nov 2006 19:50:44 +0300 Subject: Bug#20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view The regression is caused by the fix for bug 14767. When INSERT ... SELECT used a view in the SELECT list that was not inlined, and there was an active transaction, the server could crash in Query_cache::invalidate. On INSERT ... SELECT only the table being inserted into is invalidated. Thus views that can't be inlined are skipped from invalidation. The bug manifests itself in two ways so there is 2 test cases. One checks that the only the table being inserted into is invalidated. And the second one checks that there is no crash on INSERT ... SELECT. mysql-test/t/query_cache.test: Added a test case for bug#20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view mysql-test/r/query_cache.result: Added a test case for bug#20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view sql/sql_parse.cc: Bug#20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view On INSERT ... SELECT only the table being inserted into is invalidated. --- mysql-test/r/query_cache.result | 48 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/query_cache.test | 29 +++++++++++++++++++++++++ 2 files changed, 77 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 5224280e134..7645ad488cc 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1274,3 +1274,51 @@ Variable_name Value Last_query_cost 0.000000 drop table t1; SET GLOBAL query_cache_size=0; +set global query_cache_size=1024*1024; +flush status; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3); +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +create table t2 like t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 2 +insert into t2 select * from t1; +select * from t1; +a +1 +2 +3 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 3 +drop table t1, t2; +create table t1(c1 int); +create table t2(c1 int); +create table t3(c1 int); +create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; +start transaction; +insert into t1(c1) select c1 from v1; +drop table t1, t2, t3; +drop view v1; +set global query_cache_size=0; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index d416f34ce45..06eb76027b6 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -870,3 +870,32 @@ select * from t1 where a > 3; show status like 'last_query_cost'; drop table t1; SET GLOBAL query_cache_size=0; + +# +# Bug #20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view +# +set global query_cache_size=1024*1024; +flush status; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3); +select * from t1; +show status like 'Qcache_hits'; +select * from t1; +show status like 'Qcache_hits'; +create table t2 like t1; +select * from t1; +show status like 'Qcache_hits'; +insert into t2 select * from t1; +select * from t1; +show status like 'Qcache_hits'; +drop table t1, t2; + +create table t1(c1 int); +create table t2(c1 int); +create table t3(c1 int); +create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; +start transaction; +insert into t1(c1) select c1 from v1; +drop table t1, t2, t3; +drop view v1; +set global query_cache_size=0; -- cgit v1.2.1 From 3ebdcee5c64f29e7a4d4876b25f1e7afe6b52a60 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 17 Nov 2006 12:02:36 +0400 Subject: merging --- mysql-test/r/order_by.result | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index be29b310434..7b04c1acdc0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -760,13 +760,6 @@ xxxxxxxxxxxxxxxxxxxaa xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxz drop table t1; -create table t1 (a int not null, b int not null, c int not null); -insert t1 values (1,1,1),(1,1,2),(1,2,1); -select a, b from t1 group by a, b order by sum(c); -a b -1 2 -1 1 -drop table t1; create table t1 ( `sid` decimal(8,0) default null, `wnid` varchar(11) not null default '', @@ -881,6 +874,13 @@ num (select num + 2 FROM t1 LIMIT 1) SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; ERROR 42S22: Unknown column 'num' in 'on clause' DROP TABLE t1; +create table t1 (a int not null, b int not null, c int not null); +insert t1 values (1,1,1),(1,1,2),(1,2,1); +select a, b from t1 group by a, b order by sum(c); +a b +1 2 +1 1 +drop table t1; CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); INSERT INTO t1 VALUES (1,1), (2,2), (3,3); explain SELECT t1.b as a, t2.b as c FROM -- cgit v1.2.1 From 934a231825080f1c7812a67318f0cf83c05854a9 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 18 Nov 2006 21:49:59 +0400 Subject: merging mysql-test/t/disabled.def: meging bug --- mysql-test/t/disabled.def | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 0d3b7cdfdeb..63fa8440e43 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -32,3 +32,4 @@ rpl_multi_engine : BUG#22583 2006-09-23 lars #ndb_binlog_ddl_multi : BUG#18976 2006-04-10 kent CRBR: multiple binlog, second binlog may miss schema log events ndb_binlog_discover : bug#21806 2006-08-24 ndb_autodiscover3 : bug#21806 +udf : for GKodinov to fix. Your fix for #21809 stopped working in 5.1 after the merge. -- cgit v1.2.1 From 1e3f42b246dd39f1d2e59c79a42b455106f35038 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 20 Nov 2006 12:46:47 +0200 Subject: 5.0-opt -> 5.1-opt merge fixed. --- mysql-test/t/disabled.def | 1 - 1 file changed, 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 63fa8440e43..0d3b7cdfdeb 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -32,4 +32,3 @@ rpl_multi_engine : BUG#22583 2006-09-23 lars #ndb_binlog_ddl_multi : BUG#18976 2006-04-10 kent CRBR: multiple binlog, second binlog may miss schema log events ndb_binlog_discover : bug#21806 2006-08-24 ndb_autodiscover3 : bug#21806 -udf : for GKodinov to fix. Your fix for #21809 stopped working in 5.1 after the merge. -- cgit v1.2.1 From 1ae9f3b985c59f0c94922cb2bc3c186abae5c619 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 21 Nov 2006 13:45:01 +0400 Subject: Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash disable filling of I_S tables for EXPLAIN mysql-test/r/information_schema.result: Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash test case mysql-test/t/information_schema.test: Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash test case --- mysql-test/r/information_schema.result | 15 +++++++++++++++ mysql-test/t/information_schema.test | 14 ++++++++++++++ 2 files changed, 29 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 3fffce73aa9..43eedc19f12 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1254,3 +1254,18 @@ COLUMN_NAME MD5(COLUMN_DEFAULT) LENGTH(COLUMN_DEFAULT) COLUMN_DEFAULT=get_value( fld1 7cf7a6782be951a1f2464a350da926a5 65532 1 DROP TABLE bug23037; DROP FUNCTION get_value; +create view v1 as +select table_schema as object_schema, +table_name as object_name, +table_type as object_type +from information_schema.tables +order by object_schema; +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY system NULL NULL NULL NULL 0 const row not found +2 DERIVED tables ALL NULL NULL NULL NULL 2 Using filesort +explain select * from (select table_name from information_schema.tables) as a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY system NULL NULL NULL NULL 0 const row not found +2 DERIVED tables ALL NULL NULL NULL NULL 2 +drop view v1; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 27007bbe16a..dd203add344 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -973,4 +973,18 @@ DROP FUNCTION get_value; + +# +# Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash +# +create view v1 as +select table_schema as object_schema, + table_name as object_name, + table_type as object_type +from information_schema.tables +order by object_schema; +explain select * from v1; +explain select * from (select table_name from information_schema.tables) as a; +drop view v1; + # End of 5.0 tests. -- cgit v1.2.1 From c0c319395160ec07c609a9628c6f66485852a33a Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 21 Nov 2006 16:45:05 +0400 Subject: after merge fix --- mysql-test/r/information_schema.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 2caa11f245b..9db11883db4 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1338,7 +1338,7 @@ order by object_schema; explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 0 const row not found -2 DERIVED tables ALL NULL NULL NULL NULL 2 Using filesort +2 DERIVED tables ALL NULL NULL NULL NULL 0 Using filesort explain select * from (select table_name from information_schema.tables) as a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 0 const row not found -- cgit v1.2.1