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

On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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(at)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);
foo_id | ri_id | move_in_type | annual_income | monthly_income_total |
dependent_count
--------+-------+--------------+---------------+----------------------+-----------------
-1 | | Move-in | 0 | 0 |
0
(1 row)

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

Ken

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