Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow parenthesized query expression for standard-compliance [CORE6511] #6740

Closed
firebird-automations opened this issue Mar 11, 2021 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @mrotteveel

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@pavel-zotov
Copy link

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} )

  1. 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
Copy link
Member

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

@mrotteveel
Copy link
Member

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
Copy link
Member

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

@mrotteveel
Copy link
Member

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
Copy link
Member

mrotteveel commented May 6, 2023

See #7569

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants