Re: Getting non_NULL right-side values on a non-matching join?

The issue also seems tied to the non-NULL constant in the view.

This one yields rows
33::int AS b_field

This one doesn't
NULL::int AS b_field

DROP VIEW IF EXISTS boo_top_view;
DROP VIEW IF EXISTS boo_view;
DROP TABLE IF EXISTS boo_table;
DROP TABLE IF EXISTS a_table;

CREATE TABLE boo_table ( client_id INTEGER,b_field INTEGER);
CREATE TABLE a_table ( client_id INTEGER );

CREATE OR REPLACE VIEW boo_view AS
SELECT
r1.client_id,
-- This one yields rows
33::int AS b_field
-- This one doesn't
-- NULL::int AS b_field

FROM boo_table r1;

CREATE OR REPLACE VIEW boo_top_view AS
SELECT
client_id,
-- my_field
b_field
FROM (
SELECT
a.client_id,
-- a.my_field
a.b_field
FROM boo_view AS a
) foo
--Problem goes away if you take out this left join
LEFT JOIN (
SELECT client_id FROM a_table
) a2 USING (client_id);

SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,b_field from (SELECT
-1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id);

On Thu, Nov 21, 2013 at 7:49 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
>> > Hello. In doing a left join with a particular view as the right table,
>> and
>> > non-matching join criteria, I am getting values returned in a few
>> fields.
>> > All the rest are NULL. I would expect all the right side values to be
>> > NULL.
>>
>> Hmmm ... the join conditions involving COALESCE() remind me of a bug I
>> just fixed last week. Are you in a position to try a patch? If so,
>> here's the fix against 9.2:
>>
>> http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=51b6ae6bba75bca2374a24cf7c740da74c955ad5
>>
>> If that doesn't help, please see if you can extract a self-contained
>> test case.
>
>
> Getting a build environment together seemed more painful, so here's a test
> case. Just for fun, I tried this in another database on a different
> machine (and with 9.0.08). I got the same results, so it doesn't seem to
> be a case of something wacky with my particular database.
>
> Cheers,
> Ken
>
> p.s., Not your problem I know, but I need to deal with this somehow and
> rather soon. If the patch you mentioned does fix this, and that's the
> easiest way to get this fixed on my machine, please do let me know and I'll
> start googling Postgres build source. Thanks!
>
>
> DROP VIEW IF EXISTS boo_top_view;
> DROP VIEW IF EXISTS boo_view;
> DROP TABLE IF EXISTS boo_table;
> DROP TABLE IF EXISTS a_table;
>
> CREATE TABLE boo_table ( client_id INTEGER);
> CREATE TABLE a_table ( client_id INTEGER );
>
> CREATE OR REPLACE VIEW boo_view AS
> SELECT
> r1.client_id,
> 666 AS my_field
> FROM boo_table r1;
>
> CREATE OR REPLACE VIEW boo_top_view AS
> SELECT
> client_id,
> my_field
> FROM (
> SELECT
> a.client_id,
> a.my_field
> FROM boo_view AS a
> ) foo
> --Problem goes away if you take out this left join
> LEFT JOIN (
> SELECT client_id FROM a_table
> ) a2 USING (client_id);
>
> SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,my_field from
> (SELECT -1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id);
>
> foo_id | ri_id | my_field
> --------+-------+----------
> -1 | | 666
>
>
>
>
>> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/ <http://agency-software.org/>*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ <http://agency-software.org/>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.