Allow parenthesized query expression for standard-compliance [CORE6511]

[firebird-automations]

Submitted by: @mrotteveel

Attachments:
query-expr-v3.diff

See also discussion with subject "Standard-compliance for query expressions" on firebird-devel (March 6th, 2021).

The SQL standard allows parentheses around query expressions (without with-clause), and Firebird does not. It would be helpful if Firebird added this support.

Specifically, SQL:2016 specifies the following:

```
<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>

<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>

::= \[ \] \[ \] \[ \] \[ \] \`\`\`

If I follow the grammar in parse.y correctly, the problem is that in Firebird, <query primary> is basically <query specification>, so it's missing the following alternative:

```
<left paren> <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
<right paren>
```

Adriano wrote the attached patch.

[firebird-automations]

Modified by: @mrotteveel

Attachment: query-expr-v3.diff [ 13556 ]

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[pavel-zotov]

Max. number of parts in UNIONED query reduced from 255 to 128 if we use '(' and ')' for enclosing each part of such query.
Consider two SQL:

1. without parenthesis:

select 1 as i from rdb$database union all select 2 from rdb$database union all ...

This query can have up to 255 parts (i.e. we can get resultset with values {1,2,3, ..., 255} )

2. with parenthesis:

(select 1 as i from rdb$database union all (select 2 from rdb$database union all (select 3 from ... )...)...)));

It can be compiled on 5.0.0.88 but maximal number of its parts is limited to 128.
Is it expected ?

Full .sql files and auxiliary .py scripts for generating them are in attached .zip

PS.
If you want to run attached .py scripts, please install FDB driver first ( pip install fdb ).
Also, one need to adjust path to client library (in each of .py file):

FB_CLNT = r'C:\FB\50SS\fbclient.dll'

gh-6740-queries-with-max-allowed-unioned-parts.sql.zip

[asfernandes]

It's the same as mixed UNION and UNION ALL, requiring more contexts (RSEs).

[mrotteveel]

One thing that may be a problem is this:

create table rowdata (id integer generated always as identity);
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;
insert into rowdata default values;

Now, the following ignores the outer order by and offset/fetch as if it is not there:

(
  select id
  from rowdata
  order by id
  offset 2 rows fetch next 5 rows only
)
order by id desc
offset 2 rows fetch next 2 rows only;

Returns: 3, 4, 5, 6, 7

Should return: 5, 4

For example PostgreSQL 15 (dbfiddle) will report "multiple ORDER BY clauses not allowed" (and same for offset and fetch if the others are removed).

Looking at the original discussion on firebird-devel, this potential problem was raised by Dmitry before implementation.

Looking at the SQL standard, I think this should apply another sort and offset/fetch, but maybe that is too much work for now, but raising an error might be a good idea.

[asfernandes]

Why did you reopened this feature? it was already delivered in beta1.
Open a new issue if it has a bug.

[mrotteveel]

Why did you reopened this feature? it was already delivered in beta1. Open a new issue if it has a bug.

OK, I'll do that...

[mrotteveel]

See #7569