Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> 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);
 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/
ken.tanzer@xxxxxxxxxxxxxxxxxxx
(253) 245-3801

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux