[sqlite] Fwd: ImportError: /usr/lib/libgdal.so.1: undefined symbol: sqlite3_column_table_name

[Eric Hill]

Make sure SQLITE_ENABLE_COLUMN_METADATA is on when you compile SQLite.

Eric

-Original Message-
From: "Tinashe Mudavanhu" 
Sent: ?3/?30/?2016 8:57 AM
To: "sqlite-users at mailinglists.sqlite.org" 
Subject: [sqlite] Fwd: ImportError: /usr/lib/libgdal.so.1: undefined symbol:
sqlite3_column_table_name

-- Forwarded message --
From: Tinashe Mudavanhu 
Date: Wed, Mar 30, 2016 at 11:05 AM
Subject: ImportError: /usr/lib/libgdal.so.1: undefined symbol:
sqlite3_column_table_name
To: sqlite-users at mailinglists.sqlite.org


Hi,

I am having the following error every time i try to import cv2 in the
Python Interpreter. I am operating on Ubuntu 14.04.

ImportError: /usr/lib/libgdal.so.1: undefined symbol:
sqlite3_column_table_name

It all started when i build sqlite from source.

How can i rectify the issue?

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

[sqlite] custom collation sequence for numeric columns?

[Eric Hill]

Hey,

SQLite has a brilliant facility for creating custom collations for columns with 
character affinity.  And it won't stop you from creating a custom collation for 
a column with numeric affinity, but your comparison function will never be 
called, it seems.

I wonder if you would ever consider allowing this to work for numeric columns?

The use case I have in mind is "value ordering" - specifying an order other 
than normal alphabetical order (or number order) for a categorical column 
(think days of the week).  While almost certainly less common, numeric columns 
can be semantically categorical, in which case a user-defined ordering is not 
out of the question.  And since it is (IMHO) in the nature of SQLite to make 
values strings when they need to be strings and numbers when they need to be 
numbers, it doesn't seem like something that would be entirely beyond the pale.

Anybody care about this besides me?

Thanks!

Eric

[sqlite] MIN/MAX of column loses decltype

[Eric Hill]

Hey, Simon,

Length(theDate) returns 9, and typeof(theDate) returns "real".  I assume typeof 
is just returning the character version of the column affinity.

Typeof(MAX(theDate)) and length(MAX(theDate)) also return "real" and 9 
respectively.

At this point, I am rethinking the decision to convert our application's native 
dates to Julian dates when moving them into SQLite.  I think I made that 
decision (been a while) so that SQLite's date functions could be applied to the 
data.  But SQLite doesn't really have any functions that manipulate Julian 
dates (as far as I can tell).  We have written our own SQL date functions 
anyway.  So maybe I can just avoid this issue entirely.

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, February 22, 2016 11:12 AM
To: SQLite mailing list 
Subject: Re: [sqlite] MIN/MAX of column loses decltype


On 22 Feb 2016, at 4:02pm, Eric Hill  wrote:

> I can call sqlite3_column_decltype() and get back "REAL_DATE" and know I 
> should convert.
> 
> However, if I execute this SQL:
> 
> SELECT MAX( theDate ) FROM t1;
> 
> My call to sqlite3_column_decltype() returns nothing - the decltype of the 
> column being aggregated is not preserved by the MAX() operation.

What happens for

SELECT theDate,length(theDate),typeof(theDate) FROM t1 LIMIT 1;

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

[sqlite] MIN/MAX of column loses decltype

[Eric Hill]

Hey,

I'm at 3.8.11.1 of SQLite - could move to more recent version if it would help.

When I move date columns into SQLite, I declare their type as "REAL_DATE".  I 
do this because I convert from my application's date (seconds since 1904) to 
Julian date (a date that SQLite likes, sort of) when the data moves back and 
forth.  So if I execute this SQL:

SELECT theDate FROM t1;

I can call sqlite3_column_decltype() and get back "REAL_DATE" and know I should 
convert.

However, if I execute this SQL:

SELECT MAX( theDate ) FROM t1;

My call to sqlite3_column_decltype() returns nothing - the decltype of the 
column being aggregated is not preserved by the MAX() operation.

I guess this is just the way it is?  Clearly, the expression inside MAX() could 
be any sort of computation, so preserving the decltype would often not be 
possible, but in this degenerate (yet common I suspect) case of the simple 
MIN() or MAX() of a column, it's just not possible to preserve the decltype?

Thanks!

Eric

[sqlite] CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP

[Eric Hill]

Suggestion:  Document the fact that CURRENT_DATE, CURRENT_TIME and 
CURRENT_TIMESTAMP are supported by SQLite on the Date And Time 
Functions page of the "SQL As 
Understood By SQLite" documentation.  The only place you can currently find 
them is on the Keywords page.

I didn't realize they were supported until I used sqlite3_create_function() to 
create my own versions and then the SQL wouldn't run.

While we can have a pedagogic argument about whether they are really keywords 
or functions with no arguments, what really matters is where somebody is going 
to expect to find them documented.  Page 463 of SQL In A Nutshell lists "ANSI 
SQL Built-In Scalar Functions," and CURRENT_DATE/TIME/TIMESTAMP are in the 
list.  I don't think any harm will befall anyone if they are documented on the 
Date And Time Functions page even though they are technically keywords.

Thanks!

Eric

[sqlite] Can't create LEFT or RIGHT functions with sqlite3_create_function()

[Eric Hill]

That's fair.  Thanks for looking into it.  I can create JLEFT and JRIGHT or 
something, or just direct people to SUBSTR.  There are reasonable workarounds.

Thanks,

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Monday, February 08, 2016 3:39 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Can't create LEFT or RIGHT functions with 
sqlite3_create_function()

On 2/8/16, Eric Hill  wrote:
> Hey,
>
> It appears that LEFT and RIGHT are treated as reserved words by 
> SQLite, so my attempts to use sqlite3_create_function() to create my 
> own LEFT and RIGHT SQL functions have been unsuccessful (I'm using 
> 3.8.11.1).  Several databases (SQL Server, MySQL) define their own 
> LEFT and RIGHT functions for performing sub-string functions, 
> apparently without conflicting with LEFT JOIN/RIGHT JOIN syntax.  Is that 
> just not possible for SQLite?

Not easily, it seems.  There is a list of keywords that can fallback to be 
identifiers
(https://www.sqlite.org/src/artifact/d7bff41d4?ln=220,233) if they cannot be 
parsed as their original keyword value.  I tried adding JOIN_KW to that list.  
(JOIN_KW is a compound keyword that includes "LEFT", "RIGHT", "NATURAL", 
"CROSS", "FULL", "INNER", and "OUTER".) The result compiled, but lots of tests 
failed.  I don't know if that is something that would be easy to fix or not.

Even if it were possible to fix it, I am not in a big rush to do so.
By allowing LEFT and RIGHT to be identifiers, we would be making a promise that 
they can be identifiers in all future versions of SQLite, which puts an 
additional constraint on future changes.  I'm not sure this is worth it.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't create LEFT or RIGHT functions with sqlite3_create_function()

[Eric Hill]

Hey,

It appears that LEFT and RIGHT are treated as reserved words by SQLite, so my 
attempts to use sqlite3_create_function() to create my own LEFT and RIGHT SQL 
functions have been unsuccessful (I'm using 3.8.11.1).  Several databases (SQL 
Server, MySQL) define their own LEFT and RIGHT functions for performing 
sub-string functions, apparently without conflicting with LEFT JOIN/RIGHT JOIN 
syntax.  Is that just not possible for SQLite?

SQLite does support its own quite powerful SUBSTR SQL function allowing 
obtaining substrings from either the left or the right, which is great, but I 
am trying to support SQL syntax that users of my product are already familiar 
with.  But if it just has to be this way, I'll live.

Thanks,

Eric

[sqlite] Lawyers, encryption, and RC4

[Eric Hill]

Thanks to everyone for their contributions on this topic.  I sent Dr. Hipp's 
explanation to our legal team, and that was good enough for them.  (This is a 
US company, for those who wondered.)

I agree with Simon that if that explanation could be added to the comment in 
random.c about the use of RC4, it could perhaps shortcut these legal 
discussions for others.

Thanks again!

Eric


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Tuesday, August 11, 2015 10:11 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Lawyers, encryption, and RC4

On 8/11/15, Eric Hill  wrote:
>
> We're getting some pushback from our lawyers suggesting that SQLite's 
> use of
> RC4 even just to generate random numbers is, in their minds, 
> encryption for export purposes.

No.

The RC4 encryption algorithm consists of three subcomponents:

(1) Key management logic
(2) The pseudo-random number generator (PRNG)
(3) The encoder/decoder

SQLite only implements (2).  It omits (1) and (3).  And hence, the RC4 kernel 
inside of SQLite cannot be used for encryption.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Lawyers, encryption, and RC4

[Eric Hill]

Sorry to bother folks with this.

We're getting some pushback from our lawyers suggesting that SQLite's use of 
RC4 even just to generate random numbers is, in their minds, encryption for 
export purposes.  Now, this makes absolutely no sense to me, I can assure you, 
and I am not finding anything online that would suggest that is a valid 
position, but I'm wondering if this has come up before and if you have any good 
ammunition for dealing with such an argument.

Thanks,

Eric

[sqlite] Segmentation faults when calling sqlite3_step

[Eric Hill]

If you are calling sqlite3_bind_ to bind parameters, what are you passing 
as the last parameter? Try SQLITE_TRANSIENT to force SQLite to make a copy of 
the value right away.  Maybe currently you have values going out of scope 
before step is called.

Eric

From: Leroy Klompenhouwer
Sent: ?6/?30/?2015 9:18 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Segmentation faults when calling sqlite3_step

We are experiencing random segmentation faults when calling the function 
sqlite3_step(). We use prepared statements in a transaction to insert records 
in the database.

We have the following setup:

- SQLite3.8.9
- Linux 32bit
- Armv6
- SDCard with ext3 to store the database


Can anyone explain what is happening?


Leroy


Coredump output

#0  0x407ddfb8 in pcache1RemoveFromHash (pPage=pPage at entry=0x5408a100) at 
sqlite3.c:40329

#1  0x407ef850 in pcache1FetchStage2 (pCache=pCache at entry=0x534ebca0, 
iKey=iKey at entry=78573, createFlag=createFlag at entry=2) at sqlite3.c:40569

#2  0x407efa80 in pcache1Fetch (p=0x534ebca0, iKey=78573, createFlag=2) at 
sqlite3.c:40694

#3  0x40816690 in sqlite3PcacheFetchStress (ppPage=, 
pgno=78573, pCache=0x5391f120) at sqlite3.c:39495

#4  sqlite3PagerAcquire (pPager=0x5391f048, pgno=pgno at entry=78573, 
ppPage=0x4ab31474, ppPage at entry=0x4ab3146c, flags=0) at sqlite3.c:46910

#5  0x40816a28 in btreeGetPage (pBt=0x43c00b50, pgno=78573, ppPage=ppPage at 
entry=0x4ab31544, flags=) at sqlite3.c:54735

#6  0x40816a7c in getAndInitPage (pBt=, pgno=, 
ppPage=0x4ab31544, bReadonly=) at sqlite3.c:54790

#7  0x4081a9e0 in balance_nonroot (bBulk=, isRoot=0, 
aOvflSpace=0x54174710 "\310.\234SP\v\300C\320E\027T", iParentIdx=, pParent=0x53a436e8) at sqlite3.c:59731

#8  balance (pCur=0x0, pCur at entry=0x4080e1b0 ) at 
sqlite3.c:60469

#9  0x4081da60 in sqlite3BtreeInsert (pCur=0x4080e1b0 , 
pKey=, nKey=, pData=0x4086d778, nData=nData at 
entry=0, nZero=nZero at entry=0, appendBias=appendBias at entry=0,

seekResult=0) at sqlite3.c:60655

#10 0x4083adb4 in sqlite3VdbeExec (p=0x3, p at entry=0x53ffb940) at 
sqlite3.c:75444

#11 0x4083e828 in sqlite3Step (p=0x53ffb940) at sqlite3.c:69347

#12 sqlite3_step (pStmt=) at sqlite3.c:3877

#13 sqlite3_step (pStmt=) at sqlite3.c:3864


Detailed output:

#0  0x407ddfb8 in pcache1RemoveFromHash (pPage=pPage at entry=0x5408a100) at 
sqlite3.c:40329

h = 

pCache = 0x534ebca0

pp = 0x7df

#1  0x407ef850 in pcache1FetchStage2 (pCache=pCache at entry=0x534ebca0, 
iKey=iKey at entry=78573, createFlag=createFlag at entry=2) at sqlite3.c:40569

pOther = 

nPinned = 

pGroup = 0x534ebcd0

pPage = 0x5408a100

#2  0x407efa80 in pcache1Fetch (p=0x534ebca0, iKey=78573, createFlag=2) at 
sqlite3.c:40694

pCache = 0x534ebca0

pPage = 0x0

#3  0x40816690 in sqlite3PcacheFetchStress (ppPage=, 
pgno=78573, pCache=0x5391f120) at sqlite3.c:39495

pPg = 

#4  sqlite3PagerAcquire (pPager=0x5391f048, pgno=pgno at entry=78573, 
ppPage=0x4ab31474, ppPage at entry=0x4ab3146c, flags=0) at sqlite3.c:46910

pBase = 

rc = 0

pPg = 0x0

iFrame = 1082188208

noContent = 0

bMmapOk = 

#5  0x40816a28 in btreeGetPage (pBt=0x43c00b50, pgno=78573, ppPage=ppPage at 
entry=0x4ab31544, flags=) at sqlite3.c:54735

rc = 1399798980

pDbPage = 0x40816a28 

#6  0x40816a7c in getAndInitPage (pBt=, pgno=, 
ppPage=0x4ab31544, bReadonly=) at sqlite3.c:54790

rc = 

#7  0x4081a9e0 in balance_nonroot (bBulk=, isRoot=0, 
aOvflSpace=0x54174710 "\310.\234SP\v\300C\320E\027T", iParentIdx=, pParent=0x53a436e8) at sqlite3.c:59731

leafCorrection = 

leafData = 

pageFlags = 

apOld = {0x, 0x53bc8de8, 0x533c2520}

aSpace1 = 

pBt = 0x43c00b50

nMaxCells = 1403269792

subtotal = 

szScratch = 

pRight = 0x2 

nNew = 0

nxDiv = 0

iOvflSpace = 0

cntOld = {80464896, 0, 0, 78580, 1403366712}

apCell = 0x0

i = 0

apNew = {0x5391f048, 0x4cbcc00, 0x0, 0x0, 0x53a5ad38}

abDone = "\000\000\000\000"

aPgno = {1402073376, 78580, 1402073376, 1715, 6}

aPgOrder = {1087295488, 0, 124, 0, 1136659280}

j = 

rc = 0

iSpace1 = 0

szNew = {17, 17, 1082063336, 78580, 1402073160}

pgno = 1403366712

k = 

apDiv = {0x5418e14f "", 0x5418e13e ""}

cntNew = {1404621896, 746250, 0, 65540, 1082010372}

szCell = 

nCell = 0

nOld = 1403269800

usableSpace = 

aPgFlags = {46552, 21503, 2, 0, 5456}

#8  balance (pCur=0x0, pCur at entry=0x4080e1b0 ) at 
sqlite3.c:60469

pSpace = 0x5418def8 "\350\336\030T\350\336\030Tl"

pParent = 

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Eric Hill]

Thanks, Dan.  I grabbed the fix and tried it out - works great, and a much 
better fix than what I suggested (not shocking).  It really cleans up 
xBestIndex.  In my simple cases, it's like the usable flag is now superfluous; 
xBestIndex is only getting passed usable constraints (and not getting called it 
all when there are no usable constraints).  But perhaps there are still 
scenarios where an unusable constraint will show up.

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, June 11, 2015 1:14 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Virtual tables/xBestIndex: Is this a bug?

On 06/11/2015 03:49 AM, Eric Hill wrote:
> Is it a bug that SQLite changes the order of the constraints passed to 
> xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
> nothing about what the order of the ON clause should be?

No. The order is undefined.

However, it is less than perfect that the constraints on "film_id=" are marked 
usable when they are really not. That's the root of your problem I think - 
SQLite is asking for the wrong thing. When it eventually figures out that it 
can't actually use the plan it requested from xBestIndex (because the film_id= 
constraint is not actually usable) it falls back to a linear scan.

There is now a change on the trunk that should fix this:

   http://www.sqlite.org/src/info/7b446771cadedafb

Dan.

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

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Eric Hill]

Hey,

This is a follow-up from the thread entitled "Virtual Table query - why isn't 
SQLite using my indexes?" in order to raise the visibility of this issue:

Consider this SQL, where all of the tables involved are virtual:


SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,

 t4.category_id, t5."name"

FROM rental  t2

 LEFT OUTER JOIN inventory t1

  ON  ( t1.inventory_id = t2.inventory_id )

 LEFT OUTER JOIN film t3

  ON  ( t3.film_id = t1.film_id )

 LEFT OUTER JOIN film_category t4

  ON  ( t4.film_id = t1.film_id )

 LEFT OUTER JOIN category t5

  ON  ( t5.category_id = t4.category_id )  ;

When xBestIndex gets called for the inventory table, the constraints will be in 
this order:


jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3

   CONST[0]: 0 (inventory_id) = Usable

   CONST[1]: 1 (film_id) = Usable

   CONST[2]: 1 (film_id) = Usable



This is helpful, because the constraint that matters, i.e., the column that 
SQLite would benefit from having an index on, is listed first.  However, if I 
instead submit this SQL:


SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,

t4.category_id, t5."name"

FROM rental  t2

LEFT OUTER JOIN inventory t1

ON  ( t2.inventory_id = t1.inventory_id )

LEFT OUTER JOIN film t3

ON  ( t3.film_id = t1.film_id )

LEFT OUTER JOIN film_category t4

ON  ( t4.film_id = t1.film_id )

LEFT OUTER JOIN category t5

ON  ( t5.category_id = t4.category_id )  ;

where the only difference is the order of the ON clause for the LEFT OUTER JOIN 
with inventory, the xBestIndex call looks like this:


jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3

   CONST[0]: 1 (film_id) = Usable

   CONST[1]: 1 (film_id) = Usable

   CONST[2]: 0 (inventory_id) = Usable

So, with just that tiny change, now the interesting constraint comes last 
instead of first.

Is it a bug that SQLite changes the order of the constraints passed to 
xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
nothing about what the order of the ON clause should be?

I am attempting to create whatever indexes SQLite tells me it needs, but SQLite 
is playing a shell game with this information.

Thanks very much!

Eric

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Surely enough, this SQL:

SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, 
t4.category_id, t5."name" 
FROM rental  t2 
LEFT OUTER JOIN inventory t1 
ON  ( t1.inventory_id = t2.inventory_id )  
LEFT OUTER JOIN film t3 
ON  ( t3.film_id = t1.film_id )  
LEFT OUTER JOIN film_category t4 
ON  ( t4.film_id = t1.film_id )  
LEFT OUTER JOIN category t5 
ON  ( t5.category_id = t4.category_id )  ;

generates the desired xBestIndex call:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable

allowing the "first constraint" heuristic to be successful.

So, in the case where my application is generating SQL, I can fix it so that 
the ON is generated in the helpful order.  But I also let users type in their 
own SQL.

Would it be too much to ask SQLite to determine  vs.  based 
on context rather than inferring based on position?  I'm looking at SQL In a 
Nutshell, 3rd Edition, page 353, and the JOIN syntax example shows the ON 
statements in the order SQLite dislikes.

Thanks very much!

Eric


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
Sent: Monday, June 08, 2015 10:21 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

Can you try changing LHS and RHS in the first ON expression?

The older, larger query has inventory_id on the LHS and film_id on the RHS. Now 
you have all fields on the RHS.

It would seem the QP may be inferring   LEFT OUTER JOIN  ON 
( = ) And placing the  fields first in the argument 
list And adding the  fields only for symmetry reasons (they can never 
be used, because they point the "wrong way")

-Urspr?ngliche Nachricht-
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Montag, 08. Juni 2015 15:55
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler. 
 The example query from that thread was:

SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title,
t3."name" AS Category, t4.customer_id, t4.store_id, t4.email,
t5.length
FROM rental  t1
LEFT OUTER JOIN inventory t8
ON  ( t8.inventory_id = t1.inventory_id )
LEFT OUTER JOIN customer t4
ON  ( t4.customer_id = t1.customer_id )
LEFT OUTER JOIN film_category t7
ON  ( t7.film_id = t8.film_id )
LEFT OUTER JOIN film t5
ON  ( t5.film_id = t8.film_id )
LEFT OUTER JOIN category t3
ON  ( t3.category_id = t7.category_id )
LEFT OUTER JOIN film_actor t6
ON  ( t6.film_id = t5.film_id )
LEFT OUTER JOIN actor t2
ON  ( t2.actor_id = t6.actor_id )  ;

The "money" call to xBestIndex in that case looked like this:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable

In response to this, I originally promised to create an index using all 3 
constraints (two if which are the same), but SQLite rejected that and did a 
table scan instead, because it really only needed an index based on 
inventory_id.  So the heuristic we ended up with, which did fix this specific 
query, was (from Gunter):

> If you know nothing about a table except for the names of the fields 
> and the number of rows, then you are best off choosing the first 
> constraint only. (rows = cost = log n)

Well, now I have a counter-example.  This time, the SQL looks like this:

SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,
t4.category_id, t5."name"
FROM rental  t2
LEFT OUTER JOIN inventory t1
ON  ( t2.inventory_id = t1.inventory_id )
LEFT OUTER JOIN film t3
ON  ( t3.film_id = t1.film_id )
LEFT OUTER JOIN film_category t4
ON  ( t4.film_id = t1.film_id )
LEFT OUTER JOIN category t5
ON  ( t5.category_id = t4.category_id )  ;

For whatever reason, the "money" call to xBestIndex in this case looks like 
this:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3
   CONST[0]: 1 (film_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 0 (inventory_id) = Usable

The order of the constraints is different!  So, using the "first constraint" 
heuristic, I commit to indexing based on film_id, but indexing inventory on 
film_id not helpful for this query.  SQLite sees that inventory is indexed on 
film_id and decides to use table scan fo

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler. 
 The example query from that thread was:

SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title, 
t3."name" AS Category, t4.customer_id, t4.store_id, t4.email, 
t5.length 
FROM rental  t1 
LEFT OUTER JOIN inventory t8 
ON  ( t8.inventory_id = t1.inventory_id )  
LEFT OUTER JOIN customer t4 
ON  ( t4.customer_id = t1.customer_id )  
LEFT OUTER JOIN film_category t7 
ON  ( t7.film_id = t8.film_id )  
LEFT OUTER JOIN film t5 
ON  ( t5.film_id = t8.film_id )  
LEFT OUTER JOIN category t3 
ON  ( t3.category_id = t7.category_id )  
LEFT OUTER JOIN film_actor t6 
ON  ( t6.film_id = t5.film_id )  
LEFT OUTER JOIN actor t2 
ON  ( t2.actor_id = t6.actor_id )  ;

The "money" call to xBestIndex in that case looked like this:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable

In response to this, I originally promised to create an index using all 3 
constraints (two if which are the same), but SQLite rejected that and did a 
table scan instead, because it really only needed an index based on 
inventory_id.  So the heuristic we ended up with, which did fix this specific 
query, was (from Gunter):

> If you know nothing about a table except for the names of the fields 
> and the number of rows, then you are best off choosing 
> the first constraint only. (rows = cost = log n)

Well, now I have a counter-example.  This time, the SQL looks like this:

SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, 
t4.category_id, t5."name" 
FROM rental  t2 
LEFT OUTER JOIN inventory t1 
ON  ( t2.inventory_id = t1.inventory_id )  
LEFT OUTER JOIN film t3 
ON  ( t3.film_id = t1.film_id )  
LEFT OUTER JOIN film_category t4 
ON  ( t4.film_id = t1.film_id )  
LEFT OUTER JOIN category t5 
ON  ( t5.category_id = t4.category_id )  ;

For whatever reason, the "money" call to xBestIndex in this case looks like 
this:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3
   CONST[0]: 1 (film_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 0 (inventory_id) = Usable

The order of the constraints is different!  So, using the "first constraint" 
heuristic, I commit to indexing based on film_id, but indexing inventory on 
film_id not helpful for this query.  SQLite sees that inventory is indexed on 
film_id and decides to use table scan for inventory, and it's game over.

If SQLite calls to xBestIndex do not in some way convey which constraints 
matter and which ones don't, I don't see how I can use virtual tables.

Here is the WhereTrace, in case it might help:

*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
 begin solver.  (nRowEst=0)
New0 cost=216,200 order=0
 after round 0 
 0 cost=216 nrow=200 order=0
 begin solver.  (nRowEst=201)
New0 cost=216,200 order=1
 after round 0 
 0 cost=216 nrow=200 order=1 rev=0x0
 Solution nRow=200 ORDERBY=1,0x0
0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
   skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
 begin solver.  (nRowEst=0)
New0 cost=271, 43 order=0
Update 1 cost=216,180 order=0  was 0 cost=271, 43 order=0
 after round 0 
 1 cost=216 nrow=180 order=0
 begin solver.  (nRowEst=181)
 sort cost=239 (1/1) increases cost 271 to 272
New0 cost=272, 43 order=0
Update 1 cost=216,180 order=1  was 0 cost=272, 43 order=0
 after round 0 
 1 cost=216 nrow=180 order=1 rev=0x0
 Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Sorry, scratch that.  T2 was not a virtual table when I ran this query.  My 
bad.  Told you I was a noob.

Eric

-Original Message-
From: Eric Hill 
Sent: Wednesday, May 20, 2015 12:32 PM
To: 'General Discussion of SQLite Database'
Subject: RE: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

Eric had said:

>> But then what about a query like this:
>>
>>SELECT * FROM T1
>>LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = 
>> T1.b ) AND ( T2.c = T1.c );
>>
>> xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in 
>> that order.  

To which Gunter replied in part:

>  an Index on the LHS Table of a LEFT OUTER join never helps, because 
> you are requesting to retrieve all rows of the LHS anyway. SQLite is calling 
> xBestIndex with constraints on T1 for symmetry purposes only (this may even 
> be considered a bug).
> Try again with indexes on T2.

Very good point, so this is what puzzles me:  xBestIndex is *never called* for 
T2 for this query (!).  To my undoubtedly noob mind, *that* seems like a bug.

Thanks again!

Eric

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Eric had said:

>> But then what about a query like this:
>>
>>SELECT * FROM T1
>>LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = T1.b ) AND 
>> ( T2.c = T1.c );
>>
>> xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in 
>> that order.  

To which Gunter replied in part:

>  an Index on the LHS Table of a LEFT OUTER join never helps, because you are 
> requesting to retrieve all rows of the LHS 
> anyway. SQLite is calling xBestIndex with constraints on T1 for symmetry 
> purposes only (this may even be considered a bug). 
> Try again with indexes on T2.

Very good point, so this is what puzzles me:  xBestIndex is *never called* for 
T2 for this query (!).  To my undoubtedly noob mind, *that* seems like a bug.

Thanks again!

Eric

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

R JOIN film_actor t5
ON  ( t5.film_id = t4.film_id )
LEFT OUTER JOIN film_category t6
ON  ( t6.film_id = t4.film_id )
LEFT OUTER JOIN category t8
ON  ( t8.category_id = 
t6.category_id )
LEFT OUTER JOIN actor t7
ON  ( t7.actor_id = t5.actor_id 
);

When xBestIndex is called, SQLite is asking for the single BEST access method, 
given any subset of the passed constraints, not an array of possible methods. 
Since the xxx_id fields are already UNIQUE, there is NO POINT in producing an 
index with additional fields (other than maybe producing a convering index, 
which is only supported for native tables - there is no equivalent method for 
virtual tables).

Sticking with the inventory table, it is worth noting, that there may be 
several distinct entries with identical film_id (it makes business sense to 
have more than 1 copy of a film that rents well), only one of which will match 
any given rental record.

SQLIte will call xBestIndex twice:

Once with no constraints, which is handled by rows = cost = n.

Once with two constraints, inventory_id and film_id; you now have several 
choices (SQL equivalent shown):

- CREATE UNIQUE INDEX inventory_id ON inventory (inventory_id );

This is answered correctly by setting rows = 1, cost = log(n), and the 
argvIndex = 1 and omit = 1 for the inventory_id constraint ONLY(!!!)

- CREATE INDEX inventory_film ON inventory (film_id);

This is answered correctly by setting rows = n /  
(=the average number of copies of a film) , cost = rows + log(n) (= the cost of 
locating the first row + the number of rows), and the argvIndex = 1 and omit = 
1 for the film_id constraint ONLY(!!!).

- CREAT UNIQUE INDEX inventory_film ON inventory ( film_id, inventory_id );

This is only useful as a covering index for native tables; as a partial 
table scan, it is equivalent to the previous non-unqiue index; as inventory_id 
would be required for a key lookup, it would be a less efficient version of the 
first index; and even worse, in the query shown you do not have film_id AND 
inventory_id as known values AT THE SAME TIME, so SQLite would discrad this 
index as USELESS anyway.

 (which is precisely what is happening in your case - SQLite is finding that it 
does not know t1.inventory_id AND t4.film_id AND t5.film_id AND t6.film_id, 
which you state as required to use your index)

Gunter

PS: Unless you have a very clever way of creating an index on the fly (in less 
than O(n log n) time), it is well worth the effort to determine which indexes 
to maintain beforehand.

-Urspr?ngliche Nachricht-
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Montag, 18. Mai 2015 22:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

Per Richard's request, I have produced the WhereTrace for the query that uses 
virtual tables and the same query that is not using virtual tables.  That 
output is at the end of this message.  Ultimately, I think my question is, how 
should I respond to xBestIndex such that SQLite will perform the query as 
efficiently with virtual tables as it does when I copy all the data into SQLite?

Hick asked:

> Are you setting the constraintUsage return parameters correctly?

I suspect not.  I think I don't understand what to do when xBestIndex is passed 
multiple constraints, because those are the cases when SQLite rejects my 
indexes.  And, from debugging, my indexes are being rejected because this 
condition (at the tail end of whereLoopFindLesser()):

if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */

is false, so the costs I am returning never get considered.

Here is an example.  For the inventory table, xBestIndex gets called twice, 
each time with 4 constraints, 3 of which happen to be the same, film_id, 
presumably because inventory.film_id is used in 3 different join constraints:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Unusable
   CONST[1]: 1 (film_id) = Unusable
   CONST[2]: 1 (film_id) = Unusable
   CONST[3]: 1 (film_id) = Unusable
   Index NOT created: est. cost: 440393770484721

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable
   CONST[3]: 1 (film_id) = Usable
   Index created: est. cost: 1

The first time, all the constraints are marked unusable, so I set argvIndex and 
omit to 0 for all four constraints and set the estimatedCost to a ridiculously 
large number.  The second time, all of the constraints are marked as usable.  
My data does not have any pre-existing indexes; I'm willing to create what

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Per Richard's request, I have produced the WhereTrace for the query that uses 
virtual tables and the same query that is not using virtual tables.  That 
output is at the end of this message.  Ultimately, I think my question is, how 
should I respond to xBestIndex such that SQLite will perform the query as 
efficiently with virtual tables as it does when I copy all the data into SQLite?

Hick asked:

> Are you setting the constraintUsage return parameters correctly?

I suspect not.  I think I don't understand what to do when xBestIndex is passed 
multiple constraints, because those are the cases when SQLite rejects my 
indexes.  And, from debugging, my indexes are being rejected because this 
condition (at the tail end of whereLoopFindLesser()):

if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */

is false, so the costs I am returning never get considered.

Here is an example.  For the inventory table, xBestIndex gets called twice, 
each time with 4 constraints, 3 of which happen to be the same, film_id, 
presumably because inventory.film_id is used in 3 different join constraints:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Unusable
   CONST[1]: 1 (film_id) = Unusable
   CONST[2]: 1 (film_id) = Unusable
   CONST[3]: 1 (film_id) = Unusable
   Index NOT created: est. cost: 440393770484721

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable
   CONST[3]: 1 (film_id) = Usable
   Index created: est. cost: 1

The first time, all the constraints are marked unusable, so I set argvIndex and 
omit to 0 for all four constraints and set the estimatedCost to a ridiculously 
large number.  The second time, all of the constraints are marked as usable.  
My data does not have any pre-existing indexes; I'm willing to create whatever 
indexes are needed to speed up the query.  So I set omit to 1 and argvIndex to 
1, 2, 3, and 4 respectively for the four constraints.  This is clearly where I 
am confusing SQLite.  

Here is a simpler example, for the film_actor table.  xBestIndex is again 
called twice:

jmpvtab BEST INDEX:  Table: film_actor  nConstraints: 2
   CONST[0]: 1 (film_id) = Unusable
   CONST[1]: 0 (actor_id) = Unusable
   Index NOT created: est. cost: 890034380901136

jmpvtab BEST INDEX:  Table: film_actor  nConstraints: 2
   CONST[0]: 1 (film_id) = Usable
   CONST[1]: 0 (actor_id) = Usable
   Index created: est. cost: 1

Here again, in the second case, I set omit to 1 for both and set argvIndex to 1 
for film_id and 2 for actor_id.  What I am trying to tell SQLite is that I am 
willing to make an index that sorts first by film_id and then by actor_id for 
rows with the same film_id.  But I'm thinking that's not what SQLite wants.

Since I'm going to have to create an index for whichever constraint that SQLite 
is going to filter on, maybe I should just take the first constraint, set omit 
to 1 and argvIndex to 1, and set omit and argvIndex to 0 for all other 
constraints?  And then set the cost to n(log n) where n is number of rows, 
since that is the cost of making an index?

Thanks very much for your help.  WhereTrace follows:

=
WhereTrace when USING virtual tables:
=

New0 cost=272, 43 order=0
Update 1 cost=216,180 order=1  was 0 cost=272, 43 order=0
 after round 0 
 1 cost=216 nrow=180 order=1 rev=0x0
 Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
   skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
 begin solver.  (nRowEst=0)
New0 cost=271, 43 order=0
Update 1 cost=216,180 order=0  was 0 cost=271, 43 order=0
 after round 0 
 1 cost=216 nrow=180 order=0
 begin solver.  (nRowEst=181)
 sort cost=239 (1/1) increases cost 271 to 272
New0 cost=272, 43 order=0
Update 1 cost=216,180 order=1  was 0 cost=272, 43 order=0
 after round 0 
 1 cost=216 nrow=180 order=1 rev=0x0
 Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
 Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Thanks, Dominique, for the tip on multifile amalgamation. After a few internet 
searches and the acquisition of gawk.exe and tclsh85.exe, I was able to build 
the multi-file amalgamation for 3.8.10.  Tomorrow, I will see if I can tell why 
SQLite is choosing table scans over using my indexes.  If anyone has tips on 
where to set breakpoints, that would be helpful.

One thing I did do:  Since I can copy the data into SQLite and do the same join 
as I was trying using virtual tables, I did that with EXPLAIN QUERY PLAN, and I 
got this:

0   0   0   SCAN TABLE rental10 AS t1
0   1   1   SEARCH TABLE inventory AS t2 USING AUTOMATIC COVERING 
INDEX (inventory_id=?)
0   2   2   SEARCH TABLE customer AS t3 USING AUTOMATIC COVERING 
INDEX (customer_id=?)
0   3   3   SEARCH TABLE film_category AS t6 USING AUTOMATIC 
COVERING INDEX (film_id=?)
0   4   4   SEARCH TABLE film AS t4 USING AUTOMATIC COVERING INDEX 
(film_id=?)
0   5   5   SEARCH TABLE film_actor AS t5 USING AUTOMATIC COVERING 
INDEX (film_id=?)
0   6   6   SEARCH TABLE category AS t8 USING AUTOMATIC COVERING 
INDEX (category_id=?)
0   7   7   SEARCH TABLE actor AS t7 USING AUTOMATIC COVERING INDEX 
(actor_id=?)

So, when SQLite has all the data, it figures out that it needs to use indexes, 
but when using my virtual tables, for some reason it doesn't.  Still working 
under the assumption that it is my fault.

Thanks,

Eric

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Thanks for your reply.

I went as high as using (number of rows)^4, resulting in a cost of ~440 
trillion for the unindexed case, along with setting the cost to 1 for the 
indexed case, and it still won't use my index.

I'd like to step out of my xBestFilter implementation into SQLite code to see 
if I can tell what is going on there, but when I step out, the call stack knows 
where I'm supposed to be, but the debugger does not find the right line in 
sqlite3.c.  I have built sqlite3.c simply by adding the amalgamation to my 
Microsoft Visual Studio 2013 C++ project.  Is there something I can do to make 
the debugger work?

I will postpone index creation until the call to xFilter, I reckon, once I work 
out these other issues.  Thanks for the tip!

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, May 15, 2015 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

On 5/15/15, Eric Hill  wrote:
>
> So, in the first case, I do not create an index (which I signify by 
> setting idxNum to -999), and I set the cost (and, est. rows) to 4581.  
> In the second case, I create an index (0) and set cost to log10(4581) 
> = 3.66 and est. rows to 4.  Yet, later, whenever xFilter is called for 
> the inventory table, SQLite passes in idxNum = 999 and nConstraints = 
> 0.  The index I dutifully created is never asked for.  In cases where 
> there is a single constraint, SQLite does ask request the index in the 
> xFilter call, but it seems that for all the cases where multiple 
> constraints are involved, the index is not being used.
>

Two things:

(1) You probably shouldn't be "creating an index" in response to an xBestIndex 
call.  xBestIndex should be thought of as a "what-if"
function.  It is asking your virtual table what it could do with a query given 
certain constraints.  SQLite makes no guarantees that it will actually ever 
call your virtual table that way - it is merely exploring possibilities.

(2) The query planner looks at many different cost factors and tries to pick 
the best overall query plan.  You've told it that running your virtual table 
without an index is 1252 times slower than running it with an index.  And it 
takes this into consideration.  That SQLite is not choosing to use the virtual 
table index indicates that some other part or parts of the join would be more 
than 1252 times slower if the virtual table index were in fact used, and so the 
overall query plan is faster even without the virtual table index.  If these 
estimates are incorrect, then an obvious work-around is merely to increase the 
cost of not using the indexing mode on the virtual table.  Have it return 10x 
or 100x the cost (45810 or 458100) when not using an index, and see if that 
helps.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

[Eric Hill]

Hey,

Let me say up front that I'm sure this is my fault.  I have SQLite version 
3.8.9.

I am using virtual tables, and I am trying to get xBestIndex and xFilter doing 
the right things so that I get optimal queries.  Currently, I am working on the 
following query:

SELECT t1.rental_date, t1.inventory_id, t1.customer_id, 
t2.film_id,
t2.store_id, t3.first_name AS cust_firstname, 
t3.last_name AS cust_lastname, t3.email,
t6.category_id, t4.title, t4.release_year, 
t4.length,
t4.rating, t4.rental_rate, t5.actor_id, t8.name 
AS category,
t7.first_name AS actor_firstname, t7.last_name 
AS actor_lastname
FROM rental10  t1
LEFT OUTER JOIN inventory t2
ON  ( t2.inventory_id = 
t1.inventory_id )
LEFT OUTER JOIN customer t3
ON  ( t3.customer_id = 
t1.customer_id )
LEFT OUTER JOIN film_category t6
ON  ( t6.film_id = t2.film_id )
LEFT OUTER JOIN film t4
ON  ( t4.film_id = t2.film_id )
LEFT OUTER JOIN film_actor t5
ON  ( t5.film_id = t2.film_id )
LEFT OUTER JOIN category t8
ON  ( t8.category_id = 
t6.category_id )
LEFT OUTER JOIN actor t7
ON  ( t7.actor_id = t5.actor_id 
);

When I execute this query, the result is correct, but it is taking too long by 
an order of magnitude or two.  It seems to be doing full table scans despite 
the fact that I am creating indexes as requested.

For example, xBestIndex gets called for the inventory table twice, with four 
constraints, once with usable set to false for all four constraints, and once 
with usable set to true for all four.  Here is my printf debugging spew:

jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Unusable
   CONST[1]: 1 (film_id) = Unusable
   CONST[2]: 1 (film_id) = Unusable
   CONST[3]: 1 (film_id) = Unusable
   Index NOT created: est. cost: 4581
jmpvtab BEST INDEX:  Table: inventory  nConstraints: 4
   CONST[0]: 0 (inventory_id) = Usable
   CONST[1]: 1 (film_id) = Usable
   CONST[2]: 1 (film_id) = Usable
   CONST[3]: 1 (film_id) = Usable
   Index created: est. cost: 3.66096029177608

So, in the first case, I do not create an index (which I signify by setting 
idxNum to -999), and I set the cost (and, est. rows) to 4581.  In the second 
case, I create an index (0) and set cost to log10(4581) = 3.66 and est. rows to 
4.  Yet, later, whenever xFilter is called for the inventory table, SQLite 
passes in idxNum = 999 and nConstraints = 0.  The index I dutifully created is 
never asked for.  In cases where there is a single constraint, SQLite does ask 
request the index in the xFilter call, but it seems that for all the cases 
where multiple constraints are involved, the index is not being used.

I did EXPLAIN QUERY PLAN for the query and got this, consistent with what I'm 
seeing:

0  0  0  SCAN TABLE rental10 AS t1 VIRTUAL 
TABLE INDEX -999:
0  1  1  SCAN TABLE inventory AS t2 VIRTUAL 
TABLE INDEX -999:
0  2  2  SCAN TABLE customer AS t3 VIRTUAL 
TABLE INDEX 0:
0  3  3  SCAN TABLE film_category AS t6 
VIRTUAL TABLE INDEX -999:
0  4  4  SCAN TABLE film AS t4 VIRTUAL 
TABLE INDEX 0:
0  5  5  SCAN TABLE film_actor AS t5 
VIRTUAL TABLE INDEX -999:
0  6  6  SCAN TABLE category AS t8 VIRTUAL 
TABLE INDEX -999:
0  7  7  SCAN TABLE actor AS t7 VIRTUAL 
TABLE INDEX 0:

Now, I know that SQLite is capable of efficiently performing this query, 
because I also have the ability to copy these tables into SQLite so that I am 
querying real tables instead of virtual tables.  SQLite can perform the query 
in under 1 second with real tables, but with virtual tables, it is taking > 25 
seconds.

Any thoughts on what I can do to convince SQLite to use my indexes?

Thanks,

Eric

[sqlite] xBestIndex() implementation question

[Eric Hill]

I'm just a few days into an xBestIndex/xFilter implementation myself, but my 
approach is, if I can create the requested index, I set the cost to log(number 
of rows) of the table, and if I am choosing not to create the requested index, 
I set the cost to number of rows.  I suspect that gives SQLite enough 
information to optimize the query.

Eric


From: Jilong Kuang
Sent: ?Wednesday?, ?May? ?13?, ?2015 ?10?:?05? ?PM
To: General Discussion of SQLite Database

Hello,

I'm working on a project using SQLite virtual table. Now I have a problem about 
the implementation of xBestIndex() function, in particular, the estimatedRow 
and estimatedCost variables.

As the aConstraint array does not contain the RHS expression value for each 
constraint (only iColumn and op), how am I supposed to deduce the appropriate 
value for both estimatedRow and estimatedCost?

For example, let's say one constraint "...Where id > 50...". The value of 50 is 
not passed into the sqlite3_index_info struct. Can you explain how to handle 
this situation?

I just want to have a more accurate cardinality estimation to improve query 
performance. But I do not know how to do it without the value field.

Thank you very much for your time.

Best regards,
Jilong
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please confirm what I THINK I know about blobs

[Eric Hill]

This approach:

CREATE TABLE blob_table (
  ModelNo TEXT,
  SerialNo TEXT,
  VSWR BLOB_DOUBLE
)

involves comments?  I don't see how.  Nothing wrong with the comments approach, 
but this is an approach that just takes advantage of the fact that SQLite does 
not have fixed data types.

Eric

From: Simon Slavin
Sent: ?Saturday?, ?May? ?9?, ?2015 ?3?:?52? ?PM
To: General Discussion of SQLite Database


On 9 May 2015, at 8:12pm, Drago, William @ CSG - NARDA-MITEQ  wrote:

> Best idea yet! Anyone see any issues with this?

It's actually a comment, and SQLite provides ways of putting proper comments in 
table definitions:

CREATE TABLE blob_table (
 ModelNo TEXT, -- new-style models as used from 2006 onwards
 SerialNo TEXT,
 VSWR BLOB -- array of ten double-length floats
)

These comments can be found if you look at the table definition in 
sqlite_master.

I've also seen SQL databases where the designer created an otherwise unused 
table to hold comments on every column, something like this:

CREATE TABLE _structure (
 tableName TEXT,
 columnName TEXT,
 introduced TEXT,
 variableType TEXT,
 theComments TEXT
)

'introduced' was the edit of their program which first used the column 
(equivalent to 'checkin' as used by the SQLite development team).  
'variableType' was not the SQL type but the type of variable in the programming 
language they were using the database with.  This helped because the language 
had numerous variable types and subtle bugs could be introduced if you, for 
example, stored a value from an unsigned integer then did maths on the value in 
a long integer.

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

[sqlite] Please confirm what I THINK I know about blobs

[Eric Hill]

The comment approach could work, I guess, but why not just encode the type into 
the column's declared type?

CREATE TABLE blob_table (
  ModelNo TEXT,
  SerialNo TEXT,
  VSWR BLOB_DOUBLE
)

That's what I do with numeric columns that I need to identify as actually 
containing dates.  As I understand it, SQLite only scans those type names to 
choose a column affinity, so you are free to be as creative with them as you 
want.  sqlite3_column_decltype() will return whatever you put as the type.

HTH,

Eric

Sent from Windows Mail

From: William Drago
Sent: ?Saturday?, ?May? ?9?, ?2015 ?7?:?18? ?AM
To: sqlite-users at mailinglists.sqlite.org

On 5/9/2015 6:40 AM, Eduardo Morras wrote:
> On Sat, 09 May 2015 06:09:41 -0400
> William Drago  wrote:
>
>> All,
>>
>> Say you encounter a blob in a database. There's no way to
>> tell if that blob carries bytes, floats, doubles, etc, correct?
>>
>> Assuming the above is true, then is it always prudent to
>> store some metadata along with your blobs so that they can
>> be identified in the future?
>>
>> Example table:
>>
>> ModelNo TEXT (e.g. SO-239)
>> SerialNo TEXT (e.g. 101)
>> VSWR BLOB (e.g. x'feab12c...')
>> VSWR_Type TEXT (e.g. double)
>>
>>
>> Does this make sense?
> You can use SQL comments on CREATE TABLE, those comments aren't deleted from 
> SQLITE_MASTER table, you can query it as a normal table.
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> );
>
> SELECT sql from sqlite_master where type='table' AND tbl_name='blob_table';
>
> will return
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> )

This is a clever idea and saves the addition of a column
just for blob type. Is this a reliable feature of SQLite?
Does anyone see any issues with this as opposed to using a
dedicated column?

Thanks,
-Bill


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