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 г. 03:30:42
Msg-id	CAD3a31XswL5ygrOU7sW2f305pYZ0n=Udx9-WQPifxYck92DWAw@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Getting non_NULL right-side values on a non-matching join? (Kevin Grittner <kgrittn@ymail.com>)
Список	pgsql-general

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

On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn@ymail.com> wrote:

Ken Tanzer <ken.tanzer@gmail.com> wrote:

> 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.

What is the output of executing?:

SELECT version();

I think I'm current on 9.2.5 / CentOs 6.4.

ag_spc=> SELECT version();

version

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

PostgreSQL 9.2.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit

(1 row)

[spc@hosting agency_code]$ rpm -qi postgresql92

Name : postgresql92 Relocations: (not relocatable)

Version : 9.2.5 Vendor: (none)

Release : 1PGDG.rhel6 Build Date: Wed 09 Oct 2013 06:02:22 AM PDT

Install Date: Tue 15 Oct 2013 06:22:39 PM PDT Build Host: koji-sl6-x86-64-pg92

Group : Applications/Databases Source RPM: postgresql92-9.2.5-1PGDG.rhel6.src.rpm

Size : 5279557 License: PostgreSQL

Signature : DSA/SHA1, Wed 09 Oct 2013 06:02:31 AM PDT, Key ID 1f16d2e1442df0f8

URL : http://www.postgresql.org/

Summary : PostgreSQL client programs and libraries

...

Don't know if this is useful information, but I was surprised that the problem continues even wrapping the view as a subquery, and then even if the subquery has a client_id IS NOT NULL clause:

ag_spc=> SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count FROM (SELECT -1 AS client_id) foo LEFT OUTER JOIN (SELECT * FROM rent_info WHERE client_id IS NOT NULL) boo USING (client_id);

--------+-------+--------------+---------------+----------------------+-----------------

-1 | | Move-in | 0 | 0 | 0

(1 row)

I'm happy to provide whatever additional information is helpful--just let me know. Thanks.

Ken