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 <iney...@perceptron.com> wrote: > > -----Original Message----- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Clemens Eisserer > > Sent: Friday, January 03, 2014 3:00 PM > > To: pgsql-general@postgresql.org; pgsql-j...@postgresql.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-j...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
