Thread: BUG #2006: queryoptimizer and comparing a primary key of biginteger and a literal

The following bug has been logged online:

Bug reference:      2006
Logged by:          Atanas Hristov
Email address:      atanashristov@hotmail.com
PostgreSQL version: 8.1 Beta 3
Operating system:   FreeBSD 5
Description:        queryoptimizer and comparing a primary key of biginteger
and a literal
Details:

foo=# CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );



foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = CAST(0 AS BIGINT);
                                                 QUERY PLAN
----------------------------------------------------------------------------
--------------------------------
 Index Scan using foo_pk on foo  (cost=0.00..4.82 rows=2 width=0) (actual
time=0.113..0.113 rows=0 loops=1)
   Index Cond: (id = 0::bigint)



foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????
                                          QUERY PLAN
----------------------------------------------------------------------------
------------------
 Seq Scan on foo  (cost=0.00..22.50 rows=2 width=0) (actual
time=0.003..0.003 rows=0 loops=1)
   Filter: (id = 0)

"Atanas Hristov" <atanashristov@hotmail.com> writes:
> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
> DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????

It works for me:

regression=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pk on foo  (cost=0.00..4.82 rows=1 width=0) (actual time=0.241..0.241 rows=0 loops=1)
   Index Cond: (id = 0)
 Total runtime: 0.456 ms
(3 rows)

I don't think you're really testing 8.1.  Try "select version()"

            regards, tom lane

Atanas Hristov wrote:
> Bug reference:      2006
> Logged by:          Atanas Hristov
> Email address:      atanashristov@hotmail.com
> PostgreSQL version: 8.1 Beta 3
> Operating system:   FreeBSD 5
> Description:        queryoptimizer and comparing a primary key of biginteger
> and a literal
> Details:
>
> foo=# CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );

> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = CAST(0 AS BIGINT);
> --------------------------------
>  Index Scan using foo_pk on foo  (cost=0.00..4.82 rows=2 width=0) (actual
> time=0.113..0.113 rows=0 loops=1)
>    Index Cond: (id = 0::bigint)

> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
> DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????

See the mailing list archives for extensive discussion of this. Since
you are using 8.1 it IS casting against the index.

> ------------------
>  Seq Scan on foo  (cost=0.00..22.50 rows=2 width=0) (actual
> time=0.003..0.003 rows=0 loops=1)
>    Filter: (id = 0)

You don't have any data in the table - what's the point of PG using an
index?

=> CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );
=> INSERT INTO foo SELECT * FROM generate_series(1,10000);
=> VACUUM ANALYSE foo;
=> EXPLAIN ANALYSE SELECT * FROM foo WHERE id=1;
                                                   QUERY PLAN
------------------------------------------------------------------------
Index Scan using foo_pk on foo  (cost=0.00..3.01 rows=1 width=8) (actual
time=43.832..43.839 rows=1  loops=1)
    Index Cond: (id = 1)

--
   Richard Huxton
   Archonet Ltd