wrong selection on gen_id ? [CORE2240]

[firebird-automations]

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]

Commented by: @AlexPeshkoff

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

[firebird-automations]

Modified by: @AlexPeshkoff

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \]

[firebird-automations]

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]

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]

Modified by: @pcisar

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

[firebird-automations]

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]

Modified by: @asfernandes

status: Closed \[ 6 \] =\> Reopened \[ 4 \] resolution: Won't Fix \[ 2 \] =\>

[firebird-automations]

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]

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]

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]

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]

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.