CTAS. Problems parsing some queries ("Token unknown")

[Pavel Zotov]

Odd cases found where CTAS can not be created but its 'inner' part is correct query.

set list on;
set autoddl off;
connect 'localhost:employee' user 'sysdba' password 'masterkey';

recreate table ctas_test as (
    (select rdb$relation_name from rdb$relations order by 1 desc)
);

recreate table ctas_test as (
    select first 1 rdb$relation_name from rdb$relations order by 1 desc optimize for first rows
);
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 2, column 73
-optimize
At line 10 in file test-bf10ae16.sql

recreate table ctas_test as (
    (select rdb$relation_name from rdb$relations order by 1 desc rows 1)
);
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 2, column 66
-rows
At line 14 in file test-bf10ae16.sql

-- BUT:

recreate table ctas_test as (

    select rdb$relation_name from rdb$relations order by 1 desc rows 1
);

-- no errors here --

Checked on 6.0.0.2067-bf10ae1 .

[Mark Rotteveel]

On 07-07-2026 13:43, Pavel Zotov wrote:
> Odd cases found where CTAS can not be created but its 'inner' part is
> correct query.>
> set list on;
> set autoddl off;
> connect 'localhost:employee' user 'sysdba' password 'masterkey';
>
> recreate table ctas_test as (
>     (select rdb$relation_name from rdb$relations order by 1 desc)
> );
>
> recreate table ctas_test as (
>     select first 1 rdb$relation_name from rdb$relations order by 1 desc

> *optimize for first rows*
> );


Optimize for first rows is part of a select *statement* (i.e. "cursor
specification"), not a query expression used here.

> Statement failed, SQLSTATE = 42000
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 2, column 73
> -optimize
> At line 10 in file test-bf10ae16.sql
>
> recreate table ctas_test as (

>     (select rdb$relation_name from rdb$relations order by 1 desc *rows 1*)

> );
> Statement failed, SQLSTATE = 42000
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 2, column 66
> -rows
> At line 14 in file test-bf10ae16.sql> -- BUT:
> recreate table ctas_test as (
>     select rdb$relation_name from rdb$relations order by 1 desc rows 1
> );
> -- no errors here --
>
> Checked on 6.0.0.2067-bf10ae1 .

That is entirely expected in the current syntax (introduced in Firebird
5 in https://github.com/FirebirdSQL/firebird/issues/6740). The same goes
for executing plain

(select rdb$relation_name from rdb$relations order by 1 desc rows 1)

vs

select rdb$relation_name from rdb$relations order by 1 desc rows 1

Those may *look* syntactically the same, they are in fact not. One is a
bare query expression, the other is a query expression that is only a
query primary, and that one doesn't have an alternative with ROWS.

Workaround is, use either:

select rdb$relation_name from rdb$relations order by 1 desc rows 1

OR

(select rdb$relation_name from rdb$relations order by 1 desc) rows 1

OR

(select rdb$relation_name from rdb$relations order by 1 desc fetch first
row only)

OR

(select rdb$relation_name from rdb$relations order by 1 desc fetch)
first row only

The syntax in parse.y is (removed code part):

```
select_expr
: with_clause select_expr_body order_clause_opt rows_clause
| with_clause select_expr_body order_clause_opt result_offset_clause
fetch_first_clause

select_expr_body
: query_term
| select_expr_body UNION distinct_noise query_term
| select_expr_body UNION ALL query_term

query_term
: query_primary

query_primary
: query_spec
| '(' select_expr_body order_clause_opt result_offset_clause
fetch_first_clause ')'
```

Observe how query_primary has no alternative with rows_clause.

Though it is now slightly more confusing with the syntax ambiguity by
allowing both CREATE TABLE ... AS <query expression> and CREATE TABLE
... AS (<query expression>).

In other words, this is not related to CTAS, and is a deficiency in our
current syntax (I can't recall if it was intentional or not, the
implementation ticket makes no mention of it).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 07-07-2026 14:10, 'Mark Rotteveel' via firebird-devel wrote:
> On 07-07-2026 13:43, Pavel Zotov wrote:
>> Odd cases found where CTAS can not be created but its 'inner' part is
>> correct query.>
>> set list on;
>> set autoddl off;
>> connect 'localhost:employee' user 'sysdba' password 'masterkey';
>>
>> recreate table ctas_test as (
>>      (select rdb$relation_name from rdb$relations order by 1 desc)
>> );
>>
>> recreate table ctas_test as (
>>      select first 1 rdb$relation_name from rdb$relations order by 1
>> desc *optimize for first rows*
>> );
>
>
> Optimize for first rows is part of a select *statement* (i.e. "cursor
> specification"), not a query expression used here.

Specifically, if we'd want/need to have this, that basically means it
would need to be a clause on the CREATE TABLE ... AS statement, or it
would need to be added explicitly as an optional clause immediately
after the select expr in:

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

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 07-07-2026 14:10, 'Mark Rotteveel' via firebird-devel wrote:

> OR
>
> (select rdb$relation_name from rdb$relations order by 1 desc fetch)
> first row only

This one should have been

(select rdb$relation_name from rdb$relations order by 1 desc) fetch
first row only

Mark
--
Mark Rotteveel

[Mark Rotteveel]

Also, OPTIMIZE FOR FIRST ROWS makes zero sense for this feature, so
adding it wouldn't make much sense, because you need to materialize all
rows for this (unless WITH NO DATA is specified).

Mark
--
Mark Rotteveel

[Pavel Zotov]

Also, OPTIMIZE FOR FIRST ROWS makes zero sense for this feature

Yes, of course.

That example was given only to illustrate the parsing issue.