Re: [JDBC] Creating an index alters the results returned

Yes, please check the server logs. There should be no reason whatsoever
that JDBC would effect the results if the query is the same

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Jan 3, 2014 at 3:42 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> > -----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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>