Bug #72582 complex query works in 5.6, fails in 5.7.2_m14

• Submitted: 8 May 2014 16:11
• Modified: 8 May 2014 17:13
• Reporter: Jason Jakob
• Status: Can't repeat
• Category: MySQL Server: Windows
• Severity: S2 (Serious)
• Version: 5.7.2_m14
• OS: Windows (craches mysqld)
• CPU Architecture: Any
• Tags: crash mysqld

[8 May 2014 16:11] Jason Jakob

Description:
running the below query works fine on version 5.6. crashes mysqld process on version 5.7.2_m14.

The client does not matter. issuing the query from MySQL Workbench, or a C# ASP.Net page using .Net connector all result in the same outcome.

How to repeat:
create schema from attached file: myproviewer_export.sql

run this query:

SELECT
    MediaId, Trim(Url) as Url, CreateDate, CompanyId, Enabled, MetaData, IsNew, StartMonth, StartDay, EndMonth, EndDay, IsContinuous, IsScheduled, ScheduleString
FROM (
 	SELECT
 		MediaId, Url, CreateDate, CompanyId, Enabled, MetaData, IsNew, StartMonth, StartDay, EndMonth, EndDay, IsContinuous, IsScheduled, ScheduleString, CONCAT('|',GROUP_CONCAT(TagId SEPARATOR '|'),'|') AS ids
 	FROM (
  		SELECT DISTINCT
            Media.MediaId as MediaId,
            Concat('http://localhost/repository/';, Media.Url) as Url,
            Media.CreateDate as CreateDate,
            Media.CompanyId as CompanyId,
                        Media.Enabled as Enabled,
            Media.MetaData as MetaData,
            MediaTags.TagId,
            CASE WHEN Media.CreateDate > DATE_SUB(NOW(), INTERVAL 60 DAY) THEN
             '1'
            ELSE
             '0'
            END as IsNew,
            MediaSchedules.StartMonth,
            MediaSchedules.StartDay,
            MediaSchedules.EndMonth,
            MediaSchedules.EndDay,
            CASE WHEN MediaSchedules.StartMonth = 0 AND MediaSchedules.StartDay = 0 AND MediaSchedules.EndMonth = 0 AND MediaSchedules.EndDay = 0  THEN
             '1'
            ELSE
             '0'
            END as IsContinuous,
            CASE WHEN MediaSchedules.StartMonth != 0 AND MediaSchedules.StartDay != 0 AND MediaSchedules.EndMonth != 0 AND MediaSchedules.EndDay != 0  THEN
             '1'
            ELSE
             '0'
            END as IsScheduled,
            concat(DATE_FORMAT(STR_TO_DATE(concat(MediaSchedules.StartMonth,'/',MediaSchedules.StartDay,'/',2014), '%m/%d/%Y') , '%b %e'), ' - ', DATE_FORMAT(STR_TO_DATE(concat(MediaSchedules.EndMonth,'/',MediaSchedules.EndDay,'/',2014), '%m/%d/%Y') , '%b %e') ) as ScheduleString
         FROM
          Media LEFT JOIN MediaSchedules ON MediaSchedules.MediaId = Media.MediaId, MediaTags, Tag
         WHERE
          MediaTags.MediaId = Media.MediaId
          AND MediaTags.TagId = Tag.TagId
          AND Tag.TagId IN (
                  109, 73
              ) 
         ORDER BY
          CreateDate DESC
			 ) a
    WHERE  
      ( IsContinuous = '0' AND IsScheduled = '0' ) 
    GROUP BY
      MediaId, Url, CreateDate, CompanyId, Enabled, MetaData, IsNew, StartMonth, StartDay, EndMonth, EndDay, IsContinuous, IsScheduled, ScheduleString
    ) b
WHERE  
    Ids LIKE '%|109|%'  AND Ids NOT LIKE '%|73|%'
LIMIT 0, 50

[8 May 2014 16:14] Jason Jakob

sql schema file to test query in bug report. company table and foreign key constraint removed for privacy

Attachment: issue_mysql_5.7.2_crash.zip (application/x-zip-compressed, text), 718.73 KiB.

[8 May 2014 17:10] MySQL Verification Team

*************************** 50. row ***************************
       MediaId: 45962
           Url: http://localhost/repository/images/clients/101001/101001_general_2096a45c-a3f8-445b-8b8e-b...
    CreateDate: 2014-05-07 15:02:27
     CompanyId: 2
       Enabled: 1
      MetaData: {"DataType":"FinalPhoto"}
         IsNew: 1
    StartMonth: NULL
      StartDay: NULL
      EndMonth: NULL
        EndDay: NULL
  IsContinuous: 0
   IsScheduled: 0
ScheduleString: NULL
50 rows in set (2.06 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.7.5               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.7.5-m15           |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

[8 May 2014 17:13] MySQL Verification Team

Please try version 5.7.4. Thanks.