Thread: strange result with union
Today, I have encounterd a strange result and I want to trace it but I do not know how. I have two views having union as in q3. q1 returns 236 rows, q2 returns 0 rows. I expected q3 to return 236 rows but I get 233 ...
q1: select * FROM view1 -- reurns 236 rows
q2: select * FROM view2 -- returns 0 rows
q3: select * FROM view1 union select * FROM view2 --returns 233 rows
q4: select * FROM view1 union all select * FROM view2 --returns 236 rows
I knwo that the union operator filter out duplicate rows but the intresting part is that view2 returns 0 rows.
If I use UNION all I get the expected result which is 236 rows. I am almost sure that view1 defenesion is dependent of view2 defnesion. for example I can drop view2 without droping view1. and I can drop view1 without dropping view2.
I am running on version "PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2"
Regards
Thus view1 must be returning 3 pairs of duplicate rows which are then being combined into 3 individual rows during the de-duplication pass.
Dave J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of salah jubeh
Sent: Wednesday, March 21, 2012 11:26 AM
To: pgsql
Subject: [GENERAL] strange result with union
Hello,
Today, I have encounterd a strange result and I want to trace it but I do not know how. I have two views having union as in q3. q1 returns 236 rows, q2 returns 0 rows. I expected q3 to return 236 rows but I get 233 ...
q1: select * FROM view1 -- reurns 236 rows
q2: select * FROM view2 -- returns 0 rows
q3: select * FROM view1 union select * FROM view2 --returns 233 rows
q4: select * FROM view1 union all select * FROM view2 --returns 236 rows
I knwo that the union operator filter out duplicate rows but the intresting part is that view2 returns 0 rows.
If I use UNION all I get the expected result which is 236 rows. I am almost sure that view1 defenesion is dependent of view2 defnesion. for example I can drop view2 without droping view1. and I can drop view1 without dropping view2.
I am running on version "PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2"
Regards
From: David Johnston <polobo@yahoo.com>
To: 'salah jubeh' <s_jubeh@yahoo.com>; 'pgsql' <pgsql-general@postgresql.org>
Sent: Wednesday, March 21, 2012 4:31 PM
Subject: Re: [GENERAL] strange result with union
Sent: Wednesday, March 21, 2012 11:26 AM
To: pgsql
Subject: [GENERAL] strange result with union
Today, I have encounterd a strange result and I want to trace it but I do not know how. I have two views having union as in q3. q1 returns 236 rows, q2 returns 0 rows. I expected q3 to return 236 rows but I get 233 ...
q1: select * FROM view1 -- reurns 236 rows
q2: select * FROM view2 -- returns 0 rows
q3: select * FROM view1 union select * FROM view2 --returns 233 rows
q4: select * FROM view1 union all select * FROM view2 --returns 236 rows
I knwo that the union operator filter out duplicate rows but the intresting part is that view2 returns 0 rows.
If I use UNION all I get the expected result which is 236 rows. I am almost sure that view1 defenesion is dependent of view2 defnesion. for example I can drop view2 without droping view1. and I can drop view1 without dropping view2.
I am running on version "PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2"
Regards