Why does the extra space affect the number of rows in the result? Isn't
that space just whitespace and discarded by the SQL parser?
I don't know. I've attached the failing output in case you want to have
a look.
Ah, now I understand.
The test is fetching statements from the *statement cache*, and the extra
space in the statement causes it to appear in the statement cache twice,
once with the extra space and once without.
ij> select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG VARCHAR) LIKE
'%932432%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------
select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG VARCHAR) LIKE
'%932432%'
select SQL_TEXT from syscs_diag.statement_cache where CAST(SQL_TEXT AS LONG VARCHAR) LIKE
'%932432%'
So the actual statement is semantically identical, but since the statement
cache is tracking the literal statement text, whitespace is significant.
It all makes sense now, thanks for being patient with me John while
it sunk in ... :)
thanks,
bryan
