Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug!)

2011-08-02 Thread Simon Slavin
On 2 Aug 2011, at 9:06am, Igor Sereda wrote: > Thanks, Simon - it's a bug then. Hope it will get pulled into the bug > tracker. > Simon Slavin-3 wrote: > >> The spec for '<=' should say that comparing any number with NULL always >> gives a NULL result. If SQLite is doing anything apart from th

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug!)

2011-08-02 Thread Igor Sereda
Thanks, Simon - it's a bug then. Hope it will get pulled into the bug tracker. Igor Simon Slavin-3 wrote: > > > On 2 Aug 2011, at 1:10am, Igor Sereda wrote: > >> To my humble knowledge, operations with NULL have well-defined semantics, >> both in SQL-you-name-it standards and in SQLite. "A

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Simon Slavin
On 2 Aug 2011, at 1:10am, Igor Sereda wrote: > To my humble knowledge, operations with NULL have well-defined semantics, > both in SQL-you-name-it standards and in SQLite. "A < B" may have three > results - TRUE, FALSE and NULL. It doesn't matter whether you can make any > sense of it - it's the

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Igor Sereda
Simon, Michael - To my humble knowledge, operations with NULL have well-defined semantics, both in SQL-you-name-it standards and in SQLite. "A < B" may have three results - TRUE, FALSE and NULL. It doesn't matter whether you can make any sense of it - it's the spec ;) Therefore I'm trying to rep

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Simon Slavin
On 1 Aug 2011, at 10:45pm, Black, Michael (IS) wrote: > If it's meaningless then shouldn't it be a syntax error? It's about as meaningless as X <= maxreal so it would take quite a lot of processing time to identify it as meaningless. Not sure as if it's worth the processing time. Any decent

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Black, Michael (IS)
avin [slav...@bigfraud.org] Sent: Monday, August 01, 2011 4:43 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?) On 1 Aug 2011, at 9:47pm, Igor Sereda wrote: > So - who else thinks it's a bug? The SQL standard

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Simon Slavin
On 1 Aug 2011, at 9:47pm, Igor Sereda wrote: > So - who else thinks it's a bug? The SQL standard says 'NULL' means 'I don't know' or 'value missing' or something of the kind. So using a comparison like X > NULL doesn't mean anything, since there can't be a well-ordering principle for a miss

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Igor Sereda
Thanks Jay, That's a good hint about the origin of the problem. However, you refer to the sort order, but the problem is with WHERE statement. Since numeric comparison with NULL always evaluates to NULL (see section 4.0 of the link you gave me), a statement like "SELECT * FROM table WHERE valu

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Jay A. Kreibich
On Mon, Aug 01, 2011 at 12:34:33PM -0700, Igor Sereda scratched on the wall: > > Hello, > > I'm seeing strange input given into xBestIndex method of my virtual table. > > I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from SQLite > 3.7.4 to 3.7.7.1. A couple of failed tests unc

[sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Igor Sereda
Hello, I'm seeing strange input given into xBestIndex method of my virtual table. I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from SQLite 3.7.4 to 3.7.7.1. A couple of failed tests uncovered that there's a problem when searching a simple virtual table with constraints that c