Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6096

Validation of a GROUP BY query fails even though the query is correct

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.3.2
    • None
    • Query Engine
    • None

    Description

      Query validation fails with 

      org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [GEN_view."_view_workflow_data".price] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.

      The failing query:

      select count("text") "text", ('50' * ceiling(("price" / '50'))) "price", count(*) over () "__RESERVED__total_records"
      from "_view_workflow_data"
      where true
      group by ('50' * ceiling(("price" / '50'))) limit 1000000
      offset 0
      

      When I connect directly to the VDB and execute the query from the console, it succeeds.

      Whole stacktrace:

      ERROR# - org.jooq.exception.DataAccessException: SQL [select count("text") "text", (? * ceiling(("price" / ?))) "price", count(*) over () "__RESERVED__total_records" from "_view_workflow_data" where true group by (? * ceiling(("price" / ?))) limit 1000000 offset 0]; TEIID30492 [GEN_view."_view_workflow_data".price] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. (c.m.w.c.d.DataObjectVerticle#?)org.jooq.exception.DataAccessException: SQL [select count("text") "text", (? * ceiling(("price" / ?))) "price", count(*) over () "__RESERVED__total_records" from "_view_workflow_data" where true group by (? * ceiling(("price" / ?))) limit 1000000 offset 0]; TEIID30492 [GEN_view."_view_workflow_data".price] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. at org.jooq_3.16.0-SNAPSHOT.DEFAULT.debug(Unknown Source) at org.jooq.impl.Tools.translate(Tools.java:3047) at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349) at com.mathesio.workflow.core.dataObject.DataObjectService.lambda$select$20(DataObjectService.java:506) at com.mathesio.teiid.Connections.ConnectionManager.lambda$getConnectionJOOQ$0(ConnectionManager.java:187) at org.jooq.impl.DefaultDSLContext.lambda$transaction$5(DefaultDSLContext.java:611) at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$3(DefaultDSLContext.java:549) at org.jooq.impl.Tools$4$1.block(Tools.java:5357) at java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3128) at org.jooq.impl.Tools$4.get(Tools.java:5354) at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:597) at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:521) at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:610) at com.mathesio.teiid.Connections.ConnectionManager.lambda$getConnectionJOOQ$1(ConnectionManager.java:186) at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$new$42(TransactionJPA.java:1813) at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$new$19(TransactionJPA.java:388) at com.mathesio.workflow.core.database.transaction.TransactionJPA.runCore(TransactionJPA.java:993) at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$run$25(TransactionJPA.java:1306) at com.mathesio.workflow.core.database.transaction.TransactionJPA$1.call(TransactionJPA.java:755) at com.mathesio.workflow.core.database.transaction.TransactionJPA$1.call(TransactionJPA.java:738) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at com.mathesio.workflow.core.database.transaction.PriorityFuture.run(PriorityFuture.java:58) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834)Caused by: org.teiid.jdbc.TeiidSQLException: TEIID30492 [GEN_view."_view_workflow_data".price] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:131) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:67) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:783) at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:581) at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:131) at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:36) at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75) at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:281) at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:333) at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:423) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:590) at org.teiid.jdbc.PreparedStatementImpl.execute(PreparedStatementImpl.java:240) at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4245) at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:230) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:335) ... 22 common frames omittedCaused by: org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [GEN_view."_view_workflow_data".price] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:341) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.Request.validateQuery(Request.java:290) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.Request.generatePlan(Request.java:434) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:119) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.Request.processRequest(Request.java:486) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.PreparedStatementRequest.processRequest(PreparedStatementRequest.java:336) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:672) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:351) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.jboss.teiid@12.3.0//org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) 10:41:36.954 
      

       VDB definition https://pastebin.com/0FDtuaaw

      Attachments

        Activity

          People

            Unassigned Unassigned
            basmastr Tomáš Tomek (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: