Bug #23205 Incorrect SUBSTRING result if using negative lenghts with InnoDB and GROUP BY

• Submitted: 12 Oct 2006 9:36
• Modified: 12 Oct 2006 14:00
• Reporter: Mike Saywell
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.20
• OS: Linux (Linux)
• CPU Architecture: Any

[12 Oct 2006 9:36] Mike Saywell

Description:
When using the InnoDB storage engine SUBSTRING may not work as expected if using negative lengths when the same column is part of a GROUP BY clause.

i.e. SELECT ID, Value, SUBSTRING(Value, -3) FROM test GROUP BY Value;

The same result occurs when doing a SELECT DISTINCT query.

MyISAM tables work as expected.

How to repeat:
mysql> CREATE TABLE `test` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(20) NOT NULL default '',
  `Value` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> INSERT INTO test SET Name="Foo", Value="Wibble";

# Correct:
mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | ble                  |
+----+--------+----------------------+
1 row in set (0.00 sec)

#Incorrect:
mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test GROUP BY Value;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | b                    |
+----+--------+----------------------+
1 row in set (0.00 sec)

#Incorrect:
mysql> SELECT DISTINCT ID, Value, SUBSTRING(Value, -3) FROM test;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | b                    |
+----+--------+----------------------+
1 row in set (0.00 sec)

Suggested fix:
Don't group by the column you are doing the substring on...

mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test GROUP BY ID;

or use the MyISAM storage engine....

mysql> ALTER TABLE test TYPE=MYISAM;
...
mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test GROUP BY Value;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | ble                  |
+----+--------+----------------------+
1 row in set (0.00 sec)

[12 Oct 2006 14:00] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat with 4.1.22-BK on Linux:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22

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

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> CREATE TABLE `test` (
    ->   `ID` int(11) NOT NULL auto_increment,
    ->   `Name` varchar(20) NOT NULL default '',
    ->   `Value` varchar(20) NOT NULL default '',
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test SET Name="Foo", Value="Wibble";
Query OK, 1 row affected (0.00 sec)

mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | ble                  |
+----+--------+----------------------+
1 row in set (0.01 sec)

mysql> SELECT ID, Value, SUBSTRING(Value, -3) FROM test GROUP BY Value;
+----+--------+----------------------+
| ID | Value  | SUBSTRING(Value, -3) |
+----+--------+----------------------+
|  1 | Wibble | ble                  |
+----+--------+----------------------+
1 row in set (0.01 sec)

So, just use newer version, 4.1.21. If the bug is still there, wait for 4.1.22 to be released officially (or build yourself from sources).