Bug #626: Query with View not returning proper results

Les Hazlewood (les(at)hazlewood(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Query with View not returning proper results

Long Description
Hello,

I have been trying step by step to find where my query was working, and the breaking point where it doesn't work anymore.

Heres my setup for your reference:

Red Hat Linux 7.1
PostgreSQL 7.1.3
Postgresql JDBC Driver pgjdbc2.jar

I create a view:

create view my_view as
<long query snipped out for brevity>

The view is created fine. I can get all the results back from it in psql no problem. For reference below, user_id and user_login_date columns both exist in the view.

Now in my java code, I query the view and use a subselect:

select * from my_view where user_id in (select user_id from users);

This query executes no problem in both psql and in java.

Now I get a little more specific (which is what i need to do..the above was just a test case to see how far I could get before an error):

select * from my_view where user_id in (select user_id from users where last_login_date is null);

This query executes no problem via the psql command prompt in a CONSOLE WINDOW ONLY.

This exact query cut-and-pasted into the java code doesn't return anything.

The java ResultSet object is not null, but the resultSet.next() method doesn't return true (which of course prevents looping, meaning no results were returned).

I'd be happy to help with any additional information if it is required.

Thanks so much,

Les

Sample Code

No file was uploaded with this report