snappydata
snappydata copied to clipboard
TIBCOSoftware
[Bug for OSS V1.1.0 and Enterprise] Syntax error: Invalid input 'A', expected UNION, INTERSECT, EXCEPT, MINUS or ')'
When executing 'create view or replace', an error is reported if two or more temporary views are used via 'with as'. -----------------------------SQL Script-------------------------
CREATE TABLE XY.COUNTRY (Id INTEGER,countryname STRING,countrycode STRING);
CREATE OR REPLACE VIEW XY.DV_COUNTRY AS WITH DV_COUNTRY_TEMP AS (SELECT * FROM XY.COUNTRY),DV_COUNTRY_TEMP_1 AS (SELECT * FROM XY.COUNTRY) SELECT T1.countryname FROM DV_COUNTRY_TEMP AS T1 LEFT JOIN DV_COUNTRY_TEMP_1 AS T2 ON T1.countrycode=T2.countrycode;
-----------------------------Error Info-------------------------
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [20000] [42X01]: (SQLState=42X01 Severity=20000) (Server=sj06.sj.net/10.14.13.236[1527] Thread=ThriftProcessor-4) Syntax error: Invalid input 'A', expected UNION, INTERSECT, EXCEPT, MINUS or ')' (line 1, column 281):
SELECT gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;.
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:467)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:407)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:405)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:849)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2778)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:98)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:96)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: (SQLState=42X01 Severity=20000) (Server=sj06.sj.net/10.14.13.236[1527] Thread=ThriftProcessor-4) Syntax error: Invalid input 'A', expected UNION, INTERSECT, EXCEPT, MINUS or ')' (line 1, column 281):
SELECT gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;.
at io.snappydata.thrift.SnappyDataService$execute_result$execute_resultStandardScheme.read(SnappyDataService.java:7033)
at io.snappydata.thrift.SnappyDataService$execute_result$execute_resultStandardScheme.read(SnappyDataService.java:7010)
at io.snappydata.thrift.SnappyDataService$execute_result.read(SnappyDataService.java:6949)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:86)
at io.snappydata.thrift.SnappyDataService$Client.recv_execute(SnappyDataService.java:256)
at io.snappydata.thrift.SnappyDataService$Client.execute(SnappyDataService.java:239)
at io.snappydata.thrift.internal.ClientService.execute(ClientService.java:932)
at io.snappydata.thrift.internal.ClientStatement.execute(ClientStatement.java:739)
at io.snappydata.thrift.internal.ClientStatement.execute(ClientStatement.java:390)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 11 more
Caused by: java.rmi.ServerException: Server STACK: java.sql.SQLSyntaxErrorException(42X01): Syntax error: Invalid input 'A', expected UNION, INTERSECT, EXCEPT, MINUS or ')' (line 1, column 281):
SELECT gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;.
at com.pivotal.gemfirexd.internal.iapi.error.StandardException.newException(StandardException.java:214)
at com.pivotal.gemfirexd.internal.engine.Misc.processFunctionException(Misc.java:831)
at com.pivotal.gemfirexd.internal.engine.Misc.processFunctionException(Misc.java:812)
at com.pivotal.gemfirexd.internal.engine.sql.execute.SnappySelectResultSet.setup(SnappySelectResultSet.java:294)
at com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage.executeFunction(GfxdFunctionMessage.java:317)
at com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage.executeFunction(GfxdFunctionMessage.java:259)
at com.pivotal.gemfirexd.internal.engine.sql.execute.SnappyActivation.executeOnLeadNode(SnappyActivation.java:337)
at com.pivotal.gemfirexd.internal.engine.sql.execute.SnappyActivation.executeWithResultSet(SnappyActivation.java:201)
at com.pivotal.gemfirexd.internal.engine.sql.execute.SnappyActivation.execute(SnappyActivation.java:157)
at com.pivotal.gemfirexd.internal.impl.sql.GenericActivationHolder.execute(GenericActivationHolder.java:462)
at com.pivotal.gemfirexd.internal.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:586)
at com.pivotal.gemfirexd.internal.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:2179)
at com.pivotal.gemfirexd.internal.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:1289)
at com.pivotal.gemfirexd.internal.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:1006)
at com.pivotal.gemfirexd.internal.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:972)
at io.snappydata.thrift.server.SnappyDataServiceImpl.execute(SnappyDataServiceImpl.java:1710)
at io.snappydata.thrift.SnappyDataService$Processor$execute.getResult(SnappyDataService.java:1511)
at io.snappydata.thrift.SnappyDataService$Processor$execute.getResult(SnappyDataService.java:1495)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at io.snappydata.thrift.server.SnappyDataServiceImpl$Processor.process(SnappyDataServiceImpl.java:201)
at io.snappydata.thrift.server.SnappyThriftServerThreadPool$WorkerProcess.run(SnappyThriftServerThreadPool.java:270)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at io.snappydata.thrift.server.SnappyThriftServer$1.lambda$newThread$0(SnappyThriftServer.java:143)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.pivotal.gemfirexd.internal.engine.jdbc.GemFireXDRuntimeException: myID: 10.14.13.236(12379)gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;.
at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryStreamingResultCollector$Itr.moveNext(GfxdQueryStreamingResultCollector.java:451)
at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryStreamingResultCollector$Itr.gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;.
at Remote Member '10.14.13.232(13684)gen_attr_0 AS countryname FROM (SELECT gen_attr_0 FROM (SELECT gen_attr_0 FROM ((SELECT gen_attr_3, gen_attr_0, gen_attr_1 FROM (SELECT Id AS gen_attr_3, countryname AS gen_attr_0, countrycode AS gen_attr_1 FROM XY.COUNTRY) AS gen_subquery_0) AS DV_COUNTRY_TEMP) AS T1 LEFT OUTER JOIN ((SELECT gen_attr_4, gen_attr_5, gen_attr_2 FROM (SELECT Id AS gen_attr_4, countryname AS gen_attr_5, countrycode AS gen_attr_2 FROM XY.COUNTRY) AS gen_subquery_1) AS DV_COUNTRY_TEMP_1) AS T2 ON (gen_attr_1 = gen_attr_2)) AS TEMP) AS TEMP
^;
at Remote Member '10.14.13.232(13684)
at io.snappydata.thrift.common.ThriftExceptionUtil.newSQLException(ThriftExceptionUtil.java:109)
at io.snappydata.thrift.internal.ClientStatement.execute(ClientStatement.java:755)
... 14 more
