MySQL Bugs: #28527: Falcon: searches fail if LIKE and partition and estonian collation

Bug #28527	Falcon: searches fail if LIKE and partition and estonian collation
Submitted:	18 May 2007 17:35	Modified:	20 Nov 2007 2:20
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	6.0.1-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I create a Falcon table with a latin7 column with an Estonian collation.
The table is partitioned and indexed.
I insert 'x' and 'y' and 'z'.
I search for LIKE 'z%'.
I get no rows, even though I inserted 'z'.

I know that Bug#18198 "Expressions are allowed as partition functions"
is not fixed, there are known difficulties with ascii partition functions.
But this particular search would work if I used engine=myisam.

How to repeat:
mysql> create table t9 (s1 varchar(5) character set latin7 collate latin7_estonian_cs)
    ->  engine=falcon
    ->  partition by range (ascii(s1))
    ->  (partition p1 values less than (100), partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t9 values ('x'),('y'),('z');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create unique index i9 on t9 (s1);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t9 where s1 like 'z%';
Empty set (0.01 sec)

Thank you for the bug report. Verified as described on FC 6.0 32-bit.

I think there was some development around partition functions in this area. Miguel, can please check whether this bug still exists?

Thanks,

Hakan

The bug still exists, but after the next merge it should be impossible to
use ascii() for a partition function, so the bug will probably disappear.

However, there's a similar result without ascii().
It probably won't disappear.
I reported it as a separate bug:
Bug#30480 Falcon: searches fail if LIKE and key partition

This may not be reproducible any more.  But also check Bug#30480

Indeed not reproducible anymore, the provided CREATE statement fails with

ERROR 1564 (HY000): This partition function is not allowed

Marking this as duplicate of BUG#30480.