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

wrong selection on gen_id ? [CORE2240] #2667

Open
firebird-automations opened this issue Dec 15, 2008 · 12 comments
Open

wrong selection on gen_id ? [CORE2240] #2667

firebird-automations opened this issue Dec 15, 2008 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Nobody guarantees you that columns in select statement are evaluated from left to right.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Michał Kalinowski (junkhead)

and

select f+1 , f
from (
select gen_id(gen_my_generator,1) as f
from rdb$database)

gives diffrent result than

f, f+1

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Bug: values should be equal.

create generator gen_my_generator;

select f, f
from (
select gen_id(gen_my_generator,1) as f
from rdb$database
);

Returns:
F F
===================== =====================
2 1

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

@firebird-automations
Copy link
Collaborator Author

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
from rdb$database

@firebird-automations
Copy link
Collaborator Author

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

     N          N

---------- ----------
.328763294 .328763294

(Oracle did not allow sequence usage in this context)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

SQL 2008, page 380 (402) explains for me:

Section 7.12 <query specification>

General Rules
1) Case:
a) If T is not a grouped table, then each <value expression> is applied to each row of T yielding a table
TEMP of M rows, where M is the cardinality of T. The i-th column of the table contains the values
derived by the evaluation of the i-th <value expression>.

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.

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