| Bug #12941 | InnoDB && Views && order by clause problem | ||
|---|---|---|---|
| Submitted: | 2 Sep 2005 1:49 | Modified: | 8 Sep 2005 16:58 |
| Reporter: | Miguel Solorzano | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0 BK source | OS: | Linux (Linux/Windows) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[4 Sep 2005 21:23]
Heikki Tuuri
Hi!
This is probably not an InnoDB bug, but a bug in the MySQL optimizer associated with views and ORDER BY.
The views here are very simple projections of the base tables dm_group_s and dm_group_r. The join is also a very simple one done where we require:
dm_group_s.group_name='tstgroup1'
and join the two tables on r_object_id.
MySQL optimizes this in the obvious way:
mysql> explain select all dm_group.r_object_id, dm_repeating.users_names
-> from dm_group_sp dm_group, dm_group_rp dm_repeating
-> where (dm_group.group_name='tstgroup1') and
-> dm_repeating.r_object_id=dm_group.r_object_id order by users_names;
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+----------------------------------
-------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra
|
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+----------------------------------
-------------------------+
| 1 | PRIMARY | dm_group_s | ref | d_1f0001a080000107,d_1f0001a080000018 |
d_1f0001a080000018 | 34 | const | 1 | Using where; Using index; Using t
emporary; Using filesort |
| 1 | PRIMARY | dm_group_r | ref | d_1f0001a080000108 |
d_1f0001a080000108 | 16 | func | 1 | Using where
|
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+----------------------------------
-------------------------+
2 rows in set (4.30 sec)
mysql>
When I run the query inside gdb, InnoDB does return the correct row from dm_group_s:
(gdb) x/30b mysql_rec
0x40c42a6c: 49 '1' 50 '2' 48 '0' 48 '0' 48 '0' 49 '1' 97 'a' 48 '0'
0x40c42a74: 56 '8' 48 '0' 48 '0' 48 '0' 48 '0' 53 '5' 52 '4' 50 '2'
0x40c42a7c: 9 '\t' 116 't' 115 's' 116 't' 103 'g' 114 'r' 111 'o' 117 'u'
0x40c42a84: 112 'p' 49 '1' 32 ' ' 32 ' ' 32 ' ' 32 ' '
(gdb)
But after that MySQL requests InnoDB to retrieve a row from dm_group_r where r_object_id is all spaces!
Below key_ptr is 16 space chars. MySQL has somehow lost the value of that column.
(gdb) bt
#0 ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_function) (
this=0x8c7ba78, buf=0x8c7bbd0 "",
key_ptr=0x8c90d40 ' ' <repeats 16 times>, key_len=16,
find_flag=HA_READ_KEY_EXACT) at ha_innodb.cc:3903
#1 0x081ff054 in join_read_always_key (tab=0x8c90f28) at sql_select.cc:9902
#2 0x081fdf80 in sub_select (join=0x8c8f650, join_tab=0x8c90f28,
end_of_records=false) at sql_select.cc:9406
#3 0x081fe22f in evaluate_join_record (join=0x8c8f650, join_tab=0x8c90dc0,
error=0, report_error=0x8c56630 "") at sql_select.cc:9522
#4 0x081fdfa3 in sub_select (join=0x8c8f650, join_tab=0x8c90dc0,
end_of_records=false) at sql_select.cc:9407
#5 0x081fdb01 in do_select (join=0x8c8f650, fields=0x0, table=0x8c78d18,
procedure=0x0) at sql_select.cc:9170
#6 0x081ea6cb in JOIN::exec() (this=0x8c8f650) at sql_select.cc:1304
#7 0x081ecc2d 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=0x8c55c38,
rref_pointer_array=0x8c55f94, tables=0x8c7d068, wild_num=0,
fields=@0x8c55efc, conds=0x8c7d7b8, og_num=1, order=0x8c7d930, group=0x0,
having=0x0, proc_param=0x0, select_options=2172930560, result=0x8c7ec90,
unit=0x8c55c88, select_lex=0x8c55e7c) at sql_select.cc:2093
#8 0x081e6eb5 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
thd=0x8c55c38, lex=0x8c55c78, result=0x8c7ec90, setup_tables_done_option=0)
at sql_select.cc:238
#9 0x081ac7ee in mysql_execute_command(THD*) (thd=0x8c55c38)
at sql_parse.cc:2462
#10 0x081b4d66 in mysql_parse(THD*, char*, unsigned) (thd=0x8c55c38,
inBuf=0x8c7cd50 "select all dm_group.r_object_id, dm_repeating.users_names\n
from dm_group_sp dm_group, dm_group_rp dm_repeating\nwhere (dm_group.group_name=
'tstgroup1') and\ndm_repeating.r_object_id=dm_group.r_object_id "...,
length=220) at sql_parse.cc:5393
#11 0x081aa877 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0x8c55c38,
packet=0x8c74cf1 "select all dm_group.r_object_id, dm_repeating.users_names\
nfrom dm_group_sp dm_group, dm_group_rp dm_repeating\nwhere (dm_group.group_name
='tstgroup1') and\ndm_repeating.r_object_id=dm_group.r_object_id "...,
packet_length=221) at sql_parse.cc:1671
#12 0x081aa03f in do_command(THD*) (thd=0x8c55c38) at sql_parse.cc:1466
#13 0x081a9135 in handle_one_connection (arg=0x8c55c38) at sql_parse.cc:1118
---Type <return> to continue, or q <return> to quit---
#14 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#15 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#16 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)
InnoDB, of course, does not find any row where r_object_id is all spaces. That is why the query result is empty.
[4 Sep 2005 21:24]
Heikki Tuuri
------------------------------------------------------------
Without ORDER BY, we get the following query plan, and the result is correct:
mysql> explain select all dm_group.r_object_id, dm_repeating.users_names
-> from dm_group_sp dm_group, dm_group_rp dm_repeating
-> where (dm_group.group_name='tstgroup1') and
-> dm_repeating.r_object_id=dm_group.r_object_id;
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+--------------------------+
| 1 | PRIMARY | dm_group_s | ref | d_1f0001a080000107,d_1f0001a080000018 |
d_1f0001a080000018 | 34 | const | 1 | Using where; Using index |
| 1 | PRIMARY | dm_group_r | ref | d_1f0001a080000108 |
d_1f0001a080000108 | 16 | func | 1 | Using where |
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-------+------+--------------------------+
2 rows in set (3.55 sec)
mysql>
----------------------------------------------------------------
Without views, we get the following query plan, and the result is correct:
mysql> explain select all dm_group_s.r_object_id, dm_group_r.users_names
-> from dm_group_s, dm_group_r
-> where (dm_group_s.group_name='tstgroup1') and
-> dm_group_r.r_object_id=dm_group_s.r_object_id order by users_names;
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-----------------------------+------+------------
-----------------------------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra
|
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-----------------------------+------+------------
-----------------------------------------------+
| 1 | SIMPLE | dm_group_s | ref | d_1f0001a080000107,d_1f0001a080000018 |
d_1f0001a080000018 | 34 | const | 1 | Using where
; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | dm_group_r | ref | d_1f0001a080000108 |
d_1f0001a080000108 | 16 | test.dm_group_s.r_object_id | 1 |
|
+----+-------------+------------+------+---------------------------------------+
--------------------+---------+-----------------------------+------+------------
-----------------------------------------------+
2 rows in set (5.27 sec)
mysql>
Regards,
Heikki
[7 Sep 2005 7:49]
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/internals/29408
[7 Sep 2005 8:49]
Sergey Petrunya
Fix pushed into 5.0.13 tree.
[7 Sep 2005 8:52]
Sergey Petrunya
Bug description for the changelog: Possible wrong query results for queries on VIEWs that are executed using temporary table s.
[8 Sep 2005 16:58]
Paul DuBois
Noted in 5.0.13 changelog.
Description: The addition of an "order by" clause changes the set of results from the SQL query. Initial testing and reproducing of this problem seems to be caused by the indices on columns. To reproduce the problem, do the following in the mysql client: miguel@hegel:~/dbs/5.0> bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.13-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table dm_group_s (r_object_id char(16) NOT NULL, -> group_name varchar(32) NOT NULL) engine = InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> mysql> create table dm_group_r (r_object_id char(16) NOT NULL, -> i_position int(11) NOT NULL, users_names varchar(32) default NULL) -> Engine = InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> mysql> create view dm_group_sp as select r_object_id, group_name from dm_group_s; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create view dm_group_rp as select r_object_id, i_position, users_names from dm_group_r; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create unique index d_1f0001a080000107 on dm_group_s(r_object_id); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> create index d_1f0001a080000018 on dm_group_s(group_name); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> create unique index d_1f0001a080000108 on dm_group_r(r_object_id,i_position); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> create index d_1f0001a080000044 on dm_group_r(users_names); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> insert into dm_group_s values('120001a080000542','tstgroup1'); Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into dm_group_r values('120001a080000542',-1, 'guser01'); Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into dm_group_r values('120001a080000542',-2, 'guser02'); Query OK, 1 row affected (0.01 sec) mysql> mysql> select all dm_group.r_object_id, dm_repeating.users_names -> from dm_group_sp dm_group, dm_group_rp dm_repeating -> where (dm_group.group_name='tstgroup1') and -> dm_repeating.r_object_id=dm_group.r_object_id; +------------------+-------------+ | r_object_id | users_names | +------------------+-------------+ | 120001a080000542 | guser02 | | 120001a080000542 | guser01 | +------------------+-------------+ 2 rows in set (0.00 sec) mysql> mysql> select all dm_group.r_object_id, dm_repeating.users_names -> from dm_group_sp dm_group, dm_group_rp dm_repeating -> where (dm_group.group_name='tstgroup1') and -> dm_repeating.r_object_id=dm_group.r_object_id order by users_names; Empty set (0.00 sec) This not happens when using MyISAM Engine: mysql> create table dm_group_s (r_object_id char(16) NOT NULL, -> group_name varchar(32) NOT NULL) engine = MyISAM; Query OK, 0 rows affected (0.04 sec) mysql> mysql> create table dm_group_r (r_object_id char(16) NOT NULL, -> i_position int(11) NOT NULL, users_names varchar(32) default NULL) -> Engine = MyISAM; Query OK, 0 rows affected (0.04 sec) <cut> mysql> select all dm_group.r_object_id, dm_repeating.users_names -> from dm_group_sp dm_group, dm_group_rp dm_repeating -> where (dm_group.group_name='tstgroup1') and -> dm_repeating.r_object_id=dm_group.r_object_id; +------------------+-------------+ | r_object_id | users_names | +------------------+-------------+ | 120001a080000542 | guser02 | | 120001a080000542 | guser01 | +------------------+-------------+ 2 rows in set (0.00 sec) mysql> mysql> select all dm_group.r_object_id, dm_repeating.users_names -> from dm_group_sp dm_group, dm_group_rp dm_repeating -> where (dm_group.group_name='tstgroup1') and -> dm_repeating.r_object_id=dm_group.r_object_id order by users_names; +------------------+-------------+ | r_object_id | users_names | +------------------+-------------+ | 120001a080000542 | guser01 | | 120001a080000542 | guser02 | +------------------+-------------+ 2 rows in set (0.01 sec) How to repeat: create table dm_group_s (r_object_id char(16) NOT NULL, group_name varchar(32) NOT NULL) engine = InnoDB; create table dm_group_r (r_object_id char(16) NOT NULL, i_position int(11) NOT NULL, users_names varchar(32) default NULL) Engine = InnoDB; create view dm_group_sp as select r_object_id, group_name from dm_group_s; create view dm_group_rp as select r_object_id, i_position, users_names from dm_group_r; create unique index d_1f0001a080000107 on dm_group_s(r_object_id); create index d_1f0001a080000018 on dm_group_s(group_name); create unique index d_1f0001a080000108 on dm_group_r(r_object_id,i_position); create index d_1f0001a080000044 on dm_group_r(users_names); insert into dm_group_s values('120001a080000542','tstgroup1'); insert into dm_group_r values('120001a080000542',-1, 'guser01'); insert into dm_group_r values('120001a080000542',-2, 'guser02'); select all dm_group.r_object_id, dm_repeating.users_names from dm_group_sp dm_group, dm_group_rp dm_repeating where (dm_group.group_name='tstgroup1') and dm_repeating.r_object_id=dm_group.r_object_id; select all dm_group.r_object_id, dm_repeating.users_names from dm_group_sp dm_group, dm_group_rp dm_repeating where (dm_group.group_name='tstgroup1') and dm_repeating.r_object_id=dm_group.r_object_id order by users_names;