Re: [sqlite] substr fails in a query: looks like a bug

[Eric Promislow]

Thanks for the interest, but I now can't repro the bug using pure sqlite3,
and only trigger it using Python (2.6) with its sqlite3 module.

- Eric

On Tue, Apr 5, 2011 at 5:21 PM, Eric Promislow wrote:

> This won't be a trivial case to reproduce -- I need to create two tables,
> with several rows, and my main db tool is broken right now.  I did find a
> workaround,
> groveling over the results in Python.
>
> If I bind the values to literals like so:
> select "... str1" as p1_path and "... str2" as p2_path where
>
> the substr clause works.  So I do need to create a real table to
> repro this.
>
>
> On Tue, Apr 5, 2011 at 5:12 PM, Igor Tandetnik wrote:
>
>> On 4/5/2011 6:59 PM, Eric Promislow wrote:
>> > Notice the clause in the middle of the query:
>> >and (substr(p2.path, length(p1.path) + 1, 1) = "/"
>> >  or substr(p2.path, length(p1.path) + 1, 1) != "/")
>> >
>> > If I comment out this full clause, the query returns the expected result
>> > set.
>> >
>> > But if I leave it uncommented, the query returns an empty set.  But the
>> > clause
>> > should be tautologically true -- either the character after the prefix
>> is
>> > "/" or it
>> > isn't.
>>
>> The third possiblity is that substr() returns null, in which case both
>> comparisons will be false.
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] substr fails in a query: looks like a bug

[Eric Promislow]

This won't be a trivial case to reproduce -- I need to create two tables,
with several rows, and my main db tool is broken right now.  I did find a
workaround,
groveling over the results in Python.

If I bind the values to literals like so:
select "... str1" as p1_path and "... str2" as p2_path where

the substr clause works.  So I do need to create a real table to
repro this.

On Tue, Apr 5, 2011 at 5:12 PM, Igor Tandetnik  wrote:

> On 4/5/2011 6:59 PM, Eric Promislow wrote:
> > Notice the clause in the middle of the query:
> >and (substr(p2.path, length(p1.path) + 1, 1) = "/"
> >  or substr(p2.path, length(p1.path) + 1, 1) != "/")
> >
> > If I comment out this full clause, the query returns the expected result
> > set.
> >
> > But if I leave it uncommented, the query returns an empty set.  But the
> > clause
> > should be tautologically true -- either the character after the prefix is
> > "/" or it
> > isn't.
>
> The third possiblity is that substr() returns null, in which case both
> comparisons will be false.
> --
> Igor Tandetnik
>
> ___
> 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] substr fails in a query: looks like a bug

[Igor Tandetnik]

On 4/5/2011 6:59 PM, Eric Promislow wrote:
> Notice the clause in the middle of the query:
>and (substr(p2.path, length(p1.path) + 1, 1) = "/"
>  or substr(p2.path, length(p1.path) + 1, 1) != "/")
>
> If I comment out this full clause, the query returns the expected result
> set.
>
> But if I leave it uncommented, the query returns an empty set.  But the
> clause
> should be tautologically true -- either the character after the prefix is
> "/" or it
> isn't.

The third possiblity is that substr() returns null, in which case both 
comparisons will be false.
-- 
Igor Tandetnik

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

Re: [sqlite] substr fails in a query: looks like a bug

[Richard Hipp]

On Tue, Apr 5, 2011 at 6:59 PM, Eric Promislow wrote:

> Notice the clause in the middle of the query:
>  and (substr(p2.path, length(p1.path) + 1, 1) = "/"
>or substr(p2.path, length(p1.path) + 1, 1) != "/")
>
> If I comment out this full clause, the query returns the expected result
> set.
>
> But if I leave it uncommented, the query returns an empty set.  But the
> clause
> should be tautologically true -- either the character after the prefix is
> "/" or it
> isn't.
>

Can you please post values of p2.path and p1.path for which the above
expression returns false?

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

Re: [sqlite] substr fails in a query: looks like a bug

[Simon Slavin]

On 5 Apr 2011, at 11:59pm, Eric Promislow wrote:

> Notice the clause in the middle of the query:
>  and (substr(p2.path, length(p1.path) + 1, 1) = "/"
>or substr(p2.path, length(p1.path) + 1, 1) != "/")
> 
> If I comment out this full clause, the query returns the expected result
> set.

Could you make us a demonstration sample ?  Make a database with one row in and 
the simplest SELECT you can that gives one result on Windows and the other in 
Unix.

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

[sqlite] substr fails in a query: looks like a bug

[Eric Promislow]

I have full paths of a set of resources in a database, and am using
substrings to find arbitrary hierarchical resources.  Worked fine on
Windows using Sqlite3 3.6.5, but on Linux with 3.5.9 the query fails.

The schema includes tables:
paths:(integer id, string path)
common_details:(integer path_id (fkey), string type, string name)

select p3.id, cd3.name, cd2.name, p2.path as p2, length(p1.path) as len_p1,
p1.path as p1, substr(p2.path, length(p1.path) + 1, 1) as p1Char
from paths as p1, paths as p2, paths as p3,
 common_details as cd3, common_details as cd2, common_details as cd1
where cd1.name = "Abbreviations" and cd1.type = "folder"
  and cd2.type = "folder" and (cd2.name like "Perl" or cd2.name like
"General")
  and cd1.path_id = p1.id
  and cd2.path_id = p2.id
  and substr(p2.path, 1, length(p1.path)) = p1.path
  and (substr(p2.path, length(p1.path) + 1, 1) == "/"
or substr(p2.path, length(p1.path) + 1, 1) != "/")
  and cd3.type = "snippet" and cd3.name like "fore"
  and cd3.path_id = p3.id
  and substr(p3.path, 1, length(p2.path)) = p2.path ;

Notice the clause in the middle of the query:
  and (substr(p2.path, length(p1.path) + 1, 1) = "/"
or substr(p2.path, length(p1.path) + 1, 1) != "/")

If I comment out this full clause, the query returns the expected result
set.

But if I leave it uncommented, the query returns an empty set.  But the
clause
should be tautologically true -- either the character after the prefix is
"/" or it
isn't.

Looks like a bug to me.  The code ran fine on Windows (where I used
only the '=' test, not the '!=' test. I do have a workaround -- it's to
return paths
in my result set and to the substring test in Python instead of sqlite.  But
I'd prefer having sqlite run this code.

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