Details
-
New Feature
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
The following query is valid ansi-sql, but is rejected by the latest mariadb:
create table r ( |
src int primary key, |
tgt int |
);
|
|
|
insert into r (src, tgt) values |
(2, 1),(3, 1),(4, 2),(5, 2),(6, 3),(7, 3),(8, 4),(9, 5);
|
|
|
(select src, tgt |
from r |
)
|
union
|
(with recursive |
TransitiveClosure as (select src, tgt |
from r |
union |
(select |
TransitiveClosure.src as src, |
r.tgt as tgt |
from TransitiveClosure, |
r
|
where r.src = TransitiveClosure.tgt) |
)
|
select src, tgt from TransitiveClosure |
);
|
Attachments
Issue Links
- is part of
-
MDEV-35388 Postgres compatible syntax
-
- Open
-
Activity
Hmm, I see, you are right.
I didn't expect that. Sorry for the inconvenience. This is too bad, for I am programmatically generating SQL, now I have to generate in a way that I define all in the upper part of the query. The testcase above should then be written as
create table r ( |
src int primary key, |
tgt int |
);
|
|
|
insert into r (src, tgt) values |
(2, 1),(3, 1),(4, 2),(5, 2),(6, 3),(7, 3),(8, 4),(9, 5);
|
|
|
with recursive |
TransitiveClosure as (select src, tgt |
from r |
union |
(select |
TransitiveClosure.src as src, |
r.tgt as tgt |
from TransitiveClosure, |
r
|
where r.src = TransitiveClosure.tgt) |
)
|
select src, tgt from r |
union
|
(select src, tgt from TransitiveClosure) |
;
|
This requires major refactoring, but is doable.
You could consider this
select src, tgt |
from r |
union
|
(
|
select * from |
(
|
with recursive TransitiveClosure as |
(
|
select src, tgt |
from r |
union |
(
|
select TransitiveClosure.src as src, r.tgt as tgt |
from TransitiveClosure, r |
where r.src = TransitiveClosure.tgt |
)
|
)
|
select src, tgt from TransitiveClosure |
) t
|
)
|
Brilliant! This only requires a minor fix, because it is local to the type of expression. It also saves some possible problems due to naming conflicts. Thansk a lot for the tip!
I am unsure which ANSI standard you are referring to.
According to the 2016 standard, that which follows the UNION keyword must be a <query term>, which removing options and self references, is a <query primary>, which is a <simple table> or a ( <query expression body>), the latter being again defined in terms of a <simple table>.
<simple table> =<query specification>OR <table value constructor>OR <explicit table><query specification> =SELECT ...So on one hand, this isn't standard SQL, on the other hand PostgreSQL accepts and executes this statement.