Re: Urgent - SQL left join bug?

On Wed, Jun 21, 2017 at 9:06 AM, Kaijiang Chen <chenkaijiang(at)gmail(dot)com>
wrote:

> Hi, I'm running PostgreSQL 9.4.10 on CentOS 6.5. It looks like that I
> found a bug with left join. It is very URGENT since it is running in the
> production servers.
>
> ### Conditions: ###
>
> I have 2 tables:
>
> TABLE 1: (2171209 records)
> \d prescription_herbs
> Table
> "public.prescription_herbs"
> Column | Type |
> Modifiers
> ---------------------+--------------------------------+-----
> ------------------------------------------------------------
> id | integer | not null default
> nextval('prescription_herbs_id_seq'::regclass)
> prescription_id | integer | not null
> herb_id | integer | not null
> weight | integer | not null
> created_at | timestamp(0) without time zone | not null
> updated_at | timestamp(0) without time zone | not null
> deleted_at | timestamp(0) without time zone |
> price | numeric(10,5) |
> special_manufacture | character varying(255) |
> cost | numeric(10,5) |
> pharmacy_id | integer |
> Indexes:
> "prescription_herbs_pkey" PRIMARY KEY, btree (id)
> "prescription_herbs_hid" btree (herb_id)
> "prescription_herbs_prid" btree (prescription_id)
>
> TABLE 2: (4406 records)
>
> \d pharmacy_herbs
> Table "public.pharmacy_herbs"
> Column | Type |
> Modifiers
> -------------+--------------------------------+-------------
> ------------------------------------------------
> id | integer | not null default
> nextval('pharmacy_herbs_id_seq'::regclass)
> pharmacy_id | integer |
> herb_id | integer |
> cost | numeric(10,5) |
> price | numeric(10,5) |
> no | character varying(255) |
> deleted_at | timestamp(0) without time zone |
> created_at | timestamp(0) without time zone | not null
> updated_at | timestamp(0) without time zone | not null
> name | character varying(255) |
> Indexes:
> "pharmacy_herbs_pkey" PRIMARY KEY, btree (id)
> "pharmacy_herbs_herb_id" btree (herb_id)
>
> ### BUG: ###
>
> I ran a SQL:
> select pha.id,ph.herb_id,pha.name,ph.weight
> from prescription_herbs as ph *left join* pharmacy_herbs as pha on
> ph.herb_id=pha.herb_id
> where ph.prescription_id=116285 and ph.deleted_at is null and
> pha.deleted_at is null and pha.pharmacy_id=22 order by ph.herb_id;
>
> Expected:
>
> It should have 10 rows because the SQL "select * from prescription_herbs
> as ph where ph.prescription_id=116285 and ph.deleted_at is null" returned
> 10 rows and I'm using *LEFT JOIN *in the above SQL.
>
> Actual Result:
>
> It returned only 9 rows and the result is the same as JOIN (not LEFT JOIN).
>
> ### More info: ###
>
> I explain the SQL:
> explain select pha.id,ph.herb_id,pha.name,ph.weight
> from prescription_herbs as ph left join pharmacy_herbs as pha on
> ph.herb_id=pha.herb_id
> where ph.prescription_id=116285 and ph.deleted_at is null and
> pha.deleted_at is null and pha.pharmacy_id=22 order by ph.herb_id;
>
> I got:
>
> Sort (cost=131.73..131.76 rows=10 width=24)
> Sort Key: ph.herb_id
> -> * Hash Join* (cost=122.02..131.56 rows=10 width=24)
> Hash Cond: (ph.herb_id = pha.herb_id)
> -> Index Scan using prescription_herbs_prid on
> prescription_herbs ph (cost=0.43..9.68 rows=23 width=8)
> Index Cond: (prescription_id = 116285)
> Filter: (deleted_at IS NULL)
> -> Hash (cost=113.08..113.08 rows=681 width=20)
> -> Seq Scan on pharmacy_herbs pha (cost=0.00..113.08
> rows=681 width=20)
> Filter: ((deleted_at IS NULL) AND (pharmacy_id = 22))
>
> I think the above* "Hash Join" SHOULD BE "Hash Left Join"*, right?
>
> I tried to explain another SQL:
> explain select * from doctors d left join prescriptions p on d.id
> =p.doctor_id;
>
> I got:
> Hash Right Join (cost=2159.33..31453.58 rows=130330 width=2936)
> Hash Cond: (p.doctor_id = d.id)
> -> Seq Scan on prescriptions p (cost=0.00..9273.30 rows=130330
> width=495)
> -> Hash (cost=576.37..576.37 rows=5037 width=2441)
> -> Seq Scan on doctors d (cost=0.00..576.37 rows=5037
> width=2441)
>
> The "Hash Right Join" is the correct node.
>
> Any help is very appreciated! WAITING...
>
> Thanks,
> Kaijiang
>
>
This is not a bug. The conditions of the "right" table pha - basically
(pha.pharmacy_id=22) and secondary ((pha.deleted_at is null) )- that you
have put in the WHERE clause make the query act as if it was an INNER join.

Move them to the ON and you'll get your 10 rows.

select ...
from prescription_herbs as ph *left join* pharmacy_herbs as pha
on ph.herb_id=pha.herb_id and pha.deleted_at is null and
pha.pharmacy_id=22
where ph.prescription_id=116285 and ph.deleted_at is null
order by ...

The (pha.deleted_at is null) part may give different results depending on
where it is placed (ON vs WHERE) but my guess - since you want all the 10
rows of the left table - is that it should be in ON, too

Pantelis