| Bug #49198 | Wrong result for second call of procedure with view in subselect | ||
|---|---|---|---|
| Submitted: | 30 Nov 2009 8:55 | Modified: | 23 Nov 2010 3:06 |
| Reporter: | Jørgen Løland | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Tor Didriksen | CPU Architecture: | Any |
| Tags: | optimizer_switch, semijoin, SP, VIEW | ||
[30 Nov 2009 9:02]
Jørgen Løland
semijoin=off (produces correct result): --------------------------------------- EXPLAIN EXTENDED SELECT t1field FROM t1 WHERE t1field IN ( SELECT v1_field FROM v1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 2 SUBQUERY A index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 4 SUBQUERY t1 index NULL PRIMARY 4 NULL 2 100.00 Using index Warnings: Note 1003 select `t1`.`t1field` AS `t1field` from `test`.`t1` where <in_optimizer>(`t1`.`t1field`,`t1`.`t1field` in ( <materialize> (select `test`.`A`.`t1field` AS `v1_field` from `test`.`t1` `A` where <in_optimizer>(`test`.`A`.`t1field`,`test`.`A`.`t1field` in ( <materialize> (select `test`.`t1`.`t1field` AS `t1field` from `test`.`t1` ), <primary_index_lookup>(`test`.`A`.`t1field` in <temporary table> on distinct_key where ((`test`.`A`.`t1field` = `materialized subselect`.`t1field`))))) ), <primary_index_lookup>(`t1`.`t1field` in <temporary table> on distinct_key where ((`t1`.`t1field` = `materialized subselect`.`v1_field`))))) semijoin=on: ------------ EXPLAIN EXTENDED SELECT t1field FROM t1 WHERE t1field IN ( SELECT v1_field FROM v1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index 1 PRIMARY A eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 100.00 Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 select `t1`.`t1field` AS `t1field` from `test`.`t1` `A` join `test`.`t1` where ((`test`.`A`.`t1field` = `t1`.`t1field`) and <in_optimizer>(`t1`.`t1field`,<exists>(<primary_index_lookup>(<cache>(`test`.`A`.`t1field`) in t1 on PRIMARY))))
[30 Nov 2009 9:03]
Valeriy Kravchuk
Verified just as described with recent 6.0.14 from bzr on Linux:
openxs@suse:/home2/openxs/dbs/6.0-code> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE t1 ( t1field integer, primary key (t1field));
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> CREATE VIEW v1 AS
-> SELECT t1field as v1_field
-> FROM t1 A
-> WHERE A.t1field IN (SELECT t1field FROM t1 );
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> SELECT t1field
-> FROM t1
-> WHERE t1field IN ( SELECT v1_field FROM v1);
-> END//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> INSERT INTO t1 VALUES(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT t1field
-> FROM t1
-> WHERE t1field IN ( SELECT v1_field FROM v1);
+---------+
| t1field |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.01 sec)
mysql> CALL p1;
+---------+
| t1field |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL p1;
+---------+
| t1field |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set session optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure p1;
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> SELECT t1field
-> FROM t1
-> WHERE t1field IN ( SELECT v1_field FROM v1);
-> END//
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> CALL p1;
+---------+
| t1field |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> CALL p1;
+---------+
| t1field |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p1;
+---------+
| t1field |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
[11 Dec 2009 7:21]
Jørgen Løland
A changeset from the last few days has made the attached test crash on second execution instead of producing a wrong result set. The current revision is: revision-id: alik@sun.com-20091211062703-09sznom962nf54aj date: 2009-12-11 09:27:03 +0300 build-date: 2009-12-11 08:22:24 +0100 revno: 3767 branch-nick: mysql-6.0-codebase-bugfixing
[16 Dec 2009 10:25]
Jørgen Løland
bzr find reports this: # Regression source: roy.lyseng@sun.com-20091208125053-gjhwf9lnq61tawr5 roy.lyseng@sun.com-20091208125053-gjhwf9lnq61tawr5
[16 Dec 2009 10:26]
Jørgen Løland
^ appears to be revision that made the test script crash instead of return wrong result on second execution.
[16 Dec 2009 11:38]
Jørgen Løland
Reverting to 2009-12-07 (before the test started to crash), this diff (found during investigation of bug#49453) produces correct result on the second execution: @@ -6681,11 +6615,9 @@ void Item_outer_ref::fix_after_pullout(s === modified file 'sql/item.cc' @@ -6681,11 +6615,9 @@ void Item_outer_ref::fix_after_pullout(s void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) { + (*ref)->fix_after_pullout(new_parent, ref); if (depended_from == new_parent) - { - (*ref)->fix_after_pullout(new_parent, ref); depended_from= NULL; - } }
[5 Jan 2010 15:00]
Jørgen Løland
The newly introduced crash has been reported as BUG#50089.
[28 Jan 2010 12:13]
Tor Didriksen
An even simpler test case: CREATE TABLE t1 ( t1field integer, primary key (t1field)); CREATE TABLE t2 ( t2field integer, primary key (t2field)); CREATE VIEW v1 AS SELECT * FROM t2 ; INSERT INTO t1 VALUES(1),(2); INSERT INTO t2 VALUES(1),(2); prepare stmt from " SELECT t1field FROM t1 WHERE t1field IN ( SELECT * FROM v1); "; execute stmt; execute stmt; drop table t1; drop table t2; drop view v1;
[28 Jan 2010 15:00]
Tor Didriksen
With a little extra debug output,
we see what happens on the second and subsequent executions:
- first we mark t2.t2field as dependent (stack trace below)
- then check_simple_equality((`test`.`t1`.`t1field` = `test`.`t2`.`t2field`))
returns FALSE because of this test:
if (left_item->type() == Item::FIELD_ITEM &&
right_item->type() == Item::FIELD_ITEM &&
!((Item_field*)left_item)->depended_from() &&
!((Item_field*)right_item)->depended_from())
- we end up with a wrong execution plan
T@4 : | | | | | | | | | | >setup_conds
T@4 : | | | | | | | | | | | info: thd->mark_used_columns: 1
T@4 : | | | | | | | | | | | >Item_cond::fix_fields
T@4 : | | | | | | | | | | | | >Item_func::fix_fields
T@4 : | | | | | | | | | | | | | >Item_field::fix_fields
T@4 : | | | | | | | | | | | | | | >find_field_in_table
T@4 : | | | | | | | | | | | | | | | enter: table: 't1', field name: 't1field'
T@4 : | | | | | | | | | | | | | | | >update_field_dependencies
T@4 : | | | | | | | | | | | | | | | | note: Field found before
T@4 : | | | | | | | | | | | | | | | <update_field_dependencies 5294
T@4 : | | | | | | | | | | | | | | <find_field_in_table 5592
T@4 : | | | | | | | | | | | | | <Item_field::fix_fields 4692
T@4 : | | | | | | | | | | | | | >Item_direct_view_ref::fix_fields
T@4 : | | | | | | | | | | | | | | >Item_field::fix_fields
T@4 : | | | | | | | | | | | | | | | >find_field_in_table
T@4 : | | | | | | | | | | | | | | | | enter: table: 't2', field name: 't2field'
T@4 : | | | | | | | | | | | | | | | | >update_field_dependencies
T@4 : | | | | | | | | | | | | | | | | <update_field_dependencies 5302
T@4 : | | | | | | | | | | | | | | | <find_field_in_table 5592
T@4 : | | | | | | | | | | | | | | | >Item_field::fix_outer_field
T@4 : | | | | | | | | | | | | | | | | >mark_as_dependent
T@4 : | | | | | | | | | | | | | | | | | info: set_depended_from this:0x7f2dbc171ab0 val:0x7f2dbc170688
T@4 : | | | | | | | | | | | | | | | | <mark_as_dependent 3851
[29 Jan 2010 15:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/98661 3859 Tor Didriksen 2010-01-29 Bug #49198 Wrong result for second call of procedure with view in subselect The problem was that fix_after_pullout() after semijoin conversion wasn't propagated from the view to the underlying table. On subesequent executions of the prepared statement, we would mark the underlying table as 'dependent' and the predicate anlysis would lead to a different (and illegal) execution plan. @ mysql-test/r/subselect_sj.result Add test cases. @ mysql-test/r/subselect_sj_jcl6.result Add test cases. @ mysql-test/t/subselect_sj.test Add test cases. @ sql/item.cc Always propagate the fix_after_pullout() to the referenced object in Item_ref::fix_after_pullout() Add DBUG_ENTER/DBUG_RETURN tracing in misc places. @ sql/item_cmpfunc.cc Add DBUG_ENTER/DBUG_RETURN tracing in misc places. @ sql/item_func.cc Add DBUG_ENTER/DBUG_RETURN tracing in misc places. @ sql/sql_base.cc Do not call DBUG_RETURN(some_function_which_also_has_dbug_trace()) as the corresponding trace/indentation gets messed up. @ sql/sql_select.cc Add DBUG_ENTER/DBUG_RETURN tracing in misc places.
[1 Feb 2010 15:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/98836 3860 Tor Didriksen 2010-02-01 Bug #49198 Wrong result for second call of procedure with view in subselect The problem was that fix_after_pullout() after semijoin conversion wasn't propagated from the view to the underlying table. On subesequent executions of the prepared statement, we would mark the underlying table as 'dependent' and the predicate anlysis would lead to a different (and illegal) execution plan. @ mysql-test/r/subselect_sj.result Add test cases. @ mysql-test/r/subselect_sj_jcl6.result Add test cases. @ mysql-test/t/subselect_sj.test Add test cases. @ sql/item.cc Always propagate the fix_after_pullout() to the referenced object in Item_ref::fix_after_pullout() @ sql/sql_base.cc Do not call DBUG_RETURN(some_function_which_also_has_dbug_trace()) as the corresponding trace/indentation gets messed up.
[8 Feb 2010 11:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/99581 3872 Tor Didriksen 2010-02-08 Bug #49198 Wrong result for second call of procedure with view in subselect The problem was that fix_after_pullout() after semijoin conversion wasn't propagated from the view to the underlying table. On subesequent executions of the prepared statement, we would mark the underlying table as 'dependent' and the predicate anlysis would lead to a different (and illegal) execution plan. @ mysql-test/r/subselect_sj.result Add test cases. @ mysql-test/r/subselect_sj_jcl6.result Add test cases. @ mysql-test/t/subselect_sj.test Add test cases. @ sql/item.cc Always propagate the fix_after_pullout() to the referenced object in Item_direct_view_ref::fix_after_pullout() @ sql/item.h Add Item_direct_view_ref::fix_after_pullout()
[8 Feb 2010 14:05]
Tor Didriksen
Pushed to bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing
[13 Feb 2010 8:37]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 0:43]
Paul DuBois
Noted in 6.0.14 changelog. With semijoin optimization enabled, the second execution of a prepared statement that referenced a view in a subquery could produce incorrect results.
[25 Feb 2010 8:52]
Sergey Petrunya
It is wrong to put DBUG_ASSERT(NULL == depended_from); into Item_ref::fix_after_pullout(). See BUG#51487 for a counterexample.
[15 Apr 2010 11:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105720 3076 Tor Didriksen 2010-04-15 Bug #49198 Wrong result for second call of procedure with view in subselect Backport of tor.didriksen@sun.com-20100208113127-g8wvxcpc2dppmxxd The problem was that fix_after_pullout() after semijoin conversion wasn't propagated from the view to the underlying table. On subesequent executions of the prepared statement, we would mark the underlying table as 'dependent' and the predicate anlysis would lead to a different (and illegal) execution plan. @ mysql-test/r/subselect_sj.result Add test cases. @ mysql-test/t/subselect_sj.test Add test cases. @ sql/item.cc Always propagate the fix_after_pullout() to the referenced object in Item_direct_view_ref::fix_after_pullout() @ sql/item.h Add Item_direct_view_ref::fix_after_pullout()
[16 Aug 2010 6:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:17]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:06]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
Description: With semijoin=on, the procedure p1 (see below) misses a record on second call. Turning semijoin off removes the problem, but this also turns off "in to exists" optimization which is in 5.1. The 5.1 server produces correct result even if in to exists optimization is used. How to repeat: --------- Test script -------- CREATE TABLE t1 ( t1field integer, primary key (t1field)); CREATE VIEW v1 AS SELECT t1field as v1_field FROM t1 A WHERE A.t1field IN (SELECT t1field FROM t1 ); delimiter |; CREATE PROCEDURE p1 () BEGIN SELECT t1field FROM t1 WHERE t1field IN ( SELECT v1_field FROM v1); END| delimiter ;| INSERT INTO t1 VALUES(1),(2); SELECT t1field FROM t1 WHERE t1field IN ( SELECT v1_field FROM v1); CALL p1; CALL p1; --------------------------- ------- Output ------------ SELECT t1field FROM t1 WHERE t1field IN ( SELECT v1_field FROM v1); t1field 1 2 CALL p1; t1field 1 2 CALL p1; t1field 2 ---------------------------- Suggested fix: Produce correct result on all calls to p1