[BUGS] Urgent - SQL left join bug? - Mailing list pgsql-bugs
| From | Kaijiang Chen |
|---|---|
| Subject | [BUGS] Urgent - SQL left join bug? |
| Date | |
| Msg-id | CAAkGvS9htFwyOPfy__sKaBRJDZoqthH6vzM_-OtmmThcJT7k2A@mail.gmail.com Whole thread Raw |
| Responses |
Re: [BUGS] Urgent - SQL left join bug?
Re: [BUGS] Urgent - SQL left join bug? |
| List | pgsql-bugs |
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:
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:
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
pgsql-bugs by date: