[
https://issues.apache.org/jira/browse/CALCITE-5582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17701230#comment-17701230
]
Benchao Li commented on CALCITE-5582:
-------------------------------------
[~rebey] The reason is that Oracle Dialect does not support {{BOOLEAN}} type.
To improve this, I'd suggest to change {{RelToSqlConverter}} and {{SqlDialect}}.
> 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)
