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

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Re: Getting non_NULL right-side values on a non-matching join?
Дата	22 ноября 2013 г. 07:59:55
Msg-id	CAD3a31WHvjQjKLO+vy5PgsX0Zt0qH+ZL+846jeYhSVkjQMMD3Q@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Getting non_NULL right-side values on a non-matching join? (Ken Tanzer <ken.tanzer@gmail.com>)
Список	pgsql-general

Дерево обсуждения

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.tanzer@gmail.com> wrote:

On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ken Tanzer <ken.tanzer@gmail.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/
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list to
learn more about AGENCY or
follow the discussion.