CODEWITHSUNDEEP
mysqlsqlmariadb
Puneet Sidhu
Puneet Sidhu

Reputation: 105

unable to use WITH AS in 10.1.34-MariaDB

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;

Upvotes: 3

Views: 1717

Answers (2)

GMB
GMB

Reputation: 222722

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

Upvotes: 3

Uueerdo
Uueerdo

Reputation: 15961

Common Table Expression WITH was introduced in MariaDB 10.2.1.

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

Upvotes: 3

Related Questions