[sqlite] Option to control implicit casting

[Joshua Thomas Wise]

SQLite3 uses manifest typing, which is great and provides a ton of flexibility. 
However, due to implicit casting rules, many operations can accidentally result 
in a different value than what was desired. If programmers don’t guard against 
every possible cause of implicit casting, many error situations are swallowed 
and instead can result in data corruption. I propose there should be a 
compile-time option to disable all implicit casting done within the SQL virtual 
machine. The option could cause all type-incompatible operations to return 
NULL, or it could cause these operations to throw hard errors. Either approach 
would be similarly useful.

Here are some examples of how implicit casting can lead to surprising results:

1. If invoking SUM() would cause integer overflow, a hard error is returned. 
The same things happens with ABS(). However, if integer overflow occurs when 
using the + operator, a REAL value is returned instead.

2. Many built-in string functions will automatically cast BLOBs to TEXTs, but 
those could contain embedded nuls, leading to undefined behavior.

3. Declaring a column with INTEGER affinity does not actually force its values 
to be integers. An integer that is out of range could be stored as a REAL 
value, unexpectedly changing the behavior of functions such as SUM() and ABS().


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

[sqlite] Wildcard with path in json extension

[Charles Leifer]

Many of the sqlite json1 functions accept a path parameter, which the
documents describe as:

For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error. A well-formed PATH is a text value
that begins with exactly one '$' character followed by zero or more
instances of ".objectlabel" or "[arrayindex]".

I was wondering if there were any plans to support wildcard paths? There's
some useful information on the MySQL JSON doc:
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax

Postgres seems to be doubling-down on json as well -- the v12 release looks
like it has a dedicated jsonpath type:
https://www.postgresql.org/docs/devel/datatype-json.html#DATATYPE-JSONPATH

What do you think?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with CLI readfile() of zero-length files.

[Richard Hipp]

On 4/8/19, Donald Griggs  wrote:
> On my system, it appears that,
> beginning with 3.27.0, attempting to use the (quite useful!) readfile()
> function on a *zero-length* file throws "Error: out of memory"
> whereas with 3.26.0, the result was set to null.

Fixed by check-in
https://www.sqlite.org/src/timeline?y=ci&c=0edad5339e36d69aed92 back
in February.  This has not yet made it into an official release - but
I encourage you to test out the latest "Prerelease Snapshot" on the
https://sqlite.org/download.html page and confirm for us that this
problem has been fixed, and that no new problems have been introduced.

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

[sqlite] problem with CLI readfile() of zero-length files.

[Donald Griggs]

On my system, it appears that,
beginning with 3.27.0, attempting to use the (quite useful!) readfile()
function on a *zero-length* file throws "Error: out of memory"
whereas with 3.26.0, the result was set to null.

(Attempting to read a *non-existent* file on either version properly
results in null.)
Doesn't appear to matter if the readfile() is part of a SELECT, an UPDATE,
or an INSERT.

Windows 10 Enterprise, version 1803 (10.0.17134)
16 GB ram.
Sqlite3.exe downloaded directly from sqlite.org.

Thanks, sqlite team, for a great product.


=== Simplist demonstration:

C:\test>*copy nul zerofile*
1 file(s) copied.

C:\test>*dir zerofile*
 Volume in drive C has no label.
 Volume Serial Number is 0C05-BA90

 Directory of C:\test

04/08/2019  01:44 PM 0 zerofile
   1 File(s)  0 bytes
   0 Dir(s)  150,912,172,032 bytes free

C:\test>*sqlite3*
SQLite version *3.27.0 *2019-02-07 17:02:52
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> *select readfile('zerofile');*
Error: out of memory
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error in docs

[Jim Dossey]

I think I found an error in the documentation here: 
https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries 


It defines the table and view:
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

It then states "The affinity of the v1.x column will be the same as the 
affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".

However column t1.b is TEXT, not INTEGER.

Do I get a check for $2.56 from DRH?  :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] export tables

[R Smith]

On 2019/04/08 3:03 PM, Mohsen Pahlevanzadeh wrote:

Hello,


I need to export some tables with dot command, How I do it?



That depends on what you mean by "export"?

Do you wish to export the table structure (schema)?
- in which case the dot commands can be used easily,

or do you wish to export the data?
- in which case the CSV export function which Gunter mentioned will work 
perfectly,


or do you wish to export schema and data both to instate the copy of the 
table into another database?
- in which case the .dump or .clone can help or perhaps another more 
specific approach.


or do you simply want to understand how the dot commands work?
- in which case here you go: 
https://www.w3resource.com/sqlite/sqlite-dot-commands.php


I'm guessing you probably need some variant of:  .dump ?Table?


Are you using the sqlite CLI from Linux, Mac or Windows? How big is the 
data you wish to export? What format do you wish the result to be in?


Different scenarios dictate different methods - telling us more would 
help greatly.



Cheers,
Ryan

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

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Nik Jain]

ok. I investigated further, and it seems my problem is something else
entirely! A SCAN is being performed on a fts5 table. I am not sure but I
think that means no index.

Query plan:
sqlite>  explain query plan  select * from productsfts p where p.attributes
match '50'limit 6;
QUERY PLAN
`--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:
Run Time: real 0.000 user 0.48 sys 0.35


Schema:
CREATE VIRTUAL TABLE productsfts using fts5(
  attributes ,
  lastprice,
  id ,
  categoryid,
  brandid
)
/* productsfts(attributes,lastprice,id,categoryid,brandid) */;

Is this normal ? When A order by clause is added to above query. It always
shows a "Using temp b-tree for order by"

On Mon, Apr 8, 2019 at 1:44 PM Wout Mertens  wrote:

> You need to create an index on both columns at once or the indexes can't be
> used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes
> are being used.
>
> Wout.
>
> On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain  wrote:
>
> >  Have a fts5 table with 2 indexed columns. Where the idea is to match by
> > one col and sort using the other one. Something like :
> >
> > "select id from fts where col1 match '50' order by price "
> >
> > This is slow. 0.07 seconds. Removing the order by clause -  0.001
> seconds.
> > How do I fix this ? I have a feeling I am using this fts table in an
> > incorrect way. One way is to run 2 queries. First on the fts table, to
> > return ids. Second on the regular table with the order by clause. "
> select
> > * from normaltable where  id in (Ids) order by price " . This approach is
> > fast. But the id list could be large sometimes.
> > Any other way ?
> > Thanks
> >
> > PS: This is my second attempt at mailing lists. Not sure if this one will
> > go through.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

[Jose Isaias Cabrera]

Wow!  Thanks, JKL.  You sent me back to school on this one. ;-)  I had to go 
back and refresh the Venn diagram. :-) Thanks.  Also, thanks for the time 
explaining it.  I have to read it a few more times to really get it into my 
brains so that I don't keep asking the same questions.  It would be nice to do 
a compilation of all the questions done in here and any pertinent 
answer/response.



From: James K. Lowden
Sent: Saturday, April 6, 2019 01:17 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not 
satisfy all of the query

On Fri, 5 Apr 2019 18:54:18 +
Jose Isaias Cabrera  wrote:

> Why does this work

I don't know what "work" means, but I can explain the difference.  With
an outer join, JOIN and WHERE are not the same.  In analyzing the
query, we consider JOIN before WHERE.

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> where a.a = 'p006'

In an outer join, the outer table -- think "outer" as Venn diagram --
is the "preserved" table.  All rows match, join criteria
notwithstanding. This component has no effect:

>   a.idate = (select max(idate) from t where a = a.a)

because "a" is the outer table, and so all rows match, join criteria
notwithstanding.

The inner table is a little different, and also different from WHERE in
an inner join.  On the inner table, JOIN restrictions can cause a row
not to match that otherwise would.  In your query:

>   b.idate = (select max(idate) from z where f = a.a)

"b" rows that don't pass that test are rejected from the join.  The "a"
columns will appear (because all "a" rows do) and the "b" columns will
be NULL.

Only once the join is completed do we consider WHERE:

> where a.a = 'p006'

This restricts rows in the outer table.

Now let's look at your other query.

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f

The *only* join criterion is a.a = b.f.  All rows passing that test are
subjected to WHERE: all "a" rows (because outer table) and "b"
rows with a matching "f".

> where a.a = 'p006'

OK, same as #1.

> AND
>   a.idate = (select max(idate) from t where a = a.a)

This further restricts the "a" table rows, unlike #1.

> AND
>   b.idate = (select max(idate) from z where f = a.a)

This restricts produced rows to those passing the test.  Different from
#1, no row passes this test for which b.idate is NULL.

Whenever you disallow NULL on the inner table of an outer join, you
effectively convert the join from outer to inner.  You're asking for:

1.  all rows in "a", whether or not they match a "b" row, provided
2.  they do match a "b" row (because b.idate cannot be NULL)

You can vote for anyone in either party from this list of Democrats.

Because both AND clauses restrict the output, we can expect the 2nd
query to produce fewer rows.  If it doesn't, there's no need for an
outer query for the data in their current state.

By the way, the distinction of JOIN and WHERE is not a relational
concept.  It was added to SQL-92 in part to provide exactly the
separation your question illustrates: how to express an outer join.

Relationally, that's not needed.  An outer join is nothing but an inner
join + a union with the outer table.  That's cumbersome to express
in SQL, and anything cumbersome to express is hard to optimize, and
query optimization was and is the unsolved technical challenge of SQL.

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

Re: [sqlite] export tables

[Clemens Ladisch]

Mohsen Pahlevanzadeh wrote:
> I need to export some tables with dot command, How I do it?

https://www.sqlite.org/cli.html#csv_export


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

[sqlite] export tables

[Mohsen Pahlevanzadeh]

Hello,


I need to export some tables with dot command, How I do it?


--regards

Mohsen

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

Re: [sqlite] [EXTERNAL] Re: Query Regression IN and Virtual Tables - followup

[Hick Gunter]

Debugging shows that R6 is in fact initialised with the expected value 140001, 
so this seems to be a display problem only. Plus, it seems to be due to 
debugger interference, as executing from the SQLite shell does show the 
instruction.

Tested by setting a breakpoint on the VColumn function and stepping out into 
the VDBE code implementation of OP_Eq.

(gdb) print *pOp
$1 = {opcode = 53 '5', p4type = -2 '\376', p5 = 67, p1 = 5, p2 = 12, p3 = 6, p4 
= {i = 6534704, p = 0x63b630, z = 0x63b630 "\250\266c", pI64 = 0x63b630, pReal 
= 0x63b630, pFunc = 0x63b630, pCtx = 0x63b630, pColl = 0x63b630, pMem = 
0x63b630, pVtab = 0x63b630, pKeyInfo = 0x63b630, ai = 0x63b630, pProgram = 
0x63b630, pTab = 0x63b630, xAdvance = 0x63b630}, zComment = 0x0}
(gdb) p *pIn3
$2 = {u = {r = 6.9169684483420357e-319, i = 140001, nZero = 140001, zPType = 
0x222e1 , pDef = 0x222e1, pRowSet = 0x222e1, 
pFrame = 0x222e1}, flags = 4, enc = 0 '\000', eSubtype = 0 '\000', n = 0, z = 
0x0, zMalloc = 0x0, szMalloc = 0, uTemp = 0, db = 0x63b390, xDel = 0xb94270, 
pScopyFrom = 0x0, pFiller = 0x0}

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dan Kennedy
Gesendet: Freitag, 05. April 2019 15:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables - 
followup


On 5/4/62 16:44, Hick Gunter wrote:
> I patched my SQlite 3.24 code to include the fix from the ticket
>
> <   if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
> ---
>> // from SQLite bugfix
>>   if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 ||
>> bIn) ){
> and changed the xBestIndex return value to be lower if the equality
> constraint from IN is not usable
>
> The generated code as reported is invalid (instruction 16 with the
> init of R6 is not shown)


So, after applying the patch to 3.24 you executed the EXPLAIN statement shown 
below in the shell tool and it mysteriously omitted instruction 16 from the 
output?

Are there any other problems? Does the SQL statement return the correct results 
if you execute it without the EXPLAIN?

Dan.




>
> explain select lsn from atx_txlog where period_no between 7300 and
> 7313 and event_Type in (140001,180001);
>
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 16000  Start at 16
> 1 VOpen  0 0 0 vtab:B90B5000
> 2 Explain2 0 0 SCAN TABLE atx_txlog VIRTUAL TABLE 
> INDEX 1:
>00
> 3 Integer7300  3 000  r[3]=7300
> 4 Integer7313  4 000  r[4]=7313
> 5 Integer1 1 000  r[1]=1
> 6 Integer2 2 000  r[2]=2
> 7 VFilter0 151
>00  iplan=r[1] zplan='
> '
> 8   Noop   0 0 000  begin IN expr
> 9   VColumn0 15500  r[5]=vcolumn(15); 
> atx_txlog.event_type
> 10  Eq 5 126 (BINARY)   43  if r[6]==r[5] 
> goto 12
> 11  Ne 5 147 (BINARY)   53  if r[7]!=r[5] 
> goto 14; end IN expr
> 12  VColumn0 21800  r[8]=vcolumn(21); 
> atx_txlog.lsn
> 13  ResultRow  8 1 000  output=r[8]
> 14VNext  0 8 000
> 15Halt   0 0 000
> 17Integer180001  7 000  r[7]=180001
> 18Goto   0 1 000
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Dan Kennedy
> Gesendet: Freitag, 29. März 2019 14:30
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual
> Tables
>
>
> On 29/3/62 14:32, Hick Gunter wrote:
>> When upgrading from 3.7.14.1 to 3.24 I noticed the following problem
>>
>> Given a virtual table like
>>
>> CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER,
>> attr1 INTEGER,...);
>>
>> whose xBestIndex function simulates (in unsupported syntax)
>>
>> CREATE VIRTUAL INDEX vt_key ON vt (key1, key2, key3);
>>
>> but also handles simple comparisons internally, the query
>>
>> SELECT * FROM vt WHERE key1 =  AND key2 BETWEEN  AND
>>  AND attr1 IN ();
>
> Thanks for reporting this.
>
> What is supposed to happen in this scenario is that xBestIndex() is
> invoked once with all 4 constraints marked as usable. The IN(...) is
> represented as an SQLITE_INDEX_CONSTRAINT_EQ constraint. If the
> xBestIndex() implementation elects to use the IN(...) operator, then
> xBestIndex() is invoked a second time, this time with the IN(...) marked as

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Wout Mertens]

You need to create an index on both columns at once or the indexes can't be
used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes
are being used.

Wout.

On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain  wrote:

>  Have a fts5 table with 2 indexed columns. Where the idea is to match by
> one col and sort using the other one. Something like :
>
> "select id from fts where col1 match '50' order by price "
>
> This is slow. 0.07 seconds. Removing the order by clause -  0.001 seconds.
> How do I fix this ? I have a feeling I am using this fts table in an
> incorrect way. One way is to run 2 queries. First on the fts table, to
> return ids. Second on the regular table with the order by clause. " select
> * from normaltable where  id in (Ids) order by price " . This approach is
> fast. But the id list could be large sometimes.
> Any other way ?
> Thanks
>
> PS: This is my second attempt at mailing lists. Not sure if this one will
> go through.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users