Thread

1. BUG #17671: Query returns unexpected result

   PG Bug reporting form <noreply@postgresql.org> — 2022-10-31T05:15:44Z

   The following bug has been logged on the website:
   
   Bug reference:      17671
   Logged by:          Nikola Mirkov
   Email address:      niko@aku.mx
   PostgreSQL version: 12.12
   Operating system:   Ubuntu 12.12-0ubuntu0.20.
   Description:        
   
   Hey guys,
   When I run the following query:
   SELECT foo, bar
   FROM table1
   WHERE fk_id = 637
   AND utc > '2022-10-16'
   UNION
   SELECT foo, bar
   FROM table2
   WHERE fk_id = 637
   AND utc > '2022-10-16'
   
   I'm getting only one row per table. If I add the column "utc" to the list in
   SELECT, I get the expected result.
   
   Why is this happening?
   
   Thank you so much!
   Niko
   
   

2. Re: BUG #17671: Query returns unexpected result

   Kieran McCusker <kieran.mccusker@gmail.com> — 2022-10-31T10:07:02Z

   It's not a bug. Union removes duplicates. Use union all.
   
   Kieran
   
   On Mon, 31 Oct 2022, 10:03 PG Bug reporting form, <noreply@postgresql.org>
   wrote:
   
   > The following bug has been logged on the website:
   >
   > Bug reference:      17671
   > Logged by:          Nikola Mirkov
   > Email address:      niko@aku.mx
   > PostgreSQL version: 12.12
   > Operating system:   Ubuntu 12.12-0ubuntu0.20.
   > Description:
   >
   > Hey guys,
   > When I run the following query:
   > SELECT foo, bar
   > FROM table1
   > WHERE fk_id = 637
   > AND utc > '2022-10-16'
   > UNION
   > SELECT foo, bar
   > FROM table2
   > WHERE fk_id = 637
   > AND utc > '2022-10-16'
   >
   > I'm getting only one row per table. If I add the column "utc" to the list
   > in
   > SELECT, I get the expected result.
   >
   > Why is this happening?
   >
   > Thank you so much!
   > Niko
   >
   >
   

3. Re: BUG #17671: Query returns unexpected result

   Japin Li <japinli@hotmail.com> — 2022-10-31T10:40:56Z

   On Mon, 31 Oct 2022 at 13:15, PG Bug reporting form <noreply@postgresql.org> wrote:
   > The following bug has been logged on the website:
   >
   > Bug reference:      17671
   > Logged by:          Nikola Mirkov
   > Email address:      niko@aku.mx
   > PostgreSQL version: 12.12
   > Operating system:   Ubuntu 12.12-0ubuntu0.20.
   > Description:        
   >
   > Hey guys,
   > When I run the following query:
   > SELECT foo, bar
   > FROM table1
   > WHERE fk_id = 637
   > AND utc > '2022-10-16'
   > UNION
   > SELECT foo, bar
   > FROM table2
   > WHERE fk_id = 637
   > AND utc > '2022-10-16'
   >
   > I'm getting only one row per table. If I add the column "utc" to the list in
   > SELECT, I get the expected result.
   >
   > Why is this happening?
   >
   
   The UNION eliminates duplicate rows from its result, in the same way as
   DISTINCT [1].
   
   [1] https://www.postgresql.org/docs/current/queries-union.html
   
   
   -- 
   Regrads,
   Japin Li.
   ChengDu WenWu Information Technology Co.,Ltd.