db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: [jira] Commented: (DERBY-2962) Change functional tests to use casts for System table queries to avoid conversion errors when run with TERRITORY_BASED collation
Date Fri, 03 Aug 2007 18:40:23 GMT
>> 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


Mime
View raw message