Skip to content

Support type cast in union #2125

Closed
Closed

Description

@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)

Activity

  1. Jefffrey commented on Nov 14, 2022

    @Jefffrey
    Contributor

    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?

  2. Ted-Jiang commented on Nov 14, 2022

    @Ted-Jiang
    MemberAuthor

    @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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions