Bug #49097 | subquery with view generates wrong result with non-prepared statement | ||
---|---|---|---|
Submitted: | 25 Nov 2009 12:14 | Modified: | 23 Nov 2010 3:03 |
Reporter: | Tor Didriksen | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 6.0.14 | OS: | Any |
Assigned to: | Tor Didriksen | CPU Architecture: | Any |
Tags: | optimizer_switch, semijoin, subquery |
[25 Nov 2009 12:14]
Tor Didriksen
[25 Nov 2009 13:09]
MySQL Verification Team
On mysql-6.0 source tree the server is crashing running on Ubuntu 9.10 X86_64: 091125 11:00:51 [Note] dbs/6.0/libexec/mysqld: ready for connections. Version: '6.0.12-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution 091125 11:01:29 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8384512 read_buffer_size=131072 max_used_connections=1 max_threads=151 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338446 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x34d95a8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f79cc05d0c8 thread_stack 0x40000 dbs/6.0/libexec/mysqld(my_print_stacktrace+0x35) [0xe0fa87] dbs/6.0/libexec/mysqld(handle_segfault+0x2a7) [0x776f75] /lib/libpthread.so.0 [0x7f79d0561190] dbs/6.0/libexec/mysqld(Item_equal::contains(Field*)+0x30) [0x6eae1a] dbs/6.0/libexec/mysqld(find_item_equal(COND_EQUAL*, Field*, bool*)+0x4e) [0x816b55] dbs/6.0/libexec/mysqld(setup_sj_materialization(st_join_table*)+0x791) [0x81440a] dbs/6.0/libexec/mysqld [0x814d8f] dbs/6.0/libexec/mysqld(JOIN::optimize()+0x1efb) [0x7ffdcd] dbs/6.0/libexec/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x267) [0x803efa] dbs/6.0/libexec/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x1a4) [0x7fb490] dbs/6.0/libexec/mysqld [0x792ab5] dbs/6.0/libexec/mysqld(mysql_execute_command(THD*)+0x9c8) [0x78a245] dbs/6.0/libexec/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x287) [0x794d7b] dbs/6.0/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xad1) [0x787aa1] dbs/6.0/libexec/mysqld(do_command(THD*)+0x252) [0x786dbb] dbs/6.0/libexec/mysqld(handle_one_connection+0x12b) [0x78432e] /lib/libpthread.so.0 [0x7f79d0558a04] /lib/libc.so.6(clone+0x6d) [0x7f79cf4c17bd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x3525e40 = SELECT city, country_id FROM t1 WHERE country_id IN ( SELECT country_id FROM v1 ) thd->thread_id=1 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. miguel@quetzal3:~$
[25 Nov 2009 13:17]
MySQL Verification Team
Also mysql-6.0-codebase is crashing which source tree are you using?. Thanks in advance. 091125 11:14:21 InnoDB: Started; log sequence number 0 0 091125 11:14:21 [Note] Event Scheduler: Loaded 0 events 091125 11:14:21 [Note] dbs/6.0codebase/libexec/mysqld: ready for connections. Version: '6.0.14-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution 091125 11:15:13 - mysqld got signal 11 ;
[25 Nov 2009 13:22]
Tor Didriksen
My simplified test case depends on my fix for Bug#48073 which is not pushed yet. The original query reported as -- this works fine.... in Bug#48073 will return *all* cities, rather than cities in 'A-countries' with semijoin=on
[4 Dec 2009 15:56]
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/92907 3756 Tor Didriksen 2009-12-04 Bug#49097 subquery with view generates wrong result with non-prepared statement. The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[7 Dec 2009 7: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/93026 3756 Tor Didriksen 2009-12-07 Bug#49097 subquery with view generates wrong result with non-prepared statement. The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[8 Dec 2009 10:06]
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/93141 3761 Tor Didriksen 2009-12-08 Bug#49097 subquery with view generates wrong result with non-prepared statement. The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[8 Dec 2009 14:34]
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/93212 3763 Tor Didriksen 2009-12-08 Bug#49097 subquery with view generates wrong result with non-prepared statement. The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[9 Dec 2009 7:09]
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/93262 3763 Tor Didriksen 2009-12-09 Bug#49097 subquery with view generates wrong result with non-prepared statement. The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[9 Dec 2009 7:18]
Tor Didriksen
Pushed to bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/
[11 Dec 2009 6:02]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[16 Dec 2009 2:40]
Paul DuBois
Noted in 6.0.14 changelog. A subquery that selected from a view could return incorrect results when used in a non-prepared statement.
[15 Apr 2010 7:38]
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/105692 3075 Tor Didriksen 2010-04-15 Bug#49097 subquery with view generates wrong result with non-prepared statement. backport of tor.didriksen@sun.com-20091209070841-svth3bj4ppgpxi1t The problem was that flatten_subqueries() would only flatten the original where clause of the outer query, thus losing the where clause of the view. @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc In flatten_subqueries(): Use the augmented 'conds' which is produced by setup_conds() when preparing views.
[16 Aug 2010 6:38]
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:19]
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:03]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.