Difficulties with MariaDB syntax using CTEs

Question

I have the following query:

WITH intervals AS (
    SELECT rom_opening_interval.start,
           rom_opening_interval.end,
           priority,
           DENSE_RANK() OVER (ORDER BY priority DESC) AS rank
    FROM rom_opening_time
        INNER JOIN rom_opening_interval ON rom_opening_time.id = rom_opening_interval.opening_time_id
    WHERE rom_opening_time.start <= CURRENT_DATE
        AND (rom_opening_time.end >= CURRENT_DATE OR rom_opening_time.end IS NULL)
        AND days & 8 > 0
)
SELECT start, end, priority
FROM intervals
WHERE rank = 1;

The SELECT part of the CTE works totally fine. If I try to execute the whole query, I get a not further specified syntax error at line 11.

I'm using MariaDB 10.5 (10.5.23-MariaDB-1:10.5.23+maria~ubu2004) and other queries with CTE work fine as well, like the following one:

WITH test AS (
    SELECT start FROM rom_opening_interval
)
SELECT start FROM test;

Am I missing something here? And if yes, what?

Answer 1

Tryed to test it as is (with some dummy sample data) and it works fine on
10.5.17-MariaDB-1:10.5.17+maria~deb11 see the fiddle
You should realy post some of your sample data to get an exact answer. This works ok. You shouldn't use reserved words as column names or any names, though.

WITH
  rom_opening_time AS
    ( Select  101 as id, '2024-06-01' as start, '2024-06-02' as end, 1 as priority Union All
      Select  102 as id, '2024-06-03' as start, '2024-06-09' as end, 1 as priority Union All
      Select  103 as id, '2024-06-06' as start, '2024-06-14' as end, 1 as priority Union All
      Select  104 as id, '2024-06-10' as start, '2024-06-13' as end, 2 as priority Union All
      Select  105 as id, '2024-06-15' as start, null         as end, 2 as priority 
    ), 
  rom_opening_interval AS
    ( Select  1 as id, 101 as opening_time_id, '2024-06-01' as start, '2024-06-02' as end, 1 as days Union All
      Select  2 as id, 102 as opening_time_id, '2024-06-03' as start, '2024-06-09' as end, 6 as days Union All
      Select  3 as id, 103 as opening_time_id, '2024-06-06' as start, '2024-06-14' as end, 8 as days Union All
      Select  4 as id, 104 as opening_time_id, '2024-06-10' as start, '2024-06-13' as end, 7 as days Union All
      Select  5 as id, 105 as opening_time_id, '2024-06-15' as start, Null         as end, Null as days 
    ), 
  intervals AS 
    ( SELECT  rom_opening_interval.start,
              rom_opening_interval.end,
              priority,
              DENSE_RANK() OVER (ORDER BY priority DESC) AS rank
      FROM    rom_opening_time
      INNER JOIN rom_opening_interval ON rom_opening_time.id = rom_opening_interval.opening_time_id
      WHERE   rom_opening_time.start <= CURRENT_DATE
          AND (rom_opening_time.end >= CURRENT_DATE OR rom_opening_time.end IS NULL)
          AND days & 8 > 0
    )

--    S Q L : 
SELECT   start, end, priority
FROM     intervals
WHERE    rank = 1;

/*    R e s u l t :
start        end        priority
----------  ----------  --------
2024-06-06  2024-06-14         1  */

UPDATE:
Changed sample data to be like in your comment...

WITH
  rom_opening_time AS
    ( Select  101 as id, Cast('2024-06-01' as DATE) as start, Cast('2024-06-02' as DATE) as end, 1 as priority,  1    as days Union All
      Select  102 as id, Cast('2024-06-03' as DATE) as start, Cast('2024-06-09' as DATE) as end, 1 as priority,  6    as days Union All
      Select  103 as id, Cast('2024-06-06' as DATE) as start, Cast('2024-06-14' as DATE) as end, 1 as priority,  8    as days Union All
      Select  104 as id, Cast('2024-06-10' as DATE) as start, Cast('2024-06-13' as DATE) as end, 2 as priority,  7    as days Union All
      Select  105 as id, Cast('2024-06-15' as DATE) as start, Cast(null as DATE)         as end, 2 as priority,  Null as days 
    ), 
  rom_opening_interval AS
    ( Select  1 as id, 101 as opening_time_id, '01:00:00' as start, '01:10:00' as end Union All
      Select  2 as id, 102 as opening_time_id, '02:00:00' as start, '02:20:00' as end Union All
      Select  3 as id, 103 as opening_time_id, '03:00:00' as start, '03:30:00' as end Union All
      Select  4 as id, 104 as opening_time_id, '04:00:00' as start, '04:40:00' as end Union All
      Select  5 as id, 105 as opening_time_id, '05:00:00' as start, Null       as end 
    ),

... Still works ok ...

/*      R e s u l t :
start     end       priority
--------  --------  --------
03:00:00  03:30:00         1  */

Answer 2

Because it's too long for a comment, I'll post here what I did as a workaround. It's not an exact solution to the problem in the question, but delivers the wanted result by using a subquery:

SELECT rom_opening_interval.start AS start,
       rom_opening_interval.end AS end
FROM rom_opening_time
    INNER JOIN rom_opening_interval ON rom_opening_time.id = rom_opening_interval.opening_time_id
WHERE priority = (
    SELECT MAX(priority)
    FROM rom_opening_time
        INNER JOIN rom_opening_interval ON rom_opening_time.id = rom_opening_interval.opening_time_id
   WHERE rom_opening_time.start <= :date
      AND (rom_opening_time.end >= :date OR rom_opening_time.end IS NULL)
      AND days & :day = :day)
ORDER BY rom_opening_interval.start

Answer 3

try:

SELECT start, end, priority
FROM (
    SELECT
        rom_opening_interval.start,
        rom_opening_interval.end,
        priority,
        DENSE_RANK() OVER (ORDER BY priority DESC) AS rank
    FROM rom_opening_time
    INNER JOIN rom_opening_interval ON rom_opening_time.id = rom_opening_interval.opening_time_id
    WHERE rom_opening_time.start <= CURRENT_DATE
        AND (rom_opening_time.end >= CURRENT_DATE OR rom_opening_time.end IS NULL)
        AND days & 8 > 0
) AS intervals
WHERE rank = 1;