| Bug #18242 | MySQL Server aborts when Having clause added to Select in SP | ||
|---|---|---|---|
| Submitted: | 15 Mar 2006 0:20 | Modified: | 15 Mar 2006 0:56 |
| Reporter: | Gordon Bruce | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.7 | OS: | Linux (Linux) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[15 Mar 2006 0:22]
Gordon Bruce
Sucessful query results
Attachment: Successful_Query_Results.txt (text/plain), 2.64 KiB.
[15 Mar 2006 0:24]
Gordon Bruce
Table definitions and err file contents
Attachment: Tables_and_err_file.txt (text/plain), 5.91 KiB.
[15 Mar 2006 0:26]
Gordon Bruce
Linux zeus 2.6.15-gentoo-r1 #10 SMP PREEMPT Tue Mar 7 15:36:28 MST 2006 i686 Intel(R) Xeon(TM) CPU 3.80GHz GenuineIntel GNU/Linux
[15 Mar 2006 0:56]
MySQL Verification Team
Thank you for the bug report. I was unable for to get the crash testing
against the current source server:
<cut>
-> LEFT JOIN pbx_user_route_number AS prn5
-> ON (purt.sbsc_Username = prn5.sbsc_Username
-> AND purt.purt_Sequence = prn5.purt_Sequence
-> AND prn5.purn_Seq = 4
-> )
-> WHERE purt.sbsc_Username = Username
-> AND purt_Enabled = 'Yes'
-> HAVING IF(purt_Command = 'dial', LENGTH(number) >0, LENGTH(number) = 0);
-> END//
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ;
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.8-beta-debug |
+------------------+
1 row in set (0.00 sec)
mysql> call Get_User_Route('3254101',NULL)\G
*************************** 1. row ***************************
username: 3254101
sequence: 0
number: SIP/3254102,SIP/3254123,SIP/3254127
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
*************************** 2. row ***************************
username: 3254101
sequence: 1
number: SIP/3254103,SIP/3254122
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
*************************** 3. row ***************************
username: 3254101
sequence: 2
number: SIP/3254104
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
3 rows in set (0.06 sec)
Query OK, 0 rows affected (0.07 sec)
mysql> call Get_User_Route('3254101','')\G
*************************** 1. row ***************************
username: 3254101
sequence: 0
number: SIP/3254102,SIP/3254123,SIP/3254127
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
*************************** 2. row ***************************
username: 3254101
sequence: 1
number: SIP/3254103,SIP/3254122
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
*************************** 3. row ***************************
username: 3254101
sequence: 2
number: SIP/3254104
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
3 rows in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> call Get_User_Route('3254101','555')\G
*************************** 1. row ***************************
username: 3254101
sequence: 0
number: SIP/3254102,SIP/3254123,SIP/3254127
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> call Get_User_Route('3254101','5552')\G
*************************** 1. row ***************************
username: 3254101
sequence: 0
number: SIP/3254102,SIP/3254123,SIP/3254127
command: Dial
host: NULL
context: NULL
timeout: NULL
url: NULL
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
Description: Ran the 4 cases shown below with the 3 tables, but without the HAVING cluase in the stored procedure Get_User_Route and got expected results. Added this line HAVING IF(purt_Command = 'dial', LENGTH(number) >0, LENGTH(number) = 0); on the end of the SELECT and MySQL crashed with mysqld got signal 11. Runs fine on a windows XP box. Table defs, data, query results and err file contents in attached files. Query OK, 0 rows affected (0.00 sec) DROP PROCEDURE IF EXISTS Get_User_Route; DELIMITER // CREATE PROCEDURE Get_User_Route(IN Username varchar(64), IN Caller_ID varchar(16)) BEGIN SELECT purt.sbsc_Username AS username, purt.purt_Sequence AS sequence, IF(purt_Command='dial', CASE WHEN Caller_ID IS NULL THEN CONCAT(IF(ISNULL(prn1.purn_Number),'',CONCAT(prn1.purn_Protocol,'/',prn1.purn_Number)), IF(ISNULL(prn2.purn_Number),'',CONCAT(', ',prn2.purn_Protocol,'/',prn2.purn_Number)), IF(ISNULL(prn3.purn_Number),'',CONCAT(', ',prn3.purn_Protocol,'/',prn3.purn_Number)), IF(ISNULL(prn4.purn_Number),'',CONCAT(', ',prn4.purn_Protocol,'/',prn4.purn_Number)), IF(ISNULL(prn5.purn_Number),'',CONCAT(', ',prn5.purn_Protocol,'/',prn5.purn_Number)) ) WHEN Caller_ID = '' THEN CONCAT(IF(ISNULL(prn1.purn_Number),'',CONCAT(prn1.purn_Protocol,'/',prn1.purn_Number)), IF(ISNULL(prn2.purn_Number),'',CONCAT(', ',prn2.purn_Protocol,'/',prn2.purn_Number)), IF(ISNULL(prn3.purn_Number),'',CONCAT(', ',prn3.purn_Protocol,'/',prn3.purn_Number)), IF(ISNULL(prn4.purn_Number),'',CONCAT(', ',prn4.purn_Protocol,'/',prn4.purn_Number)), IF(ISNULL(prn5.purn_Number),'',CONCAT(', ',prn5.purn_Protocol,'/',prn5.purn_Number)) ) WHEN LEFT(Caller_ID,LENGTH(purt_Cidnum)) = purt_Cidnum THEN CONCAT(IF(ISNULL(prn1.purn_Number),'',CONCAT(prn1.purn_Protocol,'/',prn1.purn_Number)), IF(ISNULL(prn2.purn_Number),'',CONCAT(', ',prn2.purn_Protocol,'/',prn2.purn_Number)), IF(ISNULL(prn3.purn_Number),'',CONCAT(', ',prn3.purn_Protocol,'/',prn3.purn_Number)), IF(ISNULL(prn4.purn_Number),'',CONCAT(', ',prn4.purn_Protocol,'/',prn4.purn_Number)), IF(ISNULL(prn5.purn_Number),'',CONCAT(', ',prn5.purn_Protocol,'/',prn5.purn_Number)) ) ELSE '' END, '') AS number, purt_Command AS command, IF(purt_Command = 'dial',padi_Host,'') AS host, CASE WHEN purt_Command = 'dial' THEN padi_Context ELSE '' END AS context, CASE WHEN purt_Command = 'dial' THEN padi_Timeout ELSE '' END AS timeout, CASE WHEN purt_Command = 'dial'THEN padi_URL ELSE '' END AS url FROM pbx_user_route AS purt LEFT JOIN pbx_app_dial AS padi USING (sbsc_Username, purt_Sequence, purt_Options ) LEFT JOIN pbx_user_route_number AS prn1 ON (purt.sbsc_Username = prn1.sbsc_Username AND purt.purt_Sequence = prn1.purt_Sequence AND prn1.purn_Seq = 0 ) LEFT JOIN pbx_user_route_number AS prn2 ON (purt.sbsc_Username = prn2.sbsc_Username AND purt.purt_Sequence = prn2.purt_Sequence AND prn2.purn_Seq = 1 ) LEFT JOIN pbx_user_route_number AS prn3 ON (purt.sbsc_Username = prn3.sbsc_Username AND purt.purt_Sequence = prn3.purt_Sequence AND prn3.purn_Seq = 2 ) LEFT JOIN pbx_user_route_number AS prn4 ON (purt.sbsc_Username = prn4.sbsc_Username AND purt.purt_Sequence = prn4.purt_Sequence AND prn4.purn_Seq = 3 ) LEFT JOIN pbx_user_route_number AS prn5 ON (purt.sbsc_Username = prn5.sbsc_Username AND purt.purt_Sequence = prn5.purt_Sequence AND prn5.purn_Seq = 4 ) WHERE purt.sbsc_Username = Username AND purt_Enabled = 'Yes' HAVING IF(purt_Command = 'dial', LENGTH(number) >0, LENGTH(number) = 0); END// DELIMITER ; How to repeat: Load the 3 tables and the stored procedure.