MySQL Bugs: #12779: Wrong left join result if InnoDB estimates a table to be empty when it is not

Bug #12779	Wrong left join result if InnoDB estimates a table to be empty when it is not
Submitted:	24 Aug 2005 8:27	Modified:	26 Aug 2005 20:28
Reporter:	Yoshiaki Tajika (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.10, 4.1.15	OS:	Windows (Windows)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Rollback is incomplete. It may be associated with innodb, foreign key, and join.

How to repeat:
create table t1 ( 
c1 int primary key, 
c2 int
) engine=innodb default charset=latin1;

create table t2 ( 
c1 int primary key, 
c2 int,
constraint fk_t2 foreign key(c1) references t1(c1)
) engine=innodb default charset=latin1;

insert into t1 values(1, 10), (2, 20);
insert into t2 values(1, 100);

select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;  -- (a)
begin;
delete from t2; 
rollback; 
select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;  -- (b)

The query (a) showed below.
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 |    1 |  100 |
|  2 |   20 | NULL | NULL |
+----+------+------+------+

But the query (b) showed below.
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 | NULL | NULL |
|  2 |   20 | NULL | NULL |
+----+------+------+------+

They should be same, because deletion was rollbacked.

Suggested fix:
I have no idea.

Thank you for your bug report. I was able to repeat this problem with 4.1.15:

hundin:~/mysql-4.1/client> ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

ary key, 
    -> c2 int
    -> ) engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> create table t2 ( 
    -> c1 int primary key, 
    -> c2 int,
    -> constraint fk_t2 foreign key(c1) references t1(c1)
    -> ) engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(1, 10), (2, 20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1, 100);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 |    1 |  100 |
|  2 |   20 | NULL | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t2; 
Query OK, 1 row affected (0.00 sec)

mysql> rollback; 
Query OK, 0 rows affected (0.00 sec)

mysql> select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 | NULL | NULL |
|  2 |   20 | NULL | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |   10 |
|  2 |   20 |
+----+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)
mysql> select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 | NULL | NULL |
|  2 |   20 | NULL | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.25 sec)

mysql> check table t2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t2 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.21 sec)

mysql> select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1;
+----+------+------+------+
| c1 | c2   | c1   | c2   |
+----+------+------+------+
|  1 |   10 | NULL | NULL |
|  2 |   20 | NULL | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

Results from show innodb status and show variables

Attachment: info.txt (text/plain), 24.48 KiB.

Hi!

The reason probably is that in MySQL query optimization, InnoDB estimates t2 row count at 0. InnoDB does know that the rollback canceled the delete.

MySQL's left join optimization code seem to trust that the row count estimate 0 is reliable, and thinks there are no rows in t2.

(gdb) bt
#0  ha_innobase::info(unsigned) (this=0x8c78cc8, flag=18) at ha_innodb.cc:5417
#1  0x081ebc11 in make_join_statistics (join=0x8c7af98, tables=0x8c7a9e0,
    conds=0x0, keyuse_array=0x8c7bcb0) at sql_select.cc:2199
#2  0x081e6c9e in JOIN::optimize() (this=0x8c7af98) at sql_select.cc:656
#3  0x081eb6cc in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Ite
m>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, sel
ect_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8c518b0,
    rref_pointer_array=0x8c51c0c, tables=0x8c7a848, wild_num=2,
    fields=@0x8c51b74, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2156153344, result=0x8c7af88,
    unit=0x8c51900, select_lex=0x8c51af4) at sql_select.cc:2079
#4  0x081e59d1 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
    thd=0x8c518b0, lex=0x8c518f0, result=0x8c7af88, setup_tables_done_option=0)
    at sql_select.cc:238
#5  0x081aaf65 in mysql_execute_command(THD*) (thd=0x8c518b0)
    at sql_parse.cc:2434
#6  0x081b3a2a in mysql_parse(THD*, char*, unsigned) (thd=0x8c518b0,
    inBuf=0x8c7a680 "select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1",
    length=55) at sql_parse.cc:5444
#7  0x081a904b in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8c518b0,
    packet=0x8c72621 "select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c1",
packet_length=56) at sql_parse.cc:1664
#8  0x081a882d in do_command(THD*) (thd=0x8c518b0) at sql_parse.cc:1463
#9  0x081a7925 in handle_one_connection (arg=0x8c518b0) at sql_parse.cc:1116
#10 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#11 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#12 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)

Fix: let ha_innobase::info() add 1 to the row count estimate if the estimate would be 0. This kind of trick is already used in ::rows_in_range().

Regards,
Heikki

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/internals/28752

Fixed in 4.0.26, 4.1.15, and 5.0.13 (when the merge 4.1 -> 5.0 is complete).

Thank you for the bug report.

Heikki

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/internals/28755

Noted in 4.0.26, 4.1.15, 5.0.13 changelogs.