Re: Getting non_NULL right-side values on a non-matching join? - Mailing list pgsql-general
On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Ken Tanzer <ken.tanzer@gmail.com> wrote:What is the output of executing?:
> 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.
SELECT version();
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
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: