Re: Urgent - SQL left join bug?

The correct SQL should be:

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 *and pha.pharmacy_id=22*
where ph.prescription_id=116285 and ph.deleted_at is null and
pha.deleted_at is null order by ph.herb_id;

On Wed, Jun 21, 2017 at 4:26 PM, Kaijiang Chen <chenkaijiang(at)gmail(dot)com>
wrote:

> Hi, Pantelis and Heikki, thank you very much for such a quick response!
>
> I got it. I was so silly.....
>
> On Wed, Jun 21, 2017 at 4:23 PM, Pantelis Theodosiou <ypercube(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 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
>>
>
>