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
wrong selection on gen_id ? [CORE2240] #2667
Comments
Commented by: @AlexPeshkoff Nobody guarantees you that columns in select statement are evaluated from left to right. |
Commented by: @asfernandes Alex, but I think the first example is a valid bug. I believe (didn't tested yet) the gen_id expression is being cloned, and run twice. |
Commented by: Michał Kalinowski (junkhead) and select f+1 , f gives diffrent result than f, f+1 |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @asfernandes Bug: values should be equal. create generator gen_my_generator; select f, f Returns: |
Commented by: @hvlad Why it should be equal ? Is it required by standard ? Is following query is equivalent to yours ? select gen_id(gen_my_generator,1) as f, gen_id(gen_my_generator,1) as f |
Commented by: @asfernandes The name of this is "derived table". Why would you expect reading its column should return different result? If you find that derived fields may be considered as computed expressions, I'll agree. Now, I have Oracle agreeing with my way to see it: SQL> select n, n from (select dbms_random.value n from dual);
---------- ---------- (Oracle did not allow sequence usage in this context) |
Commented by: @AlexPeshkoff As far as I remember standard order of expressions evaluation (and count of them for derived tables) is implementattion defined. May be I'm wrong - please provide standard reference. |
Commented by: @romansimakov IMO, "select gen_id(gen_my_generator,1) as f, gen_id(gen_my_generator,1) as f from rdb$database", is not equivalent to initial query. In this example gen_id is called for calculating each FIELD in only row, but in derived table case the field values of derived table record should be used for calculating record of query result. In other words we should not fetch new record from derived table to calculate each FIELD value. |
Commented by: @asfernandes SQL 2008, page 380 (402) explains for me: Section 7.12 <query specification> General Rules So a TEMP table with one column should be created. In the outer query, the value of the TEMP table should be read, hence not evaluating the expression more than one time. |
Submitted by: Michał Kalinowski (junkhead)
ok, so i got simple select
select f, f+1
from (
select gen_id(gen_my_generator,1) as f
from rdb$database)
and this select gives me two the same values, but it should give me generator value increased by 1...
so, the similar problem is with
select gen_id(gen_my_generator,1),
gen_id(gen_my_generator,1)
from rdb$database
second column is lower then first, whats wrong?
The text was updated successfully, but these errors were encountered: