strange result with union

From: salah jubeh <s_jubeh(at)yahoo(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: strange result with union
Date: 2012-03-21 15:25:30
Message-ID: 1332343530.85799.YahooMailNeo@web161502.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-03-21 15:31:58 Re: strange result with union
Previous Message Steve Crawford 2012-03-21 15:01:07 Re: huge price database question..