Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions
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
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
> 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
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