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 Kathey Marsden <km...@sbcglobal.net> on 2007/06/23 00:28:53 UTC
comparisons between system and user table columns with TERRITORY_BASED
collation
If I create a database with TERRITORY_BASED collation and try to do a
comparison between a user table column and a system table column, I get
the following message. If I create it with UCS_BASIC collation there is
no problem with the query. It makes sense to me that I can't compare
these columns but I wonder if it would be worthwhile to make a more
informative error message since in fact normally comparisons between
VARCHAR and VARCHAR are supported, just not comparisons between
TERRITORY_BASED and UCS_BASIC.
Thoughts?
Kathey
ij> connect
'jdbc:derby:enusdb;create=true;territory=en_US;collation=TERRITORY_BASED';
ij(CONNECTION1)> create table t (vc varchar(30));
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into t values('t');
1 row inserted/updated/deleted
ij(CONNECTION1)> select tableName, vc from sys.systables, t where vc =
tableName;
ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
Re: comparisons between system and user table columns with TERRITORY_BASED
collation
Posted by Kathey Marsden <km...@sbcglobal.net>.
Mamta Satoor wrote:
> My earlier message was little vague. In the second paragraph, I am
> talking about Jira entry DERBY-2668.
>
> As for DERBY-2866, Kathey can I close it as a duplicate of DERBY-2668?
> I think they are both talking about the same problem.
>
Yes, it looks like a dup.
Kathey
Re: comparisons between system and user table columns with TERRITORY_BASED collation
Posted by Mamta Satoor <ms...@gmail.com>.
My earlier message was little vague. In the second paragraph, I am talking
about Jira entry DERBY-2668.
As for DERBY-2866, Kathey can I close it as a duplicate of DERBY-2668? I
think they are both talking about the same problem.
Mamta
On 6/26/07, Mamta Satoor <ms...@gmail.com> wrote:
>
> Dan, I will go ahead and enter a Jira entry which will handle the task of
> checking how all the collation determination logic can be put in one central
> place.
>
> As for the error message, I agree that it is confusing. I will go ahead
> and put the comments from this thread about possibly using the actual
> collation type in the error message. I hope to work on this Jira entry after
> finishing couple other todos on collation.
>
> Mamta
>
>
> On 6/26/07, Daniel John Debrunner <djd@apache.org > wrote:
> >
> >
> > > Mike Matrigali < mikem_app@sbcglobal.net> writes:
> > >
> > >> This sounds like an easy solution to this issue, but will change the
> > >> error message for all comparison mismatches that are not territory
> > >> related. Does that sound ok?
> >
> > I would have thought that there would be a different error message.
> > Looking at the comments in DERBY-2668 it seems to says a single message
> > is used because DataTypeDescriptor.comparable() is used to determine if
> > the collations are compatible for comparision. However, I'm not sure how
> >
> > this can be implementing the required logic behind collation
> > determination, which requires inspection of all the types involved, not
> > just two. I think this goes back to my concern that the required complex
> > logic for determining collation is not in a single method, rather spread
> >
> > out across several locations, each with different logic. Even the logic
> > in DTD.comparable does not match what is required by the SQL spec
> > (section 9.13 I think).
> >
> > Dan.
> >
> >
> >
>
Re: comparisons between system and user table columns with TERRITORY_BASED collation
Posted by Mamta Satoor <ms...@gmail.com>.
Created DERBY-2875 for the task of checking how all the
collationdetermination logic which is based on section
9.13 in SQL spec can be put in one central place.
Mamta
On 6/26/07, Mamta Satoor <ms...@gmail.com> wrote:
>
> Dan, I will go ahead and enter a Jira entry which will handle the task of
> checking how all the collation determination logic can be put in one
> central place.
>
> As for the error message, I agree that it is confusing. I will go ahead
> and put the comments from this thread about possibly using the actual
> collation type in the error message. I hope to work on this Jira entry
> after finishing couple other todos on collation.
>
> Mamta
>
>
> On 6/26/07, Daniel John Debrunner <dj...@apache.org> wrote:
> >
> >
> > > Mike Matrigali <mi...@sbcglobal.net> writes:
> > >
> > >> This sounds like an easy solution to this issue, but will change the
> > >> error message for all comparison mismatches that are not territory
> > >> related. Does that sound ok?
> >
> > I would have thought that there would be a different error message.
> > Looking at the comments in DERBY-2668 it seems to says a single message
> > is used because DataTypeDescriptor.comparable() is used to determine if
> > the collations are compatible for comparision. However, I'm not sure how
> >
> > this can be implementing the required logic behind collation
> > determination, which requires inspection of all the types involved, not
> > just two. I think this goes back to my concern that the required complex
> > logic for determining collation is not in a single method, rather spread
> >
> > out across several locations, each with different logic. Even the logic
> > in DTD.comparable does not match what is required by the SQL spec
> > (section 9.13 I think).
> >
> > Dan.
> >
> >
> >
>
Re: comparisons between system and user table columns with TERRITORY_BASED collation
Posted by Mamta Satoor <ms...@gmail.com>.
Dan, I will go ahead and enter a Jira entry which will handle the task of
checking how all the collation determination logic can be put in one central
place.
As for the error message, I agree that it is confusing. I will go ahead and
put the comments from this thread about possibly using the actual collation
type in the error message. I hope to work on this Jira entry after finishing
couple other todos on collation.
Mamta
On 6/26/07, Daniel John Debrunner <dj...@apache.org> wrote:
>
>
> > Mike Matrigali <mi...@sbcglobal.net> writes:
> >
> >> This sounds like an easy solution to this issue, but will change the
> >> error message for all comparison mismatches that are not territory
> >> related. Does that sound ok?
>
> I would have thought that there would be a different error message.
> Looking at the comments in DERBY-2668 it seems to says a single message
> is used because DataTypeDescriptor.comparable() is used to determine if
> the collations are compatible for comparision. However, I'm not sure how
> this can be implementing the required logic behind collation
> determination, which requires inspection of all the types involved, not
> just two. I think this goes back to my concern that the required complex
> logic for determining collation is not in a single method, rather spread
> out across several locations, each with different logic. Even the logic
> in DTD.comparable does not match what is required by the SQL spec
> (section 9.13 I think).
>
> Dan.
>
>
>
Re: comparisons between system and user table columns with TERRITORY_BASED
collation
Posted by Daniel John Debrunner <dj...@apache.org>.
> Mike Matrigali <mi...@sbcglobal.net> writes:
>
>> This sounds like an easy solution to this issue, but will change the
>> error message for all comparison mismatches that are not territory
>> related. Does that sound ok?
I would have thought that there would be a different error message.
Looking at the comments in DERBY-2668 it seems to says a single message
is used because DataTypeDescriptor.comparable() is used to determine if
the collations are compatible for comparision. However, I'm not sure how
this can be implementing the required logic behind collation
determination, which requires inspection of all the types involved, not
just two. I think this goes back to my concern that the required complex
logic for determining collation is not in a single method, rather spread
out across several locations, each with different logic. Even the logic
in DTD.comparable does not match what is required by the SQL spec
(section 9.13 I think).
Dan.
Re: comparisons between system and user table columns with
TERRITORY_BASED collation
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Mike Matrigali <mi...@sbcglobal.net> writes:
> This sounds like an easy solution to this issue, but will change the
> error message for all comparison mismatches that are not territory
> related. Does that sound ok?
Sounds OK to me. This doesn't look too bad:
ERROR 42818: Comparisons between 'VARCHAR(UCS_BASIC)' and 'INTEGER' are
not supported
> Bryan Pendleton wrote:
>>> ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not
>>> supported.
>>
>> Yes, I think that this error message would be quite confusing.
>>
>> 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?
>>
>> thanks,
>>
>> bryan
>>
>>
>>
--
Knut Anders
Re: comparisons between system and user table columns with TERRITORY_BASED
collation
Posted by Mike Matrigali <mi...@sbcglobal.net>.
This sounds like an easy solution to this issue, but will change the
error message for all comparison mismatches that are not territory
related. Does that sound ok?
Bryan Pendleton wrote:
>> ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not
>> supported.
>
> Yes, I think that this error message would be quite confusing.
>
> 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?
>
> thanks,
>
> bryan
>
>
>
Re: comparisons between system and user table columns with TERRITORY_BASED
collation
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
Yes, I think that this error message would be quite confusing.
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?
thanks,
bryan