Re: [sqlite] Sorting by rowid needs temp b-tree?

[Igor Tandetnik]

Nikolaus Rath  wrote:
> After creating another index, it seems to work:
> 
> sqlite> create index foo on contents(parent_inode);
> sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE 
> parent_inode=42 AND rowid > 12932 ORDER BY rowid;
> 0|0|0|SEARCH TABLE contents USING INDEX foo (parent_inode=?) (~3 rows)
> 
> Is that a good solution, or am I missing something? I assume that SQLite
> deliberately chose the new index to avoid the explicit sorting, rather
> than just because it was the first one at hand?

I believe SQLite did choose the new index deliberately, yes.
-- 
Igor Tandetnik

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

Re: [sqlite] Sorting by rowid needs temp b-tree?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath  wrote:
>> Hello,
>> 
>> sqlite> explain query plan SELECT name_id, inode, rowid FROM
>> contents WHERE parent_inode=42 AND rowid > 12932 ORDER BY rowid;
>> 0|0|0|SEARCH TABLE contents USING INDEX sqlite_autoindex_contents_1
>> (parent_inode=?) (~6 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY
>> 
>> Why does ordering by rowid need a temporary b-tree?
>
> Show CREATE TABLE statement.
> What constraint is sqlite_autoindex_contents_1 generated from? My
> educated guess is that you have a constraint on two or more columns,
> of which parent_inode is the first one. As a result, rows coming out
> of the index are not sorted by rowid (instead, they are sorted by the
> second column mentioned in the constraint), and have to be sorted
> explicitly.

You guessed correctly:

CREATE TABLE contents (
rowid INTEGER PRIMARY KEY AUTOINCREMENT,
name_id   INT NOT NULL REFERENCES names(id),
inode INT NOT NULL REFERENCES inodes(id),
parent_inode INT NOT NULL REFERENCES inodes(id),

UNIQUE (parent_inode, name_id)
);

>> Isn't the ordering by rowid also the order in which rows are stored
>
> Yes.
>
>> and therefore also already the order in which the SEARCH will find
>> them?
>
> Not necessarily. SEARCH will find them in the order they are listed in
> the index, not in the order they are stored in the underlying table.

After creating another index, it seems to work:

sqlite> create index foo on contents(parent_inode);
sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE 
parent_inode=42 AND rowid > 12932 ORDER BY rowid;
0|0|0|SEARCH TABLE contents USING INDEX foo (parent_inode=?) (~3 rows)

Is that a good solution, or am I missing something? I assume that SQLite
deliberately chose the new index to avoid the explicit sorting, rather
than just because it was the first one at hand?

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union-having bug

[Igor Tandetnik]

Gillman, David  wrote:
> Is this expected behavior?  (The failure of my query to return a row.)

Your query does not have a well-defined meaning, so no particular behavior is 
expected from it.
-- 
Igor Tandetnik

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

Re: [sqlite] union-having bug

[Gillman, David]

Is this expected behavior?  (The failure of my query to return a row.)

David
  
-Original Message-
From: Kit [mailto:kit.sa...@gmail.com] 
Sent: Saturday, December 03, 2011 5:24 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] union-having bug

2011/12/3 Gillman, David :
> Is this behavior known?  The third query returns no rows even though bar = 1.
> sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 
> sqlite> foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind 
> sqlite> having bar > 0;

select ind, sum(foo) fooo, sum(bar) barr from (select 1 ind, 0 foo, 1 bar union 
select 1 ind, 1 foo, 0 bar) group by ind having barr >0;
1|1|1
--
Kit
___
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] Sorting by rowid needs temp b-tree?

[Igor Tandetnik]

Nikolaus Rath  wrote:
> Hello,
> 
> sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE 
> parent_inode=42 AND rowid > 12932 ORDER BY rowid;
> 0|0|0|SEARCH TABLE contents USING INDEX sqlite_autoindex_contents_1 
> (parent_inode=?) (~6 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> 
> Why does ordering by rowid need a temporary b-tree?

Show CREATE TABLE statement. What constraint is sqlite_autoindex_contents_1 
generated from? My educated guess is that you have a constraint on two or more 
columns, of which parent_inode is the first one. As a result, rows coming out 
of the index are not sorted by rowid (instead, they are sorted by the second 
column mentioned in the constraint), and have to be sorted explicitly.

> Isn't the ordering
> by rowid also the order in which rows are stored

Yes.

> and therefore also
> already the order in which the SEARCH will find them?

Not necessarily. SEARCH will find them in the order they are listed in the 
index, not in the order they are stored in the underlying table.
-- 
Igor Tandetnik

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

[sqlite] Sorting by rowid needs temp b-tree?

[Nikolaus Rath]

Hello,

sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE 
parent_inode=42 AND rowid > 12932 ORDER BY rowid;
0|0|0|SEARCH TABLE contents USING INDEX sqlite_autoindex_contents_1 
(parent_inode=?) (~6 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY

Why does ordering by rowid need a temporary b-tree? Isn't the ordering
by rowid also the order in which rows are stored, and therefore also
already the order in which the SEARCH will find them?

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RE Infinite Loop in MATCH on self written fts3 tokenizer

[Johannes Krude]

hi,

On Sunday 04 December 2011 14:23:09 Black, Michael (IS) wrote:
> It says "here's token 'hal'" and if you return the pointer to "h" it points
> to the same place so it returns "hal" right back to youergo the loop.
I have read through the ext/fts3/fts3/expr.c code and found out the following: 
piEndOffset must point to the zero byte after the returned token. fts3 expects 
the tokenizer to generate exactly one token for each search string.

The first call to my xNext always returned the prefix with length 1 and 
piStartOffset=piEndOffset=0. Therefore fts3 incremented its internal pointer 
by 0 after each loop and then called xNext on the same string again.

I fixed this by returning first the longest prefix (the given word itself) and 
pointing piEndOffset after the returned string. Now it works.

> You don't say why you're doing this.  FTS already supports prefix queries.
The fts documentation states, that if I want to efficently search for prefixes 
I should give the maximum size of such prefixes such that fts can optimize for 
those prefixes. I want to efficently search for prefixes of any length.

The drawback of my tokenizer is, that it consumes a lot of space, for 56Mb of 
strings I get a 1.2Gb file. I assume since everything is done in trees, a 
search with my tokenizer is in O(log(n)) where n is the number of tokens in 
the table. Is this still O(log(n)) if I write a tokenizer for which 
input=output and use the fts prefix search?

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

[sqlite] RE Infinite Loop in MATCH on self written fts3 tokenizer

[Black, Michael (IS)]

Because tokenizers expect the pointer to increment and you're apparently not 
doing that.



It says "here's token 'hal'" and if you return the pointer to "h" it points to 
the same place so it returns "hal" right back to youergo the loop.



I think you would have to maintain state and your own copy of the text to 
return which also means you're code wouldn't be thread safe.



You don't say why you're doing this.  FTS already supports prefix queries.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Johannes Krude [johan...@krude.de]
Sent: Saturday, December 03, 2011 12:31 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Infinite Loop in MATCH on self written fts3 tokenizer

hi,

I have written an fts3 tokenizer which generates all prefixes of the input
text. After inserting, "hallo" into an fts4 table, the fts4aux table has
entries for "h", "ha", "hal", "hall", and "hallo".

If I try to do a "SELECT * FROM table WHERE string MATCH 'hal';", sqlite goes
into an infinte loop with xOpen xNext and Xclose on my tokenizer. The argument
for xOpen is always "hal", and xNext gets only called once in every loop. Why
does the tokenizer gets called on a SELECT MATCH query? What would cause
sqlite3 to go into such an infinite loop?

Greetings johannes
___
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] Help with an SQL statement

[Adolfo Jiménez Millán]

Problem solved:

a). I was using an old test directory with version 3.5.4 of sqlite3.exe, hence 
the errors in the condition (I suppose).

b) Once updated to the 3.7.9 version, and went away the syntax errors, I 
mistakenly believed that the sentence did not produce any results because the 
dBase file maintained its size on disk, and I expected a substantial increase 
with the addition of the new FTS4 stuff.

The fact is that a 95 MB dBase, did not experienced any increase in size after 
include about 300 records in the new  FTS4 table. Possibly because the dBase l 
test, contains tables with many deleted space, that now has been reused.

Sorry for having wasted the time of those who have been kind enough to read my 
first post.

--
Adolfo

>
>  Original message 
> From: Adolfo Jiménez Millán 
>
>Hi all:
>
>Assuming a normal table:
>
>CREATE TABLE names (Id INTEGER PRIMARY KEY, Nm INTEGER);
>
>And an FTS4 table:
>
>CREATE VIRTUAL TABLE fts USING fts4 (name, tokenize=simple);
>
>I want populate the FTS4 table from the content of the names table using the 
>second form of the INSERT statement:
>
>INSERT INTO fts (rowid, name) SELECT so.Id, so.Nm FROM names so WHERE 
>(so.Id>1000 AND so.Id<2000);
>
>I get an Error:constraint failed
>
>Even if I use an statement that does not return error, I.e:
>
>INSERT INTO fts (name) SELECT so.Nm FROM names so WHERE so.Id>1000;
>
>The result do nothing although the names table is populated.
>


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