Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13371

Classic CTE query fails in 10.2.2

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.2
    • Fix Version/s: 10.2.6
    • Component/s: Optimizer - CTE
    • Labels:
    • Environment:
      Linux Mint 18 64-bit

      Description

      Joe Celko's "Classroom scheduling problem" has a classic CTE solution that works unproblematically eg in SQL Server 2014 and PostgreSQL 9.6, but fails in MariaDB 10.2.2. An explanation of the query problem and its CTE solution is here.

      DROP TABLE IF EXISTS Rooms, Classes; 
      CREATE TABLE Rooms( 
        room_nbr CHAR(2) NOT NULL PRIMARY KEY, room_size INTEGER NOT NULL 
      ) ; 
      CREATE TABLE Classes( 
        class_nbr CHAR(2) NOT NULL PRIMARY KEY, class_size INTEGER NOT NULL 
      ) ; 
      INSERT INTO Classes   
      VALUES ('c1', 80),('c2', 70),('c3', 65),('c4', 55),('c5', 50),('c6', 40); 
      INSERT INTO Rooms  
      VALUES ('r1', 70),('r2', 40),('r3', 50),('r4', 85),('r5', 30),('r6', 65),('r7', 55); 
       
      WITH  
        Matches AS ( 
          SELECT  
            class_nbr, class_size, room_nbr, room_size,  
            CASE WHEN class_size = room_size THEN 1 ELSE 0 END AS exact_match 
          FROM Classes 
          JOIN Rooms ON class_size <= room_size 
        ), 
        Preferences AS ( 
          SELECT 
            class_nbr, class_size,  
            ROW_NUMBER() OVER ( 
              PARTITION BY class_nbr ORDER BY exact_match, room_size, room_nbr 
            ) AS class_room_pref,
            room_nbr, room_size,  
            ROW_NUMBER() OVER ( 
              PARTITION BY room_nbr ORDER BY exact_match, class_size DESC, class_nbr 
            ) AS room_class_pref
          FROM Matches m 
          WHERE NOT EXISTS (  
            SELECT 1 FROM Matches WHERE room_nbr = m.room_nbr AND class_size > m.class_size 
          ) 
        ), 
        Final AS ( 
          SELECT 
            class_nbr, class_size, room_nbr, room_size, 
            ROW_NUMBER() OVER (
              PARTITION BY class_nbr ORDER BY class_room_pref
            ) AS final_pref
          FROM Preferences p 
          WHERE NOT EXISTS ( 
            SELECT 1 FROM Preferences 
            WHERE room_nbr = p.room_nbr  
              AND class_room_pref = room_class_pref  
              AND  room_class_pref < p.room_class_pref 
          ) 
        ) 
      SELECT c.class_nbr, c.class_size, f.room_nbr, f.room_size 
      FROM Classes c 
      LEFT JOIN Final f ON c.class_nbr = f.class_nbr AND f.final_pref = 1 
      ORDER BY 1; 
      

      MariaDB 10.2.2 responds with: "Table 'test.Matches' doesn't exist."

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              pzbrawl1937 Peter Brawley
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: