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:
None 
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:20] Gordon Bruce
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.
[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>