From 1a0e1f1328c933d63e0bb0d99b9011058abd82cd Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 21 Nov 2006 10:11:43 +0200 Subject: Bug#23556: TRUNCATE TABLE still maps to DELETE This is the 5.0 part of the fix. Currently TRUNCATE command will not call delete_all_rows() in the handler (that implements the "fast" TRUNCATE for InnoDB) when there are triggers on the table. As decided by the architecture team TRUNCATE must use "fast" TRUNCATE even when there are triggers. Thus it must ignore the triggers. Made TRUNCATE to ignore the triggers and call delete_all_rows() for all storage engines to maintain engine consistency. mysql-test/r/trigger.result: Bug#23556: TRUNCATE TABLE still maps to DELETE - test case mysql-test/t/trigger.test: Bug#23556: TRUNCATE TABLE still maps to DELETE - test case sql/sql_delete.cc: Bug#23556: TRUNCATE TABLE still maps to DELETE - We implemenent fast TRUNCATE for InnoDB even if triggers are present. - TRUNCATE ignores triggers. --- mysql-test/r/trigger.result | 25 +++++++++++++++++++++++++ mysql-test/t/trigger.test | 25 +++++++++++++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 5d643057666..54534ea5930 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1241,4 +1241,29 @@ i j 2 2 13 13 drop table t1; +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW +INSERT INTO t2 VALUES (OLD.a); +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 0 +SELECT COUNT(*) FROM t2; +COUNT(*) +0 +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 8 +SELECT COUNT(*) FROM t2; +COUNT(*) +8 +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 92320648033..dd3293b815f 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1498,5 +1498,30 @@ update t1 set i= i+ 10 where j > 2; select * from t1; drop table t1; +# +# Bug#23556 TRUNCATE TABLE still maps to DELETE +# +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW + INSERT INTO t2 VALUES (OLD.a); + +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; + +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +SELECT COUNT(*) FROM t2; + +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; --echo End of 5.0 tests -- cgit v1.2.1 From da561a802caff95d755b8d2061c27c248f22e3cd Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 25 Jan 2007 18:44:35 -0800 Subject: Fixed bug #24653. The bug report has demonstrated the following two problems. 1. If an ORDER/GROUP BY list includes a constant expression being optimized away and, at the same time, containing single-row subselects that return more that one row, no error is reported. Strictly speaking the standard allows to ignore error in this case. Yet, now a corresponding fatal error is reported in this case. 2. If a query requires sorting by expressions containing single-row subselects that, however, return more than one row, then the execution of the query may cause a server crash. To fix this some code has been added that blocks execution of a subselect item in case of a fatal error in the method Item_subselect::exec. mysql-test/r/subselect.result: Added a test cases for bug #24653. mysql-test/t/subselect.test: Added a test cases for bug #24653. sql/filesort.cc: Fixed bug #24653. Added a check for fatal error after reading the next row from the table in the function find_all_keys. sql/item.cc: Fixed bug #24653. Down-ported calculation of the attribute with_subselect of for Item objects. sql/item.h: Fixed bug #24653. Down-ported calculation of the attribute with_subselect of for Item objects. sql/item_cmpfunc.cc: Fixed bug #24653. Down-ported calculation of the attribute with_subselect of for Item objects. sql/item_cmpfunc.h: Fixed bug #24653. Down-ported calculation of the attribute with_subselect of for Item objects. sql/item_func.cc: Fixed bug #24653. Down-ported calculation of the attribute with_subselect of for Item objects. sql/item_subselect.cc: Fixed bug #24653. Added a check for fatal error in the method Item_subselect::exec to block evaluation of subselects in erroneous situations. Down-ported calculation of the attribute with_subselect of for Item objects. sql/sql_select.cc: Fixed bug #24653. Added a check to verify that any constant expression used in ORDER BY and/or GROUP BY lists which is optimized away does not contain subselects returning more than one row. If it does a fatal error is reported. --- mysql-test/r/subselect.result | 74 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect.test | 59 ++++++++++++++++++++++++++++++++++ 2 files changed, 133 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a4ea8e21d61..a339a139687 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3026,3 +3026,77 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP TABLE t1; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); +CREATE TABLE t2 (b int, c int); +INSERT INTO t2 VALUES +(2,1), (1,3), (2,1), (4,4), (2,2), (1,4); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); +a +2 +4 +1 +3 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +a +1 +2 +3 +4 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; +ERROR 21000: Subquery returns more than 1 row +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); +b MAX(c) +1 4 +2 2 +4 4 +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +a +1 +2 +3 +4 +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +a +4 +SELECT a FROM t1 GROUP BY a +HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +a +2 +4 +1 +3 +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); +ERROR 21000: Subquery returns more than 1 row +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +a +2 +1 +3 +4 +SELECT a FROM t1 +ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), +(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index f816551e51f..67a18e7a30f 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1993,4 +1993,63 @@ SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; DROP TABLE t1; + +# +# Bug 24653: sorting by expressions containing subselects +# that return more than one row +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); + +CREATE TABLE t2 (b int, c int); +INSERT INTO t2 VALUES + (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); + +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; + +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); +--error 1242 +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); + + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +DROP TABLE t1,t2; + # End of 4.1 tests -- cgit v1.2.1 From 1495924319c1b6fab0261a7206e872f43f0c4857 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 26 Jan 2007 17:10:45 -0800 Subject: Fixed bug #24420. Objects of the classes Item_func_is_not_null_test and Item_func_trig_cond must be transparent for the method Item::split_sum_func2 as these classes are pure helpers. It means that the method Item::split_sum_func2 should look at those objects as at pure wrappers. mysql-test/r/subselect3.result: Added a test case for bug #24420. mysql-test/t/subselect3.test: Added a test case for bug #24420. --- mysql-test/r/subselect3.result | 16 ++++++++++++++++ mysql-test/t/subselect3.test | 17 +++++++++++++++++ 2 files changed, 33 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 07ff17c9df9..29143b9e504 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -629,3 +629,19 @@ cc NULL NULL aa 1 1 bb NULL NULL drop table t1,t2; +create table t1 (a int, b int); +insert into t1 values (0,0), (2,2), (3,3); +create table t2 (a int, b int); +insert into t2 values (1,1), (3,3); +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; +a b Z +0 0 0 +2 2 0 +3 3 1 +insert into t2 values (NULL,4); +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; +a b Z +0 0 0 +2 2 0 +3 3 1 +drop table t1,t2; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 23d78721dbe..ed8480ba464 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -472,3 +472,20 @@ select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z drop table t1,t2; +# +# BUG#24420: row-based IN suqueries with aggregation when the left operand +# of the subquery predicate may contain NULL values +# + +create table t1 (a int, b int); +insert into t1 values (0,0), (2,2), (3,3); +create table t2 (a int, b int); +insert into t2 values (1,1), (3,3); + +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; + +insert into t2 values (NULL,4); +select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; + +drop table t1,t2; + -- cgit v1.2.1 From 3cb3a9a149ecc29634c748d0da7932d1814a9b7f Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 30 Jan 2007 17:43:34 +0200 Subject: Bug #25643: SEC_TO_TIME function problem Checking for NULL before calling the val_xxx() methods only checks for such arguments that are known to be NULLs at compile time. The arguments that may or may not contain NULLs (e.g. function calls and possibly others) are not checked at all. Fixed by first calling the val_xxx() method and then checking for null in SEC_TO_TIME(). In addition QUARTER() was not returning 0 (as all the val_int() functions do when processing a NULL value). mysql-test/r/func_time.result: Bug #25643: SEC_TO_TIME function problem - test case mysql-test/t/func_time.test: Bug #25643: SEC_TO_TIME function problem - test case sql/item_timefunc.cc: Bug #25643: SEC_TO_TIME function problem - null handling fixed for QUARTER() and SEC_TO_TIME() --- mysql-test/r/func_time.result | 14 ++++++++++++++ mysql-test/t/func_time.test | 12 ++++++++++++ 2 files changed, 26 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 45c05f0b5b5..32f930ca6ba 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1207,3 +1207,17 @@ SET NAMES DEFAULT; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE NULL +CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), +(2, '11:00:00', '11:15:00', '1972-02-06'); +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) +FROM t1; +t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) +10:00:00 NULL NULL NULL +11:00:00 11:15:00 00:15:00 1 +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) +FROM t1 ORDER BY a DESC; +t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) +11:00:00 11:15:00 00:15:00 1 +10:00:00 NULL NULL NULL +DROP TABLE t1; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index a69cbb67c5b..1aa4b434a83 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -713,3 +713,15 @@ SET NAMES DEFAULT; # select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; + +# +# Bug #25643: SEC_TO_TIME function problem +# +CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), + (2, '11:00:00', '11:15:00', '1972-02-06'); +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) + FROM t1; +SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) + FROM t1 ORDER BY a DESC; +DROP TABLE t1; -- cgit v1.2.1 From d01ec5e72704513ce0e13fe5dd924c088757507e Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 31 Jan 2007 10:18:26 +0200 Subject: Bug #25551: inconsistent behaviour in grouping NULL, depending on index type The optimizer takes away columns from GROUP BY/DISTINCT if they constitute all the parts of an unique index. However if some of the columns can contain NULLs this cannot be done (because an UNIQUE index can have multiple rows with NULL values). Fixed by not using UNIQUE indexes with nullable columns to remove grouping columns from GROUP BY/DISTINCT. mysql-test/r/distinct.result: Bug #25551: inconsistent behaviour in grouping NULL, depending on index type - test case mysql-test/t/distinct.test: Bug #25551: inconsistent behaviour in grouping NULL, depending on index type - test case sql/sql_select.cc: Bug #25551: inconsistent behaviour in grouping NULL, depending on index type - UNIQUE NULL indices don't guarantee GROUP BY/DISTINCT. --- mysql-test/r/distinct.result | 26 +++++++++++++++++++++++++- mysql-test/t/distinct.test | 17 ++++++++++++++++- 2 files changed, 41 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 32151305698..3508a83a810 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -530,7 +530,8 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b)); +CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, +PRIMARY KEY (a,b)); INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT DISTINCT a FROM t2; id select_type table type possible_keys key key_len ref rows Extra @@ -644,3 +645,26 @@ SELECT COUNT(*) FROM COUNT(*) 2 DROP TABLE t1, t2; +CREATE TABLE t1 (a INT, UNIQUE (a)); +INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT DISTINCT a FROM t1; +a +NULL +1 +2 +3 +4 +EXPLAIN SELECT a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 Using index +SELECT a FROM t1 GROUP BY a; +a +NULL +1 +2 +3 +4 +DROP TABLE t1; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 8734b940241..476e4ce7735 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -364,7 +364,8 @@ EXPLAIN SELECT a FROM t1 GROUP BY a; EXPLAIN SELECT a,b FROM t1 GROUP BY a,b; EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b; -CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b)); +CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, + PRIMARY KEY (a,b)); INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT DISTINCT a FROM t2; EXPLAIN SELECT DISTINCT a,a FROM t2; @@ -525,3 +526,17 @@ SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; DROP TABLE t1, t2; + +# +# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type +# +CREATE TABLE t1 (a INT, UNIQUE (a)); +INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3); +EXPLAIN SELECT DISTINCT a FROM t1; +#result must have one row with NULL +SELECT DISTINCT a FROM t1; +EXPLAIN SELECT a FROM t1 GROUP BY a; +#result must have one row with NULL +SELECT a FROM t1 GROUP BY a; + +DROP TABLE t1; -- cgit v1.2.1 From fbc16a85c2e91385c8d4cce7ab32d092025c76ad Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 31 Jan 2007 16:04:38 +0200 Subject: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join Two problems here: Problem 1: While constructing the join columns list the optimizer does as follows: 1. Sets the join_using_fields/natural_join members of the right JOIN operand. 2. Makes a "table reference" (TABLE_LIST) to parent the two tables. 3. Assigns the join_using_fields/is_natural_join of the wrapper table using join_using_fields/natural_join of the rightmost table 4. Sets join_using_fields to NULL for the right JOIN operand. 5. Passes the parent table up to the same procedure on the upper level. Step 1 overrides the the join_using_fields that are set for a nested join wrapping table in step 4. Fixed by making a designated variable SELECT_LEX::prev_join_using to pass the data from step 1 to step 4 without destroying the wrapping table data. Problem 2: The optimizer checks for ambiguous columns while transforming NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no distinction between columns that are used in the generated join condition (where ambiguity can be checked) and the other columns (where ambiguity can be checked only when resolving references coming from outside the JOIN construct itself). Fixed by allowing the non-USING columns to be present in multiple copies in both sides of the join and moving the ambiguity check to the place where unqualified references to the join columns are resolved (find_field_in_natural_join()). mysql-test/r/join_nested.result: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - test case mysql-test/t/join_nested.test: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - test case sql/mysql_priv.h: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - use SELECT_LEX to store the ref to JOIN USING list needed by the parser sql/sql_base.cc: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - proper check for duplicate cols - more detailed debug output sql/sql_lex.h: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - use SELECT_LEX to store the ref to JOIN USING list needed by the parser sql/sql_parse.cc: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - proper check for duplicate cols in JOIN USING sql/sql_yacc.yy: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - use SELECT_LEX to store the ref to JOIN USING list needed by the parser sql/table.cc: BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join - return null if no table ref (as in nested join columns). --- mysql-test/r/join_nested.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/join_nested.test | 39 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 67 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index f5c98f383b7..006488f9d43 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1605,3 +1605,31 @@ WHERE t1.id='5'; id ct pc nm 5 NULL NULL NULL DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +b +SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +ERROR 23000: Column 'c' in field list is ambiguous +SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); +b +SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); +ERROR 23000: Column 'c' in field list is ambiguous +DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, b INT); +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); +INSERT INTO t3 VALUES (1,1); +SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a); +ERROR 23000: Column 'a' in from clause is ambiguous +DROP TABLE t1,t2,t3; +End of 5.0 tests diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index e7405418be7..f29366797f6 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1045,3 +1045,42 @@ SELECT t1.*, t4.nm WHERE t1.id='5'; DROP TABLE t1,t2,t3,t4; + +# +# BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); + +SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); + +--error ER_NON_UNIQ_ERROR +SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); + +SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); + +--error ER_NON_UNIQ_ERROR +SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) +JOIN t5 USING (a)) USING (a); + +DROP TABLE t1,t2,t3,t4,t5; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, b INT); + +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); +INSERT INTO t3 VALUES (1,1); + +--error ER_NON_UNIQ_ERROR +SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a); + +DROP TABLE t1,t2,t3; + +--echo End of 5.0 tests -- cgit v1.2.1 From c15b2e4152854f48ba3f43ace52103010bca603d Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 31 Jan 2007 19:31:36 -0800 Subject: Fixed bug #25407. The bug could cause choosing a sub-optimal execution plan for a single-table query if a unique index with many null keys were defined for the table. It happened because the code of the check_quick_keys function made an assumption that any key may occur in an unique index only once. Yet this is not true for keys with nulls that may have multiple occurrences in the index. mysql-test/r/null_key.result: Fixed bug #25407 Adjusted result after the fix. mysql-test/r/select.result: Added a test case for bug #25407. mysql-test/t/select.test: Added a test case for bug #25407. --- mysql-test/r/null_key.result | 4 +-- mysql-test/r/select.result | 86 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/select.test | 74 ++++++++++++++++++++++++++++++++++++++ 3 files changed, 162 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 6eb3cf312a0..0b4ed15f659 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -30,7 +30,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index +1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index @@ -258,7 +258,7 @@ INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4 INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 1 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5dcf830ca97..f3938fd6413 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3642,3 +3642,89 @@ INSERT into t1 values (1), (2), (3); SELECT * FROM t1 LIMIT 2, -1; 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 '-1' at line 1 DROP TABLE t1; +CREATE TABLE t1 ( +ID_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; +COUNT(*) +128 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; +CREATE TABLE t1 ( +ID1_with_null int NULL, +ID2_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID1_with_null, ID2_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), +(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; +COUNT(*) +192 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND +(ID2_with_null=1 OR ID2_with_null=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 19b625451d4..19c7d742f5b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3133,3 +3133,77 @@ SELECT * FROM t1 LIMIT 2, -1; DROP TABLE t1; +# +# 25407: wrong estimate of NULL keys for unique indexes +# + +CREATE TABLE t1 ( + ID_with_null int NULL, + ID_better int NOT NULL, + INDEX idx1 (ID_with_null), + INDEX idx2 (ID_better) +); + +INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; + +SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_better=1; + +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; + +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); + +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; + +DROP TABLE t1; + +CREATE TABLE t1 ( + ID1_with_null int NULL, + ID2_with_null int NULL, + ID_better int NOT NULL, + INDEX idx1 (ID1_with_null, ID2_with_null), + INDEX idx2 (ID_better) +); + +INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), + (3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); + +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; + +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_better=1; + +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; + +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); + +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +EXPLAIN SELECT * FROM t1 + WHERE ID_better=1 AND ID1_with_null IS NULL AND + (ID2_with_null=1 OR ID2_with_null=2); + +DROP TABLE t1; -- cgit v1.2.1 From 0c523325120825d6462f5c82ddf40d9b41c8b527 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 1 Feb 2007 13:25:50 +0200 Subject: trigger.result: merge of the 5.0-opt tree mysql-test/r/trigger.result: merge of the 5.0-opt tree --- mysql-test/r/trigger.result | 50 ++++++++++++++++++++++----------------------- 1 file changed, 25 insertions(+), 25 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 340b775cb85..fe0e444f87d 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1241,6 +1241,31 @@ i j 2 2 13 13 drop table t1; +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW +INSERT INTO t2 VALUES (OLD.a); +FLUSH STATUS; +TRUNCATE t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 0 +SELECT COUNT(*) FROM t2; +COUNT(*) +0 +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +DELETE FROM t2; +FLUSH STATUS; +DELETE FROM t1; +SHOW STATUS LIKE 'handler_delete'; +Variable_name Value +Handler_delete 8 +SELECT COUNT(*) FROM t2; +COUNT(*) +8 +DROP TRIGGER trg_t1; +DROP TABLE t1,t2; drop table if exists t1; drop function if exists f1; create table t1 (i int); @@ -1310,29 +1335,4 @@ SELECT fubar_id FROM t2; fubar_id 1 DROP TABLE t1,t2; -CREATE TABLE t1 (a INT PRIMARY KEY); -CREATE TABLE t2 (a INT PRIMARY KEY); -INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); -CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW -INSERT INTO t2 VALUES (OLD.a); -FLUSH STATUS; -TRUNCATE t1; -SHOW STATUS LIKE 'handler_delete'; -Variable_name Value -Handler_delete 0 -SELECT COUNT(*) FROM t2; -COUNT(*) -0 -INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); -DELETE FROM t2; -FLUSH STATUS; -DELETE FROM t1; -SHOW STATUS LIKE 'handler_delete'; -Variable_name Value -Handler_delete 8 -SELECT COUNT(*) FROM t2; -COUNT(*) -8 -DROP TRIGGER trg_t1; -DROP TABLE t1,t2; End of 5.0 tests -- cgit v1.2.1 From a30830460794651c0e9fc5ec4779cf77680514ee Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 1 Feb 2007 19:12:45 +0400 Subject: Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail additional call of file->extra() method with HA_EXTRA_NO_CACHE parameter mysql-test/r/information_schema.result: test result mysql-test/t/information_schema.test: test case sql/sql_show.cc: additional call of file->extra() method with HA_EXTRA_NO_CACHE parameter --- mysql-test/r/information_schema.result | 10 ++++++++++ mysql-test/t/information_schema.test | 12 ++++++++++++ 2 files changed, 22 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 21d7bfb1b21..b93a4c28849 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1269,3 +1269,13 @@ 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; +create table t1 (f1 int(11)); +create table t2 (f1 int(11), f2 int(11)); +select table_name from information_schema.tables +where table_schema = 'test' and table_name not in +(select table_name from information_schema.columns +where table_schema = 'test' and column_name = 'f3'); +table_name +t1 +t2 +drop table t1,t2; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 623ccee49e4..d1dd485e21c 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -987,4 +987,16 @@ explain select * from v1; explain select * from (select table_name from information_schema.tables) as a; drop view v1; +# +# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail +# +create table t1 (f1 int(11)); +create table t2 (f1 int(11), f2 int(11)); + +select table_name from information_schema.tables +where table_schema = 'test' and table_name not in +(select table_name from information_schema.columns + where table_schema = 'test' and column_name = 'f3'); +drop table t1,t2; + # End of 5.0 tests. -- cgit v1.2.1