unable to use WITH AS in 10.1.34-MariaDB

user_3252418 0
Viewed 819

I am unable to use WITH AS in MariaDB. I found no documentation which says that WITH AS is not used in this version of MariaDB. Below is the sample query that I am trying to replicate:

WITH service_audit AS (
    SELECT
      id                    AS service_id
    , revision_from         AS revision_from
    , revision_until        AS revision_until
    FROM audit
    WHERE
    (   revision_from >= '2019-04-01 00:00:00'
        AND revision_from< '2019-09-30 23:59:59'
    )
    ORDER BY
      id, revision_from, revision_until
)
SELECT
    service_id
    revision_from,
    revision_until,
    LAG(service_id, 1) OVER (PARTITION BY service_id ORDER BY service_id, revision_from, revision_until) service_id_lag
FROM
    service_audit;

and this is the error that I get:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'service_audit AS ( [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'service_audit AS ( SELECT id AS servi' at line 1 Query is: WITH service_audit A ...

you can replicate the issue using:

WITH
  cte1 AS (SELECT 'a' as a, 'b' as b FROM dual),
  cte2 AS (SELECT 'c' as c, 'd' as d FROM dual)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
mariadb mysql sql
2 Answers

Common Table Expression WITH was introduced in MariaDB 10.2.1.

from here (literally my first result on google from search "mariadb with")

user_41042240

There is no need for a CTE in this query, you can just turn it to a simple SELECT:

SELECT
    id service_id
    revision_from,
    revision_until,
    LAG(service_id, 1) OVER (
        PARTITION BY service_id ORDER BY service_id, revision_from, revision_until
    ) service_id_lag
FROM audit
WHERE revision_from >= '2019-04-01 00:00:00' AND revision_from< '2019-09-30 23:59:59'
ORDER BY id, revision_from, revision_until
user_106767160
Related