Issue Details (XML | Word | Printable)

Key: CORE-5798
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: fbbt
Votes: 1
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

"where" condition applies wrongly

Created: 18/Apr/18 06:14 PM   Updated: 19/Apr/18 01:55 PM
Component/s: Engine
Affects Version/s: 3.0.3
Fix Version/s: None

QA Status: No test


 Description  « Hide
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, ...

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 19/Apr/18 05:13 AM
The same issue as in CORE-4925. It's not about the WHERE clause, but about expressions begin re-evaluated after the sort.

Karol Bieniaszewski added a comment - 19/Apr/18 11:39 AM - edited
What is the purpose of this reevaluation?


 

Dmitry Yemanov added a comment - 19/Apr/18 12:28 PM
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.

Karol Bieniaszewski added a comment - 19/Apr/18 12:44 PM - edited
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

fbbt added a comment - 19/Apr/18 01:43 PM
>>The same issue as in CORE-4925. 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.

Dmitry Yemanov added a comment - 19/Apr/18 01:55 PM
In your case, the derived table is not materialized, thus redundant evaluations. This is really a different issue, although also an implementation detail.