Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Hamish Symington
Hi - > Adding the warning to the explain plan output should work well. It'd be a good place for it, I'd agree - it'd've saved me a trawl through the documentation and posting to this list! :) Thanks, Hamish ___ sqlite-users mailing list

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Jim Morris
Adding the warning to the explain plan output should work well. ... And yet the coding mistake in the SQL query was very subtle. It makes me wonder if we shouldn't somehow come up with a "warning" mechanism in SQLite to give developers a heads-up on error-prone constructs, such as using ==

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Richard Hipp
On Thu, Oct 11, 2012 at 10:20 AM, Pavel Ivanov wrote: > On Thu, Oct 11, 2012 at 7:09 AM, Hamish Symington > wrote: > >> Note that I was mistaken earlier when I said that "X=Y" and "Y=X" should > >> result in the same answer. If X and Y have

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Pavel Ivanov
On Thu, Oct 11, 2012 at 7:09 AM, Hamish Symington wrote: >> Note that I was mistaken earlier when I said that "X=Y" and "Y=X" should >> result in the same answer. If X and Y have different default collating >> sequences, then X=Y does not mean the same thing as Y=X

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Hamish Symington
Hello, > In your schema, SaleItem_SaleUUID collates using NOCASE and Picture_UUID > collates using BINARY. I think you mean SaleItem_PictureUUID, not SaleItem_SaleUUID, but yes. > Note that I was mistaken earlier when I said that "X=Y" and "Y=X" should > result in the same answer. If X and Y

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Richard Hipp
On Wed, Oct 10, 2012 at 10:53 AM, Hamish Symington < ham...@lightbluesoftware.com> wrote: > > If I perform the query > > SELECT SaleItem.*, Picture_FileName FROM SaleItem LEFT JOIN Picture ON > SaleItem_PictureUUID=Picture_UUID WHERE SaleItem_SaleUUID = 'DAB8FE97- > D308-4809-B496-E55142DC05B5' >

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Richard Hipp
On Thu, Oct 11, 2012 at 3:13 AM, Hamish Symington < ham...@lightbluesoftware.com> wrote: > > > If you run ANALYZE on your database, SQLite will have more information > with > > which to estimate the run-time of each plan, and is more likely to choose > > the faster on. At the very least, it much

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Hamish Symington
Hello, > Perhaps try the forthcoming 3.7.15 preview: > http://www.sqlite.org/sqlite3-20121009.zip > See "Please test the latest SQLite enhancements" for details: > http://comments.gmane.org/gmane.comp.db.sqlite.general/77259 Still present in that, as well. I should note (from further

Re: [sqlite] Ordering of fields in a join

2012-10-11 Thread Hamish Symington
Hi - >> I was under the impression that the order of fields in the ON clause >> doesn't matter. Is this impression incorrect? > It doesn't matter for the answer. You get the same result either way, > right? Just one way is faster than the other. True, I do get the same result. > If

Re: [sqlite] Ordering of fields in a join

2012-10-10 Thread Petite Abeille
On Oct 10, 2012, at 7:30 PM, Hamish Symington wrote: >>> I was under the impression that the order of fields in the ON clause >>> doesn't matter. Is this impression incorrect? >> >> You should be correct, and the latest version of SQLite has some changes in >>

Re: [sqlite] Ordering of fields in a join

2012-10-10 Thread Richard Hipp
On Wed, Oct 10, 2012 at 10:53 AM, Hamish Symington < ham...@lightbluesoftware.com> wrote: > > I was under the impression that the order of fields in the ON clause > doesn't matter. Is this impression incorrect? > It doesn't matter for the answer. You get the same result either way, right? Just

Re: [sqlite] Ordering of fields in a join

2012-10-10 Thread Hamish Symington
>> I was under the impression that the order of fields in the ON clause doesn't >> matter. Is this impression incorrect? > > You should be correct, and the latest version of SQLite has some changes in > which look relevant to this issue. Can you check which version of SQLite > you're using

Re: [sqlite] Ordering of fields in a join

2012-10-10 Thread Simon Slavin
On 10 Oct 2012, at 3:53pm, Hamish Symington wrote: > I was under the impression that the order of fields in the ON clause doesn't > matter. Is this impression incorrect? You should be correct, and the latest version of SQLite has some changes in which look

[sqlite] Ordering of fields in a join

2012-10-10 Thread Hamish Symington
Hello, I've made a new database with the latest version of SQLite, and added two tables, created thus: CREATE TABLE SaleItem (SaleItem_ID integer NOT NULL PRIMARY KEY, SaleItem_UUID varchar NOT NULL DEFAULT '', SaleItem_SaleUUID varchar NOT NULL DEFAULT '', SaleItem_PictureUUID varchar NOT NULL