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 Danny Gallagher <DG...@pathfire.com> on 2011/12/06 16:45:58 UTC
dash in a varchar column - effect on select statement
Not sure why it is behaving this way, column is just varchar.
SELECT * FROM TABLE WHERE NAME = 'LKFSASP'
The only row in the table contains a NAME = 'LKFS-ASP'
Those two values aren't equal, but the row is returned from the select.
What am I missing?
Thanks
RE: dash in a varchar column - effect on select statement
Posted by Danny Gallagher <DG...@pathfire.com>.
Rick,
Thanks for the point in the collation direction.
If I recreate the database with TERRITORY_BASED:SECONDARY
I get the behavior I want (at least as much as I've tested) the documentation gives an example about accented letters being considered equal under PRIMARY and not under SECONDARY.
Also states ' The exact interpretation of the strength part of the attribute depends upon the locale.'
Which doesn't really give me a warm and fuzzy, but looks like the way I'll go, because I need to keep the case part.
Again, thanks for the reply. Cheers!
Danny
-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com]
Sent: Tuesday, December 06, 2011 3:18 PM
To: derby-user@db.apache.org
Subject: Re: dash in a varchar column - effect on select statement
Hi Danny,
I can reproduce your results if I create the database with collation=TERRITORY_BASED:PRIMARY. I am not an expert on the behavior of this collation. However, I see that this issue came up last year on derby-user in this thread:
http://comments.gmane.org/gmane.comp.apache.db.derby.user/12851
Hope this helps,
-Rick
On 12/6/11 9:10 AM, Danny Gallagher wrote:
> Actually yes, I am using a collation: TERRITORY_BASED:PRIMARY. In this specific case would be en_US
>
> Which from my understanding and reading of the documentation should effect case, not the existence or nonexistence of characters.
>
> Thanks,
>
> Danny
>
> -----Original Message-----
> From: Rick Hillegas [mailto:rick.hillegas@oracle.com]
> Sent: Tuesday, December 06, 2011 11:11 AM
> To: derby-user@db.apache.org
> Subject: Re: dash in a varchar column - effect on select statement
>
> On 12/6/11 7:45 AM, Danny Gallagher wrote:
>> SELECT * FROM TABLE WHERE NAME = 'LKFSASP'
>>
> Hi Danny,
>
> I can't reproduce this problem (see the script below, which runs correctly for me). Are you using a special collation in this database?
>
> connect 'jdbc:derby:memory:db;create=true';
>
> create table t( a varchar( 50 ) );
> insert into t( a ) values ( 'LKFS-ASP' );
>
> -- no results
> select * from t where a = 'LKFSASP';
>
> insert into t( a ) values ( 'LKFSASP' );
>
> -- returns 1 row
> select * from t where a = 'LKFSASP';
>
> Thanks,
> -Rick
>
Re: dash in a varchar column - effect on select statement
Posted by Rick Hillegas <ri...@oracle.com>.
Hi Danny,
I can reproduce your results if I create the database with
collation=TERRITORY_BASED:PRIMARY. I am not an expert on the behavior of
this collation. However, I see that this issue came up last year on
derby-user in this thread:
http://comments.gmane.org/gmane.comp.apache.db.derby.user/12851
Hope this helps,
-Rick
On 12/6/11 9:10 AM, Danny Gallagher wrote:
> Actually yes, I am using a collation: TERRITORY_BASED:PRIMARY. In this specific case would be en_US
>
> Which from my understanding and reading of the documentation should effect case, not the existence or nonexistence of characters.
>
> Thanks,
>
> Danny
>
> -----Original Message-----
> From: Rick Hillegas [mailto:rick.hillegas@oracle.com]
> Sent: Tuesday, December 06, 2011 11:11 AM
> To: derby-user@db.apache.org
> Subject: Re: dash in a varchar column - effect on select statement
>
> On 12/6/11 7:45 AM, Danny Gallagher wrote:
>> SELECT * FROM TABLE WHERE NAME = 'LKFSASP'
>>
> Hi Danny,
>
> I can't reproduce this problem (see the script below, which runs correctly for me). Are you using a special collation in this database?
>
> connect 'jdbc:derby:memory:db;create=true';
>
> create table t( a varchar( 50 ) );
> insert into t( a ) values ( 'LKFS-ASP' );
>
> -- no results
> select * from t where a = 'LKFSASP';
>
> insert into t( a ) values ( 'LKFSASP' );
>
> -- returns 1 row
> select * from t where a = 'LKFSASP';
>
> Thanks,
> -Rick
>
RE: dash in a varchar column - effect on select statement
Posted by Danny Gallagher <DG...@pathfire.com>.
Actually yes, I am using a collation: TERRITORY_BASED:PRIMARY. In this specific case would be en_US
Which from my understanding and reading of the documentation should effect case, not the existence or nonexistence of characters.
Thanks,
Danny
-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com]
Sent: Tuesday, December 06, 2011 11:11 AM
To: derby-user@db.apache.org
Subject: Re: dash in a varchar column - effect on select statement
On 12/6/11 7:45 AM, Danny Gallagher wrote:
>
> SELECT * FROM TABLE WHERE NAME = 'LKFSASP'
>
Hi Danny,
I can't reproduce this problem (see the script below, which runs correctly for me). Are you using a special collation in this database?
connect 'jdbc:derby:memory:db;create=true';
create table t( a varchar( 50 ) );
insert into t( a ) values ( 'LKFS-ASP' );
-- no results
select * from t where a = 'LKFSASP';
insert into t( a ) values ( 'LKFSASP' );
-- returns 1 row
select * from t where a = 'LKFSASP';
Thanks,
-Rick
Re: dash in a varchar column - effect on select statement
Posted by Rick Hillegas <ri...@oracle.com>.
On 12/6/11 7:45 AM, Danny Gallagher wrote:
>
> SELECT * FROM TABLE WHERE NAME = 'LKFSASP'
>
Hi Danny,
I can't reproduce this problem (see the script below, which runs
correctly for me). Are you using a special collation in this database?
connect 'jdbc:derby:memory:db;create=true';
create table t( a varchar( 50 ) );
insert into t( a ) values ( 'LKFS-ASP' );
-- no results
select * from t where a = 'LKFSASP';
insert into t( a ) values ( 'LKFSASP' );
-- returns 1 row
select * from t where a = 'LKFSASP';
Thanks,
-Rick