Bug #42298 Maria: SELECT with join returns no rows
Submitted: 23 Jan 2009 10:27 Modified: 7 May 2009 8:33
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Maria storage engine Severity:S3 (Non-critical)
Version:6.0-maria OS:Linux
Assigned to: Guilhem Bichot CPU Architecture:Any

[23 Jan 2009 10:27] Guilhem Bichot
Description:
Does not happen in 5.1-maria. Observed when running:
./mtr --force --mysqld=--default-storage-engine=maria order_by

A testcase extracted from order_by.test is attached.
When I run it with:
./mtr --mysqld=--default-storage-engine=myisam --mem bug
I see 52.5 and other values in the result of SELECT; same with innodb/falcon/memory; but with Maria the SELECT returns no rows.
Make sure to run order_by.test with engine=maria before closing this bug.

How to repeat:
see description
[23 Jan 2009 10:29] Guilhem Bichot
tescase

Attachment: bug.test.bz2 (application/x-bzip2, text), 4.25 KiB.

[11 Mar 2009 20:39] Guilhem Bichot
Still happens. With transactional=0 row_format=dynamic too.
[11 Mar 2009 21:31] Guilhem Bichot
When reducing the test case I get different errors (6.0-maria
revision-id:guilhem@mysql.com-20090311172026-tizxk5bv74fzybwh ).
With the attached bug176.test the SELECT says:
1030: Got error 176 from storage engine
With the attached bug_crashed.test it says:
Table 't2' is marked as crashed and should be repaired.
I'd start by 176 which is the smallest testcase of the two.
[11 Mar 2009 21:32] Guilhem Bichot
testcase for error 176

Attachment: bug176.test (application/octet-stream, text), 535 bytes.

[11 Mar 2009 21:32] Guilhem Bichot
testcase for "marked as crashed"

Attachment: bug_crashed.test (application/octet-stream, text), 3.85 KiB.

[11 Mar 2009 21:33] Guilhem Bichot
run those two with
./mtr --mysqld=--default-storage-engine=maria
[12 Mar 2009 9:19] Guilhem Bichot
goes away when removing HA_DO_INDEX_COND_PUSHDOWN from ha_maria.h
[13 Mar 2009 14:11] 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/commits/69152

2726 Guilhem Bichot	2009-03-13
      Fix for multiple symptoms sharing the same cause:
      BUG#42297 Maria: crash in multi-range-read code
      BUG#42298 Maria: SELECT with join returns no rows
      BUG#42299 Maria: SELECT using cp1251-table returns no rows
      BUG#42681 Maria returns duplicate rows with range access on 'date type
      BUG#42683 Maria returns wrong results for <= NULL and <> NULL 
      BUG#43527 Maria returns no rows on multi range access with limit clause
      BUG#43530 Maria has Issues with range select <>, < with -ve range values on signed index
      BUG#43552 Maria returned wrong rows with range access on float
      BUG#43620 Maria throws 'Got error 176 from storage engine' on a range query
      BUG#43623 Maria returns no rows with date index on range access >, >=, BETWEEN
     @ mysql-test/suite/maria/r/maria.result
        after fixing the bug, we can see one more row in the result. Ah, if we had paid attention
        to maria.result when we added this straight_join test, we would have caught the bug immediately.
     @ mysql-test/suite/maria/r/maria4.result
        result
     @ mysql-test/suite/maria/t/maria4.test
        test for fixed bugs. All its pieces would fail (errno 176, missing rows, too many rows) without the entire
        bugfix of ma_rkey.c
     @ storage/maria/ma_rkey.c
        Because of missing (), icp_res was inverted compared to the result of
        ma_check_index_cond(), which wasn't desired (0==0 -> 1, 1==0 -> 0). We would go
        to "err:" wrongly and thus pick up the value of my_errno which was left from previous
        functions (for example, 176 left by the ha_tina CSV log write at start of statement!);
        sometimes the errno would be returned to client, sometimes it would just cause
        a matching row to be missed.
        This fixed BUG#42297 BUG#42298. But was not enough for BUG#43552:
        - icp_res==2 was not converted to "key not found", causing non-matching rows to be returned.
        Now the usage of icp_res is closer to ma_rnext.c and ma_rnext_same.c.
[3 Apr 2009 14:52] Bugs System
Pushed into 6.0.11-alpha (revid:guilhem@mysql.com-20090402210815-lu17n4kj8c73cfe8) (version source revid:guilhem@mysql.com-20090313141043-73a6mr7hsrqm3djc) (merge vers: 6.0.11-alpha) (pib:6)
[7 May 2009 8:33] MC Brown
A note has been added to the 6.0.11 changelog: 

Running a SELECT using a JOIN on a Maria table could return invalid result sets