Bug #3038 There is an error in MySQL optimizer
Submitted: 2 Mar 2004 5:17 Modified: 4 Apr 2004 10:26
Reporter: Dyego Souza Dantas Leal Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18-pro OS:Linux (Linux Debian 2.4 and 2.6 kernels)
Assigned to: Assigned Account CPU Architecture:Any

[2 Mar 2004 5:17] Dyego Souza Dantas Leal
Description:
The script of database is uploaded to:

support.mysql.com/pub/mysql/secret

The name is sqlreg_italo.sql.gz

Hi,
   I would like to explain what is going on with the straight_join
   when it is used with my database.

   Suppose the command bellow:

   explain select table1 left join table2 on A=B left join table3 on
   C=D where ID = 1;

   When it is executed on one of our servers, mySql uses the right
   order for the search. It indicates the order Table1, table2 and
   table3, showing the indexes for each one.

   When it is executed on another of our servers, with the same
   database, but with another data, mySql indicates a non-optimized
   order for the search, and do not use the appropriated indexes,
   using sequential search. It can indicate, for example, table3,
   table1 and table2.

   To solve it I tried to modify the command, to use the
   straight_join, that is intended to force mySql to use the order I
   used to write the command:

   explain select STRAIGHT_JOIN table1 left join table2 on A=B
   left join table3 on C=D where ID = 1;

   But it got no success is this case. I'd already used it with
   success many times, but using it in this specific database
   and in this specific situation, the Straight_Join didn't work.

   I think it would be great if you could do something, because it can
   be a mySql bug, or innoDb bug instead. Because this, I sent to you
   an e-mail with the exact command I used and with the database I
   use. With that you will be able to reproduce the bug, as I could
   before sending it to you.

How to repeat:
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create database sqlreg3_italo;use sqlreg3_italo;source sqlreg_italo.sql;
Database changed
.....

Query OK, 0 rows affected (0.02 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.02 sec)

mysql> explain select straight_join *
    -> from anparte as ap
    -> left join an on an.An_Id = ap.Ap_Anotacao
    -> left join destino_selo on ds_Destino = an.an_id
    -> left join selo on ds_selo = se_id
    -> left join l1 on L1_Protocolo = an.An_Protocolo
    -> left join l2 on an.An_Livro = "2" AND L2_Id = an.An_IdLivro
    -> left join l3 on an.An_Livro = "3" AND L3_Id = an.An_IdLivro
    -> left join tr on an.An_Livro = "T" AND Tr_Id = an.An_IdLivro
    -> left join lri on an.An_Livro = "I" AND lr_Id = an.An_IdLivro
    -> 
    -> where an.An_Livro = "2"
    -> group by ap_Anotacao
    -> order by an.An_Num
    -> limit 20;
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
| table        | type   | possible_keys                                                                                    
| key                     | key_len | ref   | rows | Extra                           |
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
| destino_selo | system | PRIMARY                                                                                          
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| selo         | system | PRIMARY,Se_Id                                                                                    
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| l1           | system | L1_Protocolo,IL1_ProtocoloEtapa                                                                  
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| l2           | system | L2_Id                                                                                            
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| l3           | system | L3_Id                                                                                            
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| tr           | system | Tr_Id                                                                                            
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| lri          | system | Lr_Id,PRIMARY_IDX2                                                                               
| NULL                    |    NULL | NULL  |    0 | const row not found             |
| ap           | index  |
IAp_AnotacaoImportancia,IAp_AnotaOnusOnusCanc,IAp_AnotaCompCompCanc,IAp_AnotaProp,IAp_AnotaClassifTransf |
IAp_AnotacaoImportancia |       8 | NULL  |    0 | Using temporary; Using filesort |
| an           | ref    | An_Id,IAn_L4_LivroIdLvNum,Ian_LivroNumLri                                                        
| Ian_LivroNumLri         |       1 | const |    1 | Using where                     |
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
9 rows in set (0.01 sec)

The   word  "straight_join"  make it explicit for the MySQL to use the
indexes in the left join order... but it isn't working...

The correct response of mysql is:

+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
| table        | type   | possible_keys                                                                                    
| key                     | key_len | ref                  | rows  | Extra                           |
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
| ap           | index  |
IAp_AnotacaoImportancia,IAp_AnotaOnusOnusCanc,IAp_AnotaCompCompCanc,IAp_AnotaProp,IAp_AnotaClassifTransf |
IAp_AnotacaoImportancia |       8 | NULL                 | 72821 | Using temporary; Using filesort |
| an           | eq_ref | PRIMARY,IAn_L4_LivroIdLvNum,Ian_LivroNumLri                                                      
| PRIMARY                 |       4 | ap.Ap_Anotacao       |     1 | Using where                     |
| destino_selo | ref    | PRIMARY                                                                                          
| PRIMARY                 |       4 | an.An_Id             |     1 | Using index                     |
| selo         | eq_ref | PRIMARY,Se_Id                                                                                    
| PRIMARY                 |       4 | destino_selo.Ds_Selo |     1 |                                 |
| l1           | eq_ref | PRIMARY,IL1_ProtocoloEtapa                                                                       
| PRIMARY                 |       4 | an.An_Protocolo      |     1 |                                 |
| l2           | eq_ref | PRIMARY                                                                                          
| PRIMARY                 |       4 | an.An_IdLivro        |     1 |                                 |
| l3           | eq_ref | PRIMARY                                                                                          
| PRIMARY                 |       4 | an.An_IdLivro        |     1 |                                 |
| tr           | eq_ref | PRIMARY                                                                                          
| PRIMARY                 |       4 | an.An_IdLivro        |     1 |                                 |
| lri          | eq_ref | PRIMARY,Lr_Id                                                                                    
| PRIMARY                 |       4 | an.An_IdLivro        |     1 |                                 |
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+

Suggested fix:
I ran the ANALYZE TABLE but it didn't work.
[2 Mar 2004 5:32] Dyego Souza Dantas Leal
The file of create database is attached in the Bug Request.
[4 Mar 2004 10:26] Dean Ellis
This output is correct for your data, as there are no matching rows (so they are being optimized away).  If you see this same behavior, where STRAIGHT_JOIN is ignored, in a situation where there are matching rows, please update this.
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[26 Jun 2009 14:40] Wagner Bianchi
Hi friends,

I got this "const row not found" on the explain output for queries. After I have inserted rows on that table, this error has finish.