However, if I implement this (not removing 0s), a test case in the
falcon_online_index test will fail as well. Looking closer it seems that
the LIKE search will fail when the field is part of a multi column
index.
1)
mysql> CREATE TABLE t1 (a int PRIMARY KEY, b VARCHAR(255), c int, d
VARCHAR(10) ) engine=falcon;
2)
Bunch of inserts
3)
mysql> select * from t1;
+----+-----------+------+--------+
| a | b | c | d |
+----+-----------+------+--------+
| 0 | TestRow0 | 0 | Char0 |
| 1 | TestRow1 | 2 | Char1 |
| 2 | TestRow2 | 4 | Char2 |
| 3 | TestRow3 | 6 | Char3 |
| 4 | TestRow4 | 8 | Char4 |
| 5 | TestRow5 | 10 | Char5 |
| 6 | TestRow6 | 12 | Char6 |
| 7 | TestRow7 | 14 | Char7 |
| 8 | TestRow8 | 16 | Char8 |
| 9 | TestRow9 | 18 | Char9 |
| 10 | TestRow10 | 20 | Char10 |
| 11 | TestRow11 | 22 | Char11 |
| 12 | TestRow12 | 24 | Char12 |
| 13 | TestRow13 | 26 | Char13 |
| 14 | TestRow14 | 28 | Char14 |
| 15 | TestRow15 | 30 | Char15 |
| 16 | TestRow16 | 32 | Char16 |
| 17 | TestRow17 | 34 | Char17 |
| 18 | TestRow18 | 36 | Char18 |
| 19 | TestRow19 | 38 | Char19 |
+----+-----------+------+--------+
20 rows in set (0.00 sec)
4)
mysql> select * from t1 where b like 'TestRow3%';
+---+----------+------+-------+
| a | b | c | d |
+---+----------+------+-------+
| 3 | TestRow3 | 6 | Char3 |
+---+----------+------+-------+
1 row in set (0.00 sec)
5)
mysql> CREATE INDEX ix_c ON t1 (b);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
6)
mysql> select * from t1 where b like 'TestRow3%';
+---+----------+------+-------+
| a | b | c | d |
+---+----------+------+-------+
| 3 | TestRow3 | 6 | Char3 |
+---+----------+------+-------+
1 row in set (0.00 sec)
7)
mysql> DROP INDEX ix_c on t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
8)
mysql> ALTER TABLE t1 ADD INDEX index_multicol (b, d);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
9)
mysql> select * from t1 where b like 'TestRow3%';
Empty set (0.00 sec)
This is the case for both the ONLINE and the non-ONLINE version of the
ALTER.
On Fri, 2008-12-05 at 09:56 +0100, Lars-Erik Bjørk wrote:
> On Thu, 2008-12-04 at 11:16 -0500, Ann W. Harrison wrote:
> > Lars-Erik,
> > >
> > > The trailing spaces (and minimum sort characters) I have been removing
> > > so far, I have converted to the given character set, so for f.eks UCS2,
> > > I have been looking to remove 0x0020, etc
> >
> > Whatever the representation for spaces is, that's what we can safely
> > remove. Everything else stays.
> > >
> > > Should it be done like this for trailing zeros as well, so that I in the
> > > case of ucs2 should be looking to remove 0x0000?
> > >
> >
> > No - we remove trailings zeros on numeric keys only. Happily they
> > don't have collations.
> >
> >
>
> Aha! Good to hear, the old implementation of
> MySQLCollation::computeLength was checking for zeroes, padChars and
> minSortChars:
>
> static inline uint computeKeyLength (uint length, const char *key,
> char padChar, char minSortChar)
> {
> for (const char *p = key + length; p > key; --p)
> if ((p[-1] != 0) && (p[-1] != padChar) && (p[-1] !=minSortChar))
> return (uint) (p - key);
> return 0;
> }
>
>
> /Lars-Erik
>
> > Cheers,
> >
> > Ann
> >
>
>
