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