Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jeff Webb]

Thanks for your help.


On 6/16/10 8:11 AM, "Pavel Ivanov"  wrote:

> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?

I'm not developer of SQLite and don't know what did they intend to do.
But applying common sense I'd say that behavior of 3.0.8 was incorrect
and it's fixed in 3.6.23. To get the same result as you used to you
can write your query like this:

select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo' collate nocase;

Here your intention would be explicit and independent of column definition.


Pavel

On Tue, Jun 15, 2010 at 6:46 PM, Jeff Webb  wrote:
> I scanned through the bug list on the website and didn't see one that was 
> related to this, so please forgive me if this has already been answered.
>
> We have created a function that takes a string and returns a substring that 
> is split on a given character (I'll provide the source below), called 
> splitstr.  In sqlite 3.0.8 we used this function and found that comparing the 
> output to a given string would use the case-sensitivity of the given string.  
> Meaning that if we used "select * from foo where splitstr(foo.col1, '@', 1 ) 
> = 'foo';" and col1 of table foo was defined as "text collate nocase" then the 
> comparisons would be case insensitive.  This was the behavior in 3.0.8.  It 
> is no longer the behavior in 3.6.23.
>
> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?
>
> To reproduce this I create the following table:
>
>> create table foo( key integer primary key, value text collate nocase );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>
>> select * from foo where splitstr( value, '@', 1 ) = 'foo';
> 4|f...@bar
>
> However, in 3.0.8  I would get all records.
>
> Splitstr() is a very simple function:
>
> static void splitstrFunc(  sqlite3_context *context,  int argc,  
> sqlite3_value **argv ){  const char *z = NULL;  char   *temp = NULL;  
> const char *p1 = NULL;  intp2 = 0;  assert( argc==3 );  z = 
> sqlite3_value_text(argv[0]);  if( z==0 )  return;  p1 = 
> sqlite3_value_text(argv[1]);  if( p1 == 0 )  return;  p2 = 
> sqlite3_value_int(argv[2]);  if( (temp = strchr( z, p1[0] )) == NULL )  
> return;  *temp = '\0';  temp++;  if( p2 == 2 )  z = temp;  else  if( p2 
> != 1 )  /* must specify either first or second */  return;  
> sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
> And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
>
> (FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
>
> Thanks for your help
>
> Jeff Webb
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jay A. Kreibich]

On Tue, Jun 15, 2010 at 03:46:17PM -0700, Jeff Webb scratched on the wall:

> Is this working as designed and the 3.0.8 behavior was incorrect? 

  It sounds like it.  Collations are typically not "promoted" through
  operations.  Having one promoted from a function parameter to the
  result seems incorrect.  I'm can't offer a definitive answer, however.

> > select * from foo where splitstr( value, '@', 1 ) = 'foo';
> 4|f...@bar

  As a workaround, try:

  SELECT * FROM foo WHERE splitstr( value, '@', 1 ) COLLATE NOCASE = 'foo';


   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Pavel Ivanov]

> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?

I'm not developer of SQLite and don't know what did they intend to do.
But applying common sense I'd say that behavior of 3.0.8 was incorrect
and it's fixed in 3.6.23. To get the same result as you used to you
can write your query like this:

select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo' collate nocase;

Here your intention would be explicit and independent of column definition.


Pavel

On Tue, Jun 15, 2010 at 6:46 PM, Jeff Webb  wrote:
> I scanned through the bug list on the website and didn't see one that was 
> related to this, so please forgive me if this has already been answered.
>
> We have created a function that takes a string and returns a substring that 
> is split on a given character (I'll provide the source below), called 
> splitstr.  In sqlite 3.0.8 we used this function and found that comparing the 
> output to a given string would use the case-sensitivity of the given string.  
> Meaning that if we used "select * from foo where splitstr(foo.col1, '@', 1 ) 
> = 'foo';" and col1 of table foo was defined as "text collate nocase" then the 
> comparisons would be case insensitive.  This was the behavior in 3.0.8.  It 
> is no longer the behavior in 3.6.23.
>
> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?
>
> To reproduce this I create the following table:
>
>> create table foo( key integer primary key, value text collate nocase );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>
>> select * from foo where splitstr( value, '@', 1 ) = 'foo';
> 4|f...@bar
>
> However, in 3.0.8  I would get all records.
>
> Splitstr() is a very simple function:
>
> static void splitstrFunc(  sqlite3_context *context,  int argc,  
> sqlite3_value **argv ){  const char *z = NULL;  char       *temp = NULL;  
> const char *p1 = NULL;  int        p2 = 0;  assert( argc==3 );  z = 
> sqlite3_value_text(argv[0]);  if( z==0 )      return;  p1 = 
> sqlite3_value_text(argv[1]);  if( p1 == 0 )      return;  p2 = 
> sqlite3_value_int(argv[2]);  if( (temp = strchr( z, p1[0] )) == NULL )      
> return;  *temp = '\0';  temp++;  if( p2 == 2 )      z = temp;  else  if( p2 
> != 1 )  /* must specify either first or second */      return;  
> sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
> And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
>
> (FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
>
> Thanks for your help
>
> Jeff Webb
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jeff Webb]

I scanned through the bug list on the website and didn't see one that was 
related to this, so please forgive me if this has already been answered.

We have created a function that takes a string and returns a substring that is 
split on a given character (I'll provide the source below), called splitstr.  
In sqlite 3.0.8 we used this function and found that comparing the output to a 
given string would use the case-sensitivity of the given string.  Meaning that 
if we used "select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo';" and 
col1 of table foo was defined as "text collate nocase" then the comparisons 
would be case insensitive.  This was the behavior in 3.0.8.  It is no longer 
the behavior in 3.6.23.

Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this a 
valid bug?

To reproduce this I create the following table:

> create table foo( key integer primary key, value text collate nocase );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );

> select * from foo where splitstr( value, '@', 1 ) = 'foo';
4|f...@bar

However, in 3.0.8  I would get all records.

Splitstr() is a very simple function:

static void splitstrFunc(  sqlite3_context *context,  int argc,  sqlite3_value 
**argv ){  const char *z = NULL;  char   *temp = NULL;  const char *p1 = 
NULL;  intp2 = 0;  assert( argc==3 );  z = sqlite3_value_text(argv[0]); 
 if( z==0 )  return;  p1 = sqlite3_value_text(argv[1]);  if( p1 == 0 )  
return;  p2 = sqlite3_value_int(argv[2]);  if( (temp = strchr( z, p1[0] )) == 
NULL )  return;  *temp = '\0';  temp++;  if( p2 == 2 )  z = temp;  else 
 if( p2 != 1 )  /* must specify either first or second */  return;  
sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:

(FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),

Thanks for your help

Jeff Webb
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users