Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[Hick Gunter]

Maybe you mean  (assuming there is not more than one record in t2 for  a given 
SSID-CELLID-SECTOR)

UPDATE t1 ...

-Ursprüngliche Nachricht-
Von: MikeSnow [mailto:michael.sab...@gmail.com]
Gesendet: Freitag, 09. Jänner 2015 00:12
An: sqlite-users@sqlite.org
Betreff: [sqlite] Error while executing query: no such column: 
t1.*B.Switch-Tower-Sector

I was wondering if anyone could let me know where I am going wrong. I am 
getting the error...
"Error while executing query: no such column: t1.*B.Switch-Tower-Sector"
but, the column,  t1.[*B.Switch-Tower-Sector], does exist.  I get results when 
I do select[*B.Switch-Tower-Sector] from t1; but an error when I do the UPDATE 
statement...

UPDATE t2
SET [*B.ANT_ORIENTATION] =
(SELECT t2.ANT_ORIENTATION
FROM t2
WHERE
t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);

Any help would be much appreciated.
thanks
mike



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Error-while-executing-query-no-such-column-t1-B-Switch-Tower-Sector-tp79905.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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

[Dan Kennedy]

On 01/08/2015 07:48 AM, Philip Warner wrote:

I just saw the SQLite Android Bindings page at

http://www.sqlite.org/android/doc/trunk/www/index.wiki

but was a little disappointed to read in the details that UNICODE and 
LOCALIZED are not supported. I'd really like the latest SQLite, and 
LOCALIZED.


How difficult would it be to add LOCALIZED collation support? I'm 
guessing that the fact it's not there means it's non-trivial, but I 
was hoping otherwise...


The stumbling block is that the Android implementations use ICU. So to 
use the Android versions I think we would have to build ICU as a static 
library as well as SQLite. And ICU is quite large.


The implementations are in the file "sqlite3_android.cpp" (part of the 
Android source tree - should be possible to google it). They look quite 
self-contained, so if you were willing to build ICU as part of your app 
and hack around with the code you could probably get them to work 
without too much trouble.


Dan.



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

Re: [sqlite] Uncached performance

[Teg]

Hello Donald,


DG> 1) Might there be further performance gains by placing the blobs in a
DG> separate table?
DG> E.g.
DG> CREATE TABLE myBlobs (
DG> idINTEGER PRIMARY KEY REFERENCES global (id),
DG> value BLOB
DG> );
DG> Then (if you haven't rebuilt a new db, and perhaps only once ever) run
DG> VACUUM and ANALYZE.

This  is  how  I  do  it.  Seems  to  give  me the best performance. I
sometimes put them in their own DB file and then attach this file too.
I have DB's with 30+ gigs of blobs in them. 

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

Re: [sqlite] a couple of crashing bugs from a fuzzer

[jungle Boogie]

Richard,
On 8 January 2015 at 17:29, Richard Hipp  wrote:
> On 1/8/15, Michal Zalewski  wrote:
>
>> I have been running afl-fuzz against sqlite and bumped in a bunch of
>> bugs that seem to crash the sqlite3 binary
>
> Fixed here: https://www.sqlite.org/src/info/fe578863313128
>
> Bug report for your trophy case: 
> https://www.sqlite.org/src/info/a59ae93ee990a55
>
> Nice work.  I've never heard of afl-fuzz before, but you can bet I'm
> going to be studying up on it!
>
>

I wish you would work at my company! I found two lazy coding bugs
today and it will probably be several weeks before anyone is
'authorized' to review the cases and then weeks to consider repair.

Contrast that with you: You're giving sqlite away and fixing bugs
within 8 hours!

OpenBSD people have found many bugs with afl, too.

-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[RSmith]

On 2015/01/09 01:12, MikeSnow wrote:

I was wondering if anyone could let me know where I am going wrong. I am
getting the error...
"Error while executing query: no such column: t1.*B.Switch-Tower-Sector"
but, the column,  t1.[*B.Switch-Tower-Sector], does exist.  I get results
when I do
select[*B.Switch-Tower-Sector] from t1;
but an error when I do the UPDATE statement...

UPDATE t2
SET [*B.ANT_ORIENTATION] =
(SELECT t2.ANT_ORIENTATION
FROM t2
WHERE
t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);


Hi Mike,

There are many things that might be improved here, and one thing that is 
definitely wrong.

In the Update Query, you are asking the DB to set a column named "*B.ANT_ORIENTATION" in a Table called "t2" to another value in a 
column called "ANT_ORIENTATION" selected from the same table "t2" which can be found in some row it has to look up by seeing where 
the "*SSID-CELLID-SECTOR" column equals a value that can be found in a column called "*D.Sqitch-Tower-Sector" in a Table called "t1" 
- But where is this table "t1" referenced in the query?  Nowhere - not in the UPDATE clause, not in the FROM clause, how must it 
know what t1 refers to?


Even if the table exists in the database, you cannot just reference some column from it without telling the query to scan that table 
in a FROM clause.


Even then, if you add that table to the from clause by means of a join or such, you still need to tell it explicitly which row to 
look up... The value for that column must be compared for which row?


Then, how must it distinguish between table t2 in the Update clause and t2 in 
the SELECT clause? You need a bit of an Alias I think.

I will try to re-write the query here to what I /THINK/ might be a pseudo-query 
of what you intended, but this is just a guess:

UPDATE t2 SET [*B.ANT_ORIENTATION] = (
  SELECT SS.ANT_ORIENTATION
FROM t2 AS S2
LEFT JOIN t1 AS S1 ON S2.[*SSID-CELLID-SECTOR] = S1.[*B.Switch-Tower-Sector]
  WHERE S2.[SOME_ID]=t2.[SOME_ID]  (...or perhaps 
S1.[SOME_COLUMN]=t2.[SOME_COLUMN]?)
);

If you give us the table layouts (schemata) and explain in simple terms what answer you need from the query, we'd be able to make a 
more accurate suggestion on best query to use.


PS: While The MS SQL SERVER type brackets [ and ] are allowed in SQLite because of niceness, it isn't best practice or correct SQL. 
The SQL standard calls for enclosing columns and table  object names in double quotes:  "column_name";  and values in single quotes: 
'value'.  Another less than optimal practice is using SQL-specific control characters in object names, such as the asterisk - or 
having columns or aliases that are reserved words for the specific engine. It should mostly work though.



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

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[Igor Tandetnik]

On 1/8/2015 6:12 PM, MikeSnow wrote:

"Error while executing query: no such column: t1.*B.Switch-Tower-Sector"
but, the column,  t1.[*B.Switch-Tower-Sector], does exist.  I get results
when I do
select[*B.Switch-Tower-Sector] from t1;
but an error when I do the UPDATE statement...

UPDATE t2
SET [*B.ANT_ORIENTATION] =
(SELECT t2.ANT_ORIENTATION
FROM t2
WHERE
t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);


Before you can use a table name elsewhere, you must introduce it in a 
FROM clause (or INSERT INTO, UPDATE or DELETE clause).

--
Igor Tandetnik

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

Re: [sqlite] a couple of crashing bugs from a fuzzer

[Richard Hipp]

On 1/8/15, Michal Zalewski  wrote:

> I have been running afl-fuzz against sqlite and bumped in a bunch of
> bugs that seem to crash the sqlite3 binary

Fixed here: https://www.sqlite.org/src/info/fe578863313128

Bug report for your trophy case: https://www.sqlite.org/src/info/a59ae93ee990a55

Nice work.  I've never heard of afl-fuzz before, but you can bet I'm
going to be studying up on it!


 (but do not seem to be a
> security problem, other than in the denial-of-service sense). There
> are four seemingly distinct patterns, with test cases included inline:
>
> -- test1.sql --
> create table t0(t);insert into t0
> select strftime();
> -- eof --
>
> This one is a failure in:
>
> sqlite3DbMallocRaw (db=0x2, n=32) at sqlite3.c:20567
> 20567   if( db->mallocFailed ){
>
> where db looks like something in the vicinity of NULL.
>
> #0  sqlite3DbMallocRaw (db=0x2, n=32) at sqlite3.c:20567
> #1  0x0053b17c in sqlite3VdbeMemGrow
> (pMem=pMem@entry=0xa9f4d8, n=, n@entry=32,
> bPreserve=bPreserve@entry=0) at sqlite3.c:61789
> #2  0x0057c904 in sqlite3VdbeMemClearAndResize (szNew=32,
> pMem=0xa9f4d8) at sqlite3.c:61831
> #3  sqlite3VdbeMemStringify (pMem=0xa9f4d8, enc=,
> bForce=) at sqlite3.c:61949
> #4  0x0057cf40 in valueToText (pVal=0xa9f4d8, enc=enc@entry=1
> '\001') at sqlite3.c:62681
> #5  0x0057d166 in sqlite3ValueText (pVal=,
> enc=1 '\001') at sqlite3.c:62714
> #6  0x00598fcd in sqlite3_value_text (pVal=) at
> sqlite3.c:67471
> #7  strftimeFunc (context=0x7fffca90, argc=0, argv=0xa9f358) at
> sqlite3.c:15657
>
> -- test2.sql --
> DETACH(select group_concat(q));
> -- eof --
>
> That one looks like a distinct NULL ptr deref:
>
> exprSrcCount (pWalker=, pExpr=0xa9eec8) at sqlite3.c:84694
> 84694   for(i=0; inSrc; i++){
>
> #0  exprSrcCount (pWalker=, pExpr=0xa9eec8) at
> sqlite3.c:84694
> #1  0x004fb86f in sqlite3WalkExpr (pWalker=0x7fffc400,
> pExpr=0xa9eec8) at sqlite3.c:79072
> #2  0x0069193f in sqlite3WalkExprList (p=,
> pWalker=0x7fffc400) at sqlite3.c:79095
> #3  sqlite3FunctionUsesThisSrc (pExpr=0xa9ed48, pSrcList=0x0) at
> sqlite3.c:19186
> #4  resolveExprStep (pWalker=pWalker@entry=0x7fffc480,
> pExpr=pExpr@entry=0xa9ed48) at sqlite3.c:14402
> #5  0x00692bcb in sqlite3WalkExpr (pExpr=0xa9ed48,
> pWalker=0x7fffc480) at sqlite3.c:79072
> #6  sqlite3ResolveExprNames (pNC=0x7fffc550, pExpr=0xa9ed48) at
> sqlite3.c:15026
> #7  0x00694b2b in resolveSelectStep (pWalker=,
> p=) at sqlite3.c:80402
> ...
>
> -- test3.sql --
> select(select strftime());
> -- eof --
>
> Faults in:
>
> sqlite3ValueText (pVal=0x1, enc=1 '\001') at sqlite3.c:62708
> 62708 if( (pVal->flags&(MEM_Str|MEM_Term))==(MEM_Str|MEM_Term) &&
> pVal->enc==enc ){
>
> #0  sqlite3ValueText (pVal=0x1, enc=1 '\001') at sqlite3.c:62708
> #1  0x00598fcd in sqlite3_value_text (pVal=) at
> sqlite3.c:67471
> #2  strftimeFunc (context=0x7fffca90, argc=0, argv=0xaa02d8) at
> sqlite3.c:15657
> #3  0x007883fb in sqlite3VdbeExec (p=0xa8d5a8) at sqlite3.c:70606
> #4  0x007aaf11 in sqlite3Step (p=0xaa0018) at sqlite3.c:67809
> #5  sqlite3_step (pStmt=) at sqlite3.c:2339
> #6  0x00425228 in shell_exec (db=0xa8d5a8,
> zSql=zSql@entry=0xa8d510 "select(select strftime());",
> pArg=pArg@entry=0x7fffcf50,
> pzErrMsg=pzErrMsg@entry=0x7fffce68, xCallback=0x41dd80
> )
> at shell.c:1365
> #7  0x0042a933 in process_input (p=p@entry=0x7fffcf50,
> in=0x77565640 <_IO_2_1_stdin_>) at shell.c:3697
>
> -- test4.sql --
> select n()AND+#00;
> -- eof --
>
> This dies for me at:
>
> #0  sqlite3ExprIsInteger (p=p@entry=0xa9edb8,
> pValue=pValue@entry=0x7fffc9a4) at sqlite3.c:16463
> #1  0x004f3aed in sqlite3ExprIsInteger (pValue=0x7fffc9a4,
> p=0xa9edb8) at sqlite3.c:81085
> #2  exprAlwaysFalse (p=0xa9edb8) at sqlite3.c:15665
> #3  sqlite3ExprAnd (db=0xa8d598, pLeft=0xa9eeb8, pRight=0xa9edb8) at
> sqlite3.c:15682
> #4  0x00687b86 in sqlite3PExpr (pParse=0xa9f0c8, op=72,
> pLeft=0xa9eeb8, pRight=0xa9edb8, pToken=0x0)
> at sqlite3.c:81167
> #5  0x00752a7c in spanBinaryExpr (pLeft=,
> pLeft=, pRight=,
> pRight=, op=, pParse= out>, pOut=) at sqlite3.c:120313
> #6  yy_reduce (yyruleno=, yypParser=) at
> sqlite3.c:57482
> #7  sqlite3Parser (yyp=0xa9f368, yymajor=-13916, yyminor=...,
> pParse=0x3d5, pParse@entry=0xa9f0c8) at sqlite3.c:58144
>
> /mz
> ___
> 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] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[Simon Slavin]

On 8 Jan 2015, at 11:12pm, MikeSnow  wrote:

> UPDATE t2
> SET   [*B.ANT_ORIENTATION] = 
> (SELECT t2.ANT_ORIENTATION
> FROM t2
> WHERE 
> t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);

You do not mention a specific row of t1, so it doesn't know what value you're 
talking about.

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

Re: [sqlite] Uncached performance

[Donald Griggs]

Hi, Maksim,

Others with more knowledge than I have given great advice regarding placing
your blob as the *last* table column, and about looking at cache size and
page size.

I wondered about three things:

1) Might there be further performance gains by placing the blobs in a
separate table?
E.g.
CREATE TABLE myBlobs (
idINTEGER PRIMARY KEY REFERENCES global (id),
value BLOB
);
Then (if you haven't rebuilt a new db, and perhaps only once ever) run
VACUUM and ANALYZE.

2) Any chance your app might be faster with your blobs in files instead of
in the db?  Is the following info useful?
 https://www.sqlite.org/intern-v-extern-blob.html


3) Are you truly certain the solution against which you compared sqlite did
not have the data in system cache?

Don't know if this is helpful,
Donald G.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[MikeSnow]

I was wondering if anyone could let me know where I am going wrong. I am
getting the error...
"Error while executing query: no such column: t1.*B.Switch-Tower-Sector"
but, the column,  t1.[*B.Switch-Tower-Sector], does exist.  I get results
when I do 
select[*B.Switch-Tower-Sector] from t1;
but an error when I do the UPDATE statement...

UPDATE t2
SET [*B.ANT_ORIENTATION] = 
(SELECT t2.ANT_ORIENTATION
FROM t2
WHERE 
t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);

Any help would be much appreciated.
thanks
mike



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Error-while-executing-query-no-such-column-t1-B-Switch-Tower-Sector-tp79905.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

[sqlite] a couple of crashing bugs from a fuzzer

[Michal Zalewski]

Hey folks,

I have been running afl-fuzz against sqlite and bumped in a bunch of
bugs that seem to crash the sqlite3 binary (but do not seem to be a
security problem, other than in the denial-of-service sense). There
are four seemingly distinct patterns, with test cases included inline:

-- test1.sql --
create table t0(t);insert into t0
select strftime();
-- eof --

This one is a failure in:

sqlite3DbMallocRaw (db=0x2, n=32) at sqlite3.c:20567
20567   if( db->mallocFailed ){

where db looks like something in the vicinity of NULL.

#0  sqlite3DbMallocRaw (db=0x2, n=32) at sqlite3.c:20567
#1  0x0053b17c in sqlite3VdbeMemGrow
(pMem=pMem@entry=0xa9f4d8, n=, n@entry=32,
bPreserve=bPreserve@entry=0) at sqlite3.c:61789
#2  0x0057c904 in sqlite3VdbeMemClearAndResize (szNew=32,
pMem=0xa9f4d8) at sqlite3.c:61831
#3  sqlite3VdbeMemStringify (pMem=0xa9f4d8, enc=,
bForce=) at sqlite3.c:61949
#4  0x0057cf40 in valueToText (pVal=0xa9f4d8, enc=enc@entry=1
'\001') at sqlite3.c:62681
#5  0x0057d166 in sqlite3ValueText (pVal=,
enc=1 '\001') at sqlite3.c:62714
#6  0x00598fcd in sqlite3_value_text (pVal=) at
sqlite3.c:67471
#7  strftimeFunc (context=0x7fffca90, argc=0, argv=0xa9f358) at
sqlite3.c:15657

-- test2.sql --
DETACH(select group_concat(q));
-- eof --

That one looks like a distinct NULL ptr deref:

exprSrcCount (pWalker=, pExpr=0xa9eec8) at sqlite3.c:84694
84694   for(i=0; inSrc; i++){

#0  exprSrcCount (pWalker=, pExpr=0xa9eec8) at sqlite3.c:84694
#1  0x004fb86f in sqlite3WalkExpr (pWalker=0x7fffc400,
pExpr=0xa9eec8) at sqlite3.c:79072
#2  0x0069193f in sqlite3WalkExprList (p=,
pWalker=0x7fffc400) at sqlite3.c:79095
#3  sqlite3FunctionUsesThisSrc (pExpr=0xa9ed48, pSrcList=0x0) at sqlite3.c:19186
#4  resolveExprStep (pWalker=pWalker@entry=0x7fffc480,
pExpr=pExpr@entry=0xa9ed48) at sqlite3.c:14402
#5  0x00692bcb in sqlite3WalkExpr (pExpr=0xa9ed48,
pWalker=0x7fffc480) at sqlite3.c:79072
#6  sqlite3ResolveExprNames (pNC=0x7fffc550, pExpr=0xa9ed48) at
sqlite3.c:15026
#7  0x00694b2b in resolveSelectStep (pWalker=,
p=) at sqlite3.c:80402
...

-- test3.sql --
select(select strftime());
-- eof --

Faults in:

sqlite3ValueText (pVal=0x1, enc=1 '\001') at sqlite3.c:62708
62708 if( (pVal->flags&(MEM_Str|MEM_Term))==(MEM_Str|MEM_Term) &&
pVal->enc==enc ){

#0  sqlite3ValueText (pVal=0x1, enc=1 '\001') at sqlite3.c:62708
#1  0x00598fcd in sqlite3_value_text (pVal=) at
sqlite3.c:67471
#2  strftimeFunc (context=0x7fffca90, argc=0, argv=0xaa02d8) at
sqlite3.c:15657
#3  0x007883fb in sqlite3VdbeExec (p=0xa8d5a8) at sqlite3.c:70606
#4  0x007aaf11 in sqlite3Step (p=0xaa0018) at sqlite3.c:67809
#5  sqlite3_step (pStmt=) at sqlite3.c:2339
#6  0x00425228 in shell_exec (db=0xa8d5a8,
zSql=zSql@entry=0xa8d510 "select(select strftime());",
pArg=pArg@entry=0x7fffcf50,
pzErrMsg=pzErrMsg@entry=0x7fffce68, xCallback=0x41dd80
)
at shell.c:1365
#7  0x0042a933 in process_input (p=p@entry=0x7fffcf50,
in=0x77565640 <_IO_2_1_stdin_>) at shell.c:3697

-- test4.sql --
select n()AND+#00;
-- eof --

This dies for me at:

#0  sqlite3ExprIsInteger (p=p@entry=0xa9edb8,
pValue=pValue@entry=0x7fffc9a4) at sqlite3.c:16463
#1  0x004f3aed in sqlite3ExprIsInteger (pValue=0x7fffc9a4,
p=0xa9edb8) at sqlite3.c:81085
#2  exprAlwaysFalse (p=0xa9edb8) at sqlite3.c:15665
#3  sqlite3ExprAnd (db=0xa8d598, pLeft=0xa9eeb8, pRight=0xa9edb8) at
sqlite3.c:15682
#4  0x00687b86 in sqlite3PExpr (pParse=0xa9f0c8, op=72,
pLeft=0xa9eeb8, pRight=0xa9edb8, pToken=0x0)
at sqlite3.c:81167
#5  0x00752a7c in spanBinaryExpr (pLeft=,
pLeft=, pRight=,
pRight=, op=, pParse=, pOut=) at sqlite3.c:120313
#6  yy_reduce (yyruleno=, yypParser=) at
sqlite3.c:57482
#7  sqlite3Parser (yyp=0xa9f368, yymajor=-13916, yyminor=...,
pParse=0x3d5, pParse@entry=0xa9f0c8) at sqlite3.c:58144

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

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

[Hick Gunter]

It is legal and well defined in SQLite. See the explain output below. This is 
because of the well-documented feature of SQLite that columns that are neither 
GROUPED BY nor aggregated will have a defined value.



First off, it is legal and perfectly normal to test for expressions containing 
columns that do not appear in the select list. Every JOIN with a foreign key 
that references a rowid does exactly that (nobody is interested in the 
internal, arbitrary rowid of the referenced table; just the facts please). The 
SQL Engine is expected to retrieve columns required to evaluate expressions.



The SQL standard defines WHERE as pertaining to the input rows and HAVING as 
pertaining to the output rows.



What you are really asking is:



SELECT count AS „count()“, a FROM (SELECT count() AS count, a, b FROM T GROUP 
BY a HAVING b > 0);



As already explained, the b value returned for each a will be (an arbitrary 
one) taken from the b values associated with this a. The fact that you are only 
interested in the first two columns of the result set does not preclude it 
containing addtional columns that are used because they are required to satisfy 
your query



asql> create temp table t (a int, b int, c int);

asql> .explain

asql> explain select count(),a from T group by a having b > 0;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

0 Trace  0 0 000  NULL

1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL

2 Integer0 5 000  clear abort flag

3 Integer0 4 000  indicate accumulator 
empty

4 Null   0 8 800  NULL

5 Gosub  7 44000  NULL

6 Goto   0 49000  NULL

7 OpenRead   0 2 1 2  00  t

8 Rewind 0 15000  NULL

9 Column 0 0 10   00  t.a

10Sequence   1 11000  NULL

11Column 0 1 12   00  t.b

12MakeRecord 103 13   00  NULL

13SorterInsert   1 13000  NULL

14Next   0 9 001  NULL

15Close  0 0 000  NULL

16OpenPseudo 2 13300  NULL

17SorterSort 1 48000  GROUP BY sort

18SorterData 1 13000  NULL

19Column 2 0 920  NULL

20Compare8 9 1 Keyinfo(1,BINARY)  00  NULL

21Jump   222622   00  NULL

22Move   9 8 100  NULL

23Gosub  6 35000  output one row

24IfPos  5 48000  check abort flag

25Gosub  7 44000  reset accumulator

26AggStep0 0 1 count(0)   00  NULL

27Column 2 0 200  NULL

28Column 2 2 300  NULL

29Integer1 4 000  indicate data in 
accumulator

30SorterNext 1 18000  NULL

31Gosub  6 35000  output final row

32Goto   0 48000  NULL

33Integer1 5 000  set abort flag

34Return 6 0 000  NULL

35IfPos  4 37000  Groupby result 
generator entry point

36Return 6 0 000  NULL

37AggFinal   1 0 0 count(0)   00  NULL

38Integer0 14000  NULL

39Le 14363 collseq(BINARY)  6c  NULL

40Copy   1 15000  NULL

41Copy   2 16000  NULL

42ResultRow  152 000  NULL

43Return 6 0 000  end groupby result 
generator

44Null   0 2 000  NULL

45Null   0 3 000  NULL

46Null   0 1 000  NULL

47Return 7 0 000  NULL

48Halt   0 0 000  NULL

49Transaction1 0 000  NULL

50VerifyCookie   1 1 000  NULL

51TableLock  1 2 0 t  00  NULL

52Goto   0 7  

Re: [sqlite] Support for millisecond

[Stephan Beal]

On Fri, Jan 9, 2015 at 12:15 AM, Keith Medcalf  wrote:

>
> You mean iso-8601 strings in the database?  Yes, you can format the
> strings however you want (ie with an ...



> ...

sqlite> select strftime('%Y-%m-%d %H:%M:%f', '2015-02-14
> 13:46:15.3948573647856354765 +04:00');
> 2015-02-14 09:46:15.394
>

i should have been careful to note that i was using custom conversions
(based on Wikipedia and its outbound links), as opposed to sqlite's
methods, and using ms precision for the ISO strings.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[David Barrett]

Sorry for the slow response -- yes, this is great logic.  We're just
disabling vacuum.  Thanks!

-david

On Mon, Dec 8, 2014 at 6:18 PM, Simon Slavin  wrote:

>
> On 9 Dec 2014, at 1:36am, David Barrett  wrote:
>
> > *Re: Why VACUUM.*  We vacuum weekly.  This particular database is a
> > "rolling journal" -- we are constantly adding new rows to the end of the
> > table, and every week we truncate off the head of the journal to only
> keep
> > 3M rows at the "tail".  Given that we're truncating the "head", without
> > vacuuming we'd be inserting the new rows at the "front" of the database
> > with the old rows at the "end" -- and then each truncation would leave
> the
> > database more and more fragmented.  Granted, this is on SSDs so the
> > fragmentation doesn't matter a *ton*, but it just adds up and gets worse
> > over time.  Anyway, agreed it's not the most important thing to do, but
> all
> > things being equal I'd like to do it if I can to keep things clean and
> > snappy.
>
> Okay.  I have some great news for you.  You can completely ignore
> VACUUMing without any time or space drawbacks.  You're wasting your time
> and using up the life of your SSD for no advantage.
>
> Fragmentation ceases to become a problem when you move from rotating disks
> to SSD.  SSD is a truly random access medium.  It's no faster to access
> block b then block b+1 than it is block b then block b+1000.  Two
> contiguous blocks used to be faster in rotating disks only because there is
> a physical read/write head and it will already be in the right place.  SSDs
> have no read/write head.  It's all solid state and accessing one block is
> no faster than another.
>
> Delete old rows and you'll release space.  Insert new rows and they'll
> take up the space released.  Don't worry about the internal 'neatness' of
> the file.  Over a long series of operations you might see an extra block
> used from time to time.  But it will be either zero or one extra block per
> table/index.  No more than that.  A messy internal file structure might
> niggle the OCD side of your nature but that's the only disadvantage.
>
> Also, SSD drives wear out fast.  We don't have good figures yet for
> mass-produced drives (manufacturers introduce new models faster than the
> old ones wear out, so it's hard to gather stats) but typical figures show a
> drive failing in from 2,000 to 3,000 write cycles of each single block.
> Your drive does something called 'wear levelling' and it has a certain
> number of blocks spare and will automatically swap them in when the first
> blocks fail, but after that your drive is smoke.  And VACUUM /thrashes/ a
> drive, doing huge amounts of reading and writing as it rebuilds tables and
> indexes.  You don't want to do something like that on an SSD without a good
> reason.
>
> So maybe once every few years, or perhaps if you have another more
> complicated maintenance routine which already takes up lots of time, do a
> VACUUM then.  But it doesn't really matter if you never VACUUM.  (Which is
> entirely unlike my home, dammit.)
>
> Simon.
> ___
> 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] Support for millisecond

[Keith Medcalf]

You mean iso-8601 strings in the database?  Yes, you can format the strings 
however you want (ie with an unlimited seconds precision).  However, the 
internal datetime function only returns seconds (it is merely an alias for 
strftime using a format specifier that only outputs seconds), and if you use 
strftime then you can get milliseconds by using %f rather than %S in the format 
string.  The string, however, can be as long as you want without bothering 
SQLite in the least -- however the output of the internal functions will be 
truncated to the precision specified in the output format specifier and will be 
unlocalized (without an offset from GMT), so you have to magically remember the 
appropriate offset (or store and assume all naive strings are GMT).

sqlite> select datetime('2015-02-14 13:46:15.3948573647856354765 +04:00');
2015-02-14 09:46:15

sqlite> select strftime('%Y-%m-%d %H:%M:%S', '2015-02-14 
13:46:15.3948573647856354765 +04:00');
2015-02-14 09:46:15

sqlite> select strftime('%Y-%m-%d %H:%M:%f', '2015-02-14 
13:46:15.3948573647856354765 +04:00');
2015-02-14 09:46:15.394

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Lance Shipman
>Sent: Thursday, 8 January, 2015 11:22
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Support for millisecond
>
>Can SQLite support millisecond precision in date time data? I looking at
>doc I think so, but it's not clear.
>
>Regards,
>
>Lance Shipman
>Product Engineer
>Esri
>Redlands, CA USA
>___
>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] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Keith Medcalf]

The table you are creating is called a keyset snapshot.  That is how all 
relational databases databases which support scrollable cursors implement them 
(only navigable databases -- hierarchical or network or network extended for 
example) support navigation within the database.  Relational databases are, 
well, relational.  

The only difference is that SQLite is, well, Lite.  It does not create the 
keyset for you by automagic, you have to do it yourself.  It cannot take a 
parameter on the _prepare of a select statement that indicates to magically 
create the snapshot for you, just as it does not understand UPDATE  SET 
... WHERE CURRENT OF CURSOR -- you have to retrieve the rowid youself and 
UPDATE  SET ... WHERE rowid= ...

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Max Vasilyev
>Sent: Thursday, 8 January, 2015 07:57
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Scrolling Cursor implementation best practices
>(pagination and arbitrary sorting)
>
>Hi Clemens,
>
>2015-01-08 13:34 GMT+03:00 Clemens Ladisch :
>
>> >
>> http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-
>how-to-scroll-correctly-with-duplicate-names
>> > and yes, we can use title+rowid as lasttitle. But... it looks too
>complex
>> > to be 'best practice' pattern.
>>
>> Feel free to propose something simpler that is still correct.
>>
>That's why I think to rownum... Now I ended up with simulating index by a
>table and use it's PK as rownum.
>
>CREATE TABLE BookTitleIdx (title_rownum INTEGER PRIMARY KEY
>AUTOINCREMENT,
>BookID INTEGER NOT NULL);
>INSERT INTO BookTitleIdx SELECT NULL, rowid FROM Book ORDER BY Title
>
>Time: 0.369s Great! 126K records.
>
>SELECT Book.Id, Author.Name as Author, Book.Title, Genre.Name as Genre
>FROM BookTitleIdx
>INNER JOIN Book ON BookTitleIdx.BookID = Book.Id
>LEFT JOIN Author ON Book.AuthorID = Author.ID
>INNER JOIN Genre ON Book.GenreID = Genre.ID
>WHERE BookTitleIdx.title_rownum > 12
>ORDER BY BookTitleIdx.title_rownum
>LIMIT 30
>
>Time: 0.001s and not surprising! As EXPLAIN QUERY PLAN looks excellent to
>me!
>
>000SEARCH TABLE BookTitleIdx USING INTEGER PRIMARY KEY
>(rowid>?)
>011SEARCH TABLE Book USING INTEGER PRIMARY KEY (rowid=?)
>022SEARCH TABLE Author USING INTEGER PRIMARY KEY (rowid=?)
>033SEARCH TABLE Genre USING INTEGER PRIMARY KEY (rowid=?)
>
>BTW, the query can be ...FROM Book INNER JOIN BookTitleIdx... no vice
>versa
>is required.
>But inner join order is critical. This query
>SELECT ...
>FROM Book
>LEFT JOIN Author ON Book.AuthorID = Author.ID
>INNER JOIN Genre ON Book.GenreID = Genre.ID
>INNER JOIN BookTitleIdx ON Book.Id = BookTitleIdx.BookID
>...
>runs 0.5s and has Book table scan, etc... Just moving last join up makes
>the query plan as I want.
>
>So, for now I'm happy guys, thank you very much!
>
>
>The latest SQLite supports the OR optimization for this query:
>>
>>   EXPLAIN QUERY PLAN SELECT ...;
>>   0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex
>>
>> Run ANALYZE, and update your SQLite.
>>
>> Updated and got 0.060s (x2 faster)!
> ANALYZE does not change anything.
>
>Thank you very much for EXPLAIN QUERY PLAN! It's much more readable than
>just EXPLAIN which i used but did not understand :)
>
>
>Cheers,
>Max
>___
>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] Should .dump preserve foreign_keys pragma?

[Keith Medcalf]

It is correct.  

On the chance that you happen to have compiled your version of SQLite with 
Foreign Key enforcement turned on by default instead of off; or, a later 
versions decides to change the default to on rather than off; when you load a 
dump file you need to have that foreign key enforcement off in order to be able 
to load the database.  This is because the tables and data are dumped in random 
order, not in hierarchical order (parents of parents then their children then 
their children and so on and so forth) or mayhaps there are self-referential or 
referential loops which cannot be resolved without turning off foreign key 
enforcement while loading the database "in bulk" rather than by following the 
application business logic processing to only add records the would meet 
referential constraints.

The batch process cannot turn it back on for you because it does not really 
know (keep track of) the state around the "load" operation.  In other words, if 
you need FK enforcement on, use the pragma to turn it on.  If you need it off, 
use the pragma to turn it off.  It is unwise to depend on the default always 
being the same for everyone everywhere for all time.

In the present circumstance the default is off.  This is because at one time 
there was no FK enforcement so no application turned FK enforcement off.  If 
you changed the default, then all those applications might stop working 
(because the DDL syntax was supported, but it was just ignored).

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Niall O'Reilly
>Sent: Thursday, 8 January, 2015 06:39
>To: General Discussion of SQLite Database
>Subject: [sqlite] Should .dump preserve foreign_keys pragma?
>
>
>  Hello.
>
>  What follows puzzles me.  Either there's something I don't
>  understand, or something is wrong.
>
>dhcp-179(niall)7: sqlite3
>SQLite version 3.8.5 2014-08-15 22:37:57
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> PRAGMA foreign_keys=on;
>sqlite> PRAGMA foreign_keys;
>1
>sqlite> .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>COMMIT;
>sqlite> ^D
>dhcp-179(niall)8:
>
>  I'ld have expected the foreign_keys pragma setting to have been
>  preserved.
>
>  The version shown is currently bundled with Apple's OSX Yosemite.
>  I've checked subsequent release history for changes and not found
>  any of relevance.
>
>  Thanks in anticipation for any enlightenment.
>
>  Best regards,
>  Niall O'Reilly
>
>___
>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] Uncached performance

[David King]

> SS> Is there a difference between a key being present in your database with
> NULL value, and the key not being present ?
> Surely there is. But, actually, those 1 queries are made only to keys
> with a non-NULL value (usually less than 1 Kb, and maybe 20% are about 16
> kb).

*Probably* not related to your performance concerns (since as you say, 
searching for the key doesn't look to be your issue), but for this case you may 
consider a partial index https://www.sqlite.org/partialindex.html to reduce the 
number of seeks a little bit by reducing the depth of the index tree.


signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for millisecond

[Stephan Beal]

On Thu, Jan 8, 2015 at 7:28 PM, Petite Abeille 
wrote:

> > On Jan 8, 2015, at 7:21 PM, Lance Shipman  wrote:
> >
> > Can SQLite support millisecond precision in date time data? I looking at
> doc I think so, but it's not clear.
>
> There is no 'date time’ data type in SQLite. Feel free to store your time
> data as either text or number. To whatever precision suits you.
>
> There are a couple of built-in utility functions to convert things back
> and forth:
>
> http://www.sqlite.org/lang_datefunc.html



Note that Julian Times provide _almost_ ms-precision, depending on the time
range you want to cover. The Fossil SCM (based on sqlite) uses Julian Times
almost exclusively. HOWEVER, there is some small amount of conversion
precision when doing round-trip Julian/ISO8601 times, so i'd avoid them if
100% round-trip fidelity is required. In my experiments, i see round-trip
conversion errors of +/-1ms in somewhere between 0.25% (64-bit systems) to
2% (32-bit systems) of all timestamp converted round-trip between Julian
and ISO8601.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for millisecond

[Petite Abeille]

> On Jan 8, 2015, at 7:21 PM, Lance Shipman  wrote:
> 
> Can SQLite support millisecond precision in date time data? I looking at doc 
> I think so, but it's not clear.

There is no 'date time’ data type in SQLite. Feel free to store your time data 
as either text or number. To whatever precision suits you.

There are a couple of built-in utility functions to convert things back and 
forth:

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

[sqlite] Support for millisecond

[Lance Shipman]

Can SQLite support millisecond precision in date time data? I looking at doc I 
think so, but it's not clear.

Regards,

Lance Shipman
Product Engineer
Esri
Redlands, CA USA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[J Decker]

On Thu, Jan 8, 2015 at 9:05 AM, Niall O'Reilly  wrote:

> At Thu, 8 Jan 2015 13:46:37 +,
> Simon Slavin wrote:
> >
> >
> > On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:
> >
> > >  I'ld have expected the foreign_keys pragma setting to have been
> > >  preserved.
> >
> > That makes sense in terms of how a sensible user would expect SQLite
> > to behave.  But unfortunately it's not what SQLite does.  See
> > section 2 of
> >
> > 
> >
> > I think that the reason is that FOREIGN KEYs were developed a long
> > time after SQLite3.  A choice was made that they should default to
> > OFF to preserve backward compatibility.
>
>   I think that was the right choice for default behaviour.
>
>   What seems wrong to me is that the design doesn't provide for
>   persistence of an explicit change to the default mode, just as in
>   the case of the pragma which sets journal mode to WAL, or the other
>   one which sets page size.  I can't see why one would wish to have
>   foreign key support for some connections but not for others.
>
>   I expect that the work involved in having this pragma set persistent
>   state (whether in the database header or in a special internal
>   table) would require only modest effort and would be almost
>   perfectly safe.
>
>   I hope I may look forward to reading a reaction from the developers.
>
>
except appears pragma isn't persistent... (especially the key one)
and when restoring several systems disable foriegn keys during the restore
and then reapply it... so really the dump script having forieng-keys
disabled as the first thing is sensible... but then if the keys were
already on, would expect an on at the end...


>   Best regards,
>   Niall O'Reilly
>
> ___
> 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] Should .dump preserve foreign_keys pragma?

[Niall O'Reilly]

At Thu, 8 Jan 2015 13:46:37 +,
Simon Slavin wrote:
> 
> 
> On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:
> 
> >  I'ld have expected the foreign_keys pragma setting to have been
> >  preserved.
> 
> That makes sense in terms of how a sensible user would expect SQLite
> to behave.  But unfortunately it's not what SQLite does.  See
> section 2 of
> 
> 
> 
> I think that the reason is that FOREIGN KEYs were developed a long
> time after SQLite3.  A choice was made that they should default to
> OFF to preserve backward compatibility.

  I think that was the right choice for default behaviour.

  What seems wrong to me is that the design doesn't provide for
  persistence of an explicit change to the default mode, just as in
  the case of the pragma which sets journal mode to WAL, or the other
  one which sets page size.  I can't see why one would wish to have
  foreign key support for some connections but not for others.

  I expect that the work involved in having this pragma set persistent
  state (whether in the database header or in a special internal
  table) would require only modest effort and would be almost
  perfectly safe.

  I hope I may look forward to reading a reaction from the developers.

  Best regards,
  Niall O'Reilly
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Uncached performance

[Kees Nuyt]

On Thu, 8 Jan 2015 15:04:28 +0500, ?? ??? 
wrote:

> CREATE TABLE global (
> [key] VARCHAR (1024),
> value BLOB,
> level INTEGER NOT NULL,
> original_name VARCHAR (1024),
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> parent_id REFERENCES global (id)
> );

The order of columns looks suboptimal.
It's better to put smaller columns first, so all PK and key info is in
the first page, and is not pushed to an overflow page when the values of
text or blob columns have a biggish length().

Other remarks:
* VARCHAR() translates to TEXT in SQLite.
* parent_id missed a type definition.

Typically, your table would look like:

CREATE TABLE global (
id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_id INTEGER REFERENCES global (id),
level INTEGER NOT NULL,
[key] TEXT,
original_name TEXT,
value BLOB
);

Other things to consider:
PRAGMA page_size=something_larger_than_default;
PRAGMA cache_size= .. ;

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Max Vasilyev]

Hi Clemens,

2015-01-08 13:34 GMT+03:00 Clemens Ladisch :

> >
> http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names
> > and yes, we can use title+rowid as lasttitle. But... it looks too complex
> > to be 'best practice' pattern.
>
> Feel free to propose something simpler that is still correct.
>
That's why I think to rownum... Now I ended up with simulating index by a
table and use it's PK as rownum.

CREATE TABLE BookTitleIdx (title_rownum INTEGER PRIMARY KEY AUTOINCREMENT,
BookID INTEGER NOT NULL);
INSERT INTO BookTitleIdx SELECT NULL, rowid FROM Book ORDER BY Title

Time: 0.369s Great! 126K records.

SELECT Book.Id, Author.Name as Author, Book.Title, Genre.Name as Genre
FROM BookTitleIdx
INNER JOIN Book ON BookTitleIdx.BookID = Book.Id
LEFT JOIN Author ON Book.AuthorID = Author.ID
INNER JOIN Genre ON Book.GenreID = Genre.ID
WHERE BookTitleIdx.title_rownum > 12
ORDER BY BookTitleIdx.title_rownum
LIMIT 30

Time: 0.001s and not surprising! As EXPLAIN QUERY PLAN looks excellent to
me!

000SEARCH TABLE BookTitleIdx USING INTEGER PRIMARY KEY (rowid>?)
011SEARCH TABLE Book USING INTEGER PRIMARY KEY (rowid=?)
022SEARCH TABLE Author USING INTEGER PRIMARY KEY (rowid=?)
033SEARCH TABLE Genre USING INTEGER PRIMARY KEY (rowid=?)

BTW, the query can be ...FROM Book INNER JOIN BookTitleIdx... no vice versa
is required.
But inner join order is critical. This query
SELECT ...
FROM Book
LEFT JOIN Author ON Book.AuthorID = Author.ID
INNER JOIN Genre ON Book.GenreID = Genre.ID
INNER JOIN BookTitleIdx ON Book.Id = BookTitleIdx.BookID
...
runs 0.5s and has Book table scan, etc... Just moving last join up makes
the query plan as I want.

So, for now I'm happy guys, thank you very much!


The latest SQLite supports the OR optimization for this query:
>
>   EXPLAIN QUERY PLAN SELECT ...;
>   0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex
>
> Run ANALYZE, and update your SQLite.
>
> Updated and got 0.060s (x2 faster)!
 ANALYZE does not change anything.

Thank you very much for EXPLAIN QUERY PLAN! It's much more readable than
just EXPLAIN which i used but did not understand :)


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

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Simon Slavin]

On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:

>  I'ld have expected the foreign_keys pragma setting to have been
>  preserved.

That makes sense in terms of how a sensible user would expect SQLite to behave. 
 But unfortunately it's not what SQLite does.  See section 2 of



I think that the reason is that FOREIGN KEYs were developed a long time after 
SQLite3.  A choice was made that they should default to OFF to preserve 
backward compatibility.

So yes, as your experiment shows, you have to execute

PRAGMA foreign_keys = ON

each time you open a database connection if you want the foreign keys to do 
their stuff.

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

[sqlite] Should .dump preserve foreign_keys pragma?

[Niall O'Reilly]

  Hello.

  What follows puzzles me.  Either there's something I don't
  understand, or something is wrong.

dhcp-179(niall)7: sqlite3
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys=on;
sqlite> PRAGMA foreign_keys;
1
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> ^D
dhcp-179(niall)8:

  I'ld have expected the foreign_keys pragma setting to have been
  preserved.

  The version shown is currently bundled with Apple's OSX Yosemite.
  I've checked subsequent release history for changes and not found
  any of relevance.

  Thanks in anticipation for any enlightenment.

  Best regards,
  Niall O'Reilly
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Uncached performance

[Максим Гумеров]

Thanks for your responces!

SS> This suggests that you are filling up a cache, especially if your BLOBs
are large.  In terms of overall time, 1 queries in 2 seconds is 5000
queries a second.  If you are using a rotating hard disk then this is not
unexpected, given that your disk probably rotates at 5,400 rpm, that gives
an average latency of 5.55ms /per read

> On the first run, thought, 1 queries take
>about 30 seconds!
KM> Sounds normal as you are loading up the OS cache with data.

In principle, of course, it's how caching works :) But, like I wrote, there
is another engine and when using it (with non-SQLite database with the same
contents), I am able to perform that 1 queries a dozen times faster,
even on a 1st run. This means SQLite uses a non-optimal solution in this
case; well that's quite OK, 'cause that another engine was optimized for
this particular DB structure, while SQLite was not. So the question is
whether things in SQLite could be sped up by, for example, tweaking some
SQLite settings / fine-tuning DB structure / splitting a big database into
several smaller ones / etc.? If there are no brilliant ideas, then maybe
it's pure bad luck by which all the required keys are spread over the
entire file in SQLite DB, while in my other DB they are condensed in a
portion of the file so that reading just a portion of the file already
fills the cache with all the necessary data. I asked my question in
assumption (possibly wrong) that this is not the case, and that there is
another reason.

Of course, if those 1 queries were meant to read the entire DB, then of
course any way to do that would require to ultimately scan the entire file
- every possible DB engine would have to do that. But since we speak of
just 10'000 keys (out of 130'000), then ideally only a portion of the file
has to be read. So, if this is the reason of delays (and I am not sure it
is, but isn't it probable?), - if SQLite reads more data than it would be
ideally necessary, - is there a way to tweak something in such a way to
reduce that excessing file accesses?

KM>   In order for caching to work on a USB attached device, it must not be
mounted in "let the user pull it out at any time" mode (the default), or
what Microsoft calls "optimize for random user yanking", but must be
mounted as a connected device (called optimized for performance).
Well OK if you say so, thought I believed that is only true for
write-caching (which of course is dangerous if the device is removable),
while read-caching does not depend on whether the drive is removable or
not. Seemed logical to me, but might be wrong.

SS> Is there a difference between a key being present in your database with
NULL value, and the key not being present ?
Surely there is. But, actually, those 1 queries are made only to keys
with a non-NULL value (usually less than 1 Kb, and maybe 20% are about 16
kb).


2015-01-08 16:24 GMT+05:00 Keith Medcalf :

>
> >My database weights a little less than 2 Gbs and contains 130'000 keys.
> >When I put it on HDD and try to make 1 queries (extracting values for
> >1 different keys) with some additional processing of extracted
> >values, it takes about 4 seconds on my PC on any run except the first,
> >with maybe half of that time being the "additional processing" mentioned;
> >even when I perform every query 3 times in a row (making it 3
> queries),
> >this time does not change. On the first run, thought, 1 queries take
> >about 30 seconds!
>
> Sounds normal as you are loading up the OS cache with data.  Accessing
> data cached in RAM is much faster than accessing data on spinning disk.
> Have you set SQLite's page cache equal to a reasonable working set since
> access to application cached pages is even faster than access to the OS
> file cache -- and it avoids a jump into the kernel to do I/O which puts you
> at the mercy of the dispatcher.  Are you 100% I/O bound during the long
> (initial) run, and 100% CPU bound during the quicker runs?
>
> >When I put the file on a USB flash drive, somehow I always get about 45
> >seconds total time, on either first run and subsequent runs. When the
> >queries are tripled, the total time is tripled as well (even though the
> >portions of the file to be read should already be cached when every
> >enuqie query is repeated 2 more times).
>
> >This leads me to the conclusion that 1) The delays are produced by
> >physical reading of the file, not by searching for a key or returning
> >the value;
>
> >2) file on USB never gets cached (why?? due to some file-mode flags used
> by
> >SQLite engine? or it's just a MS Windows 8 issue?)
>
> This is an OS "feature".  In order for caching to work on a USB attached
> device, it must not be mounted in "let the user pull it out at any time"
> mode (the default), or what Microsoft calls "optimize for random user
> yanking", but must be mounted as a connected device (called optimized for
> performance).  You should 

Re: [sqlite] Uncached performance

[Keith Medcalf]

>My database weights a little less than 2 Gbs and contains 130'000 keys.
>When I put it on HDD and try to make 1 queries (extracting values for
>1 different keys) with some additional processing of extracted
>values, it takes about 4 seconds on my PC on any run except the first, 
>with maybe half of that time being the "additional processing" mentioned; 
>even when I perform every query 3 times in a row (making it 3 queries), 
>this time does not change. On the first run, thought, 1 queries take 
>about 30 seconds!

Sounds normal as you are loading up the OS cache with data.  Accessing data 
cached in RAM is much faster than accessing data on spinning disk.  Have you 
set SQLite's page cache equal to a reasonable working set since access to 
application cached pages is even faster than access to the OS file cache -- and 
it avoids a jump into the kernel to do I/O which puts you at the mercy of the 
dispatcher.  Are you 100% I/O bound during the long (initial) run, and 100% CPU 
bound during the quicker runs?

>When I put the file on a USB flash drive, somehow I always get about 45
>seconds total time, on either first run and subsequent runs. When the
>queries are tripled, the total time is tripled as well (even though the
>portions of the file to be read should already be cached when every
>enuqie query is repeated 2 more times).

>This leads me to the conclusion that 1) The delays are produced by
>physical reading of the file, not by searching for a key or returning 
>the value;

>2) file on USB never gets cached (why?? due to some file-mode flags used by
>SQLite engine? or it's just a MS Windows 8 issue?)

This is an OS "feature".  In order for caching to work on a USB attached 
device, it must not be mounted in "let the user pull it out at any time" mode 
(the default), or what Microsoft calls "optimize for random user yanking", but 
must be mounted as a connected device (called optimized for performance).  You 
should also make sure you have it formatted as NTFS and not as a FAT variant of 
some type.

Further, USB itself is very slow, both in transfer time and especially in 
turnaround time.  Flash drives are also very slow, usually because they do not 
need to operate at a speed faster than can be supported by the slow USB 
interface.  Is this a USB 1.0, 2.0, or 3.0 connection and device?





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

Re: [sqlite] Uncached performance

[Simon Slavin]

On 8 Jan 2015, at 10:04am, Максим Гумеров  wrote:

> When I put it on HDD and try to make 1 queries (extracting values for
> 1 different keys) with some additional processing of extracted values,
> it takes about 4 seconds on my PC on any run except the first, with maybe
> half of that time being the "additional processing" mentioned; even when I
> perform every query 3 times in a row (making it 3 queries), this time
> does not change. On the first run, thought, 1 queries take about 30
> seconds!

This suggests that you are filling up a cache, especially if your BLOBs are 
large.  In terms of overall time, 1 queries in 2 seconds is 5000 queries a 
second.  If you are using a rotating hard disk then this is not unexpected, 
given that your disk probably rotates at 5,400 rpm, that gives an average 
latency of 5.55ms /per read/.

> CREATE TABLE global (
> 
>   [key] VARCHAR (1024),
>   value BLOB,
>   level INTEGER NOT NULL,
>   original_name VARCHAR (1024),
>   id INTEGER PRIMARY KEY AUTOINCREMENT,
>   parent_id REFERENCES global (id)
> 
> );

SQLite will interpret "VARCHAR (1024)" as TEXT and will not truncate.  I'm just 
warning you.

> value is a BLOB (and for 50% keys is just NULL)

Is there a difference between a key being present in your database with NULL 
value, and the key not being present ?

> And there are 3 separate indices: by level, key, and parent_id.

An index which is only on level would probably be pointless.  I suspect you're 
more likely to want to have that index include the key, too.

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

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Clemens Ladisch]

Max Vasilyev wrote:
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> and want to use WHERE, but what if 'title' is not unique?

If the ORDER BY columns are not unique, you cannot know which
rows to display on which page.  You must be able to uniquely
identify rows.

> - This is considered here:
> http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names
> and yes, we can use title+rowid as lasttitle. But... it looks too complex
> to be 'best practice' pattern.

Feel free to propose something simpler that is still correct.

> And is x100 slower than simple WHERE.
>
> SELECT * FROM "MainBooksView"
> WHERE (Title = 'fff' AND Id > 101985)  OR Title > 'fff'
> ORDER BY Title
>  LIMIT 30
> Query time: 0.102s
> I have index for the Title column.

The latest SQLite supports the OR optimization for this query:

  EXPLAIN QUERY PLAN SELECT ...;
  0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex

Run ANALYZE, and update your SQLite.


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

[sqlite] Uncached performance

[Максим Гумеров]

Hello!
Imagine I am trying to introduce a local key-value SQLite database for
caching some data retrieved from a remote server. Key is a character
string, value is a BLOB (and for 50% keys is just NULL). I will specify the
details later.

My database weights a little less than 2 Gbs and contains 130'000 keys.
When I put it on HDD and try to make 1 queries (extracting values for
1 different keys) with some additional processing of extracted values,
it takes about 4 seconds on my PC on any run except the first, with maybe
half of that time being the "additional processing" mentioned; even when I
perform every query 3 times in a row (making it 3 queries), this time
does not change. On the first run, thought, 1 queries take about 30
seconds!
When I put the file on a USB flash drive, somehow I always get about 45
seconds total time, on either first run and subsequent runs. When the
queries are tripled, the total time is tripled as well (even though the
portions of the file to be read should already be cached when every enuqie
query is repeated 2 more times).

This leads me to the conclusion that 1) The delays are produced by physical
reading of the file, not by searching for a key or returning the value; 2)
file on USB never gets cached (why?? due to some file-mode flags used by
SQLite engine? or it's just a MS Windows 8 issue?)

Now, does anybody know whether it's a known issue for SQLite that the
access is slow when the DB file is not yet cached by OS (I mean, it's
slower that one could expect: we have been previously using a hand-made
engine with which those 1 queries took just 4 seconds!)? Or maybe
that's an issue specific to something, like, string-based indexes, or this
large databases (approx. 2 Gb), or to something else?

The details:

CREATE TABLE global (

[key] VARCHAR (1024),

value BLOB,

level INTEGER NOT NULL,

original_name VARCHAR (1024),

id INTEGER PRIMARY KEY AUTOINCREMENT,

parent_id REFERENCES global (id)

);

And there are 3 separate indices: by level, key, and parent_id.

So the actual PK is integer, but most queries are like key=something.


I am opening the DB with

sqlite3_open_v2(fdatabase, fdb, 1{ SQLITE_OPEN_READONLY} , nil);

and querying it with

sqlite3_prepare(fdb, 'select value from global where key = ifnull(?,)',
-1, fReadValueQuery, ptail); + sqlite3_bind_text + sqlite3_step
+ sqlite3_column_blob


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

Re: [sqlite] Index rownum

[Simon Slavin]

On 8 Jan 2015, at 1:43am, Max Vasilyev  wrote:

> I guessed that insertion and deletion could be an issue. We need to
> re-number keys (change a lot of Btree nodes) on each operation. Or at least
> on REINDEX command (I say not strictly, just as idea).

If you need to manually set the numbers of your keys, then your key numbers are 
just variables and nothing to do with the internal organisation (e.g. Btree) of 
your database.  So create an integer column for them and then you can do what 
you want with them: index them, UPDATE them, etc..

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