Обсуждение: using the nextval('sequence_name') in sql, the result maybe is notright

On Wed, 26 Sep 2018 at 14:08, Wanglin <jluwln@163.com> wrote:
>
> Hi, all:
>     PostgreSQL version : 10.3.  I use "nextval" in the sql , but I think the result is not right, maybe it is a bug.
>    The test case as bellow:
>    create sequence seq1;
> select nextval('seq1');
> create table tx1(id1 int, id2 int);
> insert into tx1 select generate_series(1,100), random()*102;
> explain verbose select * from tx1 where id2 = nextval('seq1');;
> select * from tx1 where id2 = nextval('seq1');
> postgres=# explain verbose select * from tx1 where id2 = nextval('seq1');; QUERY PLAN
------------------------------------------------------------Seq Scan on public.tx1 (cost=0.00..43.90 rows=11 width=8)
Output:id1, id2 Filter: (tx1.id2 = nextval('seq1'::regclass)) (3 rows) 
>
> postgres=# select * from tx1 where id2 = nextval('seq1'); -- here, may be the result is not right id1 | id2
-----+-----56 | 57 (1 row) 
>
> :: I think "nextval('seq1')" equal 2, so " select * from tx1 where id2 = nextval('seq1')" equals " select * from tx1
whereid2 = 2", is it ? 

No. nextval("seq1") increments the sequence and returns the new value.
It never[*] returns the same value in subsequent calls, that is the
purpose of the function (and sequences in general).

Normally, you would assign a sequence to a surrogate key field in your
table, so that you automatically get unique values in that field
(unless you mess around).
That's not how you're using it, so I wonder what your purpose is for
the sequence?

[*] Never is never without exceptions, just like always always has.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 9/26/18 5:05 AM, Wanglin wrote:
> Hi, all:
>      PostgreSQL version : 10.3.  I use "nextval" in the sql , but I 
> think the result is not right, maybe it is a bug.
> *The test case as bellow:*
> create sequence seq1;
> select nextval('seq1');
> create table tx1(id1 int, id2 int);
> insert into tx1 select generate_series(1,100), random()*102;
> explain verbose select * from tx1 where id2 = nextval('seq1');;
> select * from tx1 where id2 = nextval('seq1');
> postgres=# explain verbose select * from tx1 where id2 = 
> nextval('seq1');; QUERY PLAN 
> ------------------------------------------------------------ Seq Scan on 
> public.tx1 (cost=0.00..43.90 rows=11 width=8) Output: id1, id2 Filter: 
> (tx1.id2 = nextval('seq1'::regclass)) (3 rows)
> 
> postgres=# select * from tx1 where id2 = nextval('seq1'); *-- here, 
> **may be the result is not right* id1 | id2 -----+----- 56 | 57 (1 row)
> 
> :: I think "nextval('seq1')" equal 2, so "select * from tx1 where id2 = 
> nextval('seq1')" equals "select * from tx1 where id2 = 2", is it ?

As Alban pointed out calling nextval() increments the sequence. As your 
EXPLAIN shows Postgres your SELECT is doing a sequence scan. Using your 
test code here I get:

select * from tx1;

  id1 | id2
-----+-----
    1 |  27
    2 |  42
    3 |  93
    4 |   2
    5 |  85

So going in sequence Postgres is going to compare 27 to nextval()(which 
is 2), not find it move to 42 = nextval()(=3) not find it and so on.

If I do:

select * from tx1 order by id2;

I get:

  id1 | id2
-----+-----
   20 |   0

<values removed for clarity>
    2 |  42
   17 |  43
   63 |  45
   88 |  45
   27 |  46
   52 |  47
   47 |  47

alter sequence seq1 restart;

select * from tx1 where id2 = nextval('seq1') order by id2;
  id1 | id2
-----+-----
   47 |  47

The sequence catches up with the values because there are duplicate 47 
values in id2.




> 
> Thanks,
>      Wanglin
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com