[sqlite] non-aggregate columns in aggregate queries

[Jeff Rogers]

Hi all,

It's a documented quirk that sqlite allows the inclusion of 
non-aggregate, non-group by columns in an agregate query:


https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

The benefits aside, it's not "standard sql", or at a minimum most other 
databases do not allow it.  Could a pragma or build-time flag be added 
to enforce "standard" behavior, i.e., raising an error rather than 
handling it as currently documented?



-J

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

[sqlite] installation bug

[Jeff Rogers]

I built a freshly updated sqlite 
(629a42d47a0d8f73de900f469845ce800bdb8959) and got an error loading it 
into tcl:
attempt to provide package sqlite3 3.7.11 failed: package sqlite3 3.7.15 
provided instead


It looks like pkgIndex.tcl is created by make, but not removed by 'make 
clean' or 'make distclean', so an old version was sticking around.


Removing pkgIndex.tcl by hand fixed the problem.

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

Re: [sqlite] Question about database design

[Jeff Rogers]

Nicolas Williams wrote:
> On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote:
 >
>> SQLite seems to do quite poorly performance-wise with fully-normalized
>> attribute tables like this, when you want to query against multiple
>> attributes.  My timing comparisons with postgres show sqlite to be as
>> much as 10x-15x slower than pg.
>>
>> My timing code is at http://paste.tclers.tk/2346
>
> You need an index on props(id) -- you always need an index on columns
> that form a foreign key.  SQLite3 requires that if you want to cascade
> deletions.

Ok, that caught me by surprise, but it improved things a lot.  With that 
index sqlite is now significantly faster than pg on 3 of the tests, but 
still similar to or slower than pg on the remaining 2 - in my code, "sx" 
and "sx3".   (I can't be sure of the exact timings because I can't run 
sqlite and the pg server on the same machine)

Any idea why pg does ok on these queries without the extra index - 
Maybe they're created by default?  SQLIte doesn't create any indexes 
automatically on primary key fields or anything else, correct?

> Without that index your joins will use full table scans.
>
> Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 =
> t2.col2 than to put the join conditions in the WHERE clause, as that's
> much easier to read.  But I can see that you have JOINs using that
> un-indexed column.

I wrote this test code some time ago, I think originally against a 
database that didn't support JOIN syntax.  I would use that were I 
rewriting it now.  (I also don't remember exactly what conditions I was 
trying to exercise, but I think it was the case where two attributes 
each match a large set but the intersection of those sets is small)

Thanks,
-J

>
>> This is a synthetic test, but I ran across the issue in a real
>> application.  I'm not sure what else I can do do optimize the queries;
>> using a denormalized table is the only thing that seems to help.
>
> Add the missing index.
>
> Nico

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

Re: [sqlite] Question about database design

[Jeff Rogers]

Andreas Kupries wrote:

> It seems to me that you are looking for
>   http://en.wikipedia.org/wiki/Database_normalization
>

SQLite seems to do quite poorly performance-wise with fully-normalized 
attribute tables like this, when you want to query against multiple 
attributes.  My timing comparisons with postgres show sqlite to be as 
much as 10x-15x slower than pg.

My timing code is at http://paste.tclers.tk/2346

This is a synthetic test, but I ran across the issue in a real 
application.  I'm not sure what else I can do do optimize the queries; 
using a denormalized table is the only thing that seems to help.

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