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:
None 
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
Description:
Discovered when analyzing/fixing Bug#48073
The description there says:
-- this works fine.... 
SELECT city_id, city, country_id
FROM sakila.city
WHERE country_id IN (
	SELECT country_id 
	FROM sakila.v_countriesstartingwith_a
) ;

However: that query returns *all* rows in sakila.city.
If the query is prepared and then executed, we get only cities within
countries starting with 'A'.

A simplified test case:
==================================
CREATE TABLE t1 (
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL
);

INSERT INTO t1 VALUES 
('Batna',2),
('Bchar',2),
('Skikda',2),
('Tafuna',3),
('Algeria',2) ;

CREATE TABLE t2 (
  country_id SMALLINT UNSIGNED NOT NULL,
  country VARCHAR(50) NOT NULL
);

INSERT INTO t2 VALUES
(2,'Algeria'),
(3,'XAmerican Samoa') ;

CREATE VIEW v1 AS 
SELECT country_id, country 
FROM t2
WHERE LEFT(country,1) = "A" 
; 

SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id 
  FROM t2
  WHERE LEFT(country,1) = "A" 
);

SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id 
  FROM v1
);

PREPARE stmt FROM
"
SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id 
  FROM v1
);
";

execute stmt;
============================

All three statements should yield the same result, the second one however,
returns *all* cities.

(if we execute the prepared statement one more than once, we crash,
 which is Bug#48834)

How to repeat:
See above.
[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.