| 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: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.3b | OS: | Windows (Windows XP SP1) |
| Assigned to: | CPU Architecture: | Any | |
[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 | +-----------------------------+
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