Strange behavior of CASE WHEN clause

[sanjeev.gour]

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.

[shawkins]

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.). 

[sanjeev.gour]

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.

[shawkins]

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.

[sanjeev.gour]

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'

[sanjeev.gour]

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.

[shawkins]

> 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.

[sanjeev.gour]

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")],

[shawkins]

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.

[sanjeev.gour]

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

[sanjeev.gour]

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.

[shawkins]

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.

[shawkins]

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.

[sanjeev.gour]

>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?

[shawkins]

> 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.