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:
None 
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:49] Georg Richter
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);
[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>