MySQL Bugs: #30480: Falcon: searches fail if LIKE and key partition

Bug #30480	Falcon: searches fail if LIKE and key partition
Submitted:	17 Aug 2007 17:49	Modified:	13 Dec 2007 10:45
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	6.0.2-alpha	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
I create a Falcon table with a latin1 column with a default collation.
The table is partitioned and indexed.
I insert two rows, containing 'Ä' and 'AA'.
I search for LIKE 'A%'.
I get no rows, even though I inserted 'AA'.

The result is similar to
Bug#28527 Falcon: searches fail if LIKE and partition and estonian collation
But Bug#28527 requires an ascii() function, which should soon be illegal,
so it will probably disappear. This is different: a key partition, no
ascii() function, a non-ASCII value, and a default collation.

How to repeat:
mysql> create table td (s2 varchar(5) character set latin1)
    ->                 engine=falcon
    ->                 partition by key(s2);
Query OK, 0 rows affected (0.02 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create index id on td (s2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into td values ('Ä'),('AA');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from td where s2 like 'A%';
Empty set (0.00 sec)

Thank you for the bug report.

miguel@luar:~/dbs/6.0> cat /etc/issue

Welcome to openSUSE 10.2 (X86-64) - Kernel \r (\l).

miguel@luar:~/dbs/6.0> bin/mysqladmin -uroot create db3
miguel@luar:~/dbs/6.0> bin/mysql -uroot db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.2-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table td (s2 varchar(5) character set latin1)
    -> engine=falcon
    -> partition by key(s2);
Query OK, 0 rows affected (1.26 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create index id on td (s2);
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into td values ('Ä'),('AA');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from td where s2 like 'A%';
Empty set (0.02 sec)

mysql>

Here is another test case, this time with a ucs2 column.
The character 'A with tilde', 'Ã', is U+00C3.

mysql> create table tf (s1 varchar(5) character set ucs2) engine=falcon partition by key(s1);
Query OK, 0 rows affected (0.02 sec)

mysql> create index i2 on tf (s1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tf values ('c'),('c'),(0x00c3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tf where s1 like 'Ã%';
Empty set (0.00 sec)

mysql> select * from tf where s1    = 'Ã';
+------+
| s1   |
+------+
| Ã   |
+------+
1 row in set (0.00 sec)

I added two disabled testcases to the falcon repository which illustrate this problem.  Bothe test cases (falcon_bug_30480_A.test & falcon_bug_30480_A.test) do the query successfully first without partitions and then show that the querry fails with partitions.

The problem, as I can see from debugging it, is that the partition engine assumes that the list of records returned is in an order sorted by key. 
 
For the test in 30480_A, the index contains 'A'-recId=2, 'C'-recId=0, 'C'-recId=0.  Each of these is returned to ha_partition because ha_partition does not provide to Falcon an endKey.  They are returned in record order ('C', 'C', 'A'), not sorted order.  But in  ha_partition::handle_unordered_scan_next_partition(), Line 3944, the condition
   if (compare_key(end_range) <= 0)
returns false and the error is set to HA_ERR_END_OF_FILE, which causes the search to stop after looking at only the first returned record, 'C'.  It seems that ha-partition is using the end range even though it was not sent into Falcon.

Raised to S2 due to missing data

Will first try to change the "if (compare_key(end_range) <= 0)" to
"if (!(file->table_flags() & HA_READ_ORDER) || compare_key(end_range) <= 0)"
on the unordered functions.

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/38346

ChangeSet@1.2671, 2007-11-23 12:24:06+01:00, mattiasj@witty. +3 -0
  Bug#30480: Falcon: searches fail if LIKE and key partition
  (also fixes the bugs: 29320, 29493 and 30536)
  
  Problem: Partitioning did not handle unordered scans correctly
  for engines with unordered read order.
  
  Solution: do not stop scanning if a record is out of range, since
  there can be more records within the range afterwards.

I have verified that the patch also solves the following duplicates:
Bug#29320, Bug#29493 and Bug#30536

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/39116

ChangeSet@1.2675, 2007-12-03 16:12:25+01:00, mattiasj@witty. +1 -0
  Bug#30480: Falcon: searches fail if LIKE and key partition
  (also fixes the bugs: Bug#29320, Bug#29493 and Bug#30536)
  
  Problem: Partitioning did not handle unordered scans correctly
  for engines with unordered read order.
  
  Solution: do not stop scanning fi a recored is out of range, since
  there can be more records within the range afterwards.
  
  Note: this is the patch that fixes the bug, but since there are no
  storage engines shipped with mysql 5.1 (falcon comes in 6.0) there
  are no test cases (it is a separate patch that only goes into 6.0)

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/39117

ChangeSet@1.2671, 2007-12-03 16:17:10+01:00, mattiasj@witty. +2 -0
  Bug#30480: Falcon: searches fail if LIKE and key partition
  (also fixes the bugs: 29320, 29493 and 30536)
  
  Problem: Partitioning did not handle unordered scans correctly
  for engines with unordered read order.
  
  Solution: do not stop scanning if a record is out of range, since
  there can be more records within the range afterwards.
  
  Note. this is the test case ONLY, the real patch is separate since it
  goes into 5.1 as well (for non mysql storage engines,
  since falcon is in 6.0)

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Bugfix documented in 5.1.23 changelog as:

        LIKE queries on tables partitioned by
        KEY and using third-party storage engines
        could return incomplete results.
      
Bugfix documented in 6.0.5 changelog as:
      
        LIKE queries on tables partitioned by
        KEY could return incomplete results. The
        problem was observed with the Falcon storage
        engine, but could affect third-party storage engines as well.