Re: ODBC v08.02.0100 - Mailing list pgsql-odbc
Ahh, I see.
In the LEFT OUTER JOIN, the resultset has potential NULLs, for all fields from the table on the right side of "LEFT OUTER JOIN".
Per the PostgreSQL documents, in the section on SQL,
the LEFT OUTER JOIN, example: test LEFT OUTER JOIN test2
will first do an inner join to find all matching rows (test.fk_test2=test2.id), and then records from the left table (test) but which have no matching records from the right table (test2), will be added and the right table (test2) fields will be filled with NULLs.
Back to your example:
The result of your left outer join should be such that
resultset.id and
resultset.field1 --- can never be NULL. This is no surprise.
resultset.fk_test2 and
resultset.field 2 and
resultset.field3 --- can be expected to be NULL sometimes. This is no surprise.
while
resultset.id2
resultset.field4
resultset.field5
can all also be expected to be NULL potentially, when there is a records in test but no matching records in test2 (per test.fk_test2=test2.id).
So I think your issue is that for the resultset it should not have NOT NULL as a constraint for
resultset. id2 and resultset.field4. Everything from test2 is nullable. Yes, it sound like you found a bug.
And, you say fk_test2 will actually be NULL sometimes. This will cause the resultset to always have a row with NULL in id2, field4, and field5
because NULL is never equal to NULL. test.fk_test2 = test.id is always false when fk_test2 is NULL, even if test2.id is NULL.
Futher test2.id probably should not be null, Hopefully it is a primary key.
Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com"luiz" <luiz@planit.com.br>
Sent by: pgsql-odbc-owner@postgresql.org 08/31/2006 16:09 |
|
Hi Greg
I'm sorry if I'm not can express clearly what I need to explain.
My english is litle.
In a left outer join like below, field4 may have null values when test.fk_test2 is null,
now the result is a char(3) empty field, then I presume that this is a incorrect resultset
On Thu, 31 Aug 2006 15:26:04 -0400, greg.campbell wrote
> So your issue is that now the field types including NULL constraints match the underlying tables?
> Is the change caused just from changing pgODBC driver versions, while the database version stayed the same.?
> Isn't this the result we would expect if we were using psql at the command line?
> I don't mean to be rude, but does your application expect a resultset where certain field never have a NULL value, but where the database design has the fields specified as nullable (no specification of NOT NULL)? For instance in your example fk_test2 is designed to allow NULL, but perhaps an application does not anticipate the possibility that NULL may be its value.
>
> Perhaps the old behavior was a bug.
>
> Greg Campbell ENG-ASE/Michelin US5
> Lexington, South Carolina
> 803-951-5561, x75561
> Fax: 803-951-5531
> greg.campbell@us.michelin.com
>
> "luiz" <luiz@planit.com.br>
>
>
> Sent by: pgsql-odbc-owner@postgresql.org > 08/31/2006 14:14 |
|
> Hi all,
>
> I make some tests with this new version and i notice a diference in the
> behaviour about the structures of tables returned by queries trough this
> version and older versions of odbc
>
> I'm using Visual FoxPro with postgres 8.1.4.
>
> In previous version all fields are marked to accept null values.
> In this version (8.02.0100) some fields now don't accept null values, seemed
> to me that now use the same definition of source table structure, like this:
>
> CREATE TABLE test (
> id serial NOT NULL,
> fk_test2 int4,
> field1 char(3) NOT NULL,
> field2 varchar(10),
> field3 date );
>
> CREATE TABLE test2 (
> id serial NOT NULL,
> field4 char(3) NOT NULL,
> field5 char(3) );
>
> SELECT test.*
> , test2.id as id2
> , test2.field4
> , test2.field5
> from test LEFT OUTER JOIN test2 on test.fk_test2 = test2.id;
>
> The fields of resultset will be now this:
>
> id int4 NOT NULL
> fk_test2 int4
> field1 char(3) NOT NULL
> field2 varchar(10)
> field3 date
> id2 int4 NOT NULL
> field4 char(3) NOT NULL
> field5 char(3)
>
> with odbc version 8.01.0200 all fields accept null values.
>
> I'm not sure about what is the correct behaviour, but this can be considered
> as a bug in the new version ?
>
> Thanks in Advance
>
> Luiz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Plan IT Tecnologia Informatica Ltda.
Attachment
pgsql-odbc by date: