SQL parser detect alias as table column

CrateDB SQL
1

Hi guys,
Have some strange error when I tried to execute the next SELECT query:

SELECT count(a.id) count_yes, a.stepid stepid_yes, a.stepid
                  FROM activity a
                  WHERE
                    a.type = 4
                    AND
                    a.stepid IN ('1bb64fbb-8d46-4eb5-ab6e-7c0aab679ea7')
                  GROUP BY a.stepid

Error:

SQLActionException[SQLParseException: Couldn't find Field{QueriedTable{DocTableRelation{doc.activity}}.stepid_yes, pointer=Reference{ident=<RefIdent: doc.activity->stepid>, granularity=DOC, position=6, default expression=null, type=text, index type=NOT_ANALYZED, nullable=true, columnstore enabled=false}} in SourceSymbols{inputs={Field{QueriedTable{DocTableRelation{doc.activity}}.count_yes, pointer=count(Ref{doc.activity.id, text})}=IC{0, bigint}, Field{QueriedTable{DocTableRelation{doc.activity}}.stepid, pointer=Reference{ident=<RefIdent: doc.activity->stepid>, granularity=DOC, position=6, default expression=null, type=text, index type=NOT_ANALYZED, nullable=true, columnstore enabled=false}}=IC{2, text}}, nonDeterministicFunctions={}}]

Looks like parser detects alias as some table column.

Please note that this works differently on different machines. This query is executed well on the server with Linux but on my local computer with Windows I get this error.

CrateDB version: 4.0.2 -SNAPSHOT-27add9a

My docker-compose config:

    crate:
        image: crate
        container_name: ezf_crate
        ports:
          - "4200:4200"
          - "5432:5432"
        expose:
          - 5432
        volumes:
          - ./cratedb/runtime/data:/data    
        environment:
          CRATE_HEAP_SIZE: 1g
        command: crate -Cnode.name=crate-1 -Cnode.store.allow_mmapfs=false
2

This is indeed a bug, could you create a Github issue please?
Thanks for reporting already.

3

@gorhome
We’ve fixed this issue by https://github.com/crate/crate/pull/8985. It will be part of the next 4.0 hotfix release, also the next nightly build will contain it.
Btw. this bug is not existent at the 3.3 releases and inside 4.x will not happen inside simple queries (no grouping, joins, subselects, etc.)
Thanks again for reporting! feel free to directly create a github issue next time you’ll encounter a possible bug :slight_smile: