Wrong type when UNION

[duongdominhchau]

If we create two tables like this:

CREATE TABLE table_1(id INTEGER PRIMARY KEY);
CREATE TABLE table_2(id INTEGER PRIMARY KEY);

And query separately:

SELECT id FROM table_1;

SELECT id FROM table_2;

The type of result column id is correctly deducted to be i32 in both queries. However, if I UNION ALL the result of these two queries together, the id type becomes Option<i32>

SELECT id FROM table_1
UNION ALL
SELECT id FROM table_2;

[duongdominhchau]

Here is the output of EXPLAIN (VERBOSE, FORMAT JSON) SELECT id FROM table_1 UNION ALL SELECT id FROM table_2:

                 QUERY PLAN
--------------------------------------------
 [                                         +
   {                                       +
     "Plan": {                             +
       "Node Type": "Append",              +
       "Parallel Aware": false,            +
       "Startup Cost": 0.00,               +
       "Total Cost": 96.50,                +
       "Plan Rows": 5100,                  +
       "Plan Width": 4,                    +
       "Subplans Removed": 0,              +
       "Plans": [                          +
         {                                 +
           "Node Type": "Seq Scan",        +
           "Parent Relationship": "Member",+
           "Parallel Aware": false,        +
           "Relation Name": "table_1",     +
           "Schema": "public",             +
           "Alias": "table_1",             +
           "Startup Cost": 0.00,           +
           "Total Cost": 35.50,            +
           "Plan Rows": 2550,              +
           "Plan Width": 4,                +
           "Output": ["table_1.id"]        +
         },                                +
         {                                 +
           "Node Type": "Seq Scan",        +
           "Parent Relationship": "Member",+
           "Parallel Aware": false,        +
           "Relation Name": "table_2",     +
           "Schema": "public",             +
           "Alias": "table_2",             +
           "Startup Cost": 0.00,           +
           "Total Cost": 35.50,            +
           "Plan Rows": 2550,              +
           "Plan Width": 4,                +
           "Output": ["table_2.id"]        +
         }                                 +
       ]                                   +
     }                                     +
   }                                       +
 ]
(1 row)

[Nukesor]

Hey :)

I'm running into the same issue here. The current workaround is to run two queries and merge the results afterwards, but being able to merge them inside the query itself would be awesome!

[felixwatts]

This also breaks recursive queries where performing multiple queries is not a practical workaround:

CREATE TABLE node (
  id SERIAL PRIMARY KEY,
  parent_id int references node(id)
);

...

        WITH RECURSIVE ancestors(id) AS (
            SELECT 42
            UNION
            SELECT node.parent_id
            FROM ancestors
            LEFT JOIN node ON node.id = ancestors.id
            WHERE node.parent_id IS NOT NULL
          )
        SELECT 
            node.*
        FROM
            ancestors INNER JOIN node ON ancestors.id = node.id;

sqlx::query_as!(Node, <above SQL>)

fails with

mismatched types
expected type `i32`
   found enum `std::option::Option<i32>

[chikko80]

Any updates on this? Seems like it's not even possible to query from a View that leverages a Union. Very problematic if no Union at all is possible with correct typing

[mike-burns]

As a workaround, I've used the type overrides for query!:

SELECT id as "id!" FROM table_1
UNION ALL
SELECT id as "id!" FROM table_2;