[ https://issues.apache.org/jira/browse/CALCITE-5582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17702197#comment-17702197 ]
Benchao Li commented on CALCITE-5582: ------------------------------------- I would take this as a problem that "Oracle dialect does not support boolean literal" problem. We can handle this in the Oracle Dialect. > Not in or not exist subquery can't work with Oracle db > ------------------------------------------------------ > > Key: CALCITE-5582 > URL: https://issues.apache.org/jira/browse/CALCITE-5582 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.33.0 > Reporter: rebey > Priority: Major > Attachments: image-2023-03-14-19-39-39-320.png > > > a subquery sql with "not in" or "not exists " be change to a left join sql by > calcite,but it generated "TRUE" keyword which is not work in ORACLE.How to > deal with? > *Test case:* > > {code:java} > public static void main(String[] args) throws Exception { > Properties config = new Properties(); > config.put("lex", "JAVA"); > config.put("caseSensitive", "false"); > config.put("schemaType", "CUSTOM"); > > config.setProperty("parserFactory","org.apache.calcite.sql.parser.impl.SqlParserImpl#FACTORY"); > // you can use any table repalce of my t_s_bf_test > String sql1 = "select * from db2.t_s_bf_test s where not exists (select > t.id from db2.t_s_bf_test t where t.id = s.id and t.id = 1)"; > // String sql1 = "select * from db2.t_s_bf_test s where s.id not in > (select t.id from db2.t_s_bf_test t)"; > Connection connection = DriverManager.getConnection("jdbc:calcite:", > config); > CalciteConnection calciteConnection = > connection.unwrap(CalciteConnection.class); > SchemaPlus rootSchema = calciteConnection.getRootSchema(); > // code for oracle datasource2 > HikariDataSource dataSource2 = new HikariDataSource(); > dataSource2.setJdbcUrl("jdbc:oracle:thin:@192.168.75.38:1521:orcl"); > dataSource2.setUsername("ypmc"); > dataSource2.setPassword("ypmc"); > dataSource2.setDriverClassName("oracle.jdbc.OracleDriver"); > rootSchema.add("db2", JdbcSchema.create(rootSchema, "db2", dataSource2, > null, null)); > // run sql query > PreparedStatement statement = calciteConnection.prepareStatement(sql1); > ResultSet resultSet = statement.executeQuery(); > // print result > printRs(resultSet); > statement.close(); > connection.close(); > } {code} > *Error :* > > {code:java} > Exception in thread "main" java.sql.SQLException: exception while executing > query: While executing SQL [SELECT "T_S_BF_TEST"."ID", > "T_S_BF_TEST"."TEMPLATE_CODE", "T_S_BF_TEST"."CREAT_USER1", > "T_S_BF_TEST"."TEST_COLUMN", "T_S_BF_TEST"."RECORD" > FROM "T_S_BF_TEST" > LEFT JOIN (SELECT "ID", TRUE "$f1" > FROM "T_S_BF_TEST" > WHERE "ID" = 1 > GROUP BY "ID") "t1" ON "T_S_BF_TEST"."ID" = "t1"."ID" > WHERE "t1"."$f1" IS NULL] on JDBC sub-schema > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:579) > at > org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137) > at adapter.Test2.main(Test2.java:67) > Caused by: java.lang.RuntimeException: While executing SQL [SELECT > "T_S_BF_TEST"."ID", "T_S_BF_TEST"."TEMPLATE_CODE", > "T_S_BF_TEST"."CREAT_USER1", "T_S_BF_TEST"."TEST_COLUMN", > "T_S_BF_TEST"."RECORD" > FROM "T_S_BF_TEST" > LEFT JOIN (SELECT "ID", TRUE "$f1" > FROM "T_S_BF_TEST" > WHERE "ID" = 1 > GROUP BY "ID") "t1" ON "T_S_BF_TEST"."ID" = "t1"."ID" > WHERE "t1"."$f1" IS NULL] on JDBC sub-schema > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > at > org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) > at > org.apache.calcite.runtime.ResultSetEnumerable.enumeratorBasedOnStatement(ResultSetEnumerable.java:282) > at > org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:257) > at > org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33) > at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:90) > at > org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:186) > at > org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64) > at > org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43) > at > org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:575) > ... 2 more > Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TRUE": 标识符无效 at > oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) > at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) > at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) > at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) > at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) > at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210) > at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30) > at > oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762) > at > oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) > at > oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792) > at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745) > at > oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334) > at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) > at > com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) > at > org.apache.calcite.runtime.ResultSetEnumerable.enumeratorBasedOnStatement(ResultSetEnumerable.java:270) > ... 9 more > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)