Re: Creating an index alters the results returned

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Clemens Eisserer
> Sent: Friday, January 03, 2014 3:00 PM
> To: pgsql-general(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)org
> Subject: [GENERAL] Creating an index alters the results returned
>
> Hi,
>
> Running postgres 9.1.11 + postgresql-9.3-1100.jdbc41.jar, I experience a
> strange phenomenon using indexes.
>
> I have the following schema:
> CREATE TABLE wplog (id serial NOT NULL,ts timestamp without time zone,
> sensor1 real, sensor2 real, noiselevel smallint, CONSTRAINT wplog_pkey
> PRIMARY KEY (id))
>
> and execute the following query on it:
>
> SELECT sensor1, sensor2, EXTRACT(EPOCH from ts) AS epoche FROM wplog
> WHERE EXTRACT(EPOCH from ts) BETWEEN 1388712180::double precision
> AND 1388780572::double precision ORDER BY id
>
> However, the results differ, depending on whether I've created an index on
> ts or not:
>
> With index:
> ResultSet Size: 6651
> minTS: 1388730187145 maxTs: 1388796688388 txdiff: 66501243
>
> Without index:
> ResultSet Size: 6830
> minTS: 1388712182800 maxTs: 1388780567963 txdiff: 68385163
>
> The index looks like: CREATE INDEX ON wplog (CAST(EXTRACT(EPOCH from
> ts) AS double precision))
>
> Even more puzzling to me is the fact, that I can only observe this difference
> when using the JDBC driver, using pgadmin to execute the query I get
> consistent results.
>
> Is this behaviour expected?
>
> Thank you in advance, Clemens
>

The fact that it works as expected in pgadmin, probably indicates that the problem is on client/jdbc side.
Turn on backend logging (log_statement = 'all') for your jdbc connection, and after executing your query (through jdbc) look for it in pg_log.
You probably will find it "slightly" different from original. I'm not using jdbc, so can't commect on why this could happen.

Regards,
Igor Neyman