[sqlite] Possible bug

[Marv Anderson]

Hello,

I am a member of this list, but I am not sure which email address you 
have.  I have tried the ones that I usually use, but my messages are 
getting held due to not recognizing my address.

I am having a problem doing a Linq Insert using SQLite in Visual Studio 
2015 (Community version) in WIndows 10.  I installed the latest release 
of all of the SQLite code using NuGet immediately before running this 
latest test.

I am assuming that I am doing something wrong, since it's hard to 
believe that a bug this obvious exists in this product, but I sure can't 
see what my error is. I hope someone can point out the problem.  I have 
had this problem in earlier releases, with other tables, with other 
versions of VS, and other versions of WIndows.  I finally decided to 
isolate the logic to the simplest possible situation. and the problem 
persists.

I am not sure if this is a proper way to submit a potential bug report, 
but I can't find any other mechanism for bug reports, and saw an earlier 
mention of submitting bugs to this list.  I hope that I have included 
everything that might be pertinent to this issue - if not, please let me 
know what else is needed.

Thanks

Marv Anderson

I get the following message every time:

SQL Logic error or missing database near "SELECT": syntax error

I think that the problem is with the CONVERT(?), since I cannot find any 
SQLite documentation that mentions this command. But it appears that the 
CONVERT is generated by the SQLite Linq package.

Here is the log file produced by the operation.

 INSERT INTO [Test]([Date1], [Char1], [Int1])
 VALUES (@p0, @p1, @p2)

 SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
 -- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [1/1/0001 
12:00:00 AM]
 -- @p1: Input String (Size = 4000; Prec = 0; Scale = 0) [Testing 
Insert]
 -- @p2: Input Int32 (Size = -1; Prec = 0; Scale = 0) [0]

 this calls the testing logic

 TestInsert test = new TestInsert();
 test.DoTheInsert("Testing Insert");

 this is the class containing the testing logic

 class TestInsert{
  [Table(Name="Test")]
  public  class qTest{
[Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = 
true, IsDbGenerated = true)]
 public int Id{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public DateTime Date1{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public String Char1{get;set;}
[Column(UpdateCheck=UpdateCheck.Never)]
 public int Int1{get;set;}
  publicqTest( string Char1){//this let's us set all of 
the values in one line of code
}//c
  publicqTest(){//this let's us set all of the values in 
one line of code
}//c
  }//cl:qProgress
  publicTestInsert(){
//DoTheInsert( "Test");
}
  publicvoid DoTheInsert( string pChar){//
DataContext context = new DataContext( cTb.Db.DB );
context.Log = new 
StreamWriter("linq-to-sql-Test.log"){ AutoFlush = true };
qTest test = new qTest();
test.Char1 = pChar;
context.GetTable().InsertOnSubmit( test);
try {
   context.SubmitChanges();
} catch(Exception ex) {
MessageBox.Show(ex.Message);
}
}//m
  }

Here is the DDL for the table into which I am trying to do the insert.

 CREATE TABLE [Test] (
   [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   [Char1] NCHAR DEFAULT "Char",
   [Int1] INTEGER NOT NULL DEFAULT 0,
   [Date1] DATETIME DEFAULT (datetime('now')));

[sqlite] compiling 3.11.1

[Tim Uy]

yup must be just me, it works fine on a clean fossil clone

On Wed, Mar 9, 2016 at 10:15 PM, Tim Uy  wrote:

> It is probably just something I mangled - but oddly my tsrc/fts5.c has no
> trace of sqlite3PagerGet and no more than 2 or so lines.
>
> On Wed, Mar 9, 2016 at 10:07 PM, Tim Uy  wrote:
>
>> I'm getting
>>
>> tsrc/fts5.c(23549): error C2198: 'sqlite3PagerGet': too few arguments for
>> call
>> tsrc/fts5.c(23568): error C2198: 'sqlite3PagerGet': too few arguments for
>> call
>>
>> What am I missing? :P
>>
>
>

[sqlite] CTE for a noob

[R Smith]

On 2016/03/09 10:01 PM, David Raymond wrote:
> For my own clarification, the statements quoted way down below aren't exactly 
> equivalent, correct?
>
> "For each pair of columns identified by a USING clause, the column from the 
> right-hand dataset is omitted from the joined dataset. This is the only 
> difference between a USING clause and its equivalent ON constraint."
>
> (Short version: USING will return fewer fields than ON, and order can matter 
> for both types because of affinity/collation)

Indeed so - the USING optimises the columns out that are coincidental 
(by assuming duplication would be unwanted) when you request the 
asterisk column wildcard, as I did in the examples. I should have been 
more specific in that it is the result-set that is algebraically equivalent.

This per-chance column layout should however never be depended on, 
always specify the columns that you want in the results in any 
production query.


Thanks, good to point this out to prospective USING users!  :)

Cheers,
Ryan

[sqlite] sqlite3_update_hook() clarification

[Sairam Gaddam]

Thank you very much!!
It helped a lot.

On Wed, Mar 9, 2016 at 9:26 PM, Clemens Ladisch  wrote:

> Sairam Gaddam wrote:
> > The documentation says that the function sqlite3_update_hook() is called
> > whenever a row is updated, deleted or inserted
>
> No.  It says that this function is called to register a callback
> function that is called for these updates.
>
> > And I don't find any definition for this callback routine.
>
> void my_little_callback(void *stuff, int op, const char *db, const char
> *table, sqlite_int64 rowid)
> {
> printf("something was updated\n");
> }
>
> int main()
> {
> ...
> sqlite3_update_hook(db, my_little_callback, NULL);
> ...
> }
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multithreaded SQLite

[Simon Slavin]

On 9 Mar 2016, at 10:15pm, Philippe Riand  wrote:

> I tries busy_timeout but that was unsuccessful.

What did you set the time to ?  Try 5 minutes.

You should definitely remove all the unusual PRAGMAs apart from that one and 
see if that makes the problem go away.

Simon.

[sqlite] compiling 3.11.1

[Tim Uy]

It is probably just something I mangled - but oddly my tsrc/fts5.c has no
trace of sqlite3PagerGet and no more than 2 or so lines.

On Wed, Mar 9, 2016 at 10:07 PM, Tim Uy  wrote:

> I'm getting
>
> tsrc/fts5.c(23549): error C2198: 'sqlite3PagerGet': too few arguments for
> call
> tsrc/fts5.c(23568): error C2198: 'sqlite3PagerGet': too few arguments for
> call
>
> What am I missing? :P
>

[sqlite] compiling 3.11.1

[Tim Uy]

I'm getting

tsrc/fts5.c(23549): error C2198: 'sqlite3PagerGet': too few arguments for
call
tsrc/fts5.c(23568): error C2198: 'sqlite3PagerGet': too few arguments for
call

What am I missing? :P

[sqlite] sqlite3_update_hook() clarification

[Sairam Gaddam]

http://www.sqlite.org/c3ref/update_hook.html

The documentation says that the function sqlite3_update_hook() is called
whenever a row is updated, deleted or inserted for a rowid table. But I
don't find this function to be invoked in my program. When will this
function be invoked??
And I am interested in its second parameter which gives the details of
table and the rowid of row which is updated. It is a callback function.

SQLITE_API void *SQLITE_STDCALL sqlite3_update_hook(
  sqlite3 *db,  /* Attach the hook to this database */
  void (*xCallback)(void*,int,char const *,char const *,sqlite_int64),
  void *pArg/* Argument to the function */
)

And I don't find any definition for this callback routine.

Can anyone kindly give information on this?

[sqlite] "Circular" order by

[R Smith]

On 2016/03/09 8:08 PM, Alberto Wu wrote:
> On 03/09/16 17:26, R Smith wrote:
>> Firstly, this is the best method - one I would use. UNION ALL is quite
>> efficient.
>> Secondly, the order by will be  honoured - you can refer to the SQL
>> standard for that even, it's an axiom of the output and probably not
>> even considered "needed" to mention.
> Ryan,
> Thanks a lot for the quick reply.
>
> I had thought about the SQL axiom too.
> However I've also noticed that sqlite actively forbids ORDER BY clauses
> in each SELECT when using UNION / UNION ALL, which makes that assumption
> kind of dull.
> In fact in order to achieve this per-SELECT sorting rather than the
> sorting of the combined result set you have to trick sqlite through the
> use of the WITH construct or via a subquery in FROM (like in my example).
> So in the end I thought I'd just be safe and ask :)

Quite right, apologies, I meant to say that your example was the correct 
way and expected to work, not the initial assumption (which already is 
obvious to not work in SQLite).
i.e. this:

SELECT * FROM (SELECT * FROM t WHERE id >= 'pen' ORDER BY id)
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE id < 'pen' ORDER BY id)
;

As long as you don't impose another order-by on an outer query, nor 
impose the UNION (without the ALL)[1], the output is simply appended and 
will be correctly sorted.

As a point of interest, is this query really much slower for you? It 
should produce the exact same order:
SELECT 0 AS Sect, * FROM t WHERE id >= 'pen'
UNION
SELECT 1, * FROM t WHERE id < 'pen'
;

You could also wrap the above inside another query, keeping the UNION 
ALL, stripping the Sect field and doing your own Order by, like so:

SELECT P.id FROM (
  SELECT 0 AS sect, id FROM t WHERE id >= 'pen'
  UNION ALL
SELECT 1, id FROM t WHERE id < 'pen'
) AS P
ORDER BY P.sect, P.id
;

Test to find the fastest case :)

Cheers!
Ryan


[1] - When using UNION only, SQLite might re-order the output set to be 
able to quicker check for duplications

[sqlite] CTE for a noob

[David Raymond]

For my own clarification, the statements quoted way down below aren't exactly 
equivalent, correct?

"For each pair of columns identified by a USING clause, the column from the 
right-hand dataset is omitted from the joined dataset. This is the only 
difference between a USING clause and its equivalent ON constraint."

(Short version: USING will return fewer fields than ON, and order can matter 
for both types because of affinity/collation)

So if you have tables tA and tB (ID integer, FirstName text COLLATE NOCASE, 
LastName text COLLATE NOCASE)...
with contents
tA: (1, 'john', 'smith')
tB: (2, 'JOHN', 'SMIth')

then
select * from tA inner join tB USING (FirstName, LastName);
gives
1, 'john', 'smith', 2

select * from tB inner join tA USING (FirstName, LastName);
gives
2, 'JOHN', 'SMIth', 1

and
select * from tA inner join tB on tA.Firstname = tB.FirstName and tA.LastName = 
tB.LastName;
gives
1, 'john', 'smith', 2, 'JOHN', 'SMIth'


And let's say tB did NOT have the COLLATE NOCASE, then
select * from tA inner join tB USING (FirstName, LastName);
would use tA's NOCASE collation (first table listed) for all fields and return 
the 1 record,

whereas
select * from tA inner join tB ON tB.FirstName = tA.FirstName and tB.LastName = 
tA.LastName;
would use tB's BINARY collation (tB on the left side of the =) and return no 
records.


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith
Sent: Tuesday, March 08, 2016 12:11 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] CTE for a noob



On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote:
> Now I'll have to use USING a bit more often to get the drift and get 
> out of this multi-call thing.  I sort of see what is going on here, 
> but practice is whats needed.

"USING" has three uses in SQLite, first to enlist a virtual table, secondly to 
suggest an Index to the Query Planner and thirdly as in the example Igor gave 
where "USING" is simply short-hand for a join where the joining index fields 
are simple and named the same. This is defined in the standard as a join 
operation, by the way, works everywhere so not special to SQLite.

Easiest is probably by dual example - these two statements are equivalent:
SELECT *
   FROM tA
   JOIN tB USING (ID)

vs.

SELECT *
   FROM tA
   JOIN tB ON tB.ID = tA.ID

Of course this next query can't be simplified since the field-names do not 
match:

SELECT *
   FROM tA
   JOIN tB ON tB.ParentID = tA.ID

which might make the "USING" thing seem a bit overrated at first glance, but 
consider the following equivalent queries to see its simplifying power:

SELECT *
   FROM tA
   JOIN tB ON tB.Surname = tA.Surname AND tB.FirstName = tA.FirstName AND 
tB.DateOfBirth = tA.DateOfBirth

vs.

SELECT *
   FROM tA
   JOIN tB USING (Surname, FirstName, DateOfbirth)

the basic format of which, I might add, covers a very large percentage of 
typical joined queries.


Cheers!
Ryan

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

[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction

[Tomash Brechko]

Hello,

With 3.11.0 if you run the following SQL you will get no result (which is
wrong):

-- cut --
BEGIN;
CREATE VIRTUAL TABLE fts USING fts4 (t);
INSERT INTO fts (rowid, t) VALUES (1, 'test');
INSERT INTO fts (fts) VALUES ('optimize');
COMMIT;
SELECT rowid FROM fts WHERE fts MATCH 'test';
-- cut --

If however you comment out either 'optimize' statement or BEGIN/COMMIT
you'll get '1' (which is correct).

Tested with 3.11.0 FTS3/4.  I have no 3.11.1 nor FTS5 so unable to test
there.  News for 3.11.1 mentions

  Fix an FTS5 issue in which the 'optimize' command could cause index
corruption.

which may or may not be related.


Regards,
-- 
  Tomash Brechko

[sqlite] Multiple connections to in-memory db via .net

[Joe Mistachkin]

Jarred Ford wrote:
>
> How can I create multiple connections to a shared SQLite in-memory
> database via .net?  Thanks in advance.
>

SQLiteConnection connection = new SQLiteConnection(
"FullUri=file::memory:?cache=shared;"); 

--
Joe Mistachkin

[sqlite] "Circular" order by

[Alberto Wu]

On 03/09/16 17:26, R Smith wrote:
> Firstly, this is the best method - one I would use. UNION ALL is quite
> efficient.
> Secondly, the order by will be  honoured - you can refer to the SQL
> standard for that even, it's an axiom of the output and probably not
> even considered "needed" to mention.

Ryan,
Thanks a lot for the quick reply.

I had thought about the SQL axiom too.
However I've also noticed that sqlite actively forbids ORDER BY clauses
in each SELECT when using UNION / UNION ALL, which makes that assumption
kind of dull.
In fact in order to achieve this per-SELECT sorting rather than the
sorting of the combined result set you have to trick sqlite through the
use of the WITH construct or via a subquery in FROM (like in my example).
So in the end I thought I'd just be safe and ask :)

> A point that might be worth considering: Union all will not rid
> duplicates and it's probably possible to get results like:

That's perfectly fine: like in my example the column is unique in my
real life case as well.
Besides, UNION does not honour the original like UNION ALL does.

> If there are two legitimate copies of pen in your input data and another
> containing a space at the end and you use the RTRIM collation. That may
> or may not be what you want, but should be very easy to deal with either
> way.

The actual column type is BINARY but thanks for the tip anyway!

Thanks again,
-- Alberto

[sqlite] How to export all entries from a sqlite database into a textfile?

[Ben Stover]

As you may know Firefox browser uses a places.sqlite to store all its bookmarks.

Assume the used sqlite database is

D:\firefox\myprofile\places.sqlite

How can I use sqlite3.exe (under Windows 7) to extract/export all entries from 
this database into a text file

D:\firefox\bookmarks\extracted.txt

...and how can import them later?

The textfile should be (if possible) human readable.

Does it matter if the sqlite database is currently locked by firefox?

Related question: Is it possible to create a single file PER ENTRY (instead of 
a whole textfile)?

Ben

[sqlite] "Circular" order by

[R Smith]

On 2016/03/09 6:07 PM, Alberto Wu wrote:
> Hi all,
>
> I'm looking for suggestions...
> What I want to achieve is to "roll" the result set of a query around by
> a certain amount (as in offset + wrap around).
>
> For example, given that:
> CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY);
> INSERT INTO t VALUES ('pen'), ('tree'), ('desk'), ('car');
>
> I would like to have the same result set of:
> SELECT * FROM t ORDER BY id ASC;
> car
> desk
> pen
> tree
>
> Except I would like it to start at "pen", producing:
> pen
> tree
> car
> desk
>
> Now, one way to do it is:
> SELECT * FROM t ORDER BY CASE WHEN id >= 'pen' THEN 0 ELSE 1 END, id;
> Except that this pretty much kills the query efficiency:
> 0|0|0|SCAN TABLE t
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> As opposed to the original query whose query plan is:
> 0|0|0|SCAN TABLE t USING COVERING INDEX sqlite_autoindex_t_1
>
> Since fiddling with ORDER BY seems to always kill the index scan
> optimization, I've instead resorted to split the query and use WHERE.
> The following happens to work and to properly make use of indexes:
> SELECT * FROM (SELECT * FROM t WHERE id >= 'pen' ORDER BY id)
> UNION ALL
> SELECT * FROM (SELECT * FROM t WHERE id < 'pen' ORDER BY id);
>
> However, looking at the docs I couldn't find any guarantee that "UNION
> ALL" preserves the inner ordering.
>
>
> Can somebody confirm that the ordering is always preserved when using
> UNION ALL?
> Or can somebody recommend an optimal way to deal with the issue which
> doesn't involve running two separate queries?

Firstly, this is the best method - one I would use. UNION ALL is quite 
efficient.
Secondly, the order by will be  honoured - you can refer to the SQL 
standard for that even, it's an axiom of the output and probably not 
even considered "needed" to mention.

A point that might be worth considering: Union all will not rid 
duplicates and it's probably possible to get results like:

pen
pen
pen
tree

If there are two legitimate copies of pen in your input data and another 
containing a space at the end and you use the RTRIM collation. That may 
or may not be what you want, but should be very easy to deal with either 
way.

Cheers,
Ryan

[sqlite] <> in Makefile.msc

[Joe Mistachkin]

There are marks in the MSVC makefile to permit tooling to remove and/or replace 
the marked sections, see "tools/mkmsvcmin.tcl".

Sent from my iPhone

> On Mar 9, 2016, at 5:26 PM, Tim Uy  wrote:
> 
> What does <>  <> mean in Makefile.msc? Just noticed it.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

[sqlite] Correlated subquery refers to wrong rowid - bug?

[R Smith]

On 2016/03/09 5:39 PM, Jean-Christophe Deschamps wrote:
>
> Sorry guys, I don't know why I wrote that. In fact I know: I shouldn't 
> be talking over the phone while reading the list.
>
> Of course I use correlated subqueries a lot, but never had to 
> re-select a column from the enclosing query. Indeed, I'd say that most 
> of the time one uses columns from enclosing query as expressions, e.g. 
> in comparison operators or functions in a where clause.

Any column reference is indeed an expression, whether in a main or sub 
query, but I agree on the very typical WHERE clause locality for such 
queries.


>
> The OP query is pretty uncommon since it's guaranteed to produce an 
> empty result, hence it's no surprise that the bug was so old.

This is very true, and I propose that the only reason the OP even found 
the bug is that he made a mistake when designing the query, and when 
tracing the steps and finding the mistake, mused over how he got any 
output from it at all - then thinking it should have produced an Error 
condition, hence posting the question. He would probably still be 
surprised to learn that the syntax is indeed valid with the bug being 
even more obscure than might be imagined. (I could be very wrong though).

I myself thought this was a duplicate of a bug found some time ago in 
which the rowid in a sub query was misunderstood also producing wrong 
answers, but that was due to automatic indexing and was fixed back in 3.9.2.

[sqlite] How to export all entries from a sqlite database into a textfile?

[Simon Slavin]

On 9 Mar 2016, at 5:47pm, Ben Stover  wrote:

> How can I use sqlite3.exe (under Windows 7) to extract/export all entries 
> from this database into a text file

As an experiment, try using the command file to open the database and show the 
schema:

sqlite3 D:\firefox\myprofile\places.sqlite
[intro text from application]
>.schema
[reply]
>.quit

If that works then you know it can access the database correctly.

Simon.

[sqlite] "Circular" order by

[James K. Lowden]

On Wed, 9 Mar 2016 20:43:14 +0200
R Smith  wrote:

> SELECT P.id FROM (
>   SELECT 0 AS sect, id FROM t WHERE id >= 'pen'
>   UNION ALL
> SELECT 1, id FROM t WHERE id < 'pen'
> ) AS P
> ORDER BY P.sect, P.id
> ;

This is the correct answer.  

I'm not sure what you meant by "axiom" in your earlier post.  To the
best of my knowledge, UNION ALL makes no promise about order (and
standard SQL proscribes ORDER BY in a subquery).  If the implementation
finds it more efficient, for instance, to send alternating rows from
each element in the union, it's free to do so.  

--jkl

[sqlite] <> in Makefile.msc

[Tim Uy]

What does <>  <> mean in Makefile.msc? Just noticed it.

[sqlite] Insert or Replace Trouble

[R Smith]

Apologies, it seems you got lots of replies explaining why, but I failed 
to mention how it would work.

Try this in your code:

CREATE TABLE messages (id  numeric PRIMARY KEY);
insert or replace into messages values (1);
insert or replace into messages values (1);
insert or replace into messages values (1);

Another few possibilities to achieve uniqueness similar to the above are:

CREATE TABLE messages (
   id  numeric,
   PRIMARY KEY (id) -- Sets the id as the Primary key
);

CREATE TABLE messages (
   id  numeric,
   CONSTRAINT "UIdxMessages" UNIQUE (id) -- Creates a Unique index on id
);

CREATE TABLE messages (
   id  numeric,
);
CREATE UNIQUE INDEX UIdxMEssages ON messages(id); -- Same as above but 
outside of Table dec



On 2016/03/09 5:10 PM, R Smith wrote:
>
>
> On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote:
>> All,
>>
>> I'm trying to use the INSERT OR REPLACE syntax so that I don't have 
>> to worry about duplicate entries without creating a trigger.  Using 
>> the schema:
>> CREATE TABLE messages (id numeric);
>>
>> and using the SQL:
>> insert or replace into messages values (1);
>>
>> Why does this result in multiple rows of the same value?  I'm I just 
>> not understanding the syntax correctly?
>
> Nothing wrong with the syntax. I assume you don't have a unique 
> constraint (Index) on that column, so the DB has no trouble adding 
> more than one entry.
>
> The REPLACE mechanism only works if it Can't insert a row because one 
> already exists AND the DB does not allow more than one to exist, only 
> then does it replace the previous.
>
> Hope that was the problem!
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correlated subquery refers to wrong rowid - bug?

[James K. Lowden]

On Wed, 9 Mar 2016 10:13:28 -0500
Richard Hipp  wrote:

> > which outputs one result (2), although the expected result would be
> > empty.

Sorry for my "what bug?" post.  I forgot that the output was wrong!  

--jkl

[sqlite] Correlated subquery refers to wrong rowid - bug?

[James K. Lowden]

On Wed, 09 Mar 2016 15:32:01 +0100
Jean-Christophe Deschamps  wrote:

> >   select id from a where id not in (select a.id from b);

> As I understand it, there is no more an a.id column in table b. It 
> looks like SQLite is trying to get clever ignoring the "a." qualifier.

It's not ignoring the qualifier.  It's processing an odd correlated
subquery.  This would produce the same:

select id from a where id not in (select a.id);

For each row in a, select the id that is not the id in the row.  

What might be a little suprising is that the columns named in the
SELECT need not come from the table in the FROM clause (if there is
one).  For example, I'm sure you find this valid: 

select id from a where id not in (
select 2 * (a.id / 2) from b
where a.id > b.id
);

Granted, that's a long walk for "odd ids in A greater than the smallest
id in B.  But it's not SQL's job to force succinct expression.  

--jkl

[sqlite] Correlated subquery refers to wrong rowid - bug?

[James K. Lowden]

On Wed, 9 Mar 2016 10:13:28 -0500
Richard Hipp  wrote:

> >   select id from a where id not in (select a.id from b);
> >
> > which outputs one result (2), although the expected result would be
> > empty.
> >
> 
> Thanks for the bug report.

What bug?  The query is valid SQL, and produces the correct results.
The programmer would have been better off writing a useful query, but
we've all been there.  

--jkl

[sqlite] Multithreaded SQLite

[Philippe Riand]

Yes, I?m using prepare(), step and finalize(). The 2 threads should actually 
have no interaction between them, but isolated. I mean the 2 threads should be 
able to do very different things (read data, write data?), within separated 
transactions.

[sqlite] Multithreaded SQLite

[Philippe Riand]

On the OS standpoint, we are using multiple ones: Android, iOS and desktop 
(Windows, OSX and Linux). So we need to figure a solution that works with all 
these OSes.
when I used the WAL mode, I got a schema locked, while in DELETE mode i got 
database locked.
>>>If WAL is supported, then you can have concurrent readers with one writer<<<
Right, but how can we get the second writer to wait until the lock is released 
and continue instead of getting an error? I tries busy_timeout but that was 
unsuccessful. I?d like to avoid ?locked? messages.

[sqlite] Insert or Replace Trouble

[R Smith]

On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote:
> All,
>
> I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry 
> about duplicate entries without creating a trigger.  Using the schema:
> CREATE TABLE messages (id numeric);
>
> and using the SQL:
> insert or replace into messages values (1);
>
> Why does this result in multiple rows of the same value?  I'm I just not 
> understanding the syntax correctly?

Nothing wrong with the syntax. I assume you don't have a unique 
constraint (Index) on that column, so the DB has no trouble adding more 
than one entry.

The REPLACE mechanism only works if it Can't insert a row because one 
already exists AND the DB does not allow more than one to exist, only 
then does it replace the previous.

Hope that was the problem!
Ryan

[sqlite] "Circular" order by

[Alberto Wu]

Hi all,

I'm looking for suggestions...
What I want to achieve is to "roll" the result set of a query around by
a certain amount (as in offset + wrap around).

For example, given that:
CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY);
INSERT INTO t VALUES ('pen'), ('tree'), ('desk'), ('car');

I would like to have the same result set of:
SELECT * FROM t ORDER BY id ASC;
car
desk
pen
tree

Except I would like it to start at "pen", producing:
pen
tree
car
desk

Now, one way to do it is:
SELECT * FROM t ORDER BY CASE WHEN id >= 'pen' THEN 0 ELSE 1 END, id;
Except that this pretty much kills the query efficiency:
0|0|0|SCAN TABLE t
0|0|0|USE TEMP B-TREE FOR ORDER BY
As opposed to the original query whose query plan is:
0|0|0|SCAN TABLE t USING COVERING INDEX sqlite_autoindex_t_1

Since fiddling with ORDER BY seems to always kill the index scan
optimization, I've instead resorted to split the query and use WHERE.
The following happens to work and to properly make use of indexes:
SELECT * FROM (SELECT * FROM t WHERE id >= 'pen' ORDER BY id)
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE id < 'pen' ORDER BY id);

However, looking at the docs I couldn't find any guarantee that "UNION
ALL" preserves the inner ordering.


Can somebody confirm that the ordering is always preserved when using
UNION ALL?
Or can somebody recommend an optimal way to deal with the issue which
doesn't involve running two separate queries?

Thanks in advance,
-- Alberto

[sqlite] Correlated subquery refers to wrong rowid - bug?

[R Smith]

On 2016/03/09 4:35 PM, Igor Tandetnik wrote:
> On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote:
>> At 15:16 09/03/2016, you wrote:
>>>   select id from a where id not in (select a.id from b);
>>
>> Shouldn't the engine bark on this, like it does on the modified version:
>>
>> select id from a where id not in (select zzz.id from b);
>>
>> "no such column zzz.id"
>
> While there is no column named zzz.id, there is in fact a column named 
> a.id. The query is syntactically valid.
>
>> As I understand it, there is no more an a.id column in table b.
>
> Yes, but why is that a problem? It is perfectly legal, and often 
> useful, for a subquery to refer to columns from enclosing query. 
> That's what makes it a *correlated* subquery.

True, but the OP's result is still in error. (Btw, I think this bug was 
fixed recently, it certainly did come up before, in 3.9 somewhere I 
think, tried to search the forum archive but no success).

To ask "SELECT a.id FROM a WHERE a.id NOT IN (SELECT a.ID FROM b)" means 
that for every iteration over table a, the correlated query should look 
in table b and return a.id (whatever that may be) as many times as there 
are rows in table b. This means that for every iteration, the subquery 
produces a repeating list of a.id values, and a.id MUST by definition 
always be in that list, so that query should have zero rows of output.

To explain better, contrast with the same query when I use text values 
in stead:

create table a(id TEXT);

create table b(id TEXT);

insert into a values ('AAA'), ('BBB'), ('CCC'), ('DDD');

insert into b values ('AAA'),('ZZZ');

select id from a where id not in (select a.id from b);

-- Zero output here, as expected

select id from a where id not in (select b.id from b);

   --   id
   -- -
   --  BBB
   --  CCC
   --  DDD
-- output as expected

As I recall, the bug only manifested when the rowid was implicated, such 
as the OP's create TABLE a (id INTEGER PRIMARY KEY); definition implies.

[sqlite] sqlite3_update_hook() clarification

[Clemens Ladisch]

Sairam Gaddam wrote:
> The documentation says that the function sqlite3_update_hook() is called
> whenever a row is updated, deleted or inserted

No.  It says that this function is called to register a callback
function that is called for these updates.

> And I don't find any definition for this callback routine.

void my_little_callback(void *stuff, int op, const char *db, const char *table, 
sqlite_int64 rowid)
{
printf("something was updated\n");
}

int main()
{
...
sqlite3_update_hook(db, my_little_callback, NULL);
...
}


Regards,
Clemens

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Jean-Christophe Deschamps]

At 16:14 09/03/2016, you wrote:
>On 3/9/2016 9:58 AM, R Smith wrote:
>>On 2016/03/09 4:35 PM, Igor Tandetnik wrote:
>>>Yes, but why is that a problem? It is perfectly legal, and often
>>>useful, for a subquery to refer to columns from enclosing query.
>>>That's what makes it a *correlated* subquery.
>>
>>True, but the OP's result is still in error.
>
>Yes, I realize that. I was only arguing against the proposition that 
>the query is syntactically invalid and should have been rejected on 
>these grounds.
>--
>Igor Tandetnik

Sorry guys, I don't know why I wrote that. In fact I know: I shouldn't 
be talking over the phone while reading the list.

Of course I use correlated subqueries a lot, but never had to re-select 
a column from the enclosing query. Indeed, I'd say that most of the 
time one uses columns from enclosing query as expressions, e.g. in 
comparison operators or functions in a where clause.

The OP query is pretty uncommon since it's guaranteed to produce an 
empty result, hence it's no surprise that the bug was so old.

[sqlite] Insert or Replace Trouble

[Clemens Ladisch]

Tilsley, Jerry M. wrote:
> CREATE TABLE messages (id numeric);
>
> insert or replace into messages values (1);
>
> Why does this result in multiple rows of the same value?

INSERT OR REPLACE deletes any old row that would cause a UNIQUE
constraint violation.

Without a UNIQUE (or PRIMARY KEY) constraint, there cannot be such
a constraint violation.


Regards,
Clemens

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Jean-Christophe Deschamps]

At 15:16 09/03/2016, you wrote:
>   select id from a where id not in (select a.id from b);

Shouldn't the engine bark on this, like it does on the modified version:

select id from a where id not in (select zzz.id from b);

"no such column zzz.id"

As I understand it, there is no more an a.id column in table b. It 
looks like SQLite is trying to get clever ignoring the "a." qualifier.

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Clemens Ladisch]

In the question 

David Paton asks about a query like this:

  create table a(id integer primary key);
  create table b(id integer primary key);
  insert into a values (1), (2);
  insert into b values (1);

  select id from a where id not in (select a.id from b);

which outputs one result (2), although the expected result would be
empty.

The EXPLAIN outputs shows that the database behaves as if the subquery
were referring to b.id, and in fact making that change in the query
results in exactly the same EXPLAIN output.


Regards,
Clemens

[sqlite] Insert or Replace Trouble

[Hick Gunter]

There is no unique constraint (express or implied) in your schema, therefore no 
conflict occurs and it is possible to insert as many identical rows as your 
diskspace will hold.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von 
Tilsley, Jerry M.
Gesendet: Mittwoch, 09. M?rz 2016 16:03
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Insert or Replace Trouble

All,

I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry 
about duplicate entries without creating a trigger.  Using the schema:
CREATE TABLE messages (id numeric);

and using the SQL:
insert or replace into messages values (1);

Why does this result in multiple rows of the same value?  I'm I just not 
understanding the syntax correctly?

Thanks,


Jerry Tilsley, CIS Lvl 2
Information Services | St. Claire Regional Medical Center Jerry.Tilsley at 
st-claire.org



Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/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: hick at 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] Insert or Replace Trouble

[Tilsley, Jerry M.]

All,

I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry 
about duplicate entries without creating a trigger.  Using the schema:
CREATE TABLE messages (id numeric);

and using the SQL:
insert or replace into messages values (1);

Why does this result in multiple rows of the same value?  I'm I just not 
understanding the syntax correctly?

Thanks,


Jerry Tilsley, CIS Lvl 2
Information Services | St. Claire Regional Medical Center
Jerry.Tilsley at st-claire.org



Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.

[sqlite] compile switches: SQLITE_OMIT_ATTACH & SQLITE_OMIT_VIRTUALTABLE

[Stephan Beal]

On Tue, Mar 8, 2016 at 2:47 PM, Gert Corthout 
wrote:

> hello,
> when I compile the amalgation with these compile
> switches:SQLITE_OMIT_VIRTUALTABLEorSQLITE_OMIT_VIRTUALTABLE
>

https://www.sqlite.org/compile.html

If any of these options are defined, then the same set of SQLITE_OMIT_*
options must also be defined when using the 'lemon' tool to generate the
parse.c file and when compiling the 'mkkeywordhash' tool which generates
the keywordhash.h file. Because of this, these options may only be used
when the library is built from canonical source, not from theamalgamation
. Some SQLITE_OMIT_* options
might work, or appear to work, when used with the amalgamation
. But this is not guaranteed. In
general, always compile from canonical sources in order to take advantage
of SQLITE_OMIT_* options.



-- 
- 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] ICU and FTS5

[Tim Uy]

Will the ICU tokenizer work with FTS5, or does some work need to be done to
port it over (slight pun intended).

[sqlite] Multithreaded SQLite

[Simon Slavin]

On 9 Mar 2016, at 5:00am, Philippe Riand  wrote:

> 1- Is it safe to share a single connection between all these threads 
> (assuming serialized mode)?

In addition to Keith's excellent reply ...

Are you using _prepare(), _step(), _finalize() ?  If so then make sure you 
understand the interaction between two threads which are doing _step() at the 
same time.  Always finalize as soon as you can, and perhaps even put each 
SELECT inside a "BEGIN EXCLUSIVE" transaction.

Also, you should get no SQLite errors with concurrent access.  None.  Even if 
you manage to get rid of 99% of the ones you have now, if you have any at all 
you still have a fault in your code or setup.  And that can come back to bite 
you when your software is deployed.

A good debugging tactic here is to remove from your code everything designed to 
speed up or optimize SQLite.  This includes but is not limited to PRAGMAs. This 
often banishes errors and gives you a stable platform to test.  If it's fast 
enough, ship it.  "Fast enough" means "optimization is a waste of my time".

Simon.

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Igor Tandetnik]

On 3/9/2016 10:39 AM, Jean-Christophe Deschamps wrote:
> Of course I use correlated subqueries a lot, but never had to re-select
> a column from the enclosing query. Indeed, I'd say that most of the time
> one uses columns from enclosing query as expressions, e.g. in comparison
> operators or functions in a where clause.

Things that appear in the list after SELECT are also expressions, no 
different from those that appear in WHERE clause or elsewhere.
-- 
Igor Tandetnik

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Richard Hipp]

On 3/9/16, Richard Hipp  wrote:
>
> The fix is here: https://www.sqlite.org/src/info/1ed6b06ea3c432f9
>

The "Pre-release Snapshot" at https://www.sqlite.org/download.html
contains the fix.  See
https://www.sqlite.org/draft/releaselog/3_12_0.html for a summary of
other changes in the Pre-release Snapshot relative to version 3.11.0.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Igor Tandetnik]

On 3/9/2016 9:58 AM, R Smith wrote:
> On 2016/03/09 4:35 PM, Igor Tandetnik wrote:
>> Yes, but why is that a problem? It is perfectly legal, and often
>> useful, for a subquery to refer to columns from enclosing query.
>> That's what makes it a *correlated* subquery.
>
> True, but the OP's result is still in error.

Yes, I realize that. I was only arguing against the proposition that the 
query is syntactically invalid and should have been rejected on these 
grounds.
-- 
Igor Tandetnik

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Richard Hipp]

On 3/9/16, Clemens Ladisch  wrote:
> In the question
> 
> David Paton asks about a query like this:
>
>   create table a(id integer primary key);
>   create table b(id integer primary key);
>   insert into a values (1), (2);
>   insert into b values (1);
>
>   select id from a where id not in (select a.id from b);
>
> which outputs one result (2), although the expected result would be
> empty.
>

Thanks for the bug report.

The fix is here: https://www.sqlite.org/src/info/1ed6b06ea3c432f9

The problem has been in the code since before SQLite 3.6.1 in 2008.
Since nobody else has bumped into it in all that time, we'll wait to
publish the fix as part of the next scheduled release (3.12.0) which
should occur in 3-4 weeks.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Correlated subquery refers to wrong rowid - bug?

[Igor Tandetnik]

On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote:
> At 15:16 09/03/2016, you wrote:
>>   select id from a where id not in (select a.id from b);
>
> Shouldn't the engine bark on this, like it does on the modified version:
>
> select id from a where id not in (select zzz.id from b);
>
> "no such column zzz.id"

While there is no column named zzz.id, there is in fact a column named 
a.id. The query is syntactically valid.

> As I understand it, there is no more an a.id column in table b.

Yes, but why is that a problem? It is perfectly legal, and often useful, 
for a subquery to refer to columns from enclosing query. That's what 
makes it a *correlated* subquery.
-- 
Igor Tandetnik

[sqlite] sqlite3 3.11 and 3.11.1 fail to build with ...

[Richard Hipp]

On 3/8/16, Michele Dionisio  wrote:
> Hi,
>
> downloading this  sqlite-autoconf-3110100.tar.gz and compiling with
>
> ./configure CFLAGS="-DUSE_PREAD -DSQLITE_OMIT_DEPRECATED
> -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN"


https://www.sqlite.org/src/info/8d4b6b2b519a80f8

In addition, OMIT options are not guaranteed to work from the
amalgamation.  If you really need SQLITE_OMIT_... then you should
build from canonical sources.

>
> fail with the following error:
>
> sqlite3.c:16183:28: warning: 'sqlite3OpcodeName' used but never defined
> [enabled by default]
>   SQLITE_PRIVATE const char *sqlite3OpcodeName(int);
>  ^
> mv -f .deps/sqlite3-sqlite3.Tpo .deps/sqlite3-sqlite3.Po
> /bin/sh ./libtool  --tag=CC   --mode=link gcc -D_REENTRANT=1
> -DSQLITE_THREADSAFE=1-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE
> -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DUSE_PREAD -DSQLITE_OMIT_DEPRECATED
> -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN
> -o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o  -ldl -lpthread
> libtool: link: gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1
> -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE
> -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DUSE_PREAD -DSQLITE_OMIT_DEPRECATED
> -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN
> -o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o  -ldl -lpthread
> sqlite3-sqlite3.o: In function `displayComment':
> sqlite3.c:(.text+0x31821): undefined reference to `sqlite3OpcodeName'
> sqlite3-sqlite3.o: In function `codeAllEqualityTerms':
> sqlite3.c:(.text+0x83370): undefined reference to `explainIndexColumnName'
>
> everything works without -DSQLITE_OMIT_EXPLAIN
>
> regards
>
> Michele Dionisio
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Creating & Dropping memory databases

[Dan Kennedy]

On 03/09/2016 12:48 AM, Simon Slavin wrote:
> On 8 Mar 2016, at 4:35pm, Dan Kennedy  wrote:
>
>> I don't think it does that. sqlite3_shutdown() is for embedded platforms to 
>> shutdown sub-systems initialized by sqlite3_initialize(). Calling it with 
>> open connections will usually either leak resources or crash.
>>
>> More here: https://www.sqlite.org/c3ref/initialize.html
> You're right:
>
> "All open database connections must be closed and all other SQLite resources 
> must be deallocated prior to invoking sqlite3_shutdown()."
>
> Hmm.  So there's no neat way to shut down SQLite if you've lost track of your 
> context.  Is there a way of asking SQLite for a list of connections ?

I don't think there is.

Dan.

[sqlite] Multithreaded SQLite

[Philippe Riand]

I?m a bit lost with the multi-threaded concurrent access errors I?m getting, 
and looking for an advise on the best solution.
Basically, I have a desktop/mobile app (single user) that embeds a tiny local 
http server. The UI is done through an embedded browser, calling the server for 
pages and data. And this browser component can submit multiple requests, which 
will be processed simultaneously on the server, by different threads. Each of 
these requests can update the same SQLIte database, and this is were the 
troubles start. 
1- Is it safe to share a single connection between all these threads (assuming 
serialized mode)? So far it seems to work, but what happens if a thread begins 
a transaction by calling ?BEGIN TRANSACTION"? Is this thread safe (the 
transaction bound to this thread), or will the statements from the other 
threads be mixed up in that transaction? Are there other known limitations, for 
example how does sqlite3_last_insert_row_id() behave?
2- If I need to create one connection per thread, what are then the best 
options to set on the connection? I tried many ones (journal mode=WAL, 
busy_timeout, ?) but I?m getting errors like database is locked or even schema 
is locked.
3- I tried to enable the shared cache, but I?m still getting database is locked 
(262). According to the doc, sqlite3_busy_handler does not help here. Is there 
a way to not get the error but simple have the thread wait until the lock is 
freed?

Or is there a better way to get this implemented using SQLite, beyond 
synchronizing my threads and making sure that only one is executing DB code at 
a time? I?m currently using 3.9.1, but can move to the latest version if it 
helps.