SEO

Hi everybody ! First of all: I apologize for my bad English and i will
appreciate any help.

I'm migrating a postgresql server from 7.3.15 to 8.0.14 and i've found
a issue about using new drivers and column order retrieval in left outer
joins. The only way that i found to avoid break old applications is to
use the driver pg74.216.jdbc3.jar (the driver
pg73jdbc3.jar has a "set autocommit" issue) to access a 8.0.14 server.
I've already tried some driver's "compatibility" options but they didn't
work for me.

To illustrate my one of my problems i've created some tables and i've
inserted some data into them. After that, i've run a query in psql
prompt:

test_db => select p.*, c.* from parent p left outer join child c on
(c.p_id = p.p_id) order by p.p_id;
p_id | p_name | p_id | c_name
------+--------+------+--------
1 | John | 1 | Alice
1 | John | 1 | Sam
2 | Paul | |
3 | Mary | 3 | Lucas
(4 rows)

When i run this query using old jdbc driver (7.4), i got the expected
result (for me), retrieving data through
resultSet.getObject(columnName):

1; John; 1; Alice
1; John; 1; Sam
2; Paul; 2; null
3; Mary; 3; Lucas

When i run this query using new jdbc driver (8.0-8.2), something
happens:

1; John; 1; Alice
1; John; 1; Sam
null; Paul; null; null
3; Mary; 3; Lucas


Which one of these behaviors is the right one ? Thanks in advance !

=================
== Source code ==
=================

create table parent (
p_id integer not null,
p_name varchar(30) not null
);

alter table parent add constraint parent_pk primary key (p_id);
alter table parent add constraint parent_name_uk unique (p_name);


create table child (
p_id integer not null,
c_name varchar(30) not null
);


alter table child add constraint child_parent_fk foreign key (p_id)
references parent(p_id);
alter table child add constraint child_name_uk unique (p_id, c_name);


insert into parent (p_id, p_name) values (1, 'John');
insert into parent (p_id, p_name) values (2, 'Paul');
insert into parent (p_id, p_name) values (3, 'Mary');


insert into child (p_id, c_name) values (1, 'Sam');
insert into child (p_id, c_name) values (1, 'Alice');
insert into child (p_id, c_name) values (3, 'Lucas');

----------------------

connection =
driver.connect("jdbcostgresql://127.0.0.1/test_db?charSet=UTF-8",
props);
statement = connection.createStatement();

resultSet = statement.executeQuery("select p.*, c.* from parent p left
outer " + "join child c on (c.p_id = p.p_id) order by p.p_id");

while (resultSet.next()) {
System.out.println(resultSet.getObject("p_id") + "; "
+ resultSet.getString("p_name") + "; "+ resultSet.getObject("p_id")
+ "; "+ resultSet.getString("c_name"));
}


--
"If there must be trouble, let it be in my day,
that my child may have peace."

Thomas Paine


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Daniel Henrique Alves Lima wrote:
> The only way that i found to avoid break old applications is to
> use the driver pg74.216.jdbc3.jar (the driver
> pg73jdbc3.jar has a "set autocommit" issue) to access a 8.0.14 server.

This may or may not work but it's not supported.

> Which one of these behaviors is the right one ? Thanks in advance !

Both are equally correct, I believe. I think the JDBC spec is silent
about which column you get back if there is ambiguity.

The difference is because earlier drivers did a simple search through
the resultset column names on every access while newer drivers populate
a map of column names -> column indexes once. Doing a search every time
has performance problems.

I think your only hope is to change the query so the column names of the
resultset are not ambiguous.

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Oliver Jowett wrote:

>> Which one of these behaviors is the right one ? Thanks in advance !
>
> Both are equally correct, I believe. I think the JDBC spec is silent
> about which column you get back if there is ambiguity.

Actually it says something about this.
"If a select list contains the same column more than once, the first
instance of the column will be returned" (JDBC3 §14.2.3 and JDBC4 §15.2.3)

--Magne

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, 14 Jan 2008, Magne Mæhre wrote:

> Actually it says something about this. "If a select list contains the
> same column more than once, the first instance of the column will be
> returned" (JDBC3 §14.2.3 and JDBC4 §15..2.3)
>

Thanks for the details. I've fixed this in CVS for the 8.0 -> 8.3dev
drivers.

Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster