11 Replies Latest reply on Dec 12, 2014 5:08 AM by sanjeev.gour

    Query planner is not pushing down CASE when combined with custom function

    sanjeev.gour

      Hi-

       

      We have a data source that handles columns of CHAR type in the following way. If the column is defined as USERID CHAR(8), and two rows are inserted like below-

      IDUSERID
      1ABCDE
      2ABCDEFGH

       

      Notice that the first row has 5 characters for userid and second has 8, so in case of first rows 3 trailing space are added to the value. Following queries against the data source directly produce the following results-

       

      SELECT USERID FROM FIRST_SCHEMA.FIRST_TABLE                                                                                                                  produces 'ABCDE   ' and 'ABCDEFGH'

      SELECT USERID FROM FIRST_SCHEMA.FIRST_TABLE WHERE USERID     = 'ABCDE'                                                                    produces 'ABCDE   '

      SELECT CASE WHEN USERID = 'ABCDE' THEN 'Match' ELSE 'No Match' END AS STATE FROM FIRST_SCHEMA.FIRST_TABLE       produces 'Match' for first row and 'No Match' for second row

       

      So when we issue a select query, the result comes back with the additional trailing spaces, in this case 'ABCDE    '. Same is the behavior when we issue a query with a WHERE condition that also handles the space and also, if we issue a query that involved a CASE statement and the WHEN condition does not contain the addition spaces, it still returns the data properly ignoring the space.

       

      All the above results are consistent when they are run against Teiid. The results differ when the query contains a custom function.

      SELECT ID, CASE WHEN USERID = 'ABCDE' THEN 'Match' ELSE 'No Match' END AS STATE FROM FIRST_SCHEMA.FIRST_TABLE              produces 'Match' and 'No Match' - expected

       

      We get 'Match' in this case because the entire query was pushed down to the data source and it handled the spaces. Now, if we introduce a custom function in the query that makes use of one column from the table, the query planner does not push the CASE to the data source, instead, it queries the columns used in the custom function and CASE clause like below-

       

      SELECT testing.primary_key('TEST','NAME',ID) as PKEY, CASE WHEN NAME = 'ABCDE' THEN 'Match' ELSE 'No Match' END AS STATE FROM FIRST_SCHEMA.FIRST_TABLE

       

      We get 'No Match' for both the rows. This is possibly caused by the fact that the CASE clause wasn't pushed down and Teiid tried matching the WHEN condition 'ABCDE' with the returned rows 'ABCDE   ' and 'ABCDEFG'. The query plan is below-

       

      ProjectNode

        + Output Columns:

          0: pkey (string)

          1: STATE (string)

        + Statistics:

          0: Node Output Rows: 2

          1: Node Next Batch Process Time: 50

          2: Node Cumulative Next Batch Process Time: 25

          3: Node Cumulative Process Time: 74

          4: Node Next Batch Calls: 2

          5: Node Blocks: 1

        + Cost Estimates:Estimated Node Cardinality: -1.0

        + Child 0:

          AccessNode

            + Output Columns:

              0: ID (integer)

              1: USERID (string)

            + Statistics:

              0: Node Output Rows: 2

              1: Node Next Batch Process Time: 73

              2: Node Cumulative Next Batch Process Time: 24

              3: Node Cumulative Process Time: 73

              4: Node Next Batch Calls: 2

              5: Node Blocks: 1

            + Cost Estimates:Estimated Node Cardinality: -1.0

            + Query:SELECT g_0.ID, g_0.USERID FROM h2extended.TEST.FIRST_SCHEMA.FIRST_TABLE AS g_0

            + Model Name:h2extended

        + Select Columns:

          0: testing.primary_key('TEST', 'NAME', convert(h2extended.TEST.FIRST_SCHEMA.FIRST_TABLE.ID, string)) AS pkey

          1: CASE WHEN h2extended.TEST.FIRST_SCHEMA.FIRST_TABLE.USERID = 'ABCEDE' THEN 'Match' ELSE 'No Match' END AS STATE

       

      The data source supports select expressions. We understand trimming the column values could solve this. However, we don't understand why Teiid doesn't push the CASE in this case and if there is any other property that the translator needs to support to get the desired behavior.

       

      Thanks.
      Sanjeev.

        • 1. Re: Query planner is not pushing down CASE when combined with custom function
          shawkins

          > The data source supports select expressions. We understand trimming the column values could solve this. However, we don't understand why Teiid doesn't push the CASE in this case and if there is any other property that the translator needs to support to get the desired behavior.

           

          The debug log will give you more detail on pushdown decisions with lines like:

           

          XXX was not pushed CapabilityX not supported by source.

           

          We attempt to push a project node as a whole.  Only if evaluation is required at the source do we push subexpressions.  So based upon the above you would need the following to push this expression down:

          select expressions

          searched case expressions

          function support for testing.primary_key

          • 2. Re: Query planner is not pushing down CASE when combined with custom function
            sanjeev.gour

            I will test with the additional logging. Among the three, the custom function is not marked to be pushed down because that cannot be evaluated by the source, would that impact the decision to push down the searched case expression?

            • 3. Re: Query planner is not pushing down CASE when combined with custom function
              shawkins

              > Among the three, the custom function is not marked to be pushed down because that cannot be evaluated by the source, would that impact the decision to push down the searched case expression?


              Unfortunately yes as we are attempting to push the entire project node.  I'll have a look again at our handling and see if that can be addressed.

              • 5. Re: Query planner is not pushing down CASE when combined with custom function
                sanjeev.gour

                Hi Steve-

                 

                Just wanted to check whether this issue figures in your priority list. Actually, this is is required for us to handle a customer issue for our product, knowing its priority would help us decide our next steps.

                 

                Thanks.

                • 6. Re: Query planner is not pushing down CASE when combined with custom function
                  shawkins

                  More than likely 8.10 will contain the initial fix.

                  • 7. Re: Query planner is not pushing down CASE when combined with custom function
                    sanjeev.gour

                    Hi Steve-

                     

                    I applied the fix for TEIID-3205 to 8.7 and I see different behaviours under these two conditions. The pushdown happens properly and for both cases the source query produces correct result (a MATCH condition for the CASE) but results from Teiid in the second case look incorrect.

                     

                    1)

                    If the query does not contain any custom function, then the result comes as expected. Example below-

                     

                    USER COMMAND:

                    SELECT S1.evtuserid, CASE WHEN S1.evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE FROM SECCM.VIEWSYSACCESS AS S1 WHERE S1.evtuserid = 'HOGWA01' LIMIT 100

                     

                    OPTIMIZATION COMPLETE:

                    PROCESSOR PLAN:

                    AccessNode(0) output=[evtuserid AS evtuserid, CASE WHEN evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE] SELECT g_0.EVTUSERID AS c_0, CASE WHEN g_0.EVTUSERID = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS c_1 FROM SECURITY_WH_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS AS g_0 WHERE g_0.EVTUSERID = 'HOGWA01' LIMIT 1

                     

                    This produces a 'Match' for the RULESTATE column.

                     

                     

                    2)

                    If the query contains a custom function and the case statement then the result doesn't look correct. Example below-

                     

                    USER COMMAND:

                    SELECT S1.evtuserid, primary_key('SECCM.VIEWSYSACCESS', 'EVTSYSID', S1.evtsysid, 'EVTUTCTOD', convert(S1.evtutctod, string), 'EVTSYSTOD', convert(S1.evtsystod, string)) AS OBJFKEYXML, CASE WHEN S1.evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE FROM SECCM.VIEWSYSACCESS AS S1 WHERE S1.evtuserid = 'HOGWA01' LIMIT 100

                     

                    OPTIMIZATION COMPLETE:

                    PROCESSOR PLAN:

                    ProjectNode(0) output=[evtuserid AS evtuserid, primary_key('SECCM.VIEWSYSACCESS', 'EVTSYSID', evtsysid, 'EVTUTCTOD', convert(evtutctod, string), 'EVTSYSTOD', convert(evtsystod, string)) AS OBJFKEYXML, CASE WHEN evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE] [evtuserid AS evtuserid, primary_key('SECCM.VIEWSYSACCESS', 'EVTSYSID', evtsysid, 'EVTUTCTOD', convert(evtutctod, string), 'EVTSYSTOD', convert(evtsystod, string)) AS OBJFKEYXML, CASE WHEN evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE]

                      AccessNode(1) output=[evtuserid, evtsysid, evtutctod, evtsystod, CASE WHEN evtuserid = 'HOGWA01' THEN 'Match' ELSE 'No Match' END] SELECT g_0.EVTUSERID AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, CASE WHEN g_0.EVTUSERID = 'HOGWA01' THEN 'Match' ELSE 'No Match' END AS c_4 FROM SECURITY_WH_DB2_D91APTIB_CMGRD1.VIEWSYSACCESS AS g_0 WHERE g_0.EVTUSERID = 'HOGWA01' LIMIT 100

                     

                    This produces a 'No Match' for the RULESTATE column.

                     

                    I am wondering what could affect the results in this case.

                     

                    Query plans for both cases are attached if they could help.

                     

                    Thanks.

                    • 8. Re: Query planner is not pushing down CASE when combined with custom function
                      shawkins

                      Presumably this means that the case expression is being evaluated in Teiid as well in the project node.  That is not the behavior I see locally.  You'll want to double check in a debugger where the value for the case expression is coming from - ProjectNode.updateTuple if it's coming from the tuple or being evaluated.  And/or you can try your scenario against 8.10 alpha1 to see if there is a difference.

                      • 9. Re: Query planner is not pushing down CASE when combined with custom function
                        sanjeev.gour

                        You are right. ProjectNode.updateTuple is trying to evaluate the CASE from the value returned by the data source. There is one more thing to note. The column that is used in CASE, I must use it in the SELECT as well, otherwise I get an error like below-

                         

                        Error: TEIID30328 Unable to evaluate evtuserid: No value was available

                         

                        This is again because the ProjectNode.updateTuple is trying to evaluate the CASE from the value returned by the data source and if it not present in the returned result, it complains.

                         

                        What can I do to tell Teiid not to evaluate it in the updateTuple and how to avoid using the evtuserid in the SELECT?

                         

                        Thanks.

                        • 10. Re: Query planner is not pushing down CASE when combined with custom function
                          shawkins

                          > What can I do to tell Teiid not to evaluate it in the updateTuple and how to avoid using the evtuserid in the SELECT?

                           

                          You first need to ensure that you don't have a patching issue.  Can you retry your scenario in 8.10 alpha1 or off of master?  As I don't see this behavior locally.

                          • 11. Re: Query planner is not pushing down CASE when combined with custom function
                            sanjeev.gour

                            Hi Steve-

                             

                            I cannot test it with 8.10 right now as our project doesn't work with it yet. I have a test case but that is built with H2 as data source and H2 doesn't seem to have CHAR datatype hence it does recreate the same scenario as I want. For current release with 8.7 we found an alternate option by avoiding CASE so this usecase does not apply right now.

                             

                            Thanks.