MySQL Bugs: #24778: Innodb: No result when using ORDER BY

Bug #24778	Innodb: No result when using ORDER BY
Submitted:	2 Dec 2006 16:17	Modified:	24 Apr 2007 0:58
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.32-BK, 5.0.27	OS:	Linux (Debian GNU/Linux Sid)
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
Hello

As reported in http://bugs.debian.org/397597 Enrico Zini seems to have found a bug in the InnoDB engine. The same query gives a NO result when using "ORDER BY"
but 53 rows when not using ORDER BY.

bye,

-christian-

How to repeat:
// The file initial-data is attached.

// Setup
# mysqladmin drop test
# mysqladmin create test
# mysql test < initial-build 

// Queries
# mysql -t -v test < t.select       
--------------
SELECT
  pa.lat
FROM
  context AS c 
  JOIN pseudoana AS pa ON c.id_ana = pa.id 
  JOIN data AS d ON d.id_context=c.id 
WHERE
  c.id_report=1 AND 
  c.datetime >= '1001-01-01 00:00:00' 
ORDER BY  c.id_ana, c.datetime, c.ltype, c.l1, c.l2, c.ptype, c.p1, c.p2
--------------

--------------
SELECT
  pa.lat
FROM
  context AS c 
  JOIN pseudoana AS pa ON c.id_ana = pa.id 
  JOIN data AS d ON d.id_context=c.id 
WHERE
  c.id_report=1 AND 
  c.datetime >= '1001-01-01 00:00:00'
--------------

+---------+
| lat     |
+---------+
| 3388000 | 
...

// Queries with EXPLAIN

# mysql test < t.select  -t
+----+-------------+-------+-------+--------------------------------------------+------------+---------+-----------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                              | key        | key_len | ref       | rows | Extra                                        |
+----+-------------+-------+-------+--------------------------------------------+------------+---------+-----------+------+----------------------------------------------+
|  1 | SIMPLE      | pa    | index | PRIMARY                                    | lat        | 17      | NULL      |    2 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | c     | ref   | PRIMARY,id_ana,id_ana_2,id_report,datetime | NULL       | NULL    | NULL      |    1 | Using where; Using index                     | 
|  1 | SIMPLE      | d     | ref   | id_context                                 | id_context | 4       | test.c.id |    1 | Using index                                  | 
+----+-------------+-------+-------+--------------------------------------------+------------+---------+-----------+------+----------------------------------------------+
+----+-------------+-------+-------+--------------------------------------------+------------+---------+------------+------+-------------+
| id | select_type | table | type  | possible_keys                              | key        | key_len | ref        | rows | Extra       |
+----+-------------+-------+-------+--------------------------------------------+------------+---------+------------+------+-------------+
|  1 | SIMPLE      | pa    | index | PRIMARY                                    | lat        | 17      | NULL       |    2 | Using index | 
|  1 | SIMPLE      | c     | ref   | PRIMARY,id_ana,id_ana_2,id_report,datetime | id_ana_2   | 4       | test.pa.id |    1 | Using where | 
|  1 | SIMPLE      | d     | ref   | id_context                                 | id_context | 4       | test.c.id  |    1 | Using index | 
+----+-------------+-------+-------+--------------------------------------------+------------+---------+------------+------+-------------+

Suggested fix:
none known

Setup data for the test case

Attachment: initial-build (application/octet-stream, text), 30.54 KiB.

Queries

Attachment: t.select (application/octet-stream, text), 453 bytes.

BTW, the bug vanishes when dumping and restoring the table. It would probably also do if using OPTIMIZE TABLE but I did not check that.

This seems to be fixed in 5.0.30 (enterprise source from ftp.mysql.com). But please verify it in the changelog, maybe it just needs a different ORDER BY to trigger this bug now.

bye,

-christian-

Thank you for a bug report. Verified just as described with latest 5.0.32-BK on Linux.

I get an assertion failure in a debug version of InnoDB-5.0 in the last join.

070107 19:38:43InnoDB: Assertion failure in thread 1276656560 in file ../include/btr0btr.ic line 129
InnoDB: Failing assertion: mtr_memo_contains(mtr, buf_block_align(page), 2) || mtr_memo_contains(mtr, buf_block_align(page), 1)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1276656560 (LWP 19133)]
0x084b5ef4 in btr_page_get_next (page=0x40698000 "", mtr=0x4c182610)
    at btr0btr.ic:126
126     btr0btr.ic: No such file or directory.
        in btr0btr.ic
Current language:  auto; currently c
(gdb) bt
#0  0x084b5ef4 in btr_page_get_next (page=0x40698000 "", mtr=0x4c182610)
    at btr0btr.ic:126
#1  0x084c55c0 in btr_pcur_is_after_last_in_tree (cursor=0x96cd7e8,
    mtr=0x4c182610) at btr0pcur.ic:243
#2  0x084c5a4d in btr_pcur_move_to_next (cursor=0x96cd7e8, mtr=0x4c182610)
    at btr0pcur.ic:362
#3  0x084a6223 in row_search_for_mysql (buf=0x96cce40 "ÿ\035", mode=2,
    prebuilt=0x96cd5e0, match_mode=1, direction=0) at row0sel.c:4027
#4  0x08312edc in ha_innobase::index_read (this=0x96ccd00,
    buf=0x96cce40 "ÿ\035", key_ptr=0x96d7d28 "", key_len=0,
    find_flag=HA_READ_KEY_EXACT) at ha_innodb.cc:3826
#5  0x08285dcc in join_read_always_key (tab=0x96d7190) at sql_select.cc:10702
#6  0x08284c16 in sub_select (join=0x96d4fe0, join_tab=0x96d7190,
    end_of_records=false) at sql_select.cc:10182
#7  0x08284ef8 in evaluate_join_record (join=0x96d4fe0, join_tab=0x96d7020,
    error=0, report_error=0x964bd44 "") at sql_select.cc:10300
#8  0x08284c39 in sub_select (join=0x96d4fe0, join_tab=0x96d7020,
    end_of_records=false) at sql_select.cc:10183
#9  0x082847a3 in do_select (join=0x96d4fe0, fields=0x0, table=0x96d8970,
    procedure=0x0) at sql_select.cc:9946
#10 0x08270f74 in JOIN::exec (this=0x96d4fe0) at sql_select.cc:1469
#11 0x082729cf in mysql_select (thd=0x964b2b0, rref_pointer_array=0x964b6ac,
    tables=0x96ae4a0, wild_num=0, fields=@0x964b610, conds=0x96d46d0,
    og_num=8, order=0x96d4838, group=0x0, having=0x0, proc_param=0x0,
    select_options=2156153344, result=0x96d4fd0, unit=0x964b354,
    select_lex=0x964b584) at sql_select.cc:2017
#12 0x0826d0b6 in handle_select (thd=0x964b2b0, lex=0x964b2f0,
    result=0x96d4fd0, setup_tables_done_option=0) at sql_select.cc:245
#13 0x0822fdeb in mysql_execute_command (thd=0x964b2b0) at sql_parse.cc:2612
#14 0x08238a64 in mysql_parse (thd=0x964b2b0,
    inBuf=0x96ad618 "SELECT pa.lat, pa.lon, pa.ident, c.ltype, c.l1, c.l2, c.ptype, c.p1, c.p2, c.datetime, c.id_report, d.id_var, d.value, pa.id, c.id FROM context AS c JOIN pseudoana AS pa ON c.id_ana = pa.id JOIN data "..., length=355)
    at sql_parse.cc:5825
#15 0x0822ddcd in dispatch_command (command=COM_QUERY, thd=0x964b2b0,
    packet=0x96a55b9 "SELECT pa.lat, pa.lon, pa.ident, c.ltype, c.l1, c.l2, c.ptype, c.p1, c.p2, c.datetime, c.id_report, d.id_var, d.value, pa.id, c.id FROM context AS c JOIN pseudoana AS pa ON c.id_ana = pa.id JOIN data "...,
    packet_length=356) at sql_parse.cc:1773
#16 0x0822d51d in do_command (thd=0x964b2b0) at sql_parse.cc:1557
#17 0x0822c607 in handle_one_connection (arg=0x964b2b0) at sql_parse.cc:1188
#18 0x40041b63 in start_thread () from /lib/tls/libpthread.so.0
#19 0x4018718a in clone () from /lib/tls/libc.so.6

The bug probably is that MySQL asks InnoDB to read using HA_READ_KEY_EXACT though MySQL gives key_len == 0!

#6  0x08312edc in ha_innobase::index_read (this=0x977d3f8,
    buf=0x977d538 "ÿ\035", key_ptr=0x9787eb8 "", key_len=0,
    find_flag=HA_READ_KEY_EXACT) at ha_innodb.cc:3826

InnoDB in this case does not position the cursor at all, which causes the assertion failure in the InnoDB debug version:

        if (UNIV_LIKELY(direction != 0)) {
                if (!sel_restore_position_for_mysql(&same_user_rec,
                                                BTR_SEARCH_LEAF,
                                                pcur, moves_up, &mtr)) {
                        goto next_rec;
                }

        } else if (dtuple_get_n_fields(search_tuple) > 0) {

                btr_pcur_open_with_no_init(index, search_tuple, mode,
                                        BTR_SEARCH_LEAF,
                                        pcur, 0, &mtr);

                pcur->trx_if_known = trx;
        } else {
                if (mode == PAGE_CUR_G) {
                        btr_pcur_open_at_index_side(TRUE, index,
                                        BTR_SEARCH_LEAF, pcur, FALSE, &mtr);
                } else if (mode == PAGE_CUR_L) {
                        btr_pcur_open_at_index_side(FALSE, index,
                                        BTR_SEARCH_LEAF, pcur, FALSE, &mtr);
                }
        }

This is probably a MySQL bug. key_len == 0 and HA_READ_KEY_EXACT does not sound a sensible combination.

A (more) minimal schema and data to reveal the bug.

Attachment: initial-build.mini.sql (text/x-sql), 1.88 KiB.

A (more) minimal query to go with the minimized schema.

Attachment: t.select.mini.sql (text/x-sql), 344 bytes.

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/commits/21217

ChangeSet@1.2409, 2007-03-06 13:44:28+01:00, mhansson@linux-st28.site +4 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug appears only in the case when the optimizer has chosen an index
  for accessing a particular table but finds a covering index that enables
  it to skip ORDER BY. This happens in test_if_skip_sort_order.
  
  The bug is in the assumption that: If we change the index for accessing
  a table in order to skip ORDER BY for a given plan operator, this plan 
  operator must be for the first non-const table in the plan. This is not 
  true: There can be other tables preceding it and the test case is an
  example of that. What happens is that
  create_ref_for_key is called with the set of const tables, in this case 
  the empty set. So here no key gets chosen, and we get the empty set.
  
  We should have to find all tables preceding the plan operator at hand 
  in this case. On the other hand, since we know that create_ref_for_key
  picked a key once, and we will simply swap it for another key for the same
  plan operator, it is enough to send in the set of just the previous table.
  It follows from the above reasoing that tab->key_dependent can be used,
  which is precomputed.

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/commits/21734

ChangeSet@1.2409, 2007-03-12 14:44:35+01:00, mhansson@linux-st28.site +4 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug was intruduced by the fix for bug#17212 (in 4.1). It is not 
  ok to call test_if_skip_sort_order since this function will alter the 
  execution plan. By contract it is not ok to call test_if_skip_sort_order
  in this context.
  
  This bug appears only in the case when the optimizer has chosen an index
  for accessing a particular table but finds a covering index that enables
  it to skip ORDER BY. This happens in test_if_skip_sort_order.

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/commits/21808

ChangeSet@1.2409, 2007-03-13 14:13:54+01:00, mhansson@linux-st28.site +3 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug was intruduced by the fix for bug#17212 (in 4.1). It is not 
  ok to call test_if_skip_sort_order since this function will 
  alter the execution plan. By contract it is not ok to call 
  test_if_skip_sort_order in this context.
  
  This bug appears only in the case when the optimizer has chosen 
  an index for accessing a particular table but finds a covering 
  index that enables it to skip ORDER BY. This happens in 
  test_if_skip_sort_order.

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/commits/21872

ChangeSet@1.2482, 2007-03-14 12:15:14+01:00, mhansson@linux-st28.site +3 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug was intruduced by the fix for bug#17212 (in 4.1). It is not 
  ok to call test_if_skip_sort_order since this function will 
  alter the execution plan. By contract it is not ok to call 
  test_if_skip_sort_order in this context.
  
  This bug appears only in the case when the optimizer has chosen 
  an index for accessing a particular table but finds a covering 
  index that enables it to skip ORDER BY. This happens in 
  test_if_skip_sort_order.

Pushed in 5.0.40, 5.1.17

Noted in 5.0.40, 5.1.17 changelogs.

This is alternative fix for the bug that allows for sorting on any table in the plab

Attachment: test_if_skip_sort_order.patch (text/x-patch), 828 bytes.

This was not applied in 5.0 after all.
Removed the 5.0.40 changelog entry.

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/commits/24976

ChangeSet@1.2459, 2007-04-20 11:01:53+02:00, mhansson@dl145s.mysql.com +3 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug was intruduced by the fix for bug#17212 (in 4.1). It is not 
  ok to call test_if_skip_sort_order since this function will 
  alter the execution plan. By contract it is not ok to call 
  test_if_skip_sort_order in this context.
  
  This bug appears only in the case when the optimizer has chosen 
  an index for accessing a particular table but finds a covering 
  index that enables it to skip ORDER BY. This happens in 
  test_if_skip_sort_order.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.18 changelogs.

For queries that used ORDER BY with InnoDB tables, if the optimizer
chose an index for accessing the table but found a covering index
that enabled the ORDER BY to be skipped, no results were returned.