Bug #17433 substring select problem

• Submitted: 15 Feb 2006 19:27
• Modified: 16 Feb 2006 14:54
• Reporter: Steve Morin
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11
• OS: Linux (Debian)
• CPU Architecture: Any

[15 Feb 2006 19:27] Steve Morin

Description:
Substring doesn't work as expected, when being used with a negative starting position.
Specifically doesn't work as a nested query, and with group by.

How to repeat:
create table test ( code varchar(255), id decimal(18), primary key (id) );
insert into test values('asdef',1); 
insert into test values('asdef',2);
insert into test values('asdeg',3); 
insert into test values('asdeq',4);

//baseline query which works

select substring(code,-4,4) from test;
+----------------------+
| substring(code,-4,4) |
+----------------------+
| sdef                 |
| sdef                 |
| sdeg                 |
| sdeq                 |
+----------------------+
4 rows in set (0.00 sec) 

//provides empty result set which is wrong

select * from (select substring(code,-4,4) from test) as tmp;
+----------------------+
| substring(code,-4,4) |
+----------------------+
|                      |
|                      |
|                      |
|                      |
+----------------------+
4 rows in set (0.00 sec)  

//baseline is identical results to first baseline

select substring(code,2,4) from test;
+---------------------+
| substring(code,2,4) |
+---------------------+
| sdef                |
| sdef                |
| sdeg                |
| sdeq                |
+---------------------+
4 rows in set (0.00 sec)   

//workes in this instance

select * from (select substring(code,2,4) from test) as tmp;
+---------------------+
| substring(code,2,4) |
+---------------------+
| sdef                |
| sdef                |
| sdeg                |
| sdeq                |
+---------------------+
4 rows in set (0.00 sec)

[16 Feb 2006 9:45] Valeriy Kravchuk

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

mysql> create table test ( code varchar(255), id decimal(18), primary key (id)
);
inQuery OK, 0 rows affected (0.01 sec)

mysql> insert into test values('asdef',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values('asdef',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('asdeg',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('asdeq',4);
Query OK, 1 row affected (0.00 sec)

mysql> select substring(code,-4,4) from test;
+----------------------+
| substring(code,-4,4) |
+----------------------+
| sdef                 |
| sdef                 |
| sdeg                 |
| sdeq                 |
+----------------------+
4 rows in set (0.00 sec)

mysql> select * from (select substring(code,-4,4) from test) as tmp;
+----------------------+
| substring(code,-4,4) |
+----------------------+
| sdef                 |
| sdef                 |
| sdeg                 |
| sdeq                 |
+----------------------+
4 rows in set (0.00 sec)

mysql> select * from (select substring(code,2,4) from test) as tmp;
+---------------------+
| substring(code,2,4) |
+---------------------+
| sdef                |
| sdef                |
| sdeg                |
| sdeq                |
+---------------------+
4 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.19    |
+-----------+
1 row in set (0.00 sec)

So, please, try to use newer version, 4.1.18.

[16 Feb 2006 14:54] Steve Morin

Looks like has been fixed and is commented in the following bug
#10269: SUBSTRING() with negative index returns empty from within a subquery