Thanks for your help.
On 6/16/10 8:11 AM, "Pavel Ivanov" <paiva...@gmail.com> 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 <jeff.w...@quest.com> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
