11 Replies Latest reply on Jan 15, 2016 10:40 AM by kchen007

    count(*) issue

    kchen007 Apprentice

      we had 2 connectors that extended from oracle/mssql connector respectively, our current version is Teiid 8.7. We are planning to move to 8.12, while doing that, the following query

        select count(*) from table, got translated to select IFNULL(anon_grp2.agg0, 1) FROM table.

      and the oracle/mssql driver will throw exception about anon_grp2.agg0 not an valid identifier.

       

      what change shall I make to fix this?

       

      thanks

      Kevin

        • 1. Re: count(*) issue
          Steven Hawkins Master

          Which 8.12 version and could you provide the the query plan if possible?

          • 2. Re: count(*) issue
            kchen007 Apprentice

            8.12.3.  here is the debug output from teiid:

            thanks,

            Kevin

             

            2016-01-13 14:56:27,859 INFO  [org.teiid.COMMAND_LOG] (New I/O worker #1)       START USER COMMAND: startTime=2016-01-13 14:56:27.859   requestID=eW6VX+tqIyfT.0        txID=null       sessionID=eW6VX+tqIyfT  applicationName=JDBC    principal=edm@dsds-security-domain      vdbName=EDM_Oracle      vdbVersion=1    sql=select count(*) from CD_WELL
            2016-01-13 14:56:27,859 INFO  [org.teiid.COMMAND_LOG] (New I/O worker #1)       START USER COMMAND: startTime=2016-01-13 14:56:27.859   requestID=eW6VX+tqIyfT.0        txID=null       sessionID=eW6VX+tqIyfT  applicationName=JDBC    principal=edm@dsds-security-domain      vdbName=EDM_Oracle      vdbVersion=1    sql=select count(*) from CD_WELL
            2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread eW6VX+tqIyfT.0 with state NEW
            2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) Request Thread eW6VX+tqIyfT.0 with state NEW
            2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 Command has no cache hint and result set cache mode is not on.
            2016-01-13 14:56:27,869 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 Command has no cache hint and result set cache mode is not on.
            2016-01-13 14:56:27,879 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 executing  select count(*) from CD_WELL
            2016-01-13 14:56:27,879 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) eW6VX+tqIyfT.0 executing  select count(*) from CD_WELL
            2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for delegate
            2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Initializing the capabilities for delegate
            2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            2016-01-13 14:56:27,959 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue0) Setting the database version to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            2016-01-13 14:56:27,979 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for eW6VX+tqIyfT.0 ProjectNode(3) output=[IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1] [IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1]
              LimitNode(4) output=[anon_grp1.agg0] limit 100
                GroupingNode(5) output=[anon_grp1.agg0] null{anon_grp1.agg0=SUM(IFNULL(anon_grp2.agg0, 1))}
                  AccessNode(6) output=[IFNULL(anon_grp2.agg0, 1)] SELECT IFNULL(anon_grp2.agg0, 1) FROM EDM.CD_WELL AS g_0

            2016-01-13 14:56:27,979 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) ProcessTree for eW6VX+tqIyfT.0 ProjectNode(3) output=[IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1] [IFNULL(convert(anon_grp1.agg0, integer), 0) AS expr1]
              LimitNode(4) output=[anon_grp1.agg0] limit 100
                GroupingNode(5) output=[anon_grp1.agg0] null{anon_grp1.agg0=SUM(IFNULL(anon_grp2.agg0, 1))}
                  AccessNode(6) output=[IFNULL(anon_grp2.agg0, 1)] SELECT IFNULL(anon_grp2.agg0, 1) FROM EDM.CD_WELL AS g_0

            • 3. Re: count(*) issue
              Steven Hawkins Master

              Is CD_WELL a source table, or is there a view in here as well?

              • 4. Re: count(*) issue
                kchen007 Apprentice

                it is a view in oracle database. but exposed as source table.

                • 5. Re: count(*) issue
                  kchen007 Apprentice

                  I tried another one which a table in oracle database and I got the same error.

                  • 6. Re: count(*) issue
                    Steven Hawkins Master

                    The issue will occur if you have a multi-source, count\(*) and a stagable grouping, and the source supports ifnull.  So something will need to be logged to capture this.


                    The workaround around would be to count over a column:


                    select count(ifnull(col, 1)) from cd_well


                    Or to disable source support for ifnull.

                    • 7. Re: count(*) issue
                      kchen007 Apprentice

                      disable the ISNULL function works. but this will may cause issues for our client who maybe use this function. Is it possible to fix this in Teiid?

                       

                      thanks

                      Kevin

                      • 8. Re: count(*) issue
                        Steven Hawkins Master

                        > Is it possible to fix this in Teiid?

                         

                        Yes, of course.  Can you log an issue?

                        • 9. Re: count(*) issue
                          kchen007 Apprentice

                          thanks!!!

                           

                          [TEIID-3903] for multi-src vdb, the select count(*) does not work for oracle/sqlserver connector

                          Kevin

                          • 10. Re: count(*) issue
                            Steven Hawkins Master

                            Thanks, the fix will be in 8.13 CR3, but 8.12.4 has already been released.

                            • 11. Re: count(*) issue
                              kchen007 Apprentice

                              Thanks Steven, I merged your change and it fixed count(*).