Support type cast in union

[Ted-Jiang]

in postgresql union support type cast like below

postgres=# select '1.2' as a union select 2.3 as b union select 3 as b ;
  a
-----
 2.3
   3
 1.2
(3 rows)

postgres=# \gdesc
 Column |  Type
--------+---------
 a      | numeric
(1 row)

Maybe we can open a ticket support this ?

In datafusion

SELECT 1 as NUMBER union SELECT '1' ;
Plan("UNION ALL schemas are expected to be the same")

Originally posted by @Ted-Jiang in #2108 (comment)

[Jefffrey]

Seems to be resolved as of master 96512ac

Running on datafusion-cli:

DataFusion CLI v14.0.0
❯ SELECT 1 as NUMBER union SELECT '1';
+--------+
| number |
+--------+
| 1      |
+--------+
1 row in set. Query took 0.007 seconds.
❯ explain verbose SELECT 1 as NUMBER union SELECT '1';
+------------------------------------------------------------+-----------------------------------------------------------------------------------+
| plan_type                                                  | plan                                                                              |
+------------------------------------------------------------+-----------------------------------------------------------------------------------+
| initial_logical_plan                                       | Distinct:                                                                         |
|                                                            |   Union                                                                           |
|                                                            |     Projection: CAST(Int64(1) AS Utf8) AS number                                  |
|                                                            |       EmptyRelation                                                               |
|                                                            |     Projection: Utf8("1") AS number                                               |
|                                                            |       EmptyRelation                                                               |
| logical_plan after inline_table_scan                       | SAME TEXT AS ABOVE                                                                |
| logical_plan after type_coercion                           | SAME TEXT AS ABOVE                                                                |
| logical_plan after simplify_expressions                    | Distinct:                                                                         |
|                                                            |   Union                                                                           |
|                                                            |     Projection: Utf8("1") AS number                                               |
|                                                            |       EmptyRelation                                                               |
|                                                            |     Projection: Utf8("1") AS number                                               |
|                                                            |       EmptyRelation                                                               |
| logical_plan after unwrap_cast_in_comparison               | SAME TEXT AS ABOVE                                                                |
...
| logical_plan after projection_push_down                    | SAME TEXT AS ABOVE                                                                |
| logical_plan                                               | Distinct:                                                                         |
|                                                            |   Union                                                                           |
|                                                            |     Projection: Utf8("1") AS number                                               |
|                                                            |       EmptyRelation                                                               |
|                                                            |     Projection: Utf8("1") AS number                                               |
|                                                            |       EmptyRelation                                                               |
...
54 rows in set. Query took 0.005 seconds.
❯

Probably fixed by #3407?

[Ted-Jiang]

@Jefffrey Thanks

❯ SELECT 1 as NUMBER union SELECT '1';

+--------+
| number |
+--------+
| 1      |
+--------+
1 row in set. Query took 0.013 seconds.
❯ SELECT 1 as NUMBER union all SELECT '1';
+--------+
| number |
+--------+
| 1      |
| 1      |

i will close this issue