Skip to content

between with NULL literals does not work: can't be evaluated because there isn't a common type to coerce the types to #1193

Closed

Description

@alamb

Reproducer:

> select 5 between null and 10 as foo from foo;
Plan("'Int64 >= Utf8' can't be evaluated because there isn't a common type to coerce the types to")
> select 5 between 5 and null as foo from foo;
Plan("'Int64 <= Utf8' can't be evaluated because there isn't a common type to coerce the types to")
> select null between 5 and null as foo;
Plan("'Utf8 >= Int64' can't be evaluated because there isn't a common type to coerce the types to")

The correct output is null

I think the solution will likely be to teach Expr::Between about type coercion (as is done in BinaryExpr and function calls) once coercion knows about the null type (and that it can be cast to any other type). See #1184 for more details

Activity

  1. changed the title [-]`between` with NULL literals does not work[/-] [+]`between` with NULL literals does not work: can't be evaluated because there isn't a common type to coerce the types to[/+] on Oct 28, 2021
  2. 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 null between 5 and null as foo;
    +-----+
    | foo |
    +-----+
    |     |
    +-----+
    1 row in set. Query took 0.003 seconds.
    ❯ explain verbose select null between 5 and null as foo;
    +------------------------------------------------------------+---------------------------------------------------------------------------------+
    | plan_type                                                  | plan                                                                            |
    +------------------------------------------------------------+---------------------------------------------------------------------------------+
    | initial_logical_plan                                       | Projection: NULL BETWEEN Int64(5) AND NULL AS foo                               |
    |                                                            |   EmptyRelation                                                                 |
    | logical_plan after inline_table_scan                       | SAME TEXT AS ABOVE                                                              |
    | logical_plan after type_coercion                           | Projection: CAST(NULL AS Int64) BETWEEN Int64(5) AND CAST(NULL AS Int64) AS foo |
    |                                                            |   EmptyRelation                                                                 |
    | logical_plan after simplify_expressions                    | Projection: Boolean(NULL) AS foo                                                |
    |                                                            |   EmptyRelation                                                                 |
    | logical_plan after unwrap_cast_in_comparison               | SAME TEXT AS ABOVE                                                              |
    | logical_plan after decorrelate_where_exists                | SAME TEXT AS ABOVE                                                              |
    ...
    | logical_plan after common_sub_expression_eliminate         | SAME TEXT AS ABOVE                                                              |
    | logical_plan after projection_push_down                    | SAME TEXT AS ABOVE                                                              |
    | logical_plan                                               | Projection: Boolean(NULL) AS foo                                                |
    |                                                            |   EmptyRelation                                                                 |
    | initial_physical_plan                                      | ProjectionExec: expr=[NULL as foo]                                              |
    |                                                            |   EmptyExec: produce_one_row=true                                               |
    |                                                            |                                                                                 |
    | physical_plan after aggregate_statistics                   | SAME TEXT AS ABOVE                                                              |
    | physical_plan after hash_build_probe_order                 | SAME TEXT AS ABOVE                                                              |
    | physical_plan after BasicEnforcement                       | SAME TEXT AS ABOVE                                                              |
    | physical_plan after coalesce_batches                       | SAME TEXT AS ABOVE                                                              |
    | physical_plan after repartition                            | SAME TEXT AS ABOVE                                                              |
    | physical_plan after BasicEnforcement                       | SAME TEXT AS ABOVE                                                              |
    | physical_plan                                              | ProjectionExec: expr=[NULL as foo]                                              |
    |                                                            |   EmptyExec: produce_one_row=true                                               |
    |                                                            |                                                                                 |
    +------------------------------------------------------------+---------------------------------------------------------------------------------+
    54 rows in set. Query took 0.003 seconds.
    ❯

    Edit: probably fixed by #3407?

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

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions