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

"where" condition applies wrongly [CORE5798] #6061

Open
firebird-automations opened this issue Apr 18, 2018 · 8 comments
Open

"where" condition applies wrongly [CORE5798] #6061

firebird-automations opened this issue Apr 18, 2018 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: fbbt (fbbt)

Votes: 1

SET GENERATOR G TO 0;

SELECT * FROM
(SELECT GEN_ID(G, 1) as GenValue FROM T)
WHERE mod(GenValue, 2) = 0

----
result: 3, 5, 7, 9, 11, 13, ...
must be: 2, 4, 6, 8, 10, ...

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE4925 [ CORE4925 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The same issue as in CORE4925. It's not about the WHERE clause, but about expressions begin re-evaluated after the sort.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

What is the purpose of this reevaluation?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Expression results are not stored/materialized inside the sort file, only the involved fields (if any). This is dictated by the sorting architecture, it knows nothing about select lists or query predicates or whatever else not belonging to the sorting itself. Everything not present inside the sort file must be re-evaluated after the sorting.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

what if field is gathered from subquery?

is see this numbers

SELECT
R.RDB$RELATION_ID, (SELECT COUNT(*) FROM RDB$DATABASE R2)
FROM
RDB$DATABASE R
ORDER BY 2

---------------------------

Current memory = 1672221600
Delta memory = 0
Max memory = 1672306736
Elapsed time= 0.005 sec
Buffers = 98304
Reads = 0
Writes = 0
Fetches = 22

---------------------------

SELECT
R.RDB$RELATION_ID, (SELECT COUNT(*) FROM RDB$DATABASE R2)
FROM
RDB$DATABASE R
ORDER BY 1

---------------------------

Current memory = 1672209704
Delta memory = 0
Max memory = 1672306736
Elapsed time= 0.005 sec
Buffers = 98304
Reads = 0
Writes = 0
Fetches = 15

@firebird-automations
Copy link
Collaborator Author

Commented by: fbbt (fbbt)

>>The same issue as in CORE4925. It's not about the WHERE clause, but about expressions begin re-evaluated after the sort.
There are no sort in my query. So it's about the WHERE clause too.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE4925 [ CORE4925 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

In your case, the derived table is not materialized, thus redundant evaluations. This is really a different issue, although also an implementation detail.

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

1 participant