Re: [sqlite] Do SQLITE_LOCKED situations call the busy-handler ?

[Keith Medcalf]

See also:

https://sqlite.org/unlock_notify.html

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Tuesday, 19 March, 2019 22:29
>To: SQLite mailing list
>Subject: Re: [sqlite] Do SQLITE_LOCKED situations call the busy-
>handler ?
>
>
>SQLITE_LOCKED is an immediate return (that is, it returns
>immediately, it is not subject to automatic retries or timeouts or
>the busy/wait handler -- it is an error indication, not necessarily a
>transient condition).
>
>You may use the sqlite3_unlock_notify API (assuming that you have
>compiled your SQLite3 with the appropriate option set to enable
>unlock_notify) to get notified when the lock condition from another
>connection to the same shared cache clears.  If the conflict is
>within the same connection, then unlock_notify will not work.
>
>https://sqlite.org/c3ref/unlock_notify.html
>
>Note that if the "extended return code" is SQLITE_LOCKED_SHAREDCACHE
>then you may use the unlock_notify so that you know when the other
>connection to the same shared cache has finished with its lock so
>that you can retry the failed operation (and you may need to do this
>multiple times since there may be multiple conflicting connections to
>the same shared cache, and where this is the case, unlock_notify is
>called when one of them, chosen at random, clears).
>
>On the other hand, if the extended error code is SQLITE_LOCKED then
>you should treat it as SQLITE_MISUSE and immediately longjump to the
>HEP instruction (Halt and Execute Programmer).
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>>Sent: Tuesday, 19 March, 2019 21:36
>>To: SQLite mailing list
>>Subject: [sqlite] Do SQLITE_LOCKED situations call the busy-handler
>?
>>
>>(For those playing along at home, SQLITE_LOCKED is a special-case
>>version of SQLITE_BUSY.  You get it only when the competing access
>>attempts come from the same connection (multi-tasking) or different
>>connections sharing the same cache.)
>>
>>Suppose I'm in a situation where I would get SQLITE_LOCKED.  Does
>>SQLite attempt to resolve the problem using whatever busy-handler
>>I've set up before returning that result ?
>>
>>The documentation here
>>
>>
>>
>>seems to say otherwise but I'd like to be sure.
>>
>>Simon.
>>___
>>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] Do SQLITE_LOCKED situations call the busy-handler ?

[Keith Medcalf]

SQLITE_LOCKED is an immediate return (that is, it returns immediately, it is 
not subject to automatic retries or timeouts or the busy/wait handler -- it is 
an error indication, not necessarily a transient condition).  

You may use the sqlite3_unlock_notify API (assuming that you have compiled your 
SQLite3 with the appropriate option set to enable unlock_notify) to get 
notified when the lock condition from another connection to the same shared 
cache clears.  If the conflict is within the same connection, then 
unlock_notify will not work.

https://sqlite.org/c3ref/unlock_notify.html

Note that if the "extended return code" is SQLITE_LOCKED_SHAREDCACHE then you 
may use the unlock_notify so that you know when the other connection to the 
same shared cache has finished with its lock so that you can retry the failed 
operation (and you may need to do this multiple times since there may be 
multiple conflicting connections to the same shared cache, and where this is 
the case, unlock_notify is called when one of them, chosen at random, clears).  

On the other hand, if the extended error code is SQLITE_LOCKED then you should 
treat it as SQLITE_MISUSE and immediately longjump to the HEP instruction (Halt 
and Execute Programmer).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Tuesday, 19 March, 2019 21:36
>To: SQLite mailing list
>Subject: [sqlite] Do SQLITE_LOCKED situations call the busy-handler ?
>
>(For those playing along at home, SQLITE_LOCKED is a special-case
>version of SQLITE_BUSY.  You get it only when the competing access
>attempts come from the same connection (multi-tasking) or different
>connections sharing the same cache.)
>
>Suppose I'm in a situation where I would get SQLITE_LOCKED.  Does
>SQLite attempt to resolve the problem using whatever busy-handler
>I've set up before returning that result ?
>
>The documentation here
>
>
>
>seems to say otherwise but I'd like to be sure.
>
>Simon.
>___
>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] Do SQLITE_LOCKED situations call the busy-handler ?

[Simon Slavin]

(For those playing along at home, SQLITE_LOCKED is a special-case version of 
SQLITE_BUSY.  You get it only when the competing access attempts come from the 
same connection (multi-tasking) or different connections sharing the same 
cache.)

Suppose I'm in a situation where I would get SQLITE_LOCKED.  Does SQLite 
attempt to resolve the problem using whatever busy-handler I've set up before 
returning that result ?

The documentation here



seems to say otherwise but I'd like to be sure.

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

Re: [sqlite] INSERTing from another table data

[Simon Davies]

On Tue, 19 Mar 2019 at 15:07, Tim Streater  wrote:
>
> My use case is a mixture of these. My need is to copy a row from a table in 
> one db (db1) to a table with identical schema in another db (db2). The 
> complication is that there is an id column, so the row needs to get a new id 
> in db2.
>
> At the minute I open db1, and do these steps (absid is the id column):
>
> attach database ':memory:' as mem
> create table mem.messages as select * from main.messages where 
> absid=
> update mem.messages set absid=null
> attach database 'db2' as dst
> insert into dst.messages select * from mem.messages
>
> which works nicely but has too many steps. I've not found a way to reduce the 
> step count.

absid is integer primary key, or a new id would not be generated in
the above scenario; so the following should work:

insert into dst.messages( notabsid_1, notabsid2,...) select
notabsid_1, notabsid_2,... from main.messages;

> --
> Cheers  --  Tim

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

Re: [sqlite] Distinguish type of statements

[Simon Slavin]

On 19 Mar 2019, at 8:53pm, siscia  wrote:

> Do you think it would be possible to add a function that does what I need?

No.  I've not part of the development team but I think it's quite complicated.  
Also, the fact that there's already a function which does what you want makes 
it unlikely there'll be another one.

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

Re: [sqlite] .import fails CHECK constraint on valid data

[Shawn Wagner]

A manual INSERT demonstrates the same behavior, actually. Using your Tc
table:

sqlite> insert into Tc values ('12');
Error: CHECK constraint failed: Tc

The thing about .import is that, instead of guessing what type each value
it reads is, they're all just bound to an insert statement as strings. When
the row is actually stored in the table, those strings are converted to
numeric types if the relevant columns have the appropriate affinity and it
can be done losslessly. Details:
https://www.sqlite.org/datatype3.html#type_affinity

For good or bad, check constraints appear to be evaluated before this
conversion.

On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden 
wrote:

> On Sun, 10 Mar 2019 17:04:46 -0400
> "James K. Lowden"  wrote:
>
> > Why does the .import command cause the CHECK constraint to fail, when
> > an ordinary INSERT does not?
>
> On Sun, 10 Mar 2019 14:12:33 -0700
> Shawn Wagner  wrote:
>
> > The check constraint is probably being evaluated (with t as a string)
> > before any type conversion to match the column affinity is done.
>
> Does anyone have a better answer?  Isn't .import supposed to work like
> INSERT?  If it doesn't, CHECK constraints for type safety are useless
> for tables that are loaded from files.
>
> --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] Distinguish type of statements

[siscia]

Hi Simon,

Thanks! Indeed I thought about a similar solution but it seems a little
contrived.

Do you think it would be possible to add a function that does what I need?
How is the design coordinated? Where should I post my RFC?

Cheers,
Simone



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import fails CHECK constraint on valid data

[James K. Lowden]

On Sun, 10 Mar 2019 17:04:46 -0400
"James K. Lowden"  wrote:

> Why does the .import command cause the CHECK constraint to fail, when
> an ordinary INSERT does not?  

On Sun, 10 Mar 2019 14:12:33 -0700
Shawn Wagner  wrote:

> The check constraint is probably being evaluated (with t as a string)
> before any type conversion to match the column affinity is done.

Does anyone have a better answer?  Isn't .import supposed to work like
INSERT?  If it doesn't, CHECK constraints for type safety are useless
for tables that are loaded from files.  

--jkl

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

Re: [sqlite] Distinguish type of statements

[Simon Slavin]

On 19 Mar 2019, at 6:40pm, siscia  wrote:

> Given a statement, is there any way to know if it is an UPDATE, DELETE, 
> INSERT, SELECT or something else?

It's not simple but if you are actually trying to execute that statement, you 
can register an authorizer function.



The function you specify is passed a parameter which is one of



so you could pick out SQLITE_SELECT, SQLITE_INSERT, SQLITE_UPDATE, 
SQLITE_DELETE, and ignore the others.  Or something like it.  Your routine 
returns either SQLITE_OK or SQLITE_DENY depending on whether you want the 
operation to continue.

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

[sqlite] Distinguish type of statements

[siscia]

Hi All,

I am already afraid that the answer to the question will be "no", but it
will save a lot of development effort on my side, so is worth to ask.

Given a statement, is there any way to know if it is an UPDATE, DELETE,
INSERT, SELECT or something else?

Thanks,
Simone



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERTing from another table data

[Tim Streater]

On 19 Mar 2019, at 13:46, R Smith  wrote:

> Three ways in SQL to create and fill a table with data from another:
>
> 1. CREATE ... AS
> Example:
> CREATE TABLE newTable AS SELECT a,b,c FROM oldTable;

> 2. CREATE TABLE + INSERT
> Example:
> CREATE TABLE newTable(a INT, b REAL, c TEXT);
> INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable;

My use case is a mixture of these. My need is to copy a row from a table in one 
db (db1) to a table with identical schema in another db (db2). The complication 
is that there is an id column, so the row needs to get a new id in db2.

At the minute I open db1, and do these steps (absid is the id column):

attach database ':memory:' as mem
create table mem.messages as select * from main.messages where 
absid=
update mem.messages set absid=null
attach database 'db2' as dst
insert into dst.messages select * from mem.messages

which works nicely but has too many steps. I've not found a way to reduce the 
step count.


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

Re: [sqlite] Recursive CTE on tree with doubly linked items

[Joshua Wise]

> On Mar 18, 2019, at 5:21 AM, Keith Medcalf  wrote:
> 
>  UPDATE tree
> SET position = (SELECT position FROM _children WHERE id = tree.id) -- 
> Multiply by x to number by x
>   WHERE id IN (SELECT id FROM _children);
>  DELETE FROM _children;
> END;

I don’t see the window function causing a significant performance loss, but 
your UPDATE statement is much better. You could also get rid of the gentleman’s 
agreement by temporarily setting both parent and position to NULL.

CREATE TEMP VIEW normalize_tree(parent) AS SELECT NULL;
CREATE TEMP TABLE _children(id INTEGER PRIMARY KEY, position REAL);
CREATE TEMP TRIGGER normalize_tree_impl INSTEAD OF UPDATE ON normalize_tree
BEGIN
INSERT INTO _children
SELECT id, row_number() OVER (ORDER BY position)
FROM tree
WHERE parent = new.parent
ORDER BY position;
UPDATE tree
SET (parent, position) = (NULL, NULL)
WHERE id IN (SELECT id FROM _children);
UPDATE tree
SET (parent, position) = (new.parent, (SELECT position FROM 
_children WHERE id = tree.id ))
WHERE id IN (SELECT id FROM _children);
DELETE FROM _children;
END;

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

Re: [sqlite] INSERTing from another table data

[Jose Isaias Cabrera]

Wow!  Thanks.  I did not know these choices.  Now I do. ;-)


From: sqlite-users on behalf of R Smith
Sent: Tuesday, March 19, 2019 09:46 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] INSERTing from another table data

I see the "ignore this" retraction, but I thought to mention the
following any way, for future reference:

Three ways in SQL to create and fill a table with data from another:

1. CREATE ... AS
Example:
CREATE TABLE newTable AS SELECT a,b,c FROM oldTable;
(This method has the advantage of being fast and cheap in programming
time, but takes away control over column affinities etc.)
https://www.w3schools.com/sql/sql_create_table.asp

2. CREATE TABLE + INSERT
Example:
CREATE TABLE newTable(a INT, b REAL, c TEXT);
INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable;
(This method gives more control over the new table's schema, but does
require 2 steps).
https://www.w3schools.com/sql/sql_insert_into_select.asp

3. SELECT ... INTO
Example:
SELECT a,b,c FROM oldTable INTO newTable;
(This SQL has much the same advantages and disadvantages as 1. above,
except that SQLite specifically does not support this method [that I
know of])
https://www.w3schools.com/sql/sql_select_into.asp


On 2019/03/19 3:15 PM, Jose Isaias Cabrera wrote:
> Greetings.
>
> I have this table,
>
>
> create table a (a, b, c);
>
> insert into a values (1, 2, 3);
>
> insert into a values (2, 3, 4);
>
> insert into a values (3, 4, 5);
>
> insert into a values (4, 5, 6);
>
> insert into a values (5, 6, 7);
>
> insert into a values (6, 7, 8);
>
> and I also have this table,
>
>
> create table b (a, b, c, d, e);
>
> I want to INSERT the data in table a, to b.  I tried these,
>
> sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 
> 'user1','2019-03-01 14:22:33' FROM a);
> Error: near "SELECT": syntax error
>
> I then tried,
> sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 
> 'user1','2019-03-01 14:22:33' FROM a));
> Error: 1 values for 5 columns
>
> and I also tried,
>
> sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 
> 'user1','2019-03-01 14:22:33'));
> Error: near ")": syntax error
>
> I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but 
> I couldn't make sense of it.
>
> Any help would be greatly appreciated.
>
> thanks.
>
> josé
>
> ___
> 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] FTS5 Transaction Leads to OOB Read

[Chu]

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

Re: [sqlite] INSERTing from another table data

[R Smith]

I see the "ignore this" retraction, but I thought to mention the 
following any way, for future reference:


Three ways in SQL to create and fill a table with data from another:

1. CREATE ... AS
Example:
CREATE TABLE newTable AS SELECT a,b,c FROM oldTable;
(This method has the advantage of being fast and cheap in programming 
time, but takes away control over column affinities etc.)

https://www.w3schools.com/sql/sql_create_table.asp

2. CREATE TABLE + INSERT
Example:
CREATE TABLE newTable(a INT, b REAL, c TEXT);
INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable;
(This method gives more control over the new table's schema, but does 
require 2 steps).

https://www.w3schools.com/sql/sql_insert_into_select.asp

3. SELECT ... INTO
Example:
SELECT a,b,c FROM oldTable INTO newTable;
(This SQL has much the same advantages and disadvantages as 1. above, 
except that SQLite specifically does not support this method [that I 
know of])

https://www.w3schools.com/sql/sql_select_into.asp


On 2019/03/19 3:15 PM, Jose Isaias Cabrera wrote:

Greetings.

I have this table,


create table a (a, b, c);

insert into a values (1, 2, 3);

insert into a values (2, 3, 4);

insert into a values (3, 4, 5);

insert into a values (4, 5, 6);

insert into a values (5, 6, 7);

insert into a values (6, 7, 8);

and I also have this table,


create table b (a, b, c, d, e);

I want to INSERT the data in table a, to b.  I tried these,

sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a);
Error: near "SELECT": syntax error

I then tried,
sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a));
Error: 1 values for 5 columns

and I also tried,

sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 
'user1','2019-03-01 14:22:33'));
Error: near ")": syntax error

I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but I 
couldn't make sense of it.

Any help would be greatly appreciated.

thanks.

josé

___
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] INSERTing from another table data

[Jose Isaias Cabrera]

Ignore this.  Sorry.  I should always count to 10 before sending things.  
Apologies.



From: sqlite-users  on behalf of 
Jose Isaias Cabrera 
Sent: Tuesday, March 19, 2019 09:15 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] INSERTing from another table data


Greetings.

I have this table,


create table a (a, b, c);

insert into a values (1, 2, 3);

insert into a values (2, 3, 4);

insert into a values (3, 4, 5);

insert into a values (4, 5, 6);

insert into a values (5, 6, 7);

insert into a values (6, 7, 8);

and I also have this table,


create table b (a, b, c, d, e);

I want to INSERT the data in table a, to b.  I tried these,

sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a);
Error: near "SELECT": syntax error

I then tried,
sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a));
Error: 1 values for 5 columns

and I also tried,

sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 
'user1','2019-03-01 14:22:33'));
Error: near ")": syntax error

I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but I 
couldn't make sense of it.

Any help would be greatly appreciated.

thanks.

josé

___
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] INSERTing from another table data

[Jose Isaias Cabrera]

Greetings.

I have this table,


create table a (a, b, c);

insert into a values (1, 2, 3);

insert into a values (2, 3, 4);

insert into a values (3, 4, 5);

insert into a values (4, 5, 6);

insert into a values (5, 6, 7);

insert into a values (6, 7, 8);

and I also have this table,


create table b (a, b, c, d, e);

I want to INSERT the data in table a, to b.  I tried these,

sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a);
Error: near "SELECT": syntax error

I then tried,
sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 
'user1','2019-03-01 14:22:33' FROM a));
Error: 1 values for 5 columns

and I also tried,

sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 
'user1','2019-03-01 14:22:33'));
Error: near ")": syntax error

I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but I 
couldn't make sense of it.

Any help would be greatly appreciated.

thanks.

josé

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

Re: [sqlite] FTS5 Transaction Leads to OOB Read

[Dan Kennedy]

That's the error I got too. Now fixed here:

  https://sqlite.org/src/info/b3fa58dd7403dbd4

Dan.



On 19/3/62 04:22, Dominique Pellé wrote:

Chu  wrote:


The code:

```
CREATE VIRTUAL TABLE t1 USING fts5(content);

BEGIN;
 INSERT INTO t1 (content) VALUES('');
 SELECT * FROM 
t1('*');
END;
```

As you can see, it creates a virtual table with fts5, and run a transaction on 
it, this will leads to a OOB READ. The ASAN report:

```
➜  sqlite-crashes ../sqlite-autoconf-3270200/sqlite3 < 2-oob-read.sql
=
==21007==ERROR: AddressSanitizer: heap-buffer-overflow on address 
0x60d02898 at pc 0x7f0cad16e6a3 bp 0x7ffdc88ddc80 sp 0x7ffdc88dd430
READ of size 81 at 0x60d02898 thread T0
 #0 0x7f0cad16e6a2  (/lib/x86_64-linux-gnu/libasan.so.5+0xb86a2)
 #1 0x563324ca4013 in fts5HashEntrySort 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:207762
 #2 0x563324e685f9 in sqlite3Fts5HashScanInit 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:207820
 #3 0x563324e685f9 in fts5SegIterHashInit 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:210321

...snip..

Just to confirm that there is a bug when running your
queries in the SQLite-3.27.2 shell with valgrind.

I did not get a heap overflow, but valgrind complains
about uninitialized memory in the same fts5HashEntrySort
function as in your stack:

$ valgrind --track-origins=yes --num-callers=50 sqlite3_shell
==10856== Memcheck, a memory error detector
==10856== Copyright (C) 2002-2017, and GNU GPL'd, by Julian Seward et al.
==10856== Using Valgrind-3.14.0 and LibVEX; rerun with -h for copyright info
==10856== Command: ./Output/Binary/x86_64-Linux-clang/Debug/bin/sqlite3_shell
==10856==
SQLite version 3.27.2 2019-02-25 16:06:06
NDSeV devkit 3.27.2.1 2019-02-26 16:04:39 990c4f90c3340db5
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIRTUAL TABLE t1 USING fts5(content);
sqlite> BEGIN;
sqlite> INSERT INTO t1 (content) VALUES('');
sqlite> SELECT * FROM
t1('*');
==10856== Conditional jump or move depends on uninitialised value(s)
==10856==at 0x4C362B2: __memcmp_sse4_1 (vg_replace_strmem.c:)
==10856==by 0x4ECC86: fts5HashEntrySort (nds_sqlite3.c:207811)
==10856==by 0x4EC55D: sqlite3Fts5HashScanInit (nds_sqlite3.c:207869)
==10856==by 0x4EBB45: fts5SegIterHashInit (nds_sqlite3.c:210370)
==10856==by 0x4EB1BE: fts5MultiIterNew (nds_sqlite3.c:211319)
==10856==by 0x4EB5A8: fts5SetupPrefixIter (nds_sqlite3.c:212995)
==10856==by 0x4EAE4A: sqlite3Fts5IndexQuery (nds_sqlite3.c:213324)
==10856==by 0x4EAA61: fts5ExprNearInitAll (nds_sqlite3.c:205310)
==10856==by 0x4EA6F3: fts5ExprNodeFirst (nds_sqlite3.c:205827)
==10856==by 0x4EA5B4: sqlite3Fts5ExprFirst (nds_sqlite3.c:205885)
==10856==by 0x4E9D1A: fts5CursorFirst (nds_sqlite3.c:215420)
==10856==by 0x4E2DD3: fts5FilterMethod (nds_sqlite3.c:215702)
==10856==by 0x4672A7: sqlite3VdbeExec (nds_sqlite3.c:90382)
==10856==by 0x42F876: sqlite3Step (nds_sqlite3.c:81765)
==10856==by 0x42F51C: sqlite3_step (nds_sqlite3.c:81830)
==10856==by 0x4236B8: exec_prepared_stmt (shell.c:10469)
==10856==by 0x4104EB: shell_exec (shell.c:10776)
==10856==by 0x42414E: runOneSqlLine (shell.c:16136)
==10856==by 0x410C5A: process_input (shell.c:16236)
==10856==by 0x40752D: main (shell.c:16995)
==10856==  Uninitialised value was created by a heap allocation
==10856==at 0x4C2FE56: malloc (vg_replace_malloc.c:299)
==10856==by 0x4E0098: sqlite3MemMalloc (nds_sqlite3.c:22886)
==10856==by 0x441ED0: mallocWithAlarm (nds_sqlite3.c:26718)
==10856==by 0x425E0B: sqlite3Malloc (nds_sqlite3.c:26748)
==10856==by 0x425E7F: sqlite3_malloc64 (nds_sqlite3.c:26772)
==10856==by 0x4FE464: sqlite3Fts5HashWrite (nds_sqlite3.c:207636)
==10856==by 0x4FE1F0: sqlite3Fts5IndexWrite (nds_sqlite3.c:213247)
==10856==by 0x4FACB9: fts5StorageInsertCallback (nds_sqlite3.c:217629)
==10856==by 0x505B04: fts5UnicodeTokenize (nds_sqlite3.c:218923)
==10856==by 0x4F623D: sqlite3Fts5Tokenize (nds_sqlite3.c:204268)
==10856==by 0x4FA0DD: sqlite3Fts5StorageIndexInsert (nds_sqlite3.c:217984)
==10856==by 0x4F9E44: fts5StorageInsert (nds_sqlite3.c:215929)
==10856==by 0x4E359D: fts5UpdateMethod (nds_sqlite3.c:216036)
==10856==by 0x4677E6: sqlite3VdbeExec (nds_sqlite3.c:90593)
==10856==by 0x42F876: sqlite3Step (nds_sqlite3.c:81765)
==10856==by 0x42F51C: sqlite3_step (nds_sqlite3.c:81830)
==10856==by 0x4236B8: exec_prepared_stmt (shell.c:10469)
==10856==by 0x4104EB: shell_exec (shell.c:10776)
==10856==by 0x42414E: runOneSqlLine (shell.c:16136)
==10856==by 0x410C5A: 

Re: [sqlite] FTS5 Transaction Leads to NULL Pointer

[Dan Kennedy]

On 18/3/62 15:48, Chu wrote:

The code:

```
CREATE VIRTUAL TABLE t1 USING fts5(content);

INSERT INTO t1 VALUES('');

BEGIN ;
DELETE FROM t1 WHERE rowid = 1;
SELECT * FROM t1 WHERE content MATCH '';
INSERT INTO t1 VALUES('');
SELECT * FROM t1 WHERE content MATCH '';
END;
``



Thanks very much for isolating and reporting this problem, and the other 
one. Now fixed here:


  https://sqlite.org/src/info/45c73deb440496e8

Dan.




As you can see, it creates a virtual table with fts5, and run a transaction on 
it, this will leads to a crash because of null pointer. The ASAN report:

```
➜  sqlite-crashes ../sqlite-autoconf-3270200/sqlite3 < 1-null-pointer.sql
AddressSanitizer:DEADLYSIGNAL
=
==20822==ERROR: AddressSanitizer: SEGV on unknown address 0x (pc 
0x55df5393c60a bp 0x0001 sp 0x706021b0 T0)
==20822==The signal is caused by a READ memory access.
==20822==Hint: address points to the zero page.
 #0 0x55df5393c609 in fts5ChunkIterate 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:210934
 #1 0x55df5393ca5e in fts5SegiterPoslist 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:210970
 #2 0x55df5393d65d in fts5IterSetOutputs_Full 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:211177
 #3 0x55df5393f17e in fts5MultiIterNext 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:210732
 #4 0x55df539444e9 in fts5MultiIterNew 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:211309
 #5 0x55df5394702f in sqlite3Fts5IndexQuery 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:213266
 #6 0x55df5398a566 in fts5ExprNearInitAll 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:205261
 #7 0x55df5398a566 in fts5ExprNodeFirst 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:205778
 #8 0x55df5398ad3d in sqlite3Fts5ExprFirst 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:205836
 #9 0x55df5398af0d in fts5CursorFirst 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:215371
 #10 0x55df5398cc9d in fts5FilterMethod 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:215653
 #11 0x55df538a973a in sqlite3VdbeExec 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:90333
 #12 0x55df538c5439 in sqlite3Step 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:81716
 #13 0x55df538c5439 in sqlite3_step 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:81781
 #14 0x55df536f9662 in exec_prepared_stmt 
/root/Documents/sqlite-autoconf-3270200/shell.c:10445
 #15 0x55df536f9662 in shell_exec 
/root/Documents/sqlite-autoconf-3270200/shell.c:10752
 #16 0x55df536fbdf3 in runOneSqlLine 
/root/Documents/sqlite-autoconf-3270200/shell.c:16106
 #17 0x55df5370b466 in process_input 
/root/Documents/sqlite-autoconf-3270200/shell.c:16206
 #18 0x55df536d6c98 in main 
/root/Documents/sqlite-autoconf-3270200/shell.c:16967
 #19 0x7f5c4f52809a in __libc_start_main ../csu/libc-start.c:308
 #20 0x55df536d8599 in _start 
(/root/Documents/sqlite-autoconf-3270200/sqlite3+0x46599)

AddressSanitizer can not provide additional info.
SUMMARY: AddressSanitizer: SEGV 
/root/Documents/sqlite-autoconf-3270200/sqlite3.c:210934 in fts5ChunkIterate
==20822==ABORTING
```

View detail In gdb:

```
(gdb) r < 1-null-pointer.sql
The program being debugged has been started already.
Start it from the beginning? (y or n) Y
Starting program: /root/Documents/sqlite-autoconf-3270200/sqlite3 < 
1-null-pointer.sql
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".

Breakpoint 1, 0x557fe60a in fts5ChunkIterate (p=p@entry=0x60d00ad8, 
pSeg=pSeg@entry=0x61300b28, pCtx=0x7fffac00,
 xChunk=xChunk@entry=0x55622dc0 ) at 
sqlite3.c:210934
210934pData = fts5LeafRead(p, FTS5_SEGMENT_ROWID(pSeg->pSeg->iSegid, 
pgno));
(gdb) bt
#0  0x557fe60a in fts5ChunkIterate (p=p@entry=0x60d00ad8, 
pSeg=pSeg@entry=0x61300b28, pCtx=0x7fffac00,
 xChunk=xChunk@entry=0x55622dc0 ) at 
sqlite3.c:210934
#1  0x557fea5f in fts5SegiterPoslist (p=0x60d00ad8, 
pSeg=0x61300b28, pColset=pColset@entry=0x602014b8, 
pBuf=pBuf@entry=0x61300ae8)
 at sqlite3.c:210970
#2  0x557ff65e in fts5IterSetOutputs_Full (pIter=0x61300ac8, 
pSeg=) at sqlite3.c:211177
#3  0x5580117f in fts5MultiIterNext (p=p@entry=0x60d00ad8, 
pIter=pIter@entry=0x61300ac8, bFrom=bFrom@entry=0, iFrom=iFrom@entry=0)
 at sqlite3.c:210732
#4  0x558064ea in fts5MultiIterNew (p=p@entry=0x60d00ad8, 
pStruct=pStruct@entry=0x60402458, flags=flags@entry=16,
 pColset=pColset@entry=0x602014b8, pTerm=, nTerm=nTerm@entry=5, 
iLevel=, nSegment=, ppOut=)
 at sqlite3.c:211309
#5  0x55809030 in sqlite3Fts5IndexQuery (p=0x60d00ad8, 
pToken=pToken@entry=0x60201498 "", nToken=4,