Bug #51092 Linked join buffer gives wrong result for 3-way cross join
Submitted: 11 Feb 2010 13:35 Modified: 23 Nov 2010 3:19
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0-codebase-bugfixing OS:Linux
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: Contribution, join_cache_level, optimizer_switch

[11 Feb 2010 13:35] Guilhem Bichot 
Description:
I have alik@sun.com-20100209120530-02iqcoowu8yfn2z6
I create the test in "how-to-repeat", I run it with --mysqld=--optimizer_join_cache_level=X for X in 2,4,6,8 and it gives wrong results (same results for all those values). Here is the diff compared to correct results

@@ -5,12 +5,12 @@
 prepare stmt from "select * from v_27690_1, v_27690_2";
 execute stmt;
 a	b	a	b
-1	1	1	1
-1	1	1	1
-1	1	2	2
-1	1	2	2
 2	2	1	1
 2	2	1	1
+2	2	1	1
+2	2	1	1
+2	2	2	2
+2	2	2	2
 2	2	2	2
 2	2	2	2

I found this by running ps_ddl.test with --mysqld=--optimizer_join_cache_level=8

How to repeat:
This comes from the "BUG#27690" piece of ps_ddl.test.

create table t_27690_1 (a int, b int);
insert into t_27690_1 values (1,1),(2,2);

create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
create table v_27690_2 as select * from t_27690_1;

prepare stmt from "select * from v_27690_1, v_27690_2";

--sorted_result
execute stmt;

drop table t_27690_1;
drop view v_27690_1;
drop table v_27690_2;
deallocate prepare stmt;
[11 Feb 2010 13:49] Guilhem Bichot 
sorry, not related to BKA, rather probably related to incremental join buffering.
[11 Feb 2010 17:44] Sveta Smirnova 
Thank you for the report.

Verified as described.
[1 Mar 2010 9:28] Jørgen Løland 
Query executed without PS also fails:

jcl=<odd number>:
-----------------
SELECT * FROM v1, t2;
a	b	a	b
1	1	1	1
1	1	1	1
1	1	2	2
1	1	2	2
2	2	1	1
2	2	1	1
2	2	2	2
2	2	2	2

jcl=<even number>:
------------------
SELECT * FROM v1, t2;
a	b	a	b
2	2	1	1
2	2	1	1
2	2	1	1
2	2	1	1
2	2	2	2
2	2	2	2
2	2	2	2
2	2	2	2
[5 Mar 2010 8:41] Jørgen Løland 
Also fails without view, but only for 3-way joins that do not have fields from the middle table in the select list:

#Correct
SELECT t1.*,t2.* FROM t1,t2,t3;  
a	b	a	b
1	1	1	1
1	1	1	1
1	1	2	2
1	1	2	2
2	2	1	1
2	2	1	1
2	2	2	2
2	2	2	2

#Incorrect
SELECT t1.*,t3.* FROM t1, t2,t3;
a	b	a	b
2	2	1	1
2	2	1	1
2	2	1	1
2	2	1	1
2	2	2	2
2	2	2	2
2	2	2	2
2	2	2	2
[8 Mar 2010 13:53] Lenz Grimmer 
A patch has been contributed for this issue by Igor Babaev: http://lists.mysql.com/internals/37788
[11 Mar 2010 9:22] 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/102960

3805 Jorgen Loland	2010-03-11
      BUG#51092 "Linked join buffer gives wrong result for 3-way 
                  cross join"
      
      The function JOIN_CACHE::read_all_record_fields could return 0
      for an incremental join cache in two cases:
      1. there were no more records in the associated join buffer
      2. there was no table fields stored in the join buffer.
      As a result the function JOIN_CACHE::get_record() could
      return prematurely and did not read all needed fields from
      join buffers into the record buffer.
      
      Now the function JOIN_CACHE::read_all_record_fields returns
      -1 if there are no more records in the associated join buffer.
      
      Patch based on contribution by Igor.
     @ mysql-test/r/join_cache.result
        Added test for BUG#51092
     @ mysql-test/t/join_cache.test
        Added test for BUG#51092
     @ sql/sql_join_cache.cc
        Make JOIN_CACHE::get_record() and friends discriminate between the "no more records in buffer" case and the "no fields for this table in the buffer" for incremental join cache
[16 Mar 2010 11:25] 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/103430

3805 Jorgen Loland	2010-03-16
      BUG#51092 "Linked join buffer gives wrong result for 3-way 
                 cross join"
      
      The function JOIN_CACHE::read_all_record_fields could return 0
      for an incremental join cache in two cases:
      1. there were no more records in the associated join buffer
      2. there was no table fields stored in the join buffer.
      As a result the function JOIN_CACHE::get_record() could
      return prematurely and did not read all needed fields from
      join buffers into the record buffer.
      
      Now the function JOIN_CACHE::read_all_record_fields returns
      -1 if there are no more records in the associated join buffer.
      
      Patch based on contribution by Igor.
     @ mysql-test/r/join_cache.result
        Added test for BUG#51092
     @ mysql-test/t/join_cache.test
        Added test for BUG#51092
     @ sql/sql_join_cache.cc
        Make JOIN_CACHE::get_record() and friends discriminate between the "no more records in buffer" case and the "no fields for this table in the buffer" for incremental join cache
[17 Mar 2010 9:28] 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/103545

3826 Jorgen Loland	2010-03-17
      BUG#51092 "Linked join buffer gives wrong result for 3-way 
                 cross join"
            
      The function JOIN_CACHE::read_all_record_fields could return 0
      for an incremental join cache in two cases:
      1. there were no more records in the associated join buffer
      2. there was no table fields stored in the join buffer.
      As a result the function JOIN_CACHE::get_record() could
      return prematurely and did not read all needed fields from
      join buffers into the record buffer.
            
      Now the function JOIN_CACHE::read_all_record_fields returns
      -1 if there are no more records in the associated join buffer.
           
      Patch based on contribution by Igor.
     @ mysql-test/r/join_cache.result
        Added test for BUG#51092
     @ mysql-test/t/join_cache.test
        Added test for BUG#51092
     @ sql/sql_join_cache.cc
        * Make JOIN_CACHE::get_record() and friends discriminate
          between the "no more records in buffer" case and the "no
          fields for this table in the buffer" for incremental join
          cache
        * Updated copyright notice
     @ sql/sql_select.h
        * Changed return type of read_all_record_fields() from uint to int
        * Updated copyright notice
[17 Mar 2010 9:34] Jørgen Løland 
Pushed to 6.0-codebase-bugfixing
[24 Mar 2010 8:15] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)
[12 Apr 2010 22:04] Paul DuBois 
Noted in 6.0.14 changelog.

Use of incremental join buffering could produce incorrect query results.
[10 May 2010 13:59] 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/107858

3158 Oystein Grovlen	2010-05-10
      BUG#51092 "Linked join buffer gives wrong result for 3-way 
                 cross join"
      
      (Backporting of jorgen.loland@sun.com-20100317092818-1kzv948v1rkgmgpi)
            
      The function JOIN_CACHE::read_all_record_fields could return 0
      for an incremental join cache in two cases:
      1. there were no more records in the associated join buffer
      2. there was no table fields stored in the join buffer.
      As a result the function JOIN_CACHE::get_record() could
      return prematurely and did not read all needed fields from
      join buffers into the record buffer.
            
      Now the function JOIN_CACHE::read_all_record_fields returns
      -1 if there are no more records in the associated join buffer.
           
      Patch based on contribution by Igor.
     @ mysql-test/r/join_cache.result
        Added test for BUG#51092
     @ mysql-test/t/join_cache.test
        Added test for BUG#51092
     @ sql/sql_join_cache.cc
        * Make JOIN_CACHE::get_record() and friends discriminate
          between the "no more records in buffer" case and the "no
          fields for this table in the buffer" for incremental join
          cache
        * Updated copyright notice
     @ sql/sql_select.h
        * Changed return type of read_all_record_fields() from uint to int
        * Updated copyright notice
[10 May 2010 14:25] 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/107862

3162 Oystein Grovlen	2010-05-10
      BUG#51092 "Linked join buffer gives wrong result for 3-way 
                 cross join"
      
      (Backporting of jorgen.loland@sun.com-20100317092818-1kzv948v1rkgmgpi)
            
      The function JOIN_CACHE::read_all_record_fields could return 0
      for an incremental join cache in two cases:
      1. there were no more records in the associated join buffer
      2. there was no table fields stored in the join buffer.
      As a result the function JOIN_CACHE::get_record() could
      return prematurely and did not read all needed fields from
      join buffers into the record buffer.
            
      Now the function JOIN_CACHE::read_all_record_fields returns
      -1 if there are no more records in the associated join buffer.
           
      Patch based on contribution by Igor.
     @ mysql-test/r/join_cache.result
        Added test for BUG#51092
     @ mysql-test/t/join_cache.test
        Added test for BUG#51092
     @ sql/sql_join_cache.cc
        * Make JOIN_CACHE::get_record() and friends discriminate
          between the "no more records in buffer" case and the "no
          fields for this table in the buffer" for incremental join
          cache
        * Updated copyright notice
     @ sql/sql_select.h
        * Changed return type of read_all_record_fields() from uint to int
        * Updated copyright notice
[16 Aug 2010 6:41] 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:21] 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:19] Paul DuBois 
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.