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 Novice

      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.