BUG? res.next() == false, but psql finds tuples?

Hi!

Here's something really odd. Is it a bug in the jdbc driver? 8.0.311:

String query = "select lang from participant where userid = ? and
course_id = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, userId);
ps.setInt(2, courseId);
ResultSet res = ps.executeQuery();

System.err.println("[1] "+ps);

boolean hasNext = res.next();
System.err.println("hasNext: "+hasNext);

if (hasNext) {
System.err.println("[2]");
language = res.getString(1);
System.err.println("language: "+language);
}
System.err.println("[3]");

In the java error log:

---
[1] select lang from participant where userid = mango and course_id = 38
hasNext: false
[3]
---

but with psql, there's no problem getting a value:

mango=> select lang from participant where userid = 'mango' and course_id =
38;
lang
------
sv
(1 row)

(Connected to same database as same user.)

What's up? Is there a problem with the (new) prepared statement stuff?

postgresql-8.0.2, postgresql-jdbc-8.0.311, FreeBSD 4.11, java 1.4.2.

/Palle

PS. Here's a tcpdump of the query, FWIW:

13:37:14.909230 localhost.3277 > localhost.postgresql: P
564113356:564113492(136) ack 897123100 win 57344 <nop,nop,timestamp
729334714 729334409> (DF)
0x0000 4500 00bc 5038 4000 4006 ec01 7f00 0001 E(dot)(dot)(dot)P8(at)(dot)@.......
0x0010 7f00 0001 0ccd 1538 219f afcc 3579 031c .......8!...5y..
0x0020 8018 e000 6474 0000 0101 080a 2b78 c3ba ....dt......+x..
0x0030 2b78 c289 5000 0000 5100 7365 6c65 6374 +x..P...Q.select
0x0040 206c 616e 6720 6672 6f6d 2070 6172 7469 .lang.from.parti
0x0050 6369 7061 6e74 2077 6865 7265 2075 7365 cipant.where.use
0x0060 7269 6420 3d20 2431 2061 6e64 2063 6f75 rid.=.$1.and.cou
0x0070 7273 655f 6964 203d 2024 3200 0002 0000 rse_id.=.$2.....
0x0080 0413 0000 0017 4200 0000 1f00 0000 0200 ......B.........
0x0090 0000 0000 0200 0000 056d 616e 676f 0000 .........mango..
0x00a0 0002 3338 0000 4400 0000 0650 0045 0000 ..38..D....P.E..
0x00b0 0009 0000 0000 0053 0000 0004 .......S....
13:37:14.916554 localhost.postgresql > localhost.3277: P 1:59(58) ack 136
win 57344<nop,nop,timestamp 729334715 729334714> (DF)
0x0000 4500 006e 5039 4000 4006 ec4e 7f00 0001 E(dot)(dot)nP9(at)(dot)@..N....
0x0010 7f00 0001 1538 0ccd 3579 031c 219f b054 .....8..5y..!..T
0x0020 8018 e000 4908 0000 0101 080a 2b78 c3bb ....I.......+x..
0x0030 2b78 c3ba 3100 0000 0432 0000 0004 5400 +x..1....2....T.
0x0040 0000 1d00 016c 616e 6700 0000 4821 0003 .....lang...H!..
0x0050 0000 0019 ffff ffff ffff 0000 4300 0000 ............C...
0x0060 0b53 454c 4543 5400 5a00 0000 0549 .SELECT.Z....I

And here's one from psql (that works fine, result = 'sv'):

15:16:40.179908 localhost.1973 > localhost.postgresql: P 82:159(77) ack 294
win 57344 <nop,nop,timestamp 729931235 729930768> (DF)
0x0000 4500 0081 f57e 4000 4006 46f6 7f00 0001 E(dot)(dot)(dot)(dot)~(at)(dot)@.F.....
0x0010 7f00 0001 07b5 1538 3c3c 839d 3a40 cc42 .......8<<..:@.B
0x0020 8018 e000 77ae 0000 0101 080a 2b81 dde3 ....w.......+...
0x0030 2b81 dc10 5100 0000 4c73 656c 6563 7420 +...Q...Lselect.
0x0040 6c61 6e67 2066 726f 6d20 7061 7274 6963 lang.from.partic
0x0050 6970 616e 7420 7768 6572 6520 7573 6572 ipant.where.user
0x0060 6964 203d 2027 6d61 6e67 6f27 2061 6e64 id.=.'mango'.and
0x0070 2063 6f75 7273 655f 6964 203d 2033 383b .course_id.=.38;
0x0080 00 .
15:16:40.183568 localhost.postgresql > localhost.1973: P 294:355(61) ack
159 win 57344 <nop,nop,timestamp 729931236 729931235> (DF)
0x0000 4500 0071 f580 4000 4006 4704 7f00 0001 E(dot)(dot)q(dot)(dot)(at)(dot)@.G.....
0x0010 7f00 0001 1538 07b5 3a40 cc42 3c3c 83ea .....8..:@.B<<..
0x0020 8018 e000 28fb 0000 0101 080a 2b81 dde4 ....(.......+...
0x0030 2b81 dde3 5400 0000 1d00 016c 616e 6700 +...T......lang.
0x0040 0000 4821 0003 0000 0019 ffff ffff ffff ..H!............
0x0050 0000 4400 0000 0c00 0100 0000 0273 7643 ..D..........svC
0x0060 0000 000b 5345 4c45 4354 005a 0000 0005 ....SELECT.Z....
0x0070 49 I

Certainly looks like a bug, in the java version mango is not quoted so
pg thinks it is a column.

Although I just checked the test cases and we do this all the time ???

Dave

Palle Girgensohn wrote:

> Hi!
>
> Here's something really odd. Is it a bug in the jdbc driver? 8.0.311:
>
>
> String query = "select lang from participant where userid = ?
> and course_id = ?";
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, userId);
> ps.setInt(2, courseId);
> ResultSet res = ps.executeQuery();
>
> System.err.println("[1] "+ps);
>
> boolean hasNext = res.next();
> System.err.println("hasNext: "+hasNext);
>
> if (hasNext) {
> System.err.println("[2]");
> language = res.getString(1);
> System.err.println("language: "+language);
> }
> System.err.println("[3]");
>
> In the java error log:
>
> ---
> [1] select lang from participant where userid = mango and course_id = 38
> hasNext: false
> [3]
> ---
>
>
> but with psql, there's no problem getting a value:
>
> mango=> select lang from participant where userid = 'mango' and
> course_id = 38;
> lang
> ------
> sv
> (1 row)
>
> (Connected to same database as same user.)
>
> What's up? Is there a problem with the (new) prepared statement stuff?
>
> postgresql-8.0.2, postgresql-jdbc-8.0.311, FreeBSD 4.11, java 1.4.2.
>
> /Palle
>
> PS. Here's a tcpdump of the query, FWIW:
>
> 13:37:14.909230 localhost.3277 > localhost.postgresql: P
> 564113356:564113492(136) ack 897123100 win 57344 <nop,nop,timestamp
> 729334714 729334409> (DF)
> 0x0000 4500 00bc 5038 4000 4006 ec01 7f00 0001 E(dot)(dot)(dot)P8(at)(dot)@.......
> 0x0010 7f00 0001 0ccd 1538 219f afcc 3579 031c .......8!...5y..
> 0x0020 8018 e000 6474 0000 0101 080a 2b78 c3ba ....dt......+x..
> 0x0030 2b78 c289 5000 0000 5100 7365 6c65 6374 +x..P...Q.select
> 0x0040 206c 616e 6720 6672 6f6d 2070 6172 7469 .lang.from.parti
> 0x0050 6369 7061 6e74 2077 6865 7265 2075 7365 cipant.where.use
> 0x0060 7269 6420 3d20 2431 2061 6e64 2063 6f75 rid.=.$1.and.cou
> 0x0070 7273 655f 6964 203d 2024 3200 0002 0000 rse_id.=.$2.....
> 0x0080 0413 0000 0017 4200 0000 1f00 0000 0200 ......B.........
> 0x0090 0000 0000 0200 0000 056d 616e 676f 0000 .........mango..
> 0x00a0 0002 3338 0000 4400 0000 0650 0045 0000 ..38..D....P.E..
> 0x00b0 0009 0000 0000 0053 0000 0004 .......S....
> 13:37:14.916554 localhost.postgresql > localhost.3277: P 1:59(58) ack
> 136 win 57344<nop,nop,timestamp 729334715 729334714> (DF)
> 0x0000 4500 006e 5039 4000 4006 ec4e 7f00 0001 E(dot)(dot)nP9(at)(dot)@..N....
> 0x0010 7f00 0001 1538 0ccd 3579 031c 219f b054 .....8..5y..!..T
> 0x0020 8018 e000 4908 0000 0101 080a 2b78 c3bb ....I.......+x..
> 0x0030 2b78 c3ba 3100 0000 0432 0000 0004 5400 +x..1....2....T.
> 0x0040 0000 1d00 016c 616e 6700 0000 4821 0003 .....lang...H!..
> 0x0050 0000 0019 ffff ffff ffff 0000 4300 0000 ............C...
> 0x0060 0b53 454c 4543 5400 5a00 0000 0549 .SELECT.Z....I
>
>
>
> And here's one from psql (that works fine, result = 'sv'):
>
> 15:16:40.179908 localhost.1973 > localhost.postgresql: P 82:159(77)
> ack 294 win 57344 <nop,nop,timestamp 729931235 729930768> (DF)
> 0x0000 4500 0081 f57e 4000 4006 46f6 7f00 0001 E(dot)(dot)(dot)(dot)~(at)(dot)@.F.....
> 0x0010 7f00 0001 07b5 1538 3c3c 839d 3a40 cc42 .......8<<..:@.B
> 0x0020 8018 e000 77ae 0000 0101 080a 2b81 dde3 ....w.......+...
> 0x0030 2b81 dc10 5100 0000 4c73 656c 6563 7420 +...Q...Lselect.
> 0x0040 6c61 6e67 2066 726f 6d20 7061 7274 6963 lang.from.partic
> 0x0050 6970 616e 7420 7768 6572 6520 7573 6572 ipant.where.user
> 0x0060 6964 203d 2027 6d61 6e67 6f27 2061 6e64 id.=.'mango'.and
> 0x0070 2063 6f75 7273 655f 6964 203d 2033 383b .course_id.=.38;
> 0x0080 00 .
> 15:16:40.183568 localhost.postgresql > localhost.1973: P 294:355(61)
> ack 159 win 57344 <nop,nop,timestamp 729931236 729931235> (DF)
> 0x0000 4500 0071 f580 4000 4006 4704 7f00 0001 E(dot)(dot)q(dot)(dot)(at)(dot)@.G.....
> 0x0010 7f00 0001 1538 07b5 3a40 cc42 3c3c 83ea .....8..:@.B<<..
> 0x0020 8018 e000 28fb 0000 0101 080a 2b81 dde4 ....(.......+...
> 0x0030 2b81 dde3 5400 0000 1d00 016c 616e 6700 +...T......lang.
> 0x0040 0000 4821 0003 0000 0019 ffff ffff ffff ..H!............
> 0x0050 0000 4400 0000 0c00 0100 0000 0273 7643 ..D..........svC
> 0x0060 0000 000b 5345 4c45 4354 005a 0000 0005 ....SELECT.Z....
> 0x0070 49 I
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Well, honestly, i'm pretty sure the driver works as well... Problem is, all
other queries *around* this one works.

I guess we'll have to dig deeper into this...

/Palle

--On fredag, april 22, 2005 10.30.37 -0400 Dave Cramer <pg(at)fastcrypt(dot)com>
wrote:

> Palle,
>
> Well, I'm fairly sure the driver works, so my best guess is that you
> aren't connecting to the db you think you are.
>
> Dave
>
> Palle Girgensohn wrote:
>
>> Well, it uses the server side prepared statements (in the V3
>> protocol?), as you can see in the tcpdump at the end.
>>
>> The working query is
>>
>> select lang from participant where userid = 'mango' and course_id = 38
>>
>> and the dyfunctional is
>>
>> select lang from participant where userid = $1 and course_id = $2
>>
>> with args = mango and 38...
>>
>> /Palle
>>
>>
>> --On fredag, april 22, 2005 10.16.08 -0400 Dave Cramer
>> <pg(at)fastcrypt(dot)com> wrote:
>>
>>> Certainly looks like a bug, in the java version mango is not quoted
>>> so pg
>>> thinks it is a column.
>>>
>>> Although I just checked the test cases and we do this all the time ???
>>>
>>> Dave
>>>
>>> Palle Girgensohn wrote:
>>>
>>>> Hi!
>>>>
>>>> Here's something really odd. Is it a bug in the jdbc driver? 8.0.311:
>>>>
>>>>
>>>> String query = "select lang from participant where userid = ?
>>>> and course_id = ?";
>>>> PreparedStatement ps = con.prepareStatement(query);
>>>> ps.setString(1, userId);
>>>> ps.setInt(2, courseId);
>>>> ResultSet res = ps.executeQuery();
>>>>
>>>> System.err.println("[1] "+ps);
>>>>
>>>> boolean hasNext = res.next();
>>>> System.err.println("hasNext: "+hasNext);
>>>>
>>>> if (hasNext) {
>>>> System.err.println("[2]");
>>>> language = res.getString(1);
>>>> System.err.println("language: "+language);
>>>> }
>>>> System.err.println("[3]");
>>>>
>>>> In the java error log:
>>>>
>>>> ---
>>>> [1] select lang from participant where userid = mango and course_id
>>>> = 38
>>>> hasNext: false
>>>> [3]
>>>> ---
>>>>
>>>>
>>>> but with psql, there's no problem getting a value:
>>>>
>>>> mango=> select lang from participant where userid = 'mango' and
>>>> course_id = 38;
>>>> lang
>>>> ------
>>>> sv
>>>> (1 row)
>>>>
>>>> (Connected to same database as same user.)
>>>>
>>>> What's up? Is there a problem with the (new) prepared statement stuff?
>>>>
>>>> postgresql-8.0.2, postgresql-jdbc-8.0.311, FreeBSD 4.11, java 1.4.2.
>>>>
>>>> /Palle
>>>>
>>>> PS. Here's a tcpdump of the query, FWIW:
>>>>
>>>> 13:37:14.909230 localhost.3277 > localhost.postgresql: P
>>>> 564113356:564113492(136) ack 897123100 win 57344 <nop,nop,timestamp
>>>> 729334714 729334409> (DF)
>>>> 0x0000 4500 00bc 5038 4000 4006 ec01 7f00 0001
>>>> E(dot)(dot)(dot)P8(at)(dot)@.......
>>>> 0x0010 7f00 0001 0ccd 1538 219f afcc 3579 031c
>>>> .......8!...5y..
>>>> 0x0020 8018 e000 6474 0000 0101 080a 2b78 c3ba
>>>> ....dt......+x..
>>>> 0x0030 2b78 c289 5000 0000 5100 7365 6c65 6374
>>>> +x..P...Q.select
>>>> 0x0040 206c 616e 6720 6672 6f6d 2070 6172 7469
>>>> .lang.from.parti
>>>> 0x0050 6369 7061 6e74 2077 6865 7265 2075 7365
>>>> cipant.where.use
>>>> 0x0060 7269 6420 3d20 2431 2061 6e64 2063 6f75
>>>> rid.=.$1.and.cou
>>>> 0x0070 7273 655f 6964 203d 2024 3200 0002 0000
>>>> rse_id.=.$2.....
>>>> 0x0080 0413 0000 0017 4200 0000 1f00 0000 0200
>>>> ......B.........
>>>> 0x0090 0000 0000 0200 0000 056d 616e 676f 0000
>>>> .........mango..
>>>> 0x00a0 0002 3338 0000 4400 0000 0650 0045 0000
>>>> ..38..D....P.E..
>>>> 0x00b0 0009 0000 0000 0053 0000 0004 .......S....
>>>> 13:37:14.916554 localhost.postgresql > localhost.3277: P 1:59(58) ack
>>>> 136 win 57344<nop,nop,timestamp 729334715 729334714> (DF)
>>>> 0x0000 4500 006e 5039 4000 4006 ec4e 7f00 0001
>>>> E(dot)(dot)nP9(at)(dot)@..N....
>>>> 0x0010 7f00 0001 1538 0ccd 3579 031c 219f b054
>>>> .....8..5y..!..T
>>>> 0x0020 8018 e000 4908 0000 0101 080a 2b78 c3bb
>>>> ....I.......+x..
>>>> 0x0030 2b78 c3ba 3100 0000 0432 0000 0004 5400
>>>> +x..1....2....T.
>>>> 0x0040 0000 1d00 016c 616e 6700 0000 4821 0003
>>>> .....lang...H!..
>>>> 0x0050 0000 0019 ffff ffff ffff 0000 4300 0000
>>>> ............C...
>>>> 0x0060 0b53 454c 4543 5400 5a00 0000 0549 .SELECT.Z....I
>>>>
>>>>
>>>>
>>>> And here's one from psql (that works fine, result = 'sv'):
>>>>
>>>> 15:16:40.179908 localhost.1973 > localhost.postgresql: P 82:159(77)
>>>> ack 294 win 57344 <nop,nop,timestamp 729931235 729930768> (DF)
>>>> 0x0000 4500 0081 f57e 4000 4006 46f6 7f00 0001
>>>> E(dot)(dot)(dot)(dot)~(at)(dot)@.F.....
>>>> 0x0010 7f00 0001 07b5 1538 3c3c 839d 3a40 cc42
>>>> .......8<<..:@.B
>>>> 0x0020 8018 e000 77ae 0000 0101 080a 2b81 dde3
>>>> ....w.......+...
>>>> 0x0030 2b81 dc10 5100 0000 4c73 656c 6563 7420
>>>> +...Q...Lselect.
>>>> 0x0040 6c61 6e67 2066 726f 6d20 7061 7274 6963
>>>> lang.from.partic
>>>> 0x0050 6970 616e 7420 7768 6572 6520 7573 6572
>>>> ipant.where.user
>>>> 0x0060 6964 203d 2027 6d61 6e67 6f27 2061 6e64
>>>> id.=.'mango'.and
>>>> 0x0070 2063 6f75 7273 655f 6964 203d 2033 383b
>>>> .course_id.=.38;
>>>> 0x0080 00 .
>>>> 15:16:40.183568 localhost.postgresql > localhost.1973: P 294:355(61)
>>>> ack 159 win 57344 <nop,nop,timestamp 729931236 729931235> (DF)
>>>> 0x0000 4500 0071 f580 4000 4006 4704 7f00 0001
>>>> E(dot)(dot)q(dot)(dot)(at)(dot)@.G.....
>>>> 0x0010 7f00 0001 1538 07b5 3a40 cc42 3c3c 83ea
>>>> .....8..:@.B<<..
>>>> 0x0020 8018 e000 28fb 0000 0101 080a 2b81 dde4
>>>> ....(.......+...
>>>> 0x0030 2b81 dde3 5400 0000 1d00 016c 616e 6700
>>>> +...T......lang.
>>>> 0x0040 0000 4821 0003 0000 0019 ffff ffff ffff
>>>> ..H!............
>>>> 0x0050 0000 4400 0000 0c00 0100 0000 0273 7643
>>>> ..D..........svC
>>>> 0x0060 0000 000b 5345 4c45 4354 005a 0000 0005
>>>> ....SELECT.Z....
>>>> 0x0070 49 I
>>>>
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>>> message can get through to the mailing list cleanly
>>>>
>>>>
>>>
>>> --
>>> Dave Cramer
>>> http://www.postgresintl.com
>>> 519 939 0336
>>> ICQ#14675561
>>>
>>
>>
>>
>>
>>
>>
>
> --
> Dave Cramer
> http://www.postgresintl.com
> 519 939 0336
> ICQ#14675561
>

Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> Certainly looks like a bug, in the java version mango is not quoted so
> pg thinks it is a column.

But that's just sloppiness in the java log message. The TCP dump shows
clearly that the values are being sent as $n parameters.

I'm wondering about misassignment of data types or something. It's not
clear though how that would result in no error but zero rows returned.
What are the column data types exactly?

regards, tom lane

--On fredag, april 22, 2005 11.01.32 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>> Certainly looks like a bug, in the java version mango is not quoted so
>> pg thinks it is a column.
>
> But that's just sloppiness in the java log message. The TCP dump shows
> clearly that the values are being sent as $n parameters.
>
> I'm wondering about misassignment of data types or something. It's not
> clear though how that would result in no error but zero rows returned.
> What are the column data types exactly?

userid is text (setInt())
courseid is int (setString())

lang is text

Interesting that the int is transported as text, `38', but I guess there
are very good reasons for this (not having to duplicate code in the jdbvc
driver, different endians, etc).

There is no error in the pgsql log, just no result. The same connection is
beeing used for all queries. It is reproducable, every time, in our app.

/Palle

Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> --On fredag, april 22, 2005 11.01.32 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>> I'm wondering about misassignment of data types or something. It's not
>> clear though how that would result in no error but zero rows returned.
>> What are the column data types exactly?

> userid is text (setInt())
> courseid is int (setString())

The tcpdump shows that JDBC is specifying the parameter types as 1043
(varchar) and 23 (int4). It's hard to believe that varchar versus
text would be an issue ... but ... what happens if you do this in
psql?
select lang from participant
where userid = 'mango'::varchar and course_id = 38;

regards, tom lane

Palle,

I presume you mixed the two up ?

userid is text (setInt())
courseid is int (setString())

Dave

Palle Girgensohn wrote:

>
>
> --On fredag, april 22, 2005 11.01.32 -0400 Tom Lane
> <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>>
>>> Certainly looks like a bug, in the java version mango is not quoted so
>>> pg thinks it is a column.
>>
>>
>> But that's just sloppiness in the java log message. The TCP dump shows
>> clearly that the values are being sent as $n parameters.
>>
>> I'm wondering about misassignment of data types or something. It's not
>> clear though how that would result in no error but zero rows returned.
>> What are the column data types exactly?
>
>
> userid is text (setInt())
> courseid is int (setString())
>
> lang is text
>
>
> Interesting that the int is transported as text, `38', but I guess
> there are very good reasons for this (not having to duplicate code in
> the jdbvc driver, different endians, etc).
>
> There is no error in the pgsql log, just no result. The same
> connection is beeing used for all queries. It is reproducable, every
> time, in our app.
>
> /Palle
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

works fine on HEAD...

create table participant ( userid text, course_id int4)

davec=# select * from participant where userid='mango'::varchar and
course_id=38;
userid | course_id
--------+-----------
mango | 38

Dave

Tom Lane wrote:

>Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>
>
>>--On fredag, april 22, 2005 11.01.32 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>>wrote:
>>
>>
>>>I'm wondering about misassignment of data types or something. It's not
>>>clear though how that would result in no error but zero rows returned.
>>>What are the column data types exactly?
>>>
>>>
>
>
>
>>userid is text (setInt())
>>courseid is int (setString())
>>
>>
>
>The tcpdump shows that JDBC is specifying the parameter types as 1043
>(varchar) and 23 (int4). It's hard to believe that varchar versus
>text would be an issue ... but ... what happens if you do this in
>psql?
> select lang from participant
> where userid = 'mango'::varchar and course_id = 38;
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

--On fredag, april 22, 2005 11.23.12 -0400 Dave Cramer <pg(at)fastcrypt(dot)com>
wrote:

> Palle,
>
> I presume you mixed the two up ?
>
> userid is text (setInt())
> courseid is int (setString())

indeed :-)

/Palle

>
>
> Dave
>
> Palle Girgensohn wrote:
>
>>
>>
>> --On fredag, april 22, 2005 11.01.32 -0400 Tom Lane
>> <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>>>
>>>> Certainly looks like a bug, in the java version mango is not quoted so
>>>> pg thinks it is a column.
>>>
>>>
>>> But that's just sloppiness in the java log message. The TCP dump shows
>>> clearly that the values are being sent as $n parameters.
>>>
>>> I'm wondering about misassignment of data types or something. It's not
>>> clear though how that would result in no error but zero rows returned.
>>> What are the column data types exactly?
>>
>>
>> userid is text (setInt())
>> courseid is int (setString())
>>
>> lang is text
>>
>>
>> Interesting that the int is transported as text, `38', but I guess
>> there are very good reasons for this (not having to duplicate code in
>> the jdbvc driver, different endians, etc).
>>
>> There is no error in the pgsql log, just no result. The same
>> connection is beeing used for all queries. It is reproducable, every
>> time, in our app.
>>
>> /Palle
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>
> --
> Dave Cramer
> http://www.postgresintl.com
> 519 939 0336
> ICQ#14675561
>

--On fredag, april 22, 2005 11.17.33 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>> --On fredag, april 22, 2005 11.01.32 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> wrote:
>>> I'm wondering about misassignment of data types or something. It's not
>>> clear though how that would result in no error but zero rows returned.
>>> What are the column data types exactly?
>
>> userid is text (setInt())
>> courseid is int (setString())
>
> The tcpdump shows that JDBC is specifying the parameter types as 1043
> (varchar) and 23 (int4). It's hard to believe that varchar versus
> text would be an issue ... but ... what happens if you do this in
> psql?
> select lang from participant
> where userid = 'mango'::varchar and course_id = 38;

mango=# select lang from participant
mango-# where userid = 'mango'::varchar and course_id = 38;
lang
------
sv
(1 row)

i.e. works as expected...

hmmm.... is there any way to use the server's prepared statements from psql?

Dave Cramer wrote:
> Certainly looks like a bug, in the java version mango is not quoted so
> pg thinks it is a column.
>
> Although I just checked the test cases and we do this all the time ???

The (lack of) quoting is a red herring, it's just how our
PreparedStatement.toString() is implemented. The actual query sent uses
$1/$2 as you can see in the tcpdump.

-O

--On lördag, april 23, 2005 10.10.28 +1200 Oliver Jowett
<oliver(at)opencloud(dot)com> wrote:

> Dave Cramer wrote:
>> Certainly looks like a bug, in the java version mango is not quoted so
>> pg thinks it is a column.
>>
>> Although I just checked the test cases and we do this all the time ???
>
> The (lack of) quoting is a red herring, it's just how our
> PreparedStatement.toString() is implemented. The actual query sent uses
> $1/$2 as you can see in the tcpdump.

Is there a way to mimic this behaviour, i.e. using a prepared statement
with $1/$2, in psql?

/Palle

Palle Girgensohn wrote:
> --On lördag, april 23, 2005 10.10.28 +1200 Oliver Jowett
> <oliver(at)opencloud(dot)com> wrote:
>
>> Dave Cramer wrote:
>>
>>> Certainly looks like a bug, in the java version mango is not quoted so
>>> pg thinks it is a column.
>>>
>>> Although I just checked the test cases and we do this all the time ???
>>
>>
>> The (lack of) quoting is a red herring, it's just how our
>> PreparedStatement.toString() is implemented. The actual query sent uses
>> $1/$2 as you can see in the tcpdump.
>
>
> Is there a way to mimic this behaviour, i.e. using a prepared statement
> with $1/$2, in psql?

You could use PREPARE/EXECUTE which is similiar if not identical. From
memory:

PREPARE foo(int,varchar) AS SELECT ... $1 ... $2 ...;
EXECUTE foo(42, "bar");

-O

Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> hmmm.... is there any way to use the server's prepared statements from psql?

psql can't use the V3 protocol facilities, which means that you can't
duplicate the case very closely.

After reading your report this morning, I made a little test program
using libpq's PQexecParams (basically a quick hack on
src/test/examples/testlibpq3.c) to duplicate your query. I didn't
see any misbehavior; so there is some additional triggering condition
beyond what you've told us.

Given that the query packet looks 100% reasonable by eyeball, it seems
unlikely that the issue is on the JDBC side (barring obvious snafus such
as connecting to a different database than psql is talking to). But I
don't have any data with which to pursue the hypothesis of a server bug.

regards, tom lane

--On fredag, april 22, 2005 18.24.07 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>> hmmm.... is there any way to use the server's prepared statements from
>> psql?
>
> psql can't use the V3 protocol facilities, which means that you can't
> duplicate the case very closely.
>
> After reading your report this morning, I made a little test program
> using libpq's PQexecParams (basically a quick hack on
> src/test/examples/testlibpq3.c) to duplicate your query. I didn't
> see any misbehavior; so there is some additional triggering condition
> beyond what you've told us.
>
> Given that the query packet looks 100% reasonable by eyeball, it seems
> unlikely that the issue is on the JDBC side (barring obvious snafus such
> as connecting to a different database than psql is talking to). But I
> don't have any data with which to pursue the hypothesis of a server bug.

Hmm, yes, there is indeed one thing I didn't tell you... the server is
patches with my ICU patch. That might be a problem? The patch is at
http://people.freebsd.org/~girgen/postgresql-icu

I'll check modifying testlipg3.c with my server, and see what happens.

/Palle

Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> Hmm, yes, there is indeed one thing I didn't tell you... the server is
> patches with my ICU patch. That might be a problem? The patch is at
> http://people.freebsd.org/~girgen/postgresql-icu

Hmm ... that does fool around with the behavior of string comparisons,
doesn't it?

I know nothing about ICU so can't really critique the patch, but I'd
say the next step is to see if you can reproduce the failure without
ICU in place.

Another thing to think about is whether you maybe need to reindex
whatever indexes exist on participant.userid. If you've changed
the collation ordering by installing or bug-fixing ICU, then pre-
existing textual indexes are corrupt. Not sure why psql wouldn't
show the same failure, but maybe a different plan is being chosen
in the two cases? It'd be worth checking to see if you get the
same plans for

explain analyze
select lang from participant where userid = 'mango' and course_id = 38;

prepare foo(varchar,int4) as
select lang from participant where userid = $1 and course_id = $2;

explain analyze execute foo('mango',38);

regards, tom lane