Skip to content

[Datafusion] Datafusion queries involving a column name that begins with a number produces unexpected results #108

Closed

Description

@alamb

Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-10329

This bug can be worked around by wrapping column names in quotes.

Example:

{{let query = "SELECT 16_20mph, 21_25mph FROM foo;"}}

{{let logical_plan = ctx.create_logical_plan(query)?;}}

{{logical_plan.schema().fields() now has fields: [_20mph, _25mph]}}

The resulting table produced by this query looks like:
||{{_20mph}}||{{_25mph}}||
|16|21|
|16|21|

Every row is identical, where the column value is equal to the initial number that appears in the column name.

Activity

  1. alamb commented on Apr 26, 2021

    @alamb
    ContributorAuthor

    Comment from Ben Sully(sd2k) @ 2020-11-08T11:46:18.008+0000:

    This behaviour seems consistent with Postgres at least:
    
    
    {{❯ psql}}
    {{psql (12.4)}}
    {{Type "help" for help.}}
    
    {{ben=# create table foo ("16_20mph" integer, "21_25mph" integer);}}
    {{CREATE TABLE}}
    {{ben=# insert into foo values (1, 2), (3, 4);}}
    {{INSERT 0 2}}
    {{ben=# SELECT 16_20mph, 21_25mph FROM foo;}}
    {{ _20mph | _25mph }}
    {{--------+--------}}
    {{ 16 | 21}}
    {{ 16 | 21}}
    {{(2 rows)}}
    
    {{ben=# SELECT "16_20mph", "21_25mph" FROM foo;}}
    {{ 16_20mph | 21_25mph }}
    {{----------+----------}}
    {{ 1 | 2}}
    {{ 3 | 4}}
    {{(2 rows)}}
  2. Jefffrey commented on Jan 7, 2023

    @Jefffrey
    Contributor

    I believe this can be closed as its expected behaviour stemming from sqlparser, recent relevant discussion: apache/datafusion-sqlparser-rs#768 (comment)

  3. alamb commented on Jan 8, 2023

    @alamb
    ContributorAuthor

    I agree -- thanks @Jefffrey -- I agree this is "expected behavior" in the sense that it is what postgres does even though it is not very obvious

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