You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Geoff hendrey <ge...@yahoo.com> on 2008/06/23 15:41:37 UTC
any feedback on this?
I posted a comment with a suggestion on an approach for dealing with case-insensitive comparisons.
Any feedback?
https://issues.apache.org/jira/browse/DERBY-1748?focusedCommentId=12606166#action_12606166
Re: any feedback on this?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Alan Burlison <Al...@Sun.COM> writes:
>>> Derby 10.4 allows multiple nulls for unique columns.
>>
>> I'm using 10.4.1.3 but it doesn't seem to work - is there some sort
>> of spacial chant I need to do to enable this?
>
> Ah, you have to add it as a 'unique' table constraint, if you add a
> unique index it doesn't work. Odd, shouldn't you be able to do it
> either way?
The issue here is probably that Derby has supported CREATE UNIQUE INDEX
for a while, and the old semantics were preserved. When the unique
constraint was implemented, it was decided that it should follow the SQL
standard, which says that multiple nulls should be allowed.
I think the rationale for not allowing duplicate nulls in unique indices
was that 'index' was a store-level concept, not an SQL concept (CREATE
INDEX is not part of the SQL standard, as far as I know). In fact, the
UNIQUE constraints were implemented on top of non-unique indices.
I didn't follow the discussion about the implementation of the unique
constraint that closely, though, so anyone please correct me if I'm
wrong.
--
Knut Anders
Re: any feedback on this?
Posted by Anurag shekhar <An...@Sun.COM>.
> Ah, you have to add it as a 'unique' table constraint, if you add a
> unique index it doesn't work. Odd, shouldn't you be able to do it
> either way?
>
SQL spec defines behavior of unique constrain over null able field to
allow multiple
null for the fields participating in the constraint.
For Index it doesn't specifically says anything about it. So for now
even though the
index and constraint are behaving differently the behavior is not
violating any guidelines.
anurag
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
>> Derby 10.4 allows multiple nulls for unique columns.
>
> I'm using 10.4.1.3 but it doesn't seem to work - is there some sort of
> spacial chant I need to do to enable this?
Ah, you have to add it as a 'unique' table constraint, if you add a
unique index it doesn't work. Odd, shouldn't you be able to do it
either way?
--
Alan Burlison
--
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Øystein Grøvlen wrote:
> Derby 10.4 allows multiple nulls for unique columns.
I'm using 10.4.1.3 but it doesn't seem to work - is there some sort of
spacial chant I need to do to enable this?
--
Alan Burlison
--
Re: any feedback on this?
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Alan Burlison wrote:
> I've hit another snag though - I want the index to be unique and I was
> going to keep the case-converted column up-to-date using 'after'
> triggers. However you can't use after triggers to populate a column
> which has a unique index. If you use a multi-row insert, the triggers
> don't fire until after *all* the rows have been inserted, which results
> in multiple nulls in the case-converted columns, which in turn cause an
> error because an unique index can't contain multiple nulls.
Derby 10.4 allows multiple nulls for unique columns.
> Bah. Looks like I'm going to have to switch to MySQL after all. :-(
--
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Knut Anders Hatlen wrote:
> Both of the above queries would normally use the index on upper_col, if
> such an index is present.
Thanks.
I've hit another snag though - I want the index to be unique and I was
going to keep the case-converted column up-to-date using 'after'
triggers. However you can't use after triggers to populate a column
which has a unique index. If you use a multi-row insert, the triggers
don't fire until after *all* the rows have been inserted, which results
in multiple nulls in the case-converted columns, which in turn cause an
error because an unique index can't contain multiple nulls.
Bah. Looks like I'm going to have to switch to MySQL after all. :-(
--
Alan Burlison
--
Re: any feedback on this?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Alan Burlison <Al...@Sun.COM> writes:
> Bryan Pendleton wrote:
>
>> I store my critical string fields twice, in two separate columns, one
>> column has the user-desired case, and the other column has the same data
>> in all upper case.
>>
>> Then, when I want to search without considering case, I take my search
>> terms, convert them to all upper case, and search against the
>> upper-case column.
>
> That's what I thought, thanks for the confirmation. One question: if
> I use something like
> where upper_col = UPPER(?)
> will Derby still use an index, or should I convert the case of the
> search term externally then do
> where upper_col = ?
Both of the above queries would normally use the index on upper_col, if
such an index is present.
--
Knut Anders
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Bryan Pendleton wrote:
> I store my critical string fields twice, in two separate columns, one
> column has the user-desired case, and the other column has the same data
> in all upper case.
>
> Then, when I want to search without considering case, I take my search
> terms, convert them to all upper case, and search against the upper-case
> column.
That's what I thought, thanks for the confirmation. One question: if I
use something like
where upper_col = UPPER(?)
will Derby still use an index, or should I convert the case of the
search term externally then do
where upper_col = ?
Thanks,
--
Alan Burlison
--
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Rick Hillegas wrote:
> Some people have found it useful to add a CHECK constraint to the table
> to verify that wrong values aren't leaking into the upper-case column
> from somewhere in their application. Something like the following can
> give you more peace of mind:
>
> create table t
> (
> lastName varchar( 50 ),
> normalizedLastName varchar( 50 ),
> check ( normalizedLastName = upper( lastName ) )
> );
Yeah, I saw that somewhere. I've decided never to write to the
case-converted column but to populate it via triggers, that should have
the same effect. Thanks for the tip, anyway :-)
--
Alan Burlison
--
Re: any feedback on this?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Bryan Pendleton wrote:
>> How do people work around this at present? I absolutely *must* have
>> case-insensitive indexed searches in my application.
>
> I store my critical string fields twice, in two separate columns, one
> column has the user-desired case, and the other column has the same data
> in all upper case.
>
> Then, when I want to search without considering case, I take my search
> terms, convert them to all upper case, and search against the
> upper-case column.
>
> thanks,
>
> bryan
>
>
Some people have found it useful to add a CHECK constraint to the table
to verify that wrong values aren't leaking into the upper-case column
from somewhere in their application. Something like the following can
give you more peace of mind:
create table t
(
lastName varchar( 50 ),
normalizedLastName varchar( 50 ),
check ( normalizedLastName = upper( lastName ) )
);
Hope this helps,
-Rick
Re: any feedback on this?
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> How do people work around this at present? I absolutely *must* have
> case-insensitive indexed searches in my application.
I store my critical string fields twice, in two separate columns, one
column has the user-desired case, and the other column has the same data
in all upper case.
Then, when I want to search without considering case, I take my search
terms, convert them to all upper case, and search against the upper-case column.
thanks,
bryan
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Suavi Ali Demir wrote:
> for case insensitive index, you can have another column that holds lower-case version of your original column. you can create an index on that lowercase column and use it in your sql. you can use triggers to maintain the lowercase column.
Something like this seems to work:
create table test (a varchar(20), ua varchar(20));
create trigger t1 after insert on test for each row
update test set ua = upper(a);
create trigger t2 after update of a on test for each row
update test set ua = upper(a);
Does that look sane?
--
Alan Burlison
--
Re: any feedback on this?
Posted by Suavi Ali Demir <de...@yahoo.com>.
for case insensitive index, you can have another column that holds lower-case version of your original column. you can create an index on that lowercase column and use it in your sql. you can use triggers to maintain the lowercase column.
ali
--- On Mon, 6/23/08, Alan Burlison <Al...@sun.com> wrote:
> From: Alan Burlison <Al...@sun.com>
> Subject: Re: any feedback on this?
> To: "Derby Discussion" <de...@db.apache.org>
> Date: Monday, June 23, 2008, 4:42 PM
> Rick Hillegas wrote:
>
> > I have commented on the issue. Your latest proposal
> sounds to me like
> > DERBY-455 (expression indexes). That would be a very
> elegant, powerful
> > feature but I think that DERBY-481 (generated columns)
> entails less
> > effort and gives us most of the power of expression
> indexes. As you note
> > in your comment, generated columns have limited value
> if you don't index
> > them.
>
> How do people work around this at present? I absolutely
> *must* have
> case-insensitive indexed searches in my application.
>
> --
> Alan Burlison
> --
Re: any feedback on this?
Posted by Alan Burlison <Al...@sun.com>.
Rick Hillegas wrote:
> I have commented on the issue. Your latest proposal sounds to me like
> DERBY-455 (expression indexes). That would be a very elegant, powerful
> feature but I think that DERBY-481 (generated columns) entails less
> effort and gives us most of the power of expression indexes. As you note
> in your comment, generated columns have limited value if you don't index
> them.
How do people work around this at present? I absolutely *must* have
case-insensitive indexed searches in my application.
--
Alan Burlison
--
Re: any feedback on this?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Geoff hendrey wrote:
> I posted a comment with a suggestion on an approach for dealing with
> case-insensitive comparisons.
>
> Any feedback?
> https://issues.apache.org/jira/browse/DERBY-1748?focusedCommentId=12606166#action_12606166
>
Hi Geoff,
I have commented on the issue. Your latest proposal sounds to me like
DERBY-455 (expression indexes). That would be a very elegant, powerful
feature but I think that DERBY-481 (generated columns) entails less
effort and gives us most of the power of expression indexes. As you note
in your comment, generated columns have limited value if you don't index
them.
Thanks,
-Rick
Re: any feedback on this?
Posted by Mark Thornton <mt...@optrak.co.uk>.
Geoff hendrey wrote:
> I posted a comment with a suggestion on an approach for dealing with
> case-insensitive comparisons.
>
> Any feedback?
> https://issues.apache.org/jira/browse/DERBY-1748?focusedCommentId=12606166#action_12606166
>
I find declaring the collation when defining the column is much more
useful as it then automatically applies to all comparisions/indexes
involving the column. Allowing more options when specifying the database
collation would be a useful starting point (as suggested by Debrunner).
Mark Thornton