CODEWITHSUNDEEP
sqlmariadb
M a m a D
M a m a D

Reputation: 2139

MariaDB 10.4 Can not use a cte more than once

This is the code you can run,

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=74d081784b301ddfa8bf6d361c586693

with date_ranges(`start`, `end`, title) as (
    select  '2021-11-22', '2021-12-21 23:59:59', '1400-09'
    union all
    select '2021-12-22', '2022-01-20 23:59:59', '1400-10'
    union all
    select '2022-02-21', '2022-03-19 23:59:59', '1400-11'
),

     income as (
         select count(*) as input,
                dr.title as month
         from date_ranges dr
                  #join drivers u on u.created_at between dr.start and dr.end
         group by dr.title
     ),
     
          outcome as (
         select count(*) as input,
                ddr.title as month
         from date_ranges ddr
                  #join drivers u on u.created_at between ddr.start and ddr.end
         group by ddr.title
     )


select input, month
from  income i
order by month desc

The link above works well but my local database MariaDB 10.4 reports error

Unknown column 'ddr.title' in 'field list'

Upvotes: 0

Views: 93

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

This is a known bug and has been fixed in recent versions of MariaDB.

The following fiddle shows 10.4.22-MariaDB does not exhibit the issue, but I've verified that 10.5.0-MariaDB-log does produce the error, and 10.3.32-MariaDB, 10.4.22-MariaDB, 10.5.13-MariaDB, 10.6.5-MariaDB do not have this problem.

The fiddle

To work around the bug, we can add an explicit alias (derived column name) in the select list of the common table expression term that is reporting an unknown column.

Notice, for the 3rd select list item of the first UNION term in the date_ranges CTE term, I provided a derived column name via AS title. This avoids the bug. We can do this for any similar columns reported as unknown.

The adjusted SQL:

with date_ranges(`start`, `end`, title) as (
    select  '2021-11-22', '2021-12-21 23:59:59', '1400-09' AS title
    union all
    select '2021-12-22', '2022-01-20 23:59:59', '1400-10'
    union all
    select '2022-02-21', '2022-03-19 23:59:59', '1400-11'
),
     income as (
         select count(*) as input,
                dr.title as month
         from date_ranges dr
                  #join drivers u on u.created_at between dr.start and dr.end
         group by dr.title
     ),
     
          outcome as (
         select count(*) as input,
                ddr.title as month
         from date_ranges ddr
                  #join drivers u on u.created_at between ddr.start and ddr.end
         group by ddr.title
     )

select input, month
from  income i
order by month desc
;

Upvotes: 3

Related Questions