search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.64k stars 3.26k forks source link

jdbc catalog postresql bit(1) types with bool type ,but query error #23367

Open 0xqq opened 1 year ago

0xqq commented 1 year ago

Search before asking

Version

doris2.0.0

What's Wrong?

1 、show create table cat_postgresql1.doris_test.test1 CREATE TABLE test1 ( k1 boolean NULL, k2 char(100) NULL, k3 text NULL, k4 datev2 NULL, k5 float NULL, k6 smallint(6) NULL, k7 int(11) NULL, k8 bigint(20) NULL, k9 datetimev2(6) NULL, k10 decimalv3(10, 3) NULL ) ENGINE=JDBC_EXTERNAL_TABLE;

2、select select k1, k2 from cat_postgresql1.doris_test.test1 where k1 = true
or select k1, k2 from cat_postgresql1.doris_test.test1 where k1 = 1

3、 error

CAUSED BY: PSQLException: ERROR: operator does not exist: bit = boolean Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 57 at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:917) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3794) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5063) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: errCode = 2, detailMessage = (192.168.96.218)[CANCELLED][INTERNAL_ERROR]UdfRuntimeException: JDBC executor sql has error: CAUSED BY: PSQLException: ERROR: operator does not exist: bit = boolean Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 57 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131) at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 12 more

What You Expected?

select k1, k2 from cat_postgresql1.doris_test.test1 where k1 = 1

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

Code of Conduct

zy-kkk commented 1 year ago

you can try select k1, k2 from cat_postgresql1.doris_test.test1 where cast (k1 as int) = 1

0xqq commented 1 year ago

select k1, k2 from cat_postgresql1.doris_test.test1 where cast (k1 as int) = 1. also error ][INTERNAL_ERROR]UdfRuntimeException: JDBC executor sql has error: CAUSED BY: PSQLException: ERROR: operator does not exist: bit = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 57