"Select null" has different outputs from different data sources

1

Hi.
I found that “select null” has different outputs from different data sources.

First, test to directly upload the parquet file. I name it “orders” here, and then execute the following SQL:

SELECT null FROM orders group by null

The result is correct:

image
image1505×769 20.1 KB

But if the data is in other databases, such as mysql, I test mysql here. I saved the orders table in MySQL as a view of dremio, named orders2. Execute the following SQL:

SELECT null FROM orders2 group by null

I will get the error:

RuntimeException: cast function for type NULL is not defined.

    SYSTEM ERROR: RuntimeException: cast function for type NULL is not defined

SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
Fragment 0:0

[Error Id: c2283bad-1227-45b3-9419-3ea48c374dc2 on localhost:0]

  (java.lang.RuntimeException) cast function for type NULL is not defined
    com.dremio.common.expression.fn.CastFunctions.getCastFunc():60
    com.dremio.exec.expr.ExpressionMaterializationVisitor.visitCastExpression():650
    com.dremio.exec.expr.ExpressionMaterializationVisitor.visitCastExpression():84
    com.dremio.common.expression.CastExpression.accept():40
    com.dremio.exec.expr.ExpressionTreeMaterializer.materialize():112
    com.dremio.exec.expr.ExpressionTreeMaterializer.materialize():143
    com.dremio.exec.expr.ClassProducerImpl.materializeAndAllowComplex():112
    com.dremio.sabot.op.project.ProjectOperator.createSplitterWithExpressions():419
    com.dremio.sabot.op.project.ProjectOperator.createSplitterWithExpressions():395
    com.dremio.exec.store.CoercionReader.setupProjector():157
    com.dremio.exec.store.CoercionReader.newSchema():136
    com.dremio.exec.store.CoercionReader.setup():119
    com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():311
    com.dremio.sabot.op.scan.ScanOperator.setupReader():302
    com.dremio.sabot.op.scan.ScanOperator.setup():266
    com.dremio.sabot.driver.SmartOp$SmartProducer.setup():563
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
    com.dremio.sabot.driver.SmartOp$SmartProducer.accept():533
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.Pipeline.setup():69
    com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():431
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():283
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$1600():95
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():773
    com.dremio.sabot.task.AsyncTaskWrapper.run():120
    com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():243
    com.dremio.sabot.task.slicing.SlicingThread.run():171

image
image1525×799 23.5 KB

This is the test data I used:
orders.zip (403.7 KB)

image

2

@bigfacewo In case of MySQL, Dremi owill push down the SQL to Mysql, what is push down look like? Can you send us the profile? or open raw profile, planning, final physical transformation-see the JDBC push down

3

@balaji.ramaswamy Here is the profile:
892d33da-9dfa-4618-9981-def81e2d74b3.zip (11.9 KB)

4

@bigfacewo null does not have a data type and hence it is failing, can you please try the below SQL

select cast(null as varchar) as null_string from orders2
5

@balaji.ramaswamy

image
image1511×776 27 KB