CTAS. Can not use recursive query as source for a table to be created

[Pavel Zotov]

Following script will fail on 6.0.0.2050-a4fa0b9:

set bail on;
set list on;
shell del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
set echo on;
recreate table tmp_factorial as (
    with recursive
    r1 as (
        select 1 i, cast(1 as decfloat(34)) f from rdb$database
        union all
        select r.i+1, r.f * (r.i+1) from r1 as r where r.i < 1024
    )
    select * from r1
)
with data
;
commit;
select * from tmp_factorial order by f desc rows 1;

Output:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 2, column 5
-with
At line 7 in file ctas-using-recursive-qry.tmp-sql

Expected:

I    1024
F    5.418528796058857283076921944683885E+2639

[Mark Rotteveel]

That is a bug in the implementation, it uses a bare select_expr (<query
expression> in the SQL standard) there, which means it will only work
unparenthesized.

However, according to the SQL standard, it should must be parenthesized:

<table definition> ::=
CREATE [ <table scope> ] TABLE <table name> <table contents source>
[ WITH <system versioning clause> ]
[ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
<table element list>
| <typed table clause>
| <as subquery clause>

<as subquery clause> ::=
[ <left paren> <column name list> <right paren> ] AS <table subquery>
<with or without data>

<table subquery> ::=
<subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first
clause> ]

In other words, in parse.y

table_as_query_clause
: simple_table_name column_parens_opt AS select_expr with_data_opt

should probably be

table_as_query_clause
: simple_table_name column_parens_opt AS '(' select_expr ')' with_data_opt

table_subquery

or maybe:

table_as_query_clause
: simple_table_name column_parens_opt AS table_subquery with_data_opt


Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 03-07-2026 10:10, Mark Rotteveel wrote:
> table_subquery
>
> or maybe:
>
> table_as_query_clause
>     : simple_table_name column_parens_opt AS table_subquery with_data_opt

Looking at how table_subquery is used, that guess is probably not correct.

Mark
--
Mark Rotteveel

[Vlad Khorsun]

03.07.2026 11:16, 'Mark Rotteveel' via firebird-devel:

Below is corresponding part from mine impl of CTAS in HQ5, hope it helps

%type <createRelationNode> table_clause
table_clause
: simple_table_name external_file
{
$<createRelationNode>$ = newNode<CreateRelationNode>($1, $2);
}
- '(' table_elements($3) ')' table_attributes($3)
+ table_contents_source($3) table_attributes($3)
{
$$ = $3;
}
;


+%type table_contents_source(<createRelationNode>)
+table_contents_source($createRelationNode)
+ : '(' table_elements($createRelationNode) ')'
+ | as_subquery_clause($createRelationNode)
+ ;
+


+%type as_subquery_clause(<relationNode>)
+as_subquery_clause($relationNode)
+ : column_parens_opt AS '(' select_expr ')' with_data
+ {
+ RelationNode::AsSubqueryClause* clause = $<asSubqueryClause>$ =
+ newNode<RelationNode::AsSubqueryClause>();
+ clause->fieldsNames = $1;
+ clause->selectExpr = $4;
+ clause->withData = $6;
+ $relationNode->clauses.add(clause);
+ }
+ ;

Regards,
Vlad

[Adriano dos Santos Fernandes]

So per the standard `create table t1 as select * from t2` is not valid,
should be `create table t1 as (select * from t2)`.

And `WITH NO DATA | WITH DATA` is also required.

Are we going to use standard rules and be less compatible with others
products?


Adriano

[Vlad Khorsun]

03.07.2026 14:33, Adriano dos Santos Fernandes:

> So per the standard `create table t1 as select * from t2` is not valid,
> should be `create table t1 as (select * from t2)`.

Yes. And it makes less shift/reduce conflicts in parser, btw

> And `WITH NO DATA | WITH DATA` is also required.

Here I can argue with standard :)

> Are we going to use standard rules and be less compatible with others
> products?

I think, we need to use parenthesis around sub-query and allow to omit with_data
sub-clause with documented default. But I can live with required with_data too.

Regards,
Vlad

[Mark Rotteveel]

I prefer that we stick to the SQL standard syntax, or at least *also*
support it (which the current implementation doesn't).

If we really want to offer syntax flexibility, we could allow both with
and without parentheses. That said, I believe only MySQL/MariaDB has the
syntax without parentheses, and I don't see why we should cater to their
specific deviation of the standard.

Making the <with or without data> part required or keep it optional is
fine with me either way, even if that is a minor deviation from the
standard.

Mark
--
Mark Rotteveel

[Denis Simonov]

пятница, 3 июля 2026 г. в 17:57:32 UTC+3, Mark Rotteveel:

On 03-07-2026 13:33, Adriano dos Santos Fernandes wrote:
> So per the standard `create table t1 as select * from t2` is not valid,
> should be `create table t1 as (select * from t2)`.
>
> And `WITH NO DATA | WITH DATA` is also required.
>
> Are we going to use standard rules and be less compatible with others
> products?

I prefer that we stick to the SQL standard syntax, or at least *also*
support it (which the current implementation doesn't).

If we really want to offer syntax flexibility, we could allow both with
and without parentheses. That said, I believe only MySQL/MariaDB has the
syntax without parentheses, and I don't see why we should cater to their
specific deviation of the standard.

+1

 

Making the <with or without data> part required or keep it optional is
fine with me either way, even if that is a minor deviation from the
standard.

I would prefer these options to be optional.

 

Mark
--
Mark Rotteveel

[James Starkey]

If it’s from MySQL, I can pretty much guarantee that it hasn’t been thought through.  It’s called MySQL because the original name, PaintedMyselfIntoACorner, was too long to type.

Jim Starkey

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/9802c58a-f82e-4945-b9c8-e55e630f5f80n%40googlegroups.com.

[Adriano dos Santos Fernandes]

On 7/3/26 11:57, 'Mark Rotteveel' via firebird-devel wrote:

> If we really want to offer syntax flexibility, we could allow both with
> and without parentheses. That said, I believe only MySQL/MariaDB has the
> syntax without parentheses, and I don't see why we should cater to their
> specific deviation of the standard.
>

Oracle, PostgreSQL and DB2 also support it. These are the only ones I
looked for.


Adriano

[Mark Rotteveel]

DB2 (LUW) requires parentheses:
https://www.ibm.com/docs/en/db2/12.1.x?topic=statements-create-table#sdx-synid_frag-as-result-table

I couldn't find it initially in PostgreSQL, as they document it
separately from the rest of CREATE TABLE:
https://www.postgresql.org/docs/current/sql-createtableas.html

I had a hard time finding it in the Oracle syntax
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html,
I finally found it in table_properties. It does contain an example, and
it is indeed not parenthesized (but I've also seen examples elsewhere
*with* parentheses). However, Oracle's definition is far more complex,
as it also allows you to define columns together with the AS subclause
(see "Specifying column_definition with AS subquery").

It reduces the strength of my argument, but I still think it's
unnecessary, and following the standard should be good enough here, but
I'll no longer object to supporting both parenthesized *and*
unparenthesized.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 7/5/26 14:15, 'Mark Rotteveel' via firebird-devel wrote:
> On 05-07-2026 16:34, Adriano dos Santos Fernandes wrote:
>> On 7/3/26 11:57, 'Mark Rotteveel' via firebird-devel wrote:
>>
>>> If we really want to offer syntax flexibility, we could allow both with
>>> and without parentheses. That said, I believe only MySQL/MariaDB has the
>>> syntax without parentheses, and I don't see why we should cater to their
>>> specific deviation of the standard.
>>>
>>
>> Oracle, PostgreSQL and DB2 also support it. These are the only ones I
>> looked for.
>
> DB2 (LUW) requires parentheses: https://www.ibm.com/docs/en/db2/12.1.x?
> topic=statements-create-table#sdx-synid_frag-as-result-table
>

It's actually Informix that accepts it:
https://www.ibm.com/docs/en/informix-servers/15.0.x?topic=statement-as-select-clause

> I couldn't find it initially in PostgreSQL, as they document it
> separately from the rest of CREATE TABLE: https://www.postgresql.org/
> docs/current/sql-createtableas.html
>

I tested it.


Adriano

[Adriano dos Santos Fernandes]

Isn't the problem of CTAs with (WITH ...) just a more general problem?

We do not accept that:

SQL> (with q as (select 1 n from rdb$database) select * from q);

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104

-Token unknown - line 1, column 2
-with

While PostgreSQL accepts:

postgres=# (with q as (select 1 n) select * from q);
n
---
1
(1 row)


Adriano

[Mark Rotteveel]

On 06-07-2026 14:00, Adriano dos Santos Fernandes wrote:
> Isn't the problem of CTAs with (WITH ...) just a more general problem?

No (see below)

> We do not accept that:
>
> SQL> (with q as (select 1 n from rdb$database) select * from q);
> Statement failed, SQLSTATE = 42000
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 1, column 2
> -with
>
> While PostgreSQL accepts:
>
> postgres=# (with q as (select 1 n) select * from q);
> n
> ---
> 1
> (1 row)

Our syntax matches the SQL standard requirements in that regard, as it
explicitly disallows using WITH in a parenthesized top-level statement,
but that's different in the case of the CREATE TABLE ... AS syntax as
that is a query expression in a subquery.

A top-level select is a cursor specification:

```
<cursor specification> ::=
<query expression> [ <updatability clause> ]

<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first
clause> ]

<query expression body> ::=
<query term>
| <query expression body> UNION [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>

<query term> ::=
<query primary>
| <query term> INTERSECT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query primary>

<query primary> ::=
<simple table>
| <left paren> <query expression body>

[ <order by clause> ] [ <result offset clause> ] [ <fetch first
clause> ]

<right paren>

<simple table> ::=
<query specification>
| <table value constructor>
| <explicit table>
```

In other words, top-level WITH can't occur parenthesized: it would have
to be outside the parentheses.

For CREATE TABLE AS, the syntax derivation is:

```

<table definition> ::=
CREATE [ <table scope> ] TABLE <table name> <table contents source>
[ WITH <system versioning clause> ]
[ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
<table element list>
| <typed table clause>
| <as subquery clause>

<as subquery clause> ::=
[ <left paren> <column name list> <right paren> ] AS <table subquery>
<with or without data>

<table subquery> ::=
<subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

```

In other words, the derivation is through a subquery, in which case a
WITH clause *must* be parenthesized, as by definition the query
expression is in parentheses.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 06-07-2026 17:20, 'Mark Rotteveel' via firebird-devel wrote:
> In other words, the derivation is through a subquery, in which case a
> WITH clause *must* be parenthesized, as by definition the query
> expression is in parentheses.

BTW, related, but off on a tangent, what in our syntax is called
table_subquery seems to be what the standard calls <scalar subquery>,
but either way, the syntax we support for that is deficient compared to

[Mark Rotteveel]

No, never mind about the last part of the syntax being deficient, I
looked cross-eyed at that part.

Mark
--
Mark Rotteveel

[Pavel Zotov]

Test committed (after fix bf10ae16).