You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/12/22 12:42:29 UTC
[jira] Created: (DERBY-4490) SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY
unexpectedly returns NULL when used in SELECT
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
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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12800906#action_12800906 ]
Mamta A. Satoor commented on DERBY-4490:
----------------------------------------
I haven't looked at the SQL spec but the behavior of PostgreSQL sounds more logical.
> 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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12800743#action_12800743 ]
Bryan Pendleton commented on DERBY-4490:
----------------------------------------
+1 to the idea that VALUES should return the literal strings as VARCHAR in preference to CHAR.
> 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.
[jira] Assigned: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "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 reassigned DERBY-4490:
--------------------------------------
Assignee: Mamta A. Satoor
> 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
>
> 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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12800672#action_12800672 ]
Knut Anders Hatlen commented on DERBY-4490:
-------------------------------------------
Perhaps the underlying bug is not that get_database_property doesn't strip away trailing blanks, but rather that VALUES 'a', 'ab', 'abc' actually returns 'a ', 'ab ', 'abc'.
ij> select x, length(x) from (values 'a','ab','abc') v(x);
X |2
---------------
a |3
ab |3
abc|3
3 rows selected
My initial expectation was that it should return 1,2,3 instead of 3,3,3, but that expectation could be wrong. If the above query result is correct, I would be inclined to say that this bug report is invalid, because I was actually asking for the database property 'DataDictionaryVersion ', so NULL is the expected result.
I guess this then boils down to whether
VALUES 'a', 'ab', 'abc'
should return CHAR(3) or VARCHAR(3). I would have to consult the SQL standard to know for sure.
> 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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12800673#action_12800673 ]
Knut Anders Hatlen commented on DERBY-4490:
-------------------------------------------
As another data point, here's what PostgreSQL responds to the query:
# select x, length(x) from (values ('a'),('ab'),('abc')) v(x);
x | length
-----+--------
a | 1
ab | 2
abc | 3
(3 rows)
> 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.
[jira] Updated: (DERBY-4490) SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY
unexpectedly returns NULL when used in SELECT
Posted by "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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12797812#action_12797812 ]
Mamta A. Satoor commented on DERBY-4490:
----------------------------------------
I did little more testing with this and it appears that the select works when there is only one row in the values table. eg
ij> select p, syscs_util.syscs_get_database_property(p) from (values 'DataDictionaryVersion') props(p);
P |2
------------------------------------------------------------------------------------------------------------------------------------------------------
DataDictionaryVersion|10.6
It also works when there is more than one row but they are the same property ''DataDictionaryVersion'' eg
ij> select p, syscs_util.syscs_get_database_property(p) from (values 'DataDictionaryVersion', 'DataDictionaryVersion') props(p);
P |2
------------------------------------------------------------------------------------------------------------------------------------------------------
DataDictionaryVersion|10.6
DataDictionaryVersion|10.6
But as Knut pointed out, when there are 2 rows (irrespective of the order of the rows), we do not get correct value for 'DataDictionaryVersion'. eg
ij> select p, syscs_util.syscs_get_database_property(p) from (values 'DataDictionaryVersion', 'derby.database.collation') props(p);
P |2
---------------------------------------------------------------------------------------------------------------------------------------------------------
DataDictionaryVersion |NULL
derby.database.collation|UCS_BASIC
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
> 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
>
> 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.
[jira] Assigned: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "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 reassigned DERBY-4490:
--------------------------------------
Assignee: (was: Mamta A. Satoor)
I haven't had a chance to look at the SQL standards on this issue. Also, not sure what(if any) the impact of changing values() behavior would have. I am unassinging myself from this jira
> 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.1.0
> Reporter: Knut Anders Hatlen
> 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.
[jira] Commented: (DERBY-4490)
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY unexpectedly returns NULL when used
in SELECT
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12800671#action_12800671 ]
Knut Anders Hatlen commented on DERBY-4490:
-------------------------------------------
Thanks for tracking down this bug, Mamta!
It looks like the patch introduces a small asymmetry between set_database_property and get_database_property. I don't know if it matters, but I thought I should raise the issue. Currently, you can do this:
ij> call syscs_util.syscs_set_database_property('abc ', 'def');
0 rows inserted/updated/deleted
ij> values syscs_util.syscs_get_database_property('abc ');
1
--------------------------------------------------------------------------------------------------------------------------------
def
1 row selected
With the patch, the latter statement will return NULL, and there's no way of retrieving the value of the property. We could perhaps make the same changes to set_database_property so that they continue to be symmetric. Then there's of course the compatibility issue that it was previously possible to have one property named 'abc ' and another one named 'abc', whereas it won't if we apply this fix. I cannot imagine that this incompatibility would bite anyone, though.
> 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.