[
https://issues.apache.org/jira/browse/CALCITE-5582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17701249#comment-17701249
]
rebey commented on CALCITE-5582:
--------------------------------
Thanks for you reply in the eveing [~libenchao] . Custom some class may be
solve the problem,but it makes problem be complicated.I hope Calcite core can
support 'not exist subquery with Oracle' by default.Why not use another
commonly constant instead of boolean 'TRUE'?
> 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)
