Thread: Simulate count result are distinct between 8.3 and 8.4

hi people!

I have this test query to simulate rownums in 8.3:

SELECT
(select count(i)+1 from prueba
where i < xi.i
) as rownum, i, p
FROM prueba xi limit 5;

Devuelve , que esta bien:
1;1;"299361"
2;2;"421127"
3;3;"166284"
4;4;"458945"
5;5;"81619"


But in 8.4 throws this:

postgres=# SELECT
postgres-# (select count(i)+1 from prueba
postgres(# where i < xi.i
postgres(# ) as rownum, i, p
postgres-# FROM prueba xi limit 5;
 rownum |   i    |   p
--------+--------+--------
 168770 | 168763 | 908731
 168771 | 168764 | 640826
 168772 | 168765 | 571112
 168773 | 168766 | 992462
 168774 | 168767 | 992471
(5 filas)


synchronize_seqscan are on in both engines.


I only want to know why is this difference...

--
              Emanuel Calvo Franco
             Database consultant at:
                    www.siu.edu.ar
        www.emanuelcalvofranco.com.ar

Emanuel Calvo Franco wrote:
> hi people!
>
> I have this test query to simulate rownums in 8.3:
>
> SELECT
> (select count(i)+1 from prueba
> where i < xi.i
> ) as rownum, i, p
> FROM prueba xi limit 5;
>
> Devuelve , que esta bien:
> 1;1;"299361"
> 2;2;"421127"
> 3;3;"166284"
> 4;4;"458945"
> 5;5;"81619"
>
>
> But in 8.4 throws this:
>
> postgres=# SELECT
> postgres-# (select count(i)+1 from prueba
> postgres(# where i < xi.i
> postgres(# ) as rownum, i, p
> postgres-# FROM prueba xi limit 5;
>  rownum |   i    |   p
> --------+--------+--------
>  168770 | 168763 | 908731
>  168771 | 168764 | 640826
>  168772 | 168765 | 571112
>  168773 | 168766 | 992462
>  168774 | 168767 | 992471
> (5 filas)

Without an order by in your query, the db can return them as soon as it
finds the rows.

If you add an order by (to the outer part), it should be more reliable.

--
Postgresql & php tutorials
http://www.designmagick.com/

On Thu, Aug 13, 2009 at 9:37 AM, Chris <dmagick@gmail.com> wrote:

Why don't you make it simple and just use row_number() from 8.4... It can be simplified as:

select row_number() over(), i, p from prueba limit 5;

--
Shoaib Mir
http://shoaibmir.wordpress.com/

>
> Why don't you make it simple and just use row_number() from 8.4... It can be
> simplified as:
>
> select row_number() over(), i, p from prueba limit 5;
>

I know the use of WF. What surprised me, is the difference between
both versions in the same query. I'm trying to understand why happens
and  not look for another way :)


--
              Emanuel Calvo Franco
             Database consultant at:
                    www.siu.edu.ar
        www.emanuelcalvofranco.com.ar