Re: [sqlite] possible join bug/regression with sqlite-3.8.1

[Ferdinand Hübner]

On Mon, Oct 28, 2013 at 9:44 PM, Richard Hipp  wrote:
> The fix for this problem has been checked in:
>
> http://www.sqlite.org/src/info/9aac4e588c
>
> This problem was actually introduced in 3.8.0 but it was unreachable until
> some additional enhancements were added in 3.8.1.  So 3.8.1 didn't actually
> cause the bug, it merely exposed it.
>
> This is a rather obscure problem, so we do not (currently) intend to rush
> out a patch release.  The trunk is stable and usable if anybody really
> needs a fix right away.

Thank you, Richard.
I compiled today's trunk and it seems to fix the problem.

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

Re: [sqlite] possible join bug/regression with sqlite-3.8.1

[Richard Hipp]

On Sat, Oct 26, 2013 at 11:17 AM, Ferdinand Hübner <
ferdinand.hueb...@gmail.com> wrote:

> A query against the xbmc database doesn't return results anymore when
> sqlite-3.8.1 is used. With sqlite-3.8.0.2, the query returns results
> against the identical database just fine.
>

Confirming that there was a bug in 3.8.1 related to transitive WHERE-clause
constraints on multi-table queries with lots of LEFT JOIN operators.  The
ticket for the bug is here:

http://www.sqlite.org/src/info/c620261b5b5dc

The fix for this problem has been checked in:

http://www.sqlite.org/src/info/9aac4e588c

This problem was actually introduced in 3.8.0 but it was unreachable until
some additional enhancements were added in 3.8.1.  So 3.8.1 didn't actually
cause the bug, it merely exposed it.

This is a rather obscure problem, so we do not (currently) intend to rush
out a patch release.  The trunk is stable and usable if anybody really
needs a fix right away.

-- 
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] possible join bug/regression with sqlite-3.8.1

[Ferdinand Hübner]

On Sat, Oct 26, 2013 at 6:17 PM, Richard Hipp  wrote:
>
> We are looking into the problem.
>
> Meanwhile, I noticed some rather egregious inefficiencies in the XBMC
> schema and would like to contract the XBMC developers about this.  I spent
> 5 minutes clicking around on various XBMC websites trying to find an email
> address or other means of contact, with no success.  Are you able put us
> (the SQLite developers) in touch with the XBMC developers?  You can have
> the XBMC developers send me direct email to the address shown on my
> signature line below?  Tnx.

Thank you. I asked the developer on the bug tracker to get in touch with you.
I think the most efficient way to get in touch with an xbmc developer
would be opening an issue on their bug tracker.

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

Re: [sqlite] possible join bug/regression with sqlite-3.8.1

[Richard Hipp]

On Sat, Oct 26, 2013 at 11:17 AM, Ferdinand Hübner <
ferdinand.hueb...@gmail.com> wrote:

>
> Please note that I'm just an xbmc user, not a developer.
>

We are looking into the problem.

Meanwhile, I noticed some rather egregious inefficiencies in the XBMC
schema and would like to contract the XBMC developers about this.  I spent
5 minutes clicking around on various XBMC websites trying to find an email
address or other means of contact, with no success.  Are you able put us
(the SQLite developers) in touch with the XBMC developers?  You can have
the XBMC developers send me direct email to the address shown on my
signature line below?  Tnx.

-- 
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] possible join bug/regression with sqlite-3.8.1

[Simon Slavin]

On 26 Oct 2013, at 4:17pm, Ferdinand Hübner  wrote:

> If you need any more information, don't hesitate to ask.

Ignoring the fact that two different versions of SQLite give different results, 
for a minute, can you answer these questions:

Is one of these results clearly right and the other clearly wrong, or could you 
make arguments for either one ?

If you remove the redundant JOIN (and anything else you feel is redundant) is 
the result you get in the current version of SQLite correct ?

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

[sqlite] possible join bug/regression with sqlite-3.8.1

[Ferdinand Hübner]

Hello,

A query against the xbmc database doesn't return results anymore when
sqlite-3.8.1 is used. With sqlite-3.8.0.2, the query returns results
against the identical database just fine.
Please note that I'm just an xbmc user, not a developer. It's been quite a
while since I last posted to a mailing list, I hope the gmail web client
doesn't violate netiquette.

You can find a dump of a minimal database to reproduce the problem here:
https://gist.github.com/ferdinandhuebner/d0f3aad4b74e38940c3e
The query that doesn't return any rows:
https://gist.github.com/ferdinandhuebner/1bb14de6a58699afbebe

You can find a bug report on xbmc's trac: http://trac.xbmc.org/ticket/14646

To summarize what we figured out so far:
- The problem with the query seems to be the join on the "files" table
- The join on the "files" table is redundant because it is already joined
in the view "episodeview"
- If you replace the join with a left outer join the query returns results
and the aggregate on files.playCount is not zero
- If you join with a cast to integer the query returns results even though
the datatypes are already of type integer

If you need any more information, don't hesitate to ask.

Thank you for your help,
Ferdinand
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users