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:
None 
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 18:14] Peter Gulutzan
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;
[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.