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 "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2007/05/18 08:25:16 UTC
[jira] Created: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Key: DERBY-2668
URL: https://issues.apache.org/jira/browse/DERBY-2668
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.3.0.0
Reporter: Mamta A. Satoor
In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
eg sql
ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
ij> select * from sys.systables where tablename = 'T1';
ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12508246 ]
Mamta A. Satoor commented on DERBY-2668:
----------------------------------------
There is a thread on Derby list at http://www.nabble.com/comparisons-between-system-and-user-table-columns-with-TERRITORY_BASED-collation-tf3967283.html#a11261072 This talks about possible changes to the error message text. Copied portion of email from Bryan Pendleton below
Can we make it say something like:
ERROR 42818: Comparisons between 'VARCHAR(en_US)' and 'VARCHAR(UCS_BASIC)' are not supported.
That is, sort of pack the collation information into the datatype
name somehow?
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-2668:
-------------------------------------
Assignee: Kathey Marsden
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Kathey Marsden
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-2668:
----------------------------------
Attachment: derby-2668_preview_diff.txt
Before I update many masters I wanted to run a preview of the patch for this issue and the new error format by the community. The new error will look as below with collation always printing with string types when comparisons are not supported. Please let me know what you think.
Kathey
ij> select * from sys.systables where tablename = 'T1';
ERROR 42818: Comparisons between 'VARCHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported.
ij> select * from sys.systables where tablename = 1;
ERROR 42818: Comparisons between 'VARCHAR (UCS_BASIC)' and 'INTEGER' are not supported.
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Kathey Marsden
> Attachments: derby-2668_preview_diff.txt
>
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496799 ]
Mamta A. Satoor commented on DERBY-2668:
----------------------------------------
I haven't spent enough time thinking about this but I think it is more than just fixing the error message string. It seems that the comparable method in DTD should return int rather than boolean and the return int value will decide if we should throw what we are throwing today (ie something like ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported. ) or throw a new message because collations don't match. This is just my initial thought, haven't spend enough time to think about a solution
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (DERBY-2668) At the time of compilation of
a comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by Mike Matrigali <mi...@sbcglobal.net>.
The wordier message seem good to me. And is definitely better than what
we have now.
Kathey Marsden (JIRA) wrote:
> [ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12517658 ]
>
> Kathey Marsden commented on DERBY-2668:
> ---------------------------------------
>
> Here is an alternate, wordier message. Would something with this level of detail be better?
>
>
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')
>
>
>> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> Key: DERBY-2668
>> URL: https://issues.apache.org/jira/browse/DERBY-2668
>> Project: Derby
>> Issue Type: Improvement
>> Components: SQL
>> Affects Versions: 10.3.0.0
>> Reporter: Mamta A. Satoor
>> Assignee: Kathey Marsden
>> Attachments: derby-2668_preview_diff.txt
>>
>>
>> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
>> eg sql
>> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
>> ij> select * from sys.systables where tablename = 'T1';
>> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
>
[jira] Commented: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12517658 ]
Kathey Marsden commented on DERBY-2668:
---------------------------------------
Here is an alternate, wordier message. Would something with this level of detail be better?
ij> select * from sys.systables where tablename = 'T1';
ERROR 42818: Comparisons between 'VARCHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Kathey Marsden
> Attachments: derby-2668_preview_diff.txt
>
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2668) At the time of compilation of a
comparison operation, if the collation types of the operands do not match,
then we should throw a meaningful error message.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2668?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-2668.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.4.0.0
10.3.1.4
> At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2668
> URL: https://issues.apache.org/jira/browse/DERBY-2668
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Kathey Marsden
> Fix For: 10.3.1.4, 10.4.0.0
>
> Attachments: derby-2668_preview_diff.txt
>
>
> In Derby 10.3, we are adding a new feature (DERBY-1478) which will allow character string types to have a territory based collation (except for persistent character string type columns from system schema). When 2 character types are compared, they should have the same collation type associated with them. If not then Derby 10.3 will throw an exception. Currently, in Derby 10.3 codeline, the exception thrown is something like "ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported." but this error message is misleading. The error is really for collation mismatch. We should fix following error so it indicates the real reason behind the exception.
> eg sql
> ij> connect 'nodb;create=true;territory=no;collation=TERRITORY_BASED';
> ij> select * from sys.systables where tablename = 'T1';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.