| Bug #11398 | Varchar trailing space handling breaks multi-column indexing | ||
|---|---|---|---|
| Submitted: | 16 Jun 2005 21:15 | Modified: | 29 Aug 2005 21:56 |
| Reporter: | Chris Perry | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.7-beta | OS: | Solaris (Solaris 10) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[16 Jun 2005 21:55]
Chris Perry
Confirmed that this behavior is exhibited on both of the following: mysql> status -------------- bin/mysql Ver 14.10 Distrib 5.0.6-beta, for pc-linux-gnu (i686) using readline 4.3 Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.6-beta-max Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 14 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 0 Queries per second avg: 0.214 -------------- and mysql> status -------------- bin/mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline 4.3 Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.7-beta-max Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 22 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 0 Queries per second avg: 0.136 -------------- Unfortunately, I don't have a Solaris box that I can conveniently check 5.0.7 against
[16 Jun 2005 22:13]
Chris Perry
It is probably worth adding that my bug title about varchar trailing space handling is a presumption on my part, I do not know that for sure.
[17 Jun 2005 0:42]
Chris Perry
Compared old releases, problem first appears in 5.0.3, 5.0.2 does not exhibit the behavior.
[18 Jun 2005 6:36]
Vasily Kishkin
Tested on Win 2000 Sp4, MySQL server 5.0.7 beta. It's OK on 4.1.13
[16 Aug 2005 17:45]
Evgeny Potemkin
When copying varchar fields with field_conv() it's not taken into account that length_bytes of source and destination fields may be different. This results in saving wrong data in field and making wrong key later.
[16 Aug 2005 18:13]
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/internals/28343
[22 Aug 2005 12:41]
Evgeny Potemkin
Fixed in 5.0.12, cset 1.1886.87.1
[29 Aug 2005 21:56]
Mike Hillyer
Documented in 5.0.12 changelog: <listitem><para>Joins on <literal>VARCHAR</literal> columns of different lengths could produce incorrect results. (Bug #11398)</para></listitem>
Description: If a varchar field is used in a multi-column primary or unique key and joined to another table with a varchar field of different size, the query will fail if there is a where clause and it does not include one or the other varchar field (returns only records from the master table, not the joined table). How to repeat: create table var6 (f1 varchar(6) default NULL, f2 int(6) primary key not null); create table var5 (f1 varchar(5) not null, fother varchar(5) not null, UNIQUE KEY UKEY (f1, fother)); insert into var6 (f1, f2) values ("1", 1), (" 2", 2), (" 3", 3), (" 4", 4), (" 5", 5); insert into var5 (f1, fother) values ("1"," one "), ("1"," two "), ("1","three"), ("1"," four"), ("1"," five"), (" 2", " one "), (" 2", " two "), (" 2", "three"), (" 2", " four"), (" 2", " five"), (" 3", " one "), (" 3", " two "), (" 3", "three"), (" 3", " four"), (" 3", " five"), (" 4", " one "), (" 4", " two "), (" 4", "three"), (" 4", " four"), (" 4", " five"), (" 5", " one "), (" 5", " two "), (" 5", "three"), (" 5", " four"), (" 5", " five"); select * from var6 left join var5 on var6.f1 = var5.f1 where var6.f2 = 2; Returns: +------+----+------+--------+ | f1 | f2 | f1 | fother | +------+----+------+--------+ | 2 | 2 | NULL | NULL | +------+----+------+--------+ 1 row in set (0.00 sec) It should return (and does on 4.1.10-max): +------+----+------+--------+ | f1 | f2 | f1 | fother | +------+----+------+--------+ | 2 | 2 | 2 | five | | 2 | 2 | 2 | four | | 2 | 2 | 2 | one | | 2 | 2 | 2 | two | | 2 | 2 | 2 | three | +------+----+------+--------+ 5 rows in set (0.01 sec) Adding an additional constraint against f1 in var5 or var6 strangely returns the correct results: select * from var6 left join var5 on var6.f1 = var5.f1 where var6.f1 = " 2" and var6.f2 = 2; +------+----+------+--------+ | f1 | f2 | f1 | fother | +------+----+------+--------+ | 2 | 2 | 2 | five | | 2 | 2 | 2 | four | | 2 | 2 | 2 | one | | 2 | 2 | 2 | two | | 2 | 2 | 2 | three | +------+----+------+--------+ 5 rows in set (0.01 sec) select * from var6 left join var5 on var6.f1 = var5.f1 where var5.f1 = " 2" and var6.f2 = 2; +------+----+------+--------+ | f1 | f2 | f1 | fother | +------+----+------+--------+ | 2 | 2 | 2 | five | | 2 | 2 | 2 | four | | 2 | 2 | 2 | one | | 2 | 2 | 2 | two | | 2 | 2 | 2 | three | +------+----+------+--------+ 5 rows in set (0.00 sec) As does ignoring the key: select * from var6 left join var5 ignore key (UKEY) on var6.f1 = var5.f1 where var6.f2 = 2; +------+----+------+--------+ | f1 | f2 | f1 | fother | +------+----+------+--------+ | 2 | 2 | 2 | one | | 2 | 2 | 2 | two | | 2 | 2 | 2 | three | | 2 | 2 | 2 | four | | 2 | 2 | 2 | five | +------+----+------+--------+ 5 rows in set (0.00 sec) Changing the width of column f1 in var5 to varchar(6) makes the original query return the correct results. Suggested fix: For a workaround, make sure joined column widths are the same, ignore indices where they are not, or add an additional contraint that includes one of the varchar fields. Will try an upgrade to 5.0.7 to see if the problem has been fixed and post my findings.