Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi Max

Thanks for the reply.

On 26/11/2010 7:11 PM, Max Vlasov wrote:
> Mohit said that he uses a someone's db, so I can imagine a possibility that
> with two indexes ...
> CREATE INDEX IDX1 on tx(name ASC);
> CREATE INDEX IDX2 on tx(type, name ASC);
> ... the creator of database wanted to search for a name regardless of the
> type with a help of first index, and in the context of chosen type with the
> second. So, Mohit, if some unknown queries or code involved I'd better leave
> them as they are
I have access to all the queries that are planned - I will confirm that 
the points Swithun mentioned are covered.  If I remember correctly, he 
searches for all entries that have a certain kind of name, or all 
entries that are a certain type and have a certain kind of name.

Best Regards,
Mohit.
27/11/2010 | 12:41 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

On 26/11/2010 6:34 PM, Swithun Crowe wrote:
> Hello
>
> CREATE INDEX idx ON tx(name ASC, type);
>
> With the columns in this order (name followed by type), the index will be
> used for queries which have either just name, or both name and type in
> their WHERE clauses.

Swithun, thank you very much for the clear explanation.

Best Regards,
Mohit.
27/11/2010 | 12:35 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

[Igor Tandetnik]

Yuzem  wrote:
> Thanks for the answer.
> I have another problem:
> I decided to use a view to do this, I will have to drop/create the view
> every time filters is updated but I don't know how to use multiple selects.
> I want to do something like this:
> 
> CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies WHERE rating
> != '' AND SELECT 'tagged',movie FROM movies WHERE movie in (SELECT movie
> FROM tags);

SELECT 'rated' as type, movie FROM movies WHERE rating != ''
UNION ALL
SELECT 'tagged' as type, movie FROM movies
WHERE movie in (SELECT movie FROM tags);

-- 
Igor Tandetnik

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

[sqlite] Efficient Paths in Practice Through a 21-bit Codespace (Re: SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly)

[Samuel Adam]

On Fri, 26 Nov 2010 08:40:42 -0500, Jean-Christophe Deschamps  
 wrote:

> At 14:26 26/11/2010, [Samuel Adam ] wrote:
>
>> N.b., there is a severe bug (pointers calculated based on truncated
>> 16-bit
>> values above plane-0) in a popular Unicode-properties SQLite extension.
>> […]
>
> I believe you refer to Ioannis code.

Yes.

> I found this 16-bit truncation
> and decided to expand that trie to 32-bit in order to support those
> characters correctly.

With due regard to the fact that Mr. Deschamps evidently wrote working  
code and I thus far apparently have not, I have a suggestion as to  
space/time tradeoffs.

32 bits to cover Unicode’s 21-bit space always irked me.  24 bits won’t do  
due to alignment issues, and 16 bits is just too small.  However:

(a) 99% of usage in 99% of apps is confined to the Basic Multilingual  
Plane (Plane 0).  [Source: The same fundament as from which springs the  
majority of published statistics.]

(b) Modern operating systems typically load executables (including  
libraries) using memory mapping.  If RAM is constrained, an intelligent  
virtual memory subsystem will leave any unused tables on disk most of the  
time, only to be faulted-in for the 1% cases.

(c) A code path which uses 16-bit-based tables for the BMP, and only  
invokes a separate path through 32-bit-based tables for Planes 1–16, will  
permit *smaller, less-wasteful* tables to be the ones kept in RAM for the  
99% cases.

(No) thanks to contemporary chip architects, the problem thence becomes  
how best to effect these in-practice space savings without unacceptable  
time loss (usually in a tight loop) for extra branching.  For now, all I  
can say is that goto is a smart programmer’s intimate companion.

Unicode properties and characteristically similar data being quite  
commonly needed, I suspect such a method would have uses far beyond  
SQLite.  (Perhaps I should patent it sometime within the next 365 days.  
)

Very truly,

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get expression from column (smart folder orfilter implementation)

[Yuzem]

Thanks for the answer.
I have another problem:
I decided to use a view to do this, I will have to drop/create the view
every time filters is updated but I don't know how to use multiple selects.
I want to do something like this:

CREATE VIEW movies_filters AS SELECT 'rated',movie FROM movies WHERE rating
!= '' AND SELECT 'tagged',movie FROM movies WHERE movie in (SELECT movie
FROM tags);
-- 
View this message in context: 
http://old.nabble.com/How-do-I-get-expression-from-column-%28smart-folder-or-filter-implementation%29-tp30295959p30308922.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Jean-Christophe Deschamps]

At 14:26 26/11/2010, you wrote:

>N.b., there is a severe bug (pointers calculated based on truncated 
>16-bit
>values above plane-0) in a popular Unicode-properties SQLite extension.
>The extension only attempts covering a few high-plane characters—if 
>memory
>serves, three of thhem in array 198; but with the high-bits snipped 
>off, I
>rather doubt those will be what is actually affected.  I attempted
>contacting the author about the bug last year when I discovered it, but
>was unable to find a private contact method on a brief glance through 
>the
>author’s site.  Perhaps the bug has been fixed by now; I never checked
>back; anyone who intelligently investigates compiler warnings would 
>not be
>bitten anyway.  I write off the whole episode as a victory for spammers.

I believe you refer to Ioannis code.  I found this 16-bit truncation 
and decided to expand that trie to 32-bit in order to support those 
characters correctly.  As I had many several distinct needs (still 
highly related to Unicode) I decided to rewrite most of the code and 
expand it in a number of directions.  Anyone interested can contact me 
so I post the source.


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

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Twylite]

Hi,
>>> In WAL mode with synchronous=NORMAL, when the user commits
>>> a transaction, it is written into the WAL file. No sync
>>> until a checkpoint happens. So if the power fails, you might
>>> lose all the transactions that have been written into the WAL
>>> file.
>> Ahha.  That explains it.  Thanks for that.  This makes WAL mode less 
>> attractive to me.
> If you use synchronous=FULL then it should not be possible to
> lose a transaction once it has been committed (assuming the
> hard disk is playing fair).
Is there any way to force a sync to disk (e.g. that I could call every 
few seconds), other than PRAGMA wal_checkpoint ?

Regards,
Twylite

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

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Samuel Adam]

On Fri, 26 Nov 2010 07:27:02 -0500, Simon Slavin   
wrote:

> On 26 Nov 2010, at 6:52am, Niklas Bäckman wrote:
>
>> You are right of course. The shell should not count code points, but  
>> graphemes.
>>
>> http://unicode.org/faq/char_combmark.html#7
>>
[snip]
>> Or would it be possible to write such a graphemelen(s) function in not  
>> too many
>> lines of C code without needing any external Unicode libraries?
>
> No.  Sorry, but Unicode was not designed to make it simple to figure out  
> such a function.  You need lots of data to figure out how the compound  
> characters work.

“Lots of data” can still be represented efficiently:

http://www.strchr.com/multi-stage_tables
(I am not affiliated with that site in any way.)

Such coding tricks seem more usually used for case-folding tables, script  
identification, and so forth; but I don’t see why the same principles  
couldn’t be used for all Unicode properties, including the combiner stuff.

You don’t need ICU or a similar monstrosity to get at Unicode properties.   
Big, heavy libraries will help you support CLDR, different collations for  
every language, calendrical calculations and conversions, and so on, and  
so forth.  Excluding Unihan, basic Unicode-property lookups should compile  
down much lighter in weight than SQLite itself.

N.b., there is a severe bug (pointers calculated based on truncated 16-bit  
values above plane-0) in a popular Unicode-properties SQLite extension.   
The extension only attempts covering a few high-plane characters—if memory  
serves, three of them in array 198; but with the high-bits snipped off, I  
rather doubt those will be what is actually affected.  I attempted  
contacting the author about the bug last year when I discovered it, but  
was unable to find a private contact method on a brief glance through the  
author’s site.  Perhaps the bug has been fixed by now; I never checked  
back; anyone who intelligently investigates compiler warnings would not be  
bitten anyway.  I write off the whole episode as a victory for spammers.

Very truly,

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] glob and like not using index in newest sqlite

[Richard Hipp]

On Fri, Nov 26, 2010 at 6:23 AM, Spiros Ioannou wrote:

> Hello, I recently updated from 3.4.2 to 3.7.3 and the glob and like
> operators are now not using the indexes.
> My table "dict" has a column named "word" without defined type, containing
> utf-8 words. The index is:
> CREATE INDEX wordidx on dicts (word);
>
> explain query plan SELECT * from dicts where word like '%'  limit
> 1;
> 0|0|TABLE dicts
>
>
> sqlite> explain query plan SELECT * from dicts where word glob '*'
> limit 1;
> 0|0|TABLE dicts
>
> BUT:
> sqlite> explain query plan SELECT * from dicts where word > ''  limit
> 1;
> 0|0|TABLE dicts WITH INDEX wordidx
>
> using collate binary before the "limit" in glob or like doesn't help. using
> PRAGMA case_sensitive_like = 0/1 didn't help either.
> Is this a bug?
>

This is not a bug but a bug fix.  Version 3.4.2 was wrong.  Version 3.7.3 is
right.  The LIKE/GLOB indexing optimization can only be (safely) applied to
a column that has TEXT affinity.  Using the LIKE/GLOB indexing optimization
on any other column can result in an incorrect answer.  See

http://www.sqlite.org/cvstrac/tktview?tn=3901

Your fix is to declare column "word" to have type TEXT.




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



-- 
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] Efficient hash lookup table emulation with SQLite - how?

[Black, Michael (IS)]

sqlite> CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER);
sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t 
WHERE key="key1"),0)+1);
sqlite> SELECT * FROM t;
key1|1
sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t 
WHERE key="key1"),0)+1);
sqlite> SELECT * FROM t;
key1|2
 
It's not a hash table lookup though...it's a b-tree
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alexei Alexandrov
Sent: Thu 11/25/2010 8:07 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Efficient hash lookup table emulation with SQLite - 
how?



Hi,

I'm trying to solve efficiently the following task with SQlite:
* There is a trace file which contains a big number of some objects.  Each
object has a number of fields which constitute its primary key (PK).
* The objects are loaded into a table which has a number of PK columns (mapped
from the trace object PK properties) and also has a number of non-key columns
which are used to aggregate certain information about objects - e.g. count of
objects, or min/max timestamp of the object instance in the trace.
* The loading function needs to:
** Understand whether the object is present or not in the table already.  This
is done by the object PK fields.
** If it is present, update its non-key fields.
** If it is not present, insert new object filling the non-key fields with
default values.

As an example, consider table

CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER);

which would be used for counting how many times a certain word appeared in the
text.  We need to walk over word list and either set the counter to 1 or
increase it if the value is already present.

Ideally, I would like to do the following:
* INSERT operation for the primary key like

  INSERT INTO t (key, count) VALUES (?, 1);

and if primary key already exists, get the rowid of that row so that I can do

  UPDATE t SET count = count+1 WHERE rowid = ?;

passing the rowid found during failed insertion operation.

But, it's not possible now - rowid is not returned during failed PK contraint
insertion.  I cannot do "INSERT OR REPLACE" because REPLACE removes the row and
so count will be lost.

So currently I have to do:
* First, SELECT operation to try to find the row by primary key and return its
rowid.
* If select operation returned rowid, use that to do UPDATE.
* If select operation did not return anything, do INSERT.

I feel that there should be more efficient ways to do this hash table emulation.
 Are there?  Or am I trying to get something irrelevant from a SQL database?

___
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] Efficient hash lookup table emulation with SQLite - how?

[Simon Slavin]

On 25 Nov 2010, at 2:07pm, Alexei Alexandrov wrote:

> * There is a trace file which contains a big number of some objects.  Each
> object has a number of fields which constitute its primary key (PK).
> * The objects are loaded into a table which has a number of PK columns (mapped
> from the trace object PK properties) and also has a number of non-key columns
> which are used to aggregate certain information about objects - e.g. count of
> objects, or min/max timestamp of the object instance in the trace.


> * The loading function needs to:
> ** Understand whether the object is present or not in the table already.  This
> is done by the object PK fields.
> ** If it is present, update its non-key fields.
> ** If it is not present, insert new object filling the non-key fields with
> default values.

Okay, first you really have two TABLEs here.  You have a trace TABLE, and a 
summary TABLE.

The summary table doesn't really need to exist as a real SQL table.  You can 
produce everything in it using SELECT queries and GROUP BY.  For instance

SELECT count(*),max(nosesize) FROM trace GROUP BY keycol1,keycol2,keycol3

So you don't need to actually make your summary table at all.  You can do it 
all with queries.  Take a look at



No, hold on, take a look at



That's a serious solution: just have SQL do all the summary calculations for 
you whenever you need them.

However, it may be efficient to keep the summary table around, for example if 
you do queries to it again and again and don't want to require all the 
processing that would be involved in the GROUP BY queries.  One way to do this 
would be to create the trace TABLE, and to use a TRIGGER on it so that when you 
insert a new row in the trace TABLE, the summary TABLE was automatically 
updated.  See



However, unless you keep all your trace data in TABLE it will be impossible to 
'remove' a line from the trace TABLE from the summary TABLE.  Because if you're 
storing only max(nosesize) and remove the biggest nose, you don't know how big 
the second-biggest nose was without access to the other trace rows.

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

[sqlite] glob and like not using index in newest sqlite

[Spiros Ioannou]

Hello, I recently updated from 3.4.2 to 3.7.3 and the glob and like
operators are now not using the indexes.
My table "dict" has a column named "word" without defined type, containing
utf-8 words. The index is:
CREATE INDEX wordidx on dicts (word);

explain query plan SELECT * from dicts where word like '%'  limit
1;
0|0|TABLE dicts


sqlite> explain query plan SELECT * from dicts where word glob '*'
limit 1;
0|0|TABLE dicts

BUT:
sqlite> explain query plan SELECT * from dicts where word > ''  limit
1;
0|0|TABLE dicts WITH INDEX wordidx

using collate binary before the "limit" in glob or like doesn't help. using
PRAGMA case_sensitive_like = 0/1 didn't help either.
Is this a bug?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, durability, and synchronous=NORMAL

[Dan Kennedy]

On 11/25/2010 09:04 PM, Simon Slavin wrote:
>
> On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote:
>
>> In WAL mode with synchronous=NORMAL, when the user commits
>> a transaction, it is written into the WAL file. No sync
>> until a checkpoint happens. So if the power fails, you might
>> lose all the transactions that have been written into the WAL
>> file.
>
> Ahha.  That explains it.  Thanks for that.  This makes WAL mode less 
> attractive to me.

If you use synchronous=FULL then it should not be possible to
lose a transaction once it has been committed (assuming the
hard disk is playing fair).

Of course, that means a sync at the end of each transaction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Efficient hash lookup table emulation with SQLite - how?

[Alexei Alexandrov]

Hi,

I'm trying to solve efficiently the following task with SQlite:
* There is a trace file which contains a big number of some objects.  Each
object has a number of fields which constitute its primary key (PK).
* The objects are loaded into a table which has a number of PK columns (mapped
from the trace object PK properties) and also has a number of non-key columns
which are used to aggregate certain information about objects - e.g. count of
objects, or min/max timestamp of the object instance in the trace.
* The loading function needs to:
** Understand whether the object is present or not in the table already.  This
is done by the object PK fields.
** If it is present, update its non-key fields.
** If it is not present, insert new object filling the non-key fields with
default values.

As an example, consider table

CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER);

which would be used for counting how many times a certain word appeared in the
text.  We need to walk over word list and either set the counter to 1 or
increase it if the value is already present.

Ideally, I would like to do the following:
* INSERT operation for the primary key like

  INSERT INTO t (key, count) VALUES (?, 1);

and if primary key already exists, get the rowid of that row so that I can do

  UPDATE t SET count = count+1 WHERE rowid = ?;

passing the rowid found during failed insertion operation.

But, it's not possible now - rowid is not returned during failed PK contraint
insertion.  I cannot do "INSERT OR REPLACE" because REPLACE removes the row and
so count will be lost.

So currently I have to do:
* First, SELECT operation to try to find the row by primary key and return its
rowid.
* If select operation returned rowid, use that to do UPDATE.
* If select operation did not return anything, do INSERT.

I feel that there should be more efficient ways to do this hash table emulation.
 Are there?  Or am I trying to get something irrelevant from a SQL database?

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

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Simon Slavin]

On 26 Nov 2010, at 6:52am, Niklas Bäckman wrote:

> You are right of course. The shell should not count code points, but 
> graphemes.
> 
> http://unicode.org/faq/char_combmark.html#7
> 
> I guess that this probably falls out of the "lite" scope of SQLITE though?

There is absolutely no way you're going to get graphemes into the SQLite 
library until the SQLite library is written to support Unicode in other ways 
(which it currently doesn't).

The command-line tool could possibly have grapheme-counting added to it, 
though.  The 'lite' in 'SQLite' only has to refer to the routines people need 
to compile into their applications; there's no need to keep an external tool 
slim.

> Or would it be possible to write such a graphemelen(s) function in not too 
> many
> lines of C code without needing any external Unicode libraries?

No.  Sorry, but Unicode was not designed to make it simple to figure out such a 
function.  You need lots of data to figure out how the compound characters work.

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

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 13:02, Drake Wilson wrote:
> Quoth luuk34, on 2010-11-26 12:49:53 +0100:
>> The extra column seems to work,
>> but i thought this should work too?
> I would imagine so, at first glance.
>
>> But the ORDER is wrong...
> How?  The example you provided seems properly sorted.
>
>

oeps, i made an error in the order by.
so, indeed its working OK...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[Drake Wilson]

Quoth luuk34 , on 2010-11-26 12:49:53 +0100:
> The extra column seems to work,
> but i thought this should work too?

I would imagine so, at first glance.

> But the ORDER is wrong...

How?  The example you provided seems properly sorted.

> sqlite> SELECT a,b FROM (
> ...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
> ...> UNION
> ...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
> ...> )
> ...> ORDER BY a IS 0, b;
> a|b
> 3|0.0
> 4|25.0
> 1|50.0
> 2|75.0
> 5|100.0
> 7|1000.0
> 8|1.0
> sqlite>

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

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[luuk34]

On 26-11-10 12:37, Drake Wilson wrote:
> Quoth Waldemar Derr, on 2010-11-26 12:24:27 +0100:
>> --Don't working: (Error: 1st ORDER BY term does not match any column in the
>> result set.)
>>
>> SELECT * FROM OrderTest WHERE Price<  200
>> UNION
>> SELECT * FROM OrderTest WHERE Price>  500
>> ORDER BY Price IS 0, Price;
>  From http://sqlite.org/lang_select.html:
> | Otherwise, if the ORDER BY expression is any other expression, it is
> | evaluated and the the returned value used to order the output rows. If
> | the SELECT statement is a simple SELECT, then an ORDER BY may contain
> | any arbitrary expressions. However, if the SELECT is a compound
> | SELECT, then ORDER BY expressions that are not aliases to output
> | columns must be exactly the same as an expression used as an output
> | column.
>
>> Is this a bug? In MySQL it works as expected. Is there a workaround?
> Add another output column with the expression you want, give it a
> name, and ORDER BY that name.
>
> My guess is that this is because the multiple output column
> specification parts of a compound SELECT may result in different
> intrepretations of an arbitrary expr in that position, and it's
> not clear how any kind of inwards propagation would work without
> yielding surprising results.
>
> --->  Drake Wilson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The extra column seems to work,
but i thought this should work too?
But the ORDER is wrong...

sqlite> SELECT a,b FROM (
...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
...> UNION
...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
...> )
...> ORDER BY a IS 0, b;
a|b
3|0.0
4|25.0
1|50.0
2|75.0
5|100.0
7|1000.0
8|1.0
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[Drake Wilson]

Quoth Waldemar Derr , on 2010-11-26 12:24:27 +0100:
> --Don't working: (Error: 1st ORDER BY term does not match any column in the
> result set.)
> 
> SELECT * FROM OrderTest WHERE Price < 200 
> UNION
> SELECT * FROM OrderTest WHERE Price > 500 
> ORDER BY Price IS 0, Price;

>From http://sqlite.org/lang_select.html:
| Otherwise, if the ORDER BY expression is any other expression, it is
| evaluated and the the returned value used to order the output rows. If
| the SELECT statement is a simple SELECT, then an ORDER BY may contain
| any arbitrary expressions. However, if the SELECT is a compound
| SELECT, then ORDER BY expressions that are not aliases to output
| columns must be exactly the same as an expression used as an output
| column.

> Is this a bug? In MySQL it works as expected. Is there a workaround?

Add another output column with the expression you want, give it a
name, and ORDER BY that name.

My guess is that this is because the multiple output column
specification parts of a compound SELECT may result in different
intrepretations of an arbitrary expr in that position, and it's
not clear how any kind of inwards propagation would work without
yielding surprising results.

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

[sqlite] [ERROR] Failed compound query with simple expr in ORDER BY

[Waldemar Derr]

Hello all readers,

following a complete example for reproduce this behaviour (SQLite 3.7.3):


CREATE TABLE IF NOT EXISTS OrderTest (ID AUTOINC, Price FLOAT);

REPLACE INTO OrderTest VALUES (1, 50);
REPLACE INTO OrderTest VALUES (1, 50);
REPLACE INTO OrderTest VALUES (2, 75);
REPLACE INTO OrderTest VALUES (3, 0);
REPLACE INTO OrderTest VALUES (4, 25);
REPLACE INTO OrderTest VALUES (5, 100);
REPLACE INTO OrderTest VALUES (6, 250);
REPLACE INTO OrderTest VALUES (7, 1000);
REPLACE INTO OrderTest VALUES (8, 1);

--Working:

SELECT * FROM OrderTest WHERE Price < 200 
UNION
SELECT * FROM OrderTest WHERE Price > 500 
ORDER BY Price;

--Don't working: (Error: 1st ORDER BY term does not match any column in the
result set.)

SELECT * FROM OrderTest WHERE Price < 200 
UNION
SELECT * FROM OrderTest WHERE Price > 500 
ORDER BY Price IS 0, Price;


Is this a bug? In MySQL it works as expected. Is there a workaround?

Thanks in advance,

Waldemar Derr

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

Re: [sqlite] Confused about Multiple Indexes

[Max Vlasov]

On Fri, Nov 26, 2010 at 1:34 PM, Swithun Crowe <
swit...@swithun.servebeer.com> wrote:

> Hello
>
> MS> The second index should be:
> MS> CREATE INDEX IDX2 on tx(type, name ASC);
>
> MS> What I had meant to ask was whether there is any benefit in having two
> MS> indexes when one of the indexes is exactly within the other.
>
> MS> IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC'
> -
> MS> does this mean that in a sense IDX1 is a subset of IDX2 and can be
> removed?
>
> If you wanted to have just one index, rather than two, then you could
> have:
>
> CREATE INDEX idx ON tx(name ASC, type);
>
>
Mohit said that he uses a someone's db, so I can imagine a possibility that
with two indexes ...
CREATE INDEX IDX1 on tx(name ASC);
CREATE INDEX IDX2 on tx(type, name ASC);
... the creator of database wanted to search for a name regardless of the
type with a help of first index, and in the context of chosen type with the
second. So, Mohit, if some unknown queries or code involved I'd better leave
them as they are

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

Re: [sqlite] Confused about Multiple Indexes

[Swithun Crowe]

Hello

MS> The second index should be:
MS> CREATE INDEX IDX2 on tx(type, name ASC);

MS> What I had meant to ask was whether there is any benefit in having two 
MS> indexes when one of the indexes is exactly within the other.

MS> IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' - 
MS> does this mean that in a sense IDX1 is a subset of IDX2 and can be removed?

If you wanted to have just one index, rather than two, then you could 
have:

CREATE INDEX idx ON tx(name ASC, type);

With the columns in this order (name followed by type), the index will be 
used for queries which have either just name, or both name and type in 
their WHERE clauses.

I think this is what this page is saying in sections 1.0 and 1.1:

http://www.sqlite.org/optoverview.html

If you had an index (type, name ASC), then a query which used column name, 
but didn't use column type would not get to use the index.

So, yes you could get away with one index.

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

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

[Philip Graham Willoughby]

On 25 Nov 2010, at 14:06, Dan Kennedy wrote:

> On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote:
>> Hi all,
>> 
>> I'm noticing a new failure with SQLite 3.7.3 as compared to the previous 
>> version I was using, 3.6.23.1.
> 
> Are you able to share the database and the query that causes
> the assert() to fail?

Here is a database and the query which causes the assert to fail:

Database:   http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/Test.base
Query SQL:  
http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/TestQuery.sql

If for any reason you wanted to recreate the DB the SQL to do so are these:

Schema SQL: 
http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/testSchema.sql
Data SQL:   
http://dl.dropbox.com/u/10341475/SQLiteAssertionFailure/TestValues.sql

My original schema is somewhat more complex so I have cut out some parts which 
are not relevant to this problem. In attempting to create a cut-down version I 
also tried removing players 2 and 3 from the CardsPlayers and CardsPlayer[AB] 
tables, which caused the query to run successfully. I cannot tell from that 
whether the problem is triggered by the presence of any values in those tables 
or by the presence of relevant values in those tables.

With a new empty database and these SQL statements with SQLite version 3.6.12 
(or 3.7.3 without SQLITE_DEBUG) I see this:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ./testSchema.sql
sqlite> .read ./TestValues.sql
sqlite> .read ./TestQuery.sql
Otterbourne Golf Course|Jane Doe|Assumed Name
sqlite> 

With a new empty database and these SQL statements with SQLite version 3.7.3 
(with SQLITE_DEBUG) I see this:

SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ./testSchema.sql
sqlite> .read ./TestValues.sql
sqlite> .read ./TestQuery.sql
Assertion failed: (memIsValid([i])), function sqlite3VdbeExec, file 
sqlite3.c, line 64507.
Abort

SQLite 3.7.3 fails in that manner when I run just the query on the 3.6.12 
database; similarly SQLite 3.6.12 gets the right answer when it queries the 
3.7.3 database. I infer from this that the data stored is fine it's just a 
problem at query-time.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] Confused about Multiple Indexes

[Mohit Sindhwani]

Hi Swithun

Thank you for your reply.  I'm sorry I was simplifying the schema when I 
sent it out.

On 26/11/2010 5:35 PM, Swithun Crowe wrote:
> Hello
>
> MS>  CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT,
> MS>  ...);
>
> MS>  CREATE INDEX IDX1 on tx(name ASC);
> MS>  CREATE INDEX IDX2 on tx(type, search_name ASC);
>
> The two indexes cover different columns, so they do different things. The
> indexes you need depend on the queries you will be doing, so there is no
> way I can tell if you need these indexes. Columns which are used in WHERE
> clauses are good candidates for indexing, generally speaking.
>
> You don't show a column called search_name in the CREATE TABLE line. I
> assume there is one.

The second index should be:
CREATE INDEX IDX2 on tx(type, name ASC);
What I had meant to ask was whether there is any benefit in having two 
indexes when one of the indexes is exactly within the other.

IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' - 
does this mean that in a sense IDX1 is a subset of IDX2 and can be removed?

> A more specific answer would require more information, but I hope this
> helps.

I apologize for the mistake I made in sending out the schema.  Thanks 
again for taking the time to answer.

Best Regards,
Mohit.
26/11/2010 | 5:58 PM.


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

Re: [sqlite] Confused about Multiple Indexes

[Swithun Crowe]

Hello

MS> CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, 
MS> ...);

MS> CREATE INDEX IDX1 on tx(name ASC);
MS> CREATE INDEX IDX2 on tx(type, search_name ASC);

The two indexes cover different columns, so they do different things. The 
indexes you need depend on the queries you will be doing, so there is no 
way I can tell if you need these indexes. Columns which are used in WHERE 
clauses are good candidates for indexing, generally speaking.

You don't show a column called search_name in the CREATE TABLE line. I 
assume there is one.

A more specific answer would require more information, but I hope this 
helps.

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

Re: [sqlite] import and convert table to schema?

[Oliver Peters]

Wouter Overmeire  writes:

[...]

> If I compare the speed of doing this for one file to an import statement in
sqlite itself there is a big
> difference, '.import' is much faster

[...]

Use transactions like this (will be much faster):

BEGIN TRANSACTION;

INSERT ;
INSERT ;
INSERT ;
INSERT ;
...

COMMIT;

oliver

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

[sqlite] import and convert table to schema?

[Wouter Overmeire]

So far I haven`t found a solution for the following problem.

I have a number of txt files with tab spaced data.
Each file`s row looks like:
   ... 
In one file N is the same for all rows, but between files N can have different 
values.
So each row represents an array of real values of length N, and an array name.

I would like to store the data in an sqlite database, using the following 
schema:

create table array
(
id integer primary key autoincrement,
name text
);

create table array_value
(
id integer primary key autoincrement,
arrayId integer,
index integer,
value real,
foreign key(arrayId) references array(id)
);


What I do now is read the file (outside of sqlite) and use individual insert 
commands for each row of a file.
If I compare the speed of doing this for one file to an import statement in 
sqlite itself there is a big difference, '.import' is much faster
Is there a way to convert table file1 (see below) to the tables array and 
array_value using sqlite commands?
 
create table file1
(
name text,
value0 real,
value1 real,
value2 real,
.
.
.
valueN-1 real
);

.mode tabs
.import file1.txt file1


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