| Bug #48161 | Partitions: search fails with non-default collation | ||
|---|---|---|---|
| Submitted: | 19 Oct 2009 18:14 | Modified: | 13 Nov 2009 2:49 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.4.3-beta | OS: | Linux (SUSE 11.1 64-bit) |
| Assigned to: | Mikael Ronström | CPU Architecture: | Any |
[19 Oct 2009 19:09]
MySQL Verification Team
Thank you for the bug report. Verified as described. mysql> select * from t3 where s2 <> 'a' collate latin1_bin and s1 = 'A' collate latin1_bin; Empty set (0.00 sec) mysql> alter table t3 remove partitioning; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t3 where s2 <> 'a' collate latin1_bin and s1 = 'A' collate latin1_bin; +------+------+ | s1 | s2 | +------+------+ | A | A | +------+------+ 1 row in set (0.00 sec)
[26 Oct 2009 16:42]
Mikael Ronström
This bug is due to an optimiser bug in get_mm_tree that can also be reproduced with a similar test case in MySQL 5.1 without partitioning but instead using index and no index.
[4 Nov 2009 15:45]
Mikael Ronström
Duplicate of BUG#48447
[4 Nov 2009 16:28]
Peter Gulutzan
Should not be marked as a duplicate at this time. Bug#48447 is a bug in 5.1 affecting indexes. Bug#48161 is a bug in 5.4 affecting unindexed partitions. Sure, the code that causes the problem is the same for both. But it's important to keep them separate because this is a SRGAQUAL bug which technically (I think!) means the new partitioning patch cannot go into the next ("Betony") milestone unless this is fixed in the next few days. If we mark Bug#48161 as "duplicate" then it looks like there are no GA-qualifying bugs affecting the new partitioning patch -- but there is one.
[5 Nov 2009 14:46]
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/89482 2924 Mikael Ronstrom 2009-11-05 BUG#48447, BUG#48161, fixed a regression from fix of BUG#6045, where binary collations can use indexes/partition pruning for cases using equality conditions, however it cannot be used for any other condition like <, >, <=, >=, <>, also added test case for verification of BUG#47774 in this patch
[6 Nov 2009 10:50]
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/89569 2925 Mikael Ronstrom 2009-11-06 Review fixes for BUG#48161 and BUG#48447
[6 Nov 2009 22:23]
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/89682 2915 Mikael Ronstrom 2009-11-06 [merge] Merge BUG#48161, BUG#48447
[11 Nov 2009 6:53]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091110083317-h00x61ugz9fxhdod) (merge vers: 6.0.14-alpha) (pib:13)
[12 Nov 2009 8:19]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091110083426-bm3am5445pfrrci9) (merge vers: 5.5.0-beta) (pib:13)
[13 Nov 2009 2:49]
Paul DuBois
Noted in 5.5.0, 6.0.14 changelogs. Searches using a non-default collation could return different results for a table when partitioning was and was not used.
Description: I'm using mysql-trunk-wl3352. I create a partitioned table with the default collation. I insert some rows. I search using a non-default collation. I get no rows. I remove the partitioning and do the same search again. I get some rows. How to repeat: drop table if exists t3; create table t3 (s1 varchar(2) character set latin1, s2 varchar(2) character set latin1) partition by list column_list(s1,s2) (partition p1 values in (column_list('a','a'))); insert into t3 values ('A','A'); select * from t3 where s2 <> 'a' collate latin1_bin; select * from t3 where s1 = 'A' collate latin1_bin; select * from t3 where s2 <> 'a' collate latin1_bin and s1 = 'A' collate latin1_bin; alter table t3 remove partitioning; select * from t3 where s2 <> 'a' collate latin1_bin and s1 = 'A' collate latin1_bin;