1 2 Previous Next 15 Replies Latest reply on Oct 10, 2014 4:50 AM by Sanjeev Gour

    Strange behavior of CASE WHEN clause

    Sanjeev Gour Novice

      We are observing a case where the the results of an sql look strange and undesirable, we have not understood yet why this might be happening. Here are two queries and the respective plans, these query plans are identical but the results are not for some reasons. The strange thing is that in both cases the where condition is satisfied but the WHEN in CASE is satisfied in one case and not in the other.

       

      Case 1:

      Query:

      SELECT "EVTUSERID", "EVTTYPEDESC", CASE WHEN ("EVTUSERID" = 'GOUSA01') THEN 'Match' ELSE 'No Match' END AS RULESTATE 

      FROM "SECCM.VIEWSYSACCESS" S1  where (("EVTUSERID" = 'GOUSA01' AND "EVTTYPEDESC" = 'SIGNOFF'))

       

      Plan:

      OPTIMIZATION COMPLETE:

      PROCESSOR PLAN:

      ProjectNode(0) output=[evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evtuserid = 'GOUSA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE] [evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evtuserid = 'GOUSA01' THEN 'Match' ELSE 'No Match' END AS RULESTATE]

        AccessNode(1) output=[evtuserid, evttypedesc] SELECT g_0.EVTUSERID, g_0.EVTTYPEDESC FROM CMGRQ1.VIEWSYSACCESS AS g_0 WHERE (g_0.EVTUSERID = 'GOUSA01') AND (g_0.EVTTYPEDESC = 'SIGNOFF') LIMIT 2

       

      Results

      evtuserid         evttypedesc RULESTATE
      GOUSA01         SIGNOFF No Match
      GOUSA01         SIGNOFF No Match

       

      Case 2:

      Query:

      SELECT "EVTUSERID", "EVTTYPEDESC", CASE WHEN ("EVTTYPEDESC" = 'SIGNOFF') THEN 'Match' ELSE 'No Match' END AS RULESTATE 

      FROM "SECCM.VIEWSYSACCESS" S1  where (("EVTUSERID" = 'GOUSA01' AND "EVTTYPEDESC" = 'SIGNOFF'))

       

      Plan:

      OPTIMIZATION COMPLETE:

      PROCESSOR PLAN:

      ProjectNode(0) output=[evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evttypedesc = 'SIGNOFF' THEN 'Match' ELSE 'No Match' END AS RULESTATE] [evtuserid AS evtuserid, evttypedesc AS evttypedesc, CASE WHEN evttypedesc = 'SIGNOFF' THEN 'Match' ELSE 'No Match' END AS RULESTATE]

        AccessNode(1) output=[evtuserid, evttypedesc] SELECT g_0.EVTUSERID, g_0.EVTTYPEDESC FROM CMGRQ1.VIEWSYSACCESS AS g_0 WHERE (g_0.EVTUSERID = 'GOUSA01') AND (g_0.EVTTYPEDESC = 'SIGNOFF') LIMIT 2

       

      Results:

      evtuserid    evttypedesc    RULESTATE
      GOUSA01   SIGNOFF   Match
      GOUSA01   SIGNOFF   Match

       

      Help is appreciated.

      Thanks.

      Sanjeev.

        • 1. Re: Strange behavior of CASE WHEN clause
          Steven Hawkins Master

          Since the predicates are being evaluated in different places, you should double check that you are dealing only the exact character representation that you expect (whitespace, character encoding, etc.). 

          • 2. Re: Strange behavior of CASE WHEN clause
            Sanjeev Gour Novice

            Sure, but that doesn't appear to be the problem here. This is tested within the actual code and through squirrel client also. Both produce the same results.

            • 3. Re: Strange behavior of CASE WHEN clause
              Steven Hawkins Master

              Just determine the exact character representation of the result data and in the case statement to ensure there isn't an unexpected difference.  For example trailing whitespace such that a trim is needed.

              • 4. Re: Strange behavior of CASE WHEN clause
                Sanjeev Gour Novice

                Steve, I think you are right. There is some character that is causing this, it is rendered as a white space in notepade++ but it is certainly not a white space. There following works-

                 

                CASE WHEN ("EVTUSERID" like 'GOUSA01%') THEN 'Match' ELSE 'No Match'

                 

                and the following doesn't-

                CASE WHEN ("EVTUSERID" = RTRIM('GOUSA01')) THEN 'Match' ELSE 'No Match'

                • 5. Re: Strange behavior of CASE WHEN clause
                  Sanjeev Gour Novice

                  It turns out to be a trailing white space that is causing this problem. My usage of RTRIM was incorrect in the the post above, applied it on the value instead of the column name. The database column is 8 chars long CHAR type, the supplied value it less chars and hence it the returned data get additional space. Tried using the translator property trimStrings but it doesn't seem to work. The translator inherits the JDBCExecutionFactory. Other than setting this property to true, is there anything else the translator needs to support? With some debugging I see that the SQLConversionVisitor shows correct conversion like the following-

                   

                  {g_0."EVTUSERID"=[rtrim(g_0."EVTUSERID")], g_0."EVTUSERID"=g_0."EVTUSERID", SELECT g_0."EVTUSERID", g_0."EVTTYPEDESC" FROM "CMGRQ1"."VIEWSYSACCESS" AS g_0 WHERE g_0."EVTTYPEDESC" = 'SIGNOFF' AND g_0."EVTUSERID" = 'GOUSA01' LIMIT 1=SELECT g_0."EVTUSERID", g_0."EVTTYPEDESC" FROM "CMGRQ1"."VIEWSYSACCESS" AS g_0 WHERE g_0."EVTTYPEDESC" = 'SIGNOFF' AND g_0."EVTUSERID" = 'GOUSA01' LIMIT 1}

                   

                  But the result still contain the trailing spaces. Applying RTRIM in the query directly works.

                  • 6. Re: Strange behavior of CASE WHEN clause
                    Steven Hawkins Master

                    > Other than setting this property to true, is there anything else the translator needs to support?

                     

                    There's nothing built-in no. I'm not sure if what you are showing there is the source sql.  That should be displayed in the log at a detail level.  The trimStrings logic is looking for char/nchar columns using the native type metadata property, so you'll want to double check the source metadata.

                    • 7. Re: Strange behavior of CASE WHEN clause
                      Sanjeev Gour Novice

                      The metadata for this column shows CHAR through a JDBC program connecting directly to the database. The above SQL I have take from a debug point in the SQLConversionVisitor which recognizes the CHAR correctly and adds the rtrim function based on the trimStrings and char type, looks like it gets lost somewhere down the line-

                       

                      g_0."EVTUSERID"=[rtrim(g_0."EVTUSERID")],

                      • 8. Re: Strange behavior of CASE WHEN clause
                        Steven Hawkins Master

                        It would be best to look at the whole source query sent to oracle.  You can see in JDBCBaseExecution.translateCommand that it's logged at a detail/debug level.

                        1 of 1 people found this helpful
                        • 9. Re: Strange behavior of CASE WHEN clause
                          Sanjeev Gour Novice

                          Thanks Steve. Looks like there is a custom sql conversion visitor for this data source that remove the rtrim.

                          • 10. Re: Strange behavior of CASE WHEN clause
                            Sanjeev Gour Novice

                            Does setting the trimStrings to true affect the update queries? Meaning a trailing space won't be trimmed when it is part of insert values. I believe this will only impact the selects, just want to make sure.

                            • 11. Re: Strange behavior of CASE WHEN clause
                              Steven Hawkins Master

                              It does currently impact updates in the where clause and even the set clause if it uses an expression involving the affected column.  I do see though it is inappropriately effecting insert/with clause column references, which will need to be corrected.  For example "insert into foo (rtrim(x)) values ('a')".  This will need corrected.

                              • 12. Re: Strange behavior of CASE WHEN clause
                                Steven Hawkins Master

                                https://issues.jboss.org/browse/TEIID-3161 addresses the sql generation.  And to answer your specific question, no trim string does not explicitly affect the inserted value.  It would be up to the database to pad appropriately from there.

                                • 13. Re: Strange behavior of CASE WHEN clause
                                  Sanjeev Gour Novice

                                  >It does currently impact updates in the where clause and even the set clause if it uses an expression involving the affected column.  I do see though it is inappropriately effecting insert/with clause column references, which will need to be corrected.  For example "insert into foo (rtrim(x)) values ('a')".  This will need corrected.

                                   

                                  I am not sure I understand what you mean. If I set the trimStrings to true and issue an update query like "update foo set name = 'new_name ' where id = '123'", and name and id being CHAR types, would it trim the extra space in the name column which is supplied with the value? Similar if I issue an insert with additional space in the value, would that also be trimmed?

                                  • 14. Re: Strange behavior of CASE WHEN clause
                                    Steven Hawkins Master

                                    > I am not sure I understand what you mean

                                     

                                    I saw behavior not specifically related to what you were asking about, but to trim strings in general that needed fixed.

                                     

                                    > If I set the trimStrings to true and issue an update query like "update foo set name = 'new_name ' where id = '123'", and name and id being CHAR types, would it trim the extra space in the name column which is supplied with the value? Similar if I issue an insert with additional space in the value, would that also be trimmed?

                                     

                                    See my subsequent post, no Teiid will not alter insert/update values.  We'd be expecting the source system to appropriately trim/pad from there.

                                    1 2 Previous Next