[jira] Updated: (DERBY-4490) SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used in SELECT

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4490:
-----------------------------------

    Attachment: DERBY4490_null_database_property_stat_patch1.txt
                DERBY4490_null_database_property_diff_patch1.txt

While trying to normalize the data during execution phase, Derby adds trailing blanks if necessary so that all the strings are the same length and that causes us to not find the property since the code does not trim the trailing blanks when looking for a property. Following example will help understand the problem

The following query is looking for 2 properties,namely 'DataDictionaryVersion' and 'derby.database.collation'. 
select p, syscs_util.syscs_get_database_property(p) from (values 'derby.database.collation', 'DataDictionaryVersion') props(p); 

Notice that the number of character in string 'derby.database.collation' are more than the number of characters in the string 'DataDictionaryVersion'. While doing normalization during query execution, for value 'DataDictionaryVersion', Derby appends 3 spaces at the end so it became 'DataDictionaryVersion   ' which is the same length as 'derby.database.collation' now. But since there is no such property as 'DataDictionaryVersion   ', Derby would return null rather than returning the version of the database. I am proposing to fix the problem by truncating the trailing blanks when we look for the property in SystemProcedures.SYSCS_GET_DATABASE_PROPERTY thus ignoring the trailing spaces added during normalization.

I have attached the patch for my proposed change.

If no feedback, I will commit this patch early next week.

> SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used in SELECT
> ------------------------------------------------------------------------------------
>
>                 Key: DERBY-4490
>                 URL: https://issues.apache.org/jira/browse/DERBY-4490
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY4490_null_database_property_diff_patch1.txt, DERBY4490_null_database_property_stat_patch1.txt
>
>
> I see the following values returned when invoking SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY on its own:
> ij> values syscs_util.syscs_get_database_property('derby.database.collation');
> 1
> --------------------
> UCS_BASIC
> 1 row selected
> ij> values syscs_util.syscs_get_database_property('DataDictionaryVersion');
> 1
> ---------------------
> 10.6
> 1 row selected
> But when I query both properties at the same time, DataDictionaryVersion comes out as NULL:
> ij> select p, syscs_util.syscs_get_database_property(p) from (values 'derby.database.collation', 'DataDictionaryVersion') props(p);
> P                       |2
> ------------------------------------------------------------
> derby.database.collation|UCS_BASIC
> DataDictionaryVersion   |NULL
> 2 rows selected

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.