| Bug #9348 | result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B) | ||
|---|---|---|---|
| Submitted: | 23 Mar 2005 7:49 | Modified: | 28 Apr 2005 1:34 |
| Reporter: | Georg Richter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1, 5.0.3 | OS: | Linux (Linux) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[23 Mar 2005 7:53]
Georg Richter
Without PK and indexes the results for both queries are the same
[23 Mar 2005 9:29]
Hakan Küçükyılmaz
Please note the unlucky linebreak in the HTML: SELECT count(*) FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2') SELECT count(*) FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1')
[23 Mar 2005 11:09]
Heikki Tuuri
Hi!
I was not able to repeat this for a MyISAM type table. Maybe the optimizer chooses a different plan for a MyISAM table.
I analyzed the execution for the wrong result:
mysql> SELECT * FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != '
1');
+--------+------+------+----------+----------+
| CLIENT | ARG1 | ARG2 | FUNCTION | FUNCTINT |
+--------+------+------+----------+----------+
| 000 | 2 | 3 | Text004 | 0 |
+--------+------+------+----------+----------+
1 row in set (3 min 53.07 sec)
What happens with an InnoDB table is that MySQL asks for an estimate for the number of rows in three different ranges. InnoDB gives estimates 1 row, 2 rows, 2 rows.
After that, MySQL requests all rows where the two first columns are > ('000', ' 1 '). The only such row is the one with ' 2 ' as ARG1. InnoDB returns it, and MySQL returns the result to the client.
This looks like a bug in the optimization of multiple ranges in MySQL.
Regards,
Heikki
(gdb) break 'ha_innobase::records_in_range(unsigned, st_key_range*, st_key_range
*)'
Breakpoint 2 at 0x8279129: file ha_innodb.cc, line 4646.
(gdb) c
Continuing.
Breakpoint 1, row_search_for_mysql (buf=0xa374ea0 "000 2 3 \aText004 ",
mode=0, prebuilt=0x40cde068, match_mode=0, direction=1) at row0sel.c:3033
3033 dict_index_t* index = prebuilt->index;
(gdb)
Continuing.
Breakpoint 2, ha_innobase::records_in_range(unsigned, st_key_range*, st_key_rang
e*) (this=0xa374d60, keynr=0, min_key=0x85d7de2c, max_key=0x85d7de1c)
at ha_innodb.cc:4646
4646 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_pre
built;
Current language: auto; currently c++
(gdb) next
4649 mysql_byte* key_val_buff2 = (mysql_byte*) my_malloc(
(gdb)
4653 ulint buff2_len = table->s->reclength
(gdb)
4663 DBUG_ENTER("records_in_range");
(gdb)
4665 prebuilt->trx->op_info = (char*)"estimating records in index ran
ge";
(gdb)
4670 trx_search_latch_release_if_reserved(prebuilt->trx);
(gdb)
4672 active_index = keynr;
(gdb)
4674 key = table->key_info + active_index;
(gdb)
4676 index = dict_table_get_index_noninline(prebuilt->table, key->nam
e);
(gdb)
4678 range_start = dtuple_create_for_mysql(&heap1, key->key_parts);
(gdb)
4679 dict_index_copy_types(range_start, index, key->key_parts);
(gdb)
4681 range_end = dtuple_create_for_mysql(&heap2, key->key_parts);
(gdb)
4682 dict_index_copy_types(range_end, index, key->key_parts);
(gdb)
4684 row_sel_convert_mysql_key_to_innobase(
(gdb)
4693 row_sel_convert_mysql_key_to_innobase(
(gdb)
4701 mode1 = convert_search_mode_to_innobase(min_key ? min_key->flag
:
(gdb)
4703 mode2 = convert_search_mode_to_innobase(max_key ? max_key->flag
:
(gdb)
4706 n_rows = btr_estimate_n_rows_in_range(index, range_start,
(gdb) print *range_start
$4 = {info_bits = 0, n_fields = 2, n_fields_cmp = 2, fields = 0x40ce0884,
tuple_list = {prev = 0x0, next = 0x0}, magic_n = 0}
(gdb) print range_start->fields[0]
$5 = {data = 0x85d7e2a8, len = 3, type = {mtype = 13, prtype = 3080702,
len = 3, prec = 0, mbminlen = 1, mbmaxlen = 1}}
(gdb) print range_start->fields[1]
$6 = {data = 0x85d7e2ab, len = 3, type = {mtype = 13, prtype = 3080702,
len = 3, prec = 0, mbminlen = 1, mbmaxlen = 1}}
(gdb) x/3b range_start->fields[0].data
0x85d7e2a8: 0x30 0x30 0x30
(gdb) x/3b range_start->fields[1].data
0x85d7e2ab: 0x20 0x31 0x20
(gdb) print *range_end
$7 = {info_bits = 0, n_fields = 1, n_fields_cmp = 1, fields = 0x40ce0c84,
tuple_list = {prev = 0x0, next = 0x0}, magic_n = 0}
(gdb) print range_end->fields[0]
$8 = {data = 0x85d7e9a6, len = 3, type = {mtype = 13, prtype = 3080702,
len = 3, prec = 0, mbminlen = 1, mbmaxlen = 1}}
(gdb) x/3b range_end->fields[0].data
0x85d7e9a6: 0x30 0x30 0x30
(gdb) next
4708 dtuple_free_for_mysql(heap1);
(gdb) print n_rows
$9 = 1
(gdb) x/10b min_key
0x85d7de2c: 0xa8 0xe2 0xd7 0x85 0x06 0x00 0x00 0x00
0x85d7de34: 0x03 0x00
(gdb) c
Continuing.
Breakpoint 2, ha_innobase::records_in_range(unsigned, st_key_range*, st_key_rang
e*) (this=0xa374d60, keynr=1, min_key=0x85d7dedc, max_key=0x0)
at ha_innodb.cc:4646
4646 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_pre
built;
(gdb) next
4649 mysql_byte* key_val_buff2 = (mysql_byte*) my_malloc(
(gdb)
4653 ulint buff2_len = table->s->reclength
(gdb)
4663 DBUG_ENTER("records_in_range");
(gdb)
4665 prebuilt->trx->op_info = (char*)"estimating records in index ran
ge";
(gdb)
4670 trx_search_latch_release_if_reserved(prebuilt->trx);
(gdb)
4672 active_index = keynr;
(gdb)
4674 key = table->key_info + active_index;
(gdb)
4676 index = dict_table_get_index_noninline(prebuilt->table, key->nam
e);
(gdb)
4678 range_start = dtuple_create_for_mysql(&heap1, key->key_parts);
(gdb)
4679 dict_index_copy_types(range_start, index, key->key_parts);
(gdb)
4681 range_end = dtuple_create_for_mysql(&heap2, key->key_parts);
(gdb)
4682 dict_index_copy_types(range_end, index, key->key_parts);
(gdb)
4684 row_sel_convert_mysql_key_to_innobase(
(gdb)
4693 row_sel_convert_mysql_key_to_innobase(
(gdb)
4701 mode1 = convert_search_mode_to_innobase(min_key ? min_key->flag
:
(gdb)
4703 mode2 = convert_search_mode_to_innobase(max_key ? max_key->flag
:
(gdb)
4706 n_rows = btr_estimate_n_rows_in_range(index, range_start,
(gdb)
4708 dtuple_free_for_mysql(heap1);
(gdb) print n_rows
$10 = 2
(gdb) print *range_start
$11 = {info_bits = 0, n_fields = 1, n_fields_cmp = 1, fields = 0x40ce0884,
tuple_list = {prev = 0x0, next = 0x0}, magic_n = 0}
(gdb) print *range_end
$12 = {info_bits = 0, n_fields = 0, n_fields_cmp = 0, fields = 0x40ce0c84,
tuple_list = {prev = 0x0, next = 0x0}, magic_n = 0}
(gdb) c
Continuing.
Breakpoint 2, ha_innobase::records_in_range(unsigned, st_key_range*, st_key_rang
e*) (this=0xa374d60, keynr=2, min_key=0x85d7dedc, max_key=0x0)
at ha_innodb.cc:4646
4646 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_pre
built;
(gdb) next
4649 mysql_byte* key_val_buff2 = (mysql_byte*) my_malloc(
(gdb)
4653 ulint buff2_len = table->s->reclength
(gdb)
4663 DBUG_ENTER("records_in_range");
(gdb)
4665 prebuilt->trx->op_info = (char*)"estimating records in index ran
ge";
(gdb)
4670 trx_search_latch_release_if_reserved(prebuilt->trx);
(gdb)
4672 active_index = keynr;
(gdb)
4674 key = table->key_info + active_index;
(gdb)
4676 index = dict_table_get_index_noninline(prebuilt->table, key->nam
e);
(gdb)
4678 range_start = dtuple_create_for_mysql(&heap1, key->key_parts);
(gdb)
4679 dict_index_copy_types(range_start, index, key->key_parts);
(gdb)
4681 range_end = dtuple_create_for_mysql(&heap2, key->key_parts);
(gdb)
4682 dict_index_copy_types(range_end, index, key->key_parts);
(gdb)
4684 row_sel_convert_mysql_key_to_innobase(
(gdb)
4693 row_sel_convert_mysql_key_to_innobase(
(gdb)
4701 mode1 = convert_search_mode_to_innobase(min_key ? min_key->flag
:
(gdb)
4703 mode2 = convert_search_mode_to_innobase(max_key ? max_key->flag
:
(gdb)
4706 n_rows = btr_estimate_n_rows_in_range(index, range_start,
(gdb)
4708 dtuple_free_for_mysql(heap1);
(gdb) print n_rows
$13 = 2
(gdb) c
Continuing.
Breakpoint 1, row_search_for_mysql (buf=0xa374ea0 "000 1 0 \bText 017 ",
mode=1, prebuilt=0x40cde068, match_mode=0, direction=0) at row0sel.c:3033
3033 dict_index_t* index = prebuilt->index;
Current language: auto; currently c
(gdb) next
3034 dtuple_t* search_tuple = prebuilt->search_tuple;
(gdb)
3035 btr_pcur_t* pcur = prebuilt->pcur;
(gdb)
3036 trx_t* trx = prebuilt->trx;
(gdb) bt
#0 row_search_for_mysql (buf=0xa374ea0 "000 1 0 \bText 017 ", mode=1,
prebuilt=0x40cde068, match_mode=0, direction=0) at row0sel.c:3036
#1 0x08276dad in ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_
function) (this=0xa374d60, buf=0xa374ea0 "000 1 0 \bText 017 ",
key_ptr=0xa3959c8 "000 1 ", key_len=6, find_flag=HA_READ_AFTER_KEY)
at ha_innodb.cc:3402
#2 0x08269e40 in handler::read_range_first(st_key_range const*, st_key_range co
nst*, bool, bool) (this=0xa374d60, start_key=0xa37bb60, end_key=0xa37bb6c,
eq_range_arg=false, sorted=false) at handler.cc:2249
#3 0x08269a8c in handler::read_multi_range_first(st_key_multi_range**, st_key_m
ulti_range*, unsigned, bool, st_handler_buffer*) (this=0xa374d60,
found_range_p=0x85d7f2f8, ranges=0xa37bb60, range_count=1, sorted=false,
buffer=0x0) at handler.cc:2121
#4 0x08258090 in QUICK_RANGE_SELECT::get_next() (this=0xa37b8d8)
at opt_range.cc:6052
#5 0x08261621 in rr_quick (info=0xa39db3c) at records.cc:157
#6 0x081fef14 in join_init_read_record (tab=0xa39db00) at sql_select.cc:9604
#7 0x081fdbcf in sub_select (join=0xa371200, join_tab=0xa39db00,
end_of_records=false) at sql_select.cc:9045
#8 0x081fd7ee in do_select (join=0xa371200, fields=0xa37940c, table=0x0,
procedure=0x0) at sql_select.cc:8829
#9 0x081ecc57 in JOIN::exec() (this=0xa371200) at sql_select.cc:1634
#10 0x081edd2f 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=0xa379180,
rref_pointer_array=0xa3794a4, tables=0xa370a40, wild_num=1,
fields=@0xa37940c, conds=0xa371138, og_num=0, order=0x0, group=0x0,
having=0x0, proc_param=0x0, select_options=2156153344, result=0xa3711f0,
unit=0xa3791d0, select_lex=0xa3793a0) at sql_select.cc:2051
#11 0x081e8230 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
thd=0xa379180, lex=0xa3791c0, result=0xa3711f0, setup_tables_done_option=0)
at sql_select.cc:227
#12 0x081b4fb1 in mysql_execute_command(THD*) (thd=0xa379180)
at sql_parse.cc:2382
#13 0x081bd1cb in mysql_parse(THD*, char*, unsigned) (thd=0xa379180,
inBuf=0xa370908 "SELECT * FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 2
' OR ARG1 != ' 1')", length=77) at sql_parse.cc:5170
#14 0x081b30f7 in dispatch_command(enum_server_command, THD*, char*, unsigned)
(command=COM_QUERY, thd=0xa379180, packet=0xa391981 "", packet_length=78)
---Type <return> to continue, or q <return> to quit---
at sql_parse.cc:1647
#15 0x081b293d in do_command(THD*) (thd=0xa379180) at sql_parse.cc:1453
#16 0x081b1acc in handle_one_connection (arg=0xa379180) at sql_parse.cc:1110
#17 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#18 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#19 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb) frame 1
#1 0x08276dad in ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_
function) (this=0xa374d60, buf=0xa374ea0 "000 1 0 \bText 017 ",
key_ptr=0xa3959c8 "000 1 ", key_len=6, find_flag=HA_READ_AFTER_KEY)
at ha_innodb.cc:3402
3402 ret = row_search_for_mysql((byte*) buf, mode, prebuilt, match_mo
de, 0);
Current language: auto; currently c++
(gdb) x/6b key_ptr
0xa3959c8: 0x30 0x30 0x30 0x20 0x31 0x20
(gdb)
[13 Apr 2005 18:46]
Sergey Petrunya
The problem is not with index_merge code. It occurs on table with one single-part key too. My curent guess is that there is a problem with SEL_TREE construction. I'm investigating it.
[16 Apr 2005 8:10]
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/24078
[16 Apr 2005 17:50]
Sergey Petrunya
4.1 has the same problem (and the commited patch fixes it)
[16 Apr 2005 22:01]
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/24087
[16 Apr 2005 22:39]
Sergey Petrunya
Pushed into 4.1.12/5.0.5 trees
[28 Apr 2005 1:34]
Paul DuBois
Noted in 4.1.12, 5.0.5 changelogs.
[27 Dec 2007 16:17]
Justin Patel
result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B) It does not differ it will give you same result but it will be processed different <a href = "http://globalprompt.net/"> IT Outsourcing </a>
Description: SELECT count(*) FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2') returns 4 rows, while SELECT count(*) FROM VERI_CLNT WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1') returns only 1 row. How to repeat: CREATE TABLE "VERI_CLNT" ( "CLIENT" char(3) character set latin1 collate latin1_bin NOT NULL default '000', "ARG1" char(3) character set latin1 collate latin1_bin NOT NULL default '', "ARG2" char(3) character set latin1 collate latin1_bin NOT NULL default '', "FUNCTION" varchar(10) character set latin1 collate latin1_bin NOT NULL default '', "FUNCTINT" int(11) NOT NULL default '0', PRIMARY KEY ("CLIENT","ARG1","ARG2"), KEY "VERI_CLNT~1" ("ARG1","FUNCTION","FUNCTINT"), KEY "VERI_CLNT~2" ("ARG1","ARG2") ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `VERI_CLNT` VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0), ('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0), ('001',' 3',' 0','Text 017',0);