Bug #5090 MySql (mysqld-nt.exe) service crashes on a simple LEFT function
Submitted: 18 Aug 2004 12:40 Modified: 18 Aug 2004 20:46
Reporter: Stefan Patra Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.3b OS:Microsoft Windows (Windows XP SP1)
Assigned to: CPU Architecture:Any

[18 Aug 2004 12:40] Stefan Patra
Description:
I have a table (TEST) with a single integer field (SCODE) with a single row with value 10.

I've execute (in MySql Control Center 0.9.4-beta -  but also in other application) this query (the result should be 1):

SELECT LEFT(SCODE,LENGTH(SCODE)-1)+0  FROM TEST

and MySql service crashes with this Generic Error Message: [The memory could not be "read"].

Strangely this querys works just fine:

SELECT LEFT(SCODE,0)+0  FROM TEST;
SELECT LEFT(SCODE,-1)+0  FROM TEST;
SELECT LEFT(SCODE,LENGTH(SCODE))+0  FROM TEST;
SELECT RIGHT(SCODE,LENGTH(SCODE)-1)+0  FROM TEST

In previous versions of MySql (4.0.x) this query works.

I use this method to convert a portion of a string (LEFT returns a string) to an integer. SCODE field contains 2 (or more) integer values padded with 0 and I want to extract one of them.

Example: 
   SCODE = [Contract Number] + [Branch];
   where
       [Contract Number] range: 1 to 10000;
       [Branch] range: 1..999.

   SCODE = 45013 means [Contract Number]=45 and [Branch]=13.

   So, I use LEFT/RIGHT function +0 to get the integer value.

How to repeat:
After restarting the service, repeat the query:

SELECT LEFT(SCODE,LENGTH(SCODE)-1)+0  FROM TEST
[18 Aug 2004 12:49] Stefan Patra
MyCC & Error Window screenshot

Attachment: screenshot.JPG (image/pjpeg, text), 38.72 KiB.

[18 Aug 2004 15:17] MySQL Verification Team
Was not able to repeat it with 4.1.4.
If you can send us your table, we can try your data ...
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,0)+0 from nazivi limit 5"
+-----------------+
| left(naziv,0)+0 |
+-----------------+
|            NULL |
|            NULL |
|            NULL |
|               0 |
|               0 |
+-----------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,-1)+0 from nazivi limit 5"
+------------------+
| left(naziv,-1)+0 |
+------------------+
|             NULL |
|             NULL |
|             NULL |
|                0 |
|                0 |
+------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,length(naziv))+0 from nazivi limit 5"
+-----------------------------+
| left(naziv,length(naziv))+0 |
+-----------------------------+
|                        NULL |
|                        NULL |
|                        NULL |
|                           0 |
|                           0 |
+-----------------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,length(naziv)-1)+0 from nazivi limit 5"
+-------------------------------+
| left(naziv,length(naziv)-1)+0 |
+-------------------------------+
|                          NULL |
|                          NULL |
|                          NULL |
|                             0 |
|                             0 |
+-------------------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,length(naziv)+1)+0 from nazivi limit 5"
+-------------------------------+
| left(naziv,length(naziv)+1)+0 |
+-------------------------------+
|                          NULL |
|                          NULL |
|                          NULL |
|                             0 |
|                             0 |
+-------------------------------+
[18 Aug 2004 16:25] Stefan Patra
Table test .frm file

Attachment: test.frm (application/octet-stream, text), 8.36 KiB.

[18 Aug 2004 16:26] Stefan Patra
Table test .myd file

Attachment: test.MYD (application/octet-stream, text), 5 bytes.

[18 Aug 2004 16:27] Stefan Patra
Table test .myi file

Attachment: test.MYI (application/octet-stream, text), 1024 bytes.

[18 Aug 2004 16:35] Stefan Patra
Thanks for your promptitude.
I've added the table, but I think there's nothing wrong with it.

Anyway, where do I get v4.1.4 ?

Here is another dump (using mysql\bin\mysql.exe):
{

C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.3-beta-nt

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

mysql> use oc
Database changed
mysql> show fields from test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| SCODE | int(11) |      |     | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from test;
+-------+
| SCODE |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

mysql> select left(scode,length(scode)-1)+0 from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

}

Before the ERROR 2013, the service crashed (with the same message as in the added file screenshot.jpg).
[18 Aug 2004 20:46] MySQL Verification Team
There were a number of bugs fixed in LEFT between 4.1.3 and 4.1.4 which is why I can't repeat it with int column. 4.1.4 will come in few weeks.

[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(naziv,-1)+0 from nazivi limit 5"
+------------------+
| left(naziv,-1)+0 |
+------------------+
|             NULL |
|             NULL |
|             NULL |
|                0 |
|                0 |
+------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(broj,-1)+0 from nazivi limit 5"
+-----------------+
| left(broj,-1)+0 |
+-----------------+
|               0 |
|               0 |
|               0 |
|               0 |
|               0 |
+-----------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(broj,length(broj))+0 from nazivi limit 5"
+---------------------------+
| left(broj,length(broj))+0 |
+---------------------------+
|                         0 |
|                         1 |
|                         2 |
|                         3 |
|                         4 |
+---------------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(broj,length(broj)-1)+0 from nazivi limit 5"
+-----------------------------+
| left(broj,length(broj)-1)+0 |
+-----------------------------+
|                           0 |
|                           0 |
|                           0 |
|                           0 |
|                           0 |
+-----------------------------+
[/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select left(broj,length(broj)-1)+0 from nazivi limit 5"
+-----------------------------+
| left(broj,length(broj)-1)+0 |
+-----------------------------+
|                           0 |
|                           0 |
|                           0 |
|                           0 |
|                           0 |
+-----------------------------+