Issue Details (XML | Word | Printable)

Key: CORE-2240
Type: Bug Bug
Status: Reopened Reopened
Priority: Major Major
Assignee: Unassigned
Reporter: Michał Kalinowski
Votes: 0
Watchers: 3
Operations

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

wrong selection on gen_id ?

Created: 15/Dec/08 07:42 AM   Updated: 15/Dec/08 12:10 PM
Component/s: None
Affects Version/s: 2.1.0, 2.0.4
Fix Version/s: None


 Description  « Hide
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?


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 15/Dec/08 07:48 AM
Nobody guarantees you that columns in select statement are evaluated from left to right.

Adriano dos Santos Fernandes added a comment - 15/Dec/08 08:01 AM
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.

Michał Kalinowski added a comment - 15/Dec/08 08:14 AM
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

Adriano dos Santos Fernandes added a comment - 15/Dec/08 10:38 AM
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

Vlad Khorsun added a comment - 15/Dec/08 10:54 AM
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

Adriano dos Santos Fernandes added a comment - 15/Dec/08 11:24 AM
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)

Alexander Peshkov added a comment - 15/Dec/08 11:37 AM
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.

Roman Simakov added a comment - 15/Dec/08 11:43 AM
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.

Adriano dos Santos Fernandes added a comment - 15/Dec/08 12:10 PM
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.