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

Reply via email to