search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.43k stars 3k forks source link

PostgreSQL BIT should be mapped to a VARBINARY in Trino #11384

Open hashhar opened 2 years ago

hashhar commented 2 years ago

PostgreSQL BIT is a bitstring and can be > 1 bit.

Today we map all single-bit values to a boolean but don't properly handle other values.

This can be demonstrated by following test:

@Test
public void testBit()
{
    SqlDataTypeTest.create()
            .addRoundTrip("bit", "NULL", VARBINARY, "CAST(NULL AS BOOLEAN)")
            .addRoundTrip("bit(2)", "B'10'", VARBINARY, "CAST(NULL AS BOOLEAN)")
            .execute(getQueryRunner(), postgresCreateAndInsert("tpch.test_bit"));
}

This fails with:

2022-03-09T01:12:11.906-0600 SEVERE Error processing Split 20220309_071211_00000_6bnz8.1.0.0-0 io.trino.plugin.jdbc.JdbcSplit@26f014ef (start = 5.61837068556718E8, wall = 51 ms, cpu = 0 ms, wait = 0 ms, calls = 1): JDBC_ERROR: Cannot cast to boolean: "10"
io.trino.spi.TrinoException: Cannot cast to boolean: "10"
    at io.trino.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:299)
    at io.trino.plugin.jdbc.JdbcRecordCursor.getBoolean(JdbcRecordCursor.java:186)
    at io.trino.spi.connector.RecordPageSource.getNextPage(RecordPageSource.java:103)
    at io.trino.operator.TableScanOperator.getOutput(TableScanOperator.java:311)
    at io.trino.operator.Driver.processInternal(Driver.java:388)
    at io.trino.operator.Driver.lambda$processFor$9(Driver.java:292)
    at io.trino.operator.Driver.tryWithLock(Driver.java:693)
    at io.trino.operator.Driver.processFor(Driver.java:285)
    at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1092)
    at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:488)
    at io.trino.$gen.Trino_testversion____20220309_071205_2.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.postgresql.util.PSQLException: Cannot cast to boolean: "10"
    at org.postgresql.jdbc.BooleanTypeUtil.cannotCoerceException(BooleanTypeUtil.java:99)
    at org.postgresql.jdbc.BooleanTypeUtil.fromString(BooleanTypeUtil.java:67)
    at org.postgresql.jdbc.BooleanTypeUtil.castToBoolean(BooleanTypeUtil.java:43)
    at org.postgresql.jdbc.PgResultSet.getBoolean(PgResultSet.java:2215)
    at io.trino.plugin.jdbc.JdbcRecordCursor.getBoolean(JdbcRecordCursor.java:183)
    ... 13 more
hashhar commented 2 years ago

Also existing type mapping to boolean also causes issues when predicates are pushed down:

@Test
public void testBoolean()
{
    SqlDataTypeTest.create()
            .addRoundTrip("bit", "B'1'", BOOLEAN, "TRUE")
            .addRoundTrip("bit", "B'0'", BOOLEAN, "FALSE")
            .addRoundTrip("bit", "NULL", BOOLEAN, "CAST(NULL AS BOOLEAN)")
            .execute(getQueryRunner(), postgresCreateAndInsert("tpch.test_bit"))
            .execute(getQueryRunner(), postgresCreateTrinoInsert("tpch.test_bit"));
}

This fails with:

2022-03-09T01:14:42.651-0600 SEVERE Error processing Split 20220309_071442_00016_cm5c2.1.0.0-0 io.trino.plugin.jdbc.JdbcSplit@74d58ef4 (start = 5.61987759155103E8, wall = 105 ms, cpu = 0 ms, wait = 0 ms, calls = 1): JDBC_ERROR: ERROR: operator does not exist: bit = boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 79
io.trino.spi.TrinoException: ERROR: operator does not exist: bit = boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 79
    at io.trino.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:299)
    at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:173)
    at io.trino.$gen.CursorProcessor_20220309_071442_6.process(Unknown Source)
    at io.trino.operator.ScanFilterAndProjectOperator$RecordCursorToPages.process(ScanFilterAndProjectOperator.java:338)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:240)
    at io.trino.operator.WorkProcessorUtils$YieldingProcess.process(WorkProcessorUtils.java:182)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:325)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils.lambda$flatten$7(WorkProcessorUtils.java:296)
    at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:338)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:325)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:240)
    at io.trino.operator.WorkProcessorUtils.lambda$processStateMonitor$3(WorkProcessorUtils.java:219)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:240)
    at io.trino.operator.WorkProcessorUtils.lambda$finishWhen$4(WorkProcessorUtils.java:234)
    at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:391)
    at io.trino.operator.WorkProcessorSourceOperatorAdapter.getOutput(WorkProcessorSourceOperatorAdapter.java:150)
    at io.trino.operator.Driver.processInternal(Driver.java:388)
    at io.trino.operator.Driver.lambda$processFor$9(Driver.java:292)
    at io.trino.operator.Driver.tryWithLock(Driver.java:693)
    at io.trino.operator.Driver.processFor(Driver.java:285)
    at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1092)
    at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:488)
    at io.trino.$gen.Trino_testversion____20220309_071434_3.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLException: ERROR: operator does not exist: bit = boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 79
    at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:156)
    ... 31 more
Caused by: java.util.concurrent.ExecutionException: org.postgresql.util.PSQLException: ERROR: operator does not exist: bit = boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 79
    at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
    at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
    at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:151)
    ... 31 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bit = boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 79
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at io.trino.plugin.jdbc.JdbcRecordCursor.lambda$advanceNextPosition$1(JdbcRecordCursor.java:146)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    ... 3 more
ebyhr commented 2 years ago

I believe other JDBC connectors also have the same issue. e.g. MemSQL connector