Re: [sqlite] Why no persistent user settings in a database file?

[R Smith]

Hi Tom,

The settings you mention, such as .headers and .mode (along with a slew 
of others) are usability settings contained in the Command-Line facility 
(the CLI) offered by the makers of SQLite.


It is however by far not the only such interface, nor is it in any way 
related to the data in the database file.  You could use any other CLI 
or GUI for sqlite which all sport their own settings - none of which 
should be in the database file because it is not pertinent to other 
users/uses of the DB file.


That's not to say you can't use and sqlite3 database file to house such 
settings, even pass it around as an application file format  (both of 
which are often the case) but it is up to the calling entity (program) 
to decide the meaning of its data, the database layer itself should 
never be involved with that - it should be fully agnostic to the Data 
and 100% transparent.


About the CLI - It's a very useful piece of toolkit, but it's intent is 
more to be absolutely correct, well tested and provide a way to test 
sqlite queries etc. in a controlled environment uncontaminated by 
another user-program, GUI-layer or such.


For more usability, there are a few good options in the World from CLI's 
to GUI's. I see you already know SQLite studio, some of my favourites 
you might try are:


- DB Browser for SQLite: https://github.com/sqlitebrowser/sqlitebrowser
Powerful and Free SQLite GUI that runs very well on Linux, MacOS, 
Windows and probably wherever else you fancy seeing the inside of an 
SQLite file.


- SQLitespeed: https://sqlitespeed.com/
Made for user-rich and speedy features plus a nice tool to tell you if 
your schema suffers from common mistakes made in SQLite - like 
double-quoted strings, wrongful Type definitions, spelling mistakes etc. 
It's only Windows exe though, but I've been told it runs perfectly fine 
in WINE.


- SQLiteExpert: http://www.sqliteexpert.com/
Great Windows GUI with a visual query builder (which helps a lot if you 
are not an expert yet at forming SQL queries) and the personal version 
is free.


Both of these Windows ones come with the DLL available on the sqlite 
downloads, BUT let's you roll your own and drop-in replace the DLL to 
run in your dev environment with your specific sqlite build.



There are obviously more tools out there, but between these three and 
the CLI we usually get our game on. We also typically use more than one 
tool for their different strengths - which is another reminder why you 
do not wish to have any specific tool's settings (or heaven forbid, all 
of them) in your database file.



Good luck!
Ryan


On 2019/04/03 12:52 PM, Tom Browder wrote:

After coming back to SQLite from a long absence, I was surprised that
setting things like .mode and .headers in a database didn't stay that way
after exiting the file.

Then I remembered something about a resource file and found .sqliterc on an
internet search and that allowed the persistent settings I wanted.

However, would it not be more natural to keep those settings persistent
inside the db file?

Thanks,

-Tom
___
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] Feature request, sqlite3_stmt_action

[R Smith]

On 2019/03/28 9:07 AM, Olivier Mascia wrote:

Le 27 mars 2019 à 18:04, siscia  a écrit :

I would like to propose a function (named `sqlite3_stmt_action` for the sake
of discussion) that allow to understand if a specific statement is either a
SELECT, UPDATE, DELETE or INSERT.

There is probably a much more complex need that I did not grasp reading this 
request. What stops you from parsing the beginning of the statement text to 
decide if it is a select, update, delete or insert?



Having done this already, allow me to offer some recounting of the 
difficulties:


First there are typically two things a programmer is interested in 
(well, if you maintain an SQLite management utility or the like):
-  First: Will the Query produce data output back that I need to show to 
the user?, or will it silently execute?
-  If it does produce output, is this confirming the state (such as when 
calling a pragma command), or is this output that I need to show the 
user, or perhaps log?


-  Then: Will the query change the database?
-  Put another way, will it work on a read-only file?
-  or, will it alter the table content that is currently displayed? Do I 
need to re-run the display query?

-  or will it change the schema?
-  Do I need to re-parse the schema to show the user the DB layout after 
executing?


Some of these SQLite does cater for, but many not, and there are some 
work-aroundy ways of accomplishing it.


For instance, you might reparse the schema after ANY non-select query. 
But then - how do I know if it is anything other than a SELECT query?


The obvious answer is not to see if it's any of INSERT, UPDATE, CREATE, 
etc... but to simply see if it is indeed a SELECT query. Right?


But then, what about CREATE TABLE t AS SELECT a,b,c, FROM.

Or if it is a CTE, consider these two:

WITH X(z) AS (SELECT 1) SELECT z FROM X;

vs.

WITH X(z) AS (SELECT 1) INSERT INTO t(z) SELECT z FROM X;

These are already difficult to self-parse, and they are extremely simple 
examples.


I would even be happy to have something like extending the EXPLAIN QUERY 
PLAN sql interface to include something like:

EXPLAIN QUERY RESULT  ;

which outputs a simple row of values that can tell me:

- This query produces results - YES/NO (even if those results may be 
empty, is it the /intent/ of the query to produce results?),

- It updates the data - YES/NO,
- It updates the schema - YES/NO
- It is a pragma or setting adjustment - YES/NO

Maybe even, if possible, This query updates these tables: x1, x2, x3... 
etc. (some of which might hide behind an FK relation or Trigger)  but I 
know this is pushing my luck.  :)


Plus, I really do not mind if this explain takes some time, it will be 
faster and more future-proof than any self-parsing one can do.



Cheers,
Ryan


___
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] What is the most commonly used file extension for sqlite3 database files?

[R Smith]

On 2019/03/23 6:06 PM, Peng Yu wrote:

Hi,

I see a variety of extensions for sqlite3 database files, such as .db,
.db3, .sqlite3 and .sq3. Is there a most commonly used/accepted
convention for the extension? Thanks.



It's a convention-less thing, as others have mentioned. However, I can 
answer the most-used question as far as Windows environments go:


The sqlite tool I maintain has a nifty little function that can search 
any folder on a drive (including subfolders) and list all the SQLite 
files it finds, so if I point it in Windows to the current user's root 
and start a search, all the sqlite files pop out, and I do this fairly 
often on random computers as a source of amusement.


Due to the above, I have seen a LOT of sqlite files in my life used by a 
lot of applications, and I can tell you that the vast majority of them 
are all .db files. Skype uses it, Adobe has it, most downloadable DB's 
use it (chinook.db for instance). I have seen others: .sqlite and 
.sqlitedb are favourites too. Sometimes there are obscure ones, like the 
downloadable Geo-names file being a .qmv file (no idea why).


Still, the majority are .db files.


HTH,
Ryan


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

Re: [sqlite] Equiv stmts, different explain plans

[R Smith]

On 2019/03/05 4:06 AM, kk wrote:

On 05/03/2019 01:33, Richard Hipp wrote:



create table t1(c,d);
create table t2(c,d);
explain select * from t1
    where c=1 and d in (select d from t2 where c=1);
explain select * from t1
    where c=1 and d in (select d from t2 where t2.c=t1.c);



DRH, many thanks for your reply, I was expecting same output because I 
believe stmts to be equivalent//...


They are very much not equivalent. They happen to produce the same 
output with this very specific crafted schema and queries, but that does 
not say that they mean the same thing, in fact they mean very different 
things in execution. I think Keith explained it well enough technically, 
but in case it is not 100% clear yet, let me add to it this example:


Say we have a group of random people, and I asked you to separate out 
all the people aged above 25, and then from that group separate out all 
the women, and then from that group separate all who have 
husbands/partners in the original group.


The next day, with the same group of beings, I might ask to first 
separate out all partnered pairs from the group, then from that group 
separate out all females and from that remainder, get everything that's 
been on Earth more than 25 years.


You might rightfully protest that, in the end, we would have the exact 
same people we've already picked out yesterday, and it would be true - 
however, the intermediate groups along the execution plan look very 
different, and the method you've used to achieve this second result 
follows a very different set of instructions, and, if the origin group 
allowed non-humans in, the second query may actually yield different 
results.


They are not equivalent in function just because they happen to yield 
the same end-results for the specific schema and content.



Hope that is a useful clarification!

Ryan


___
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] Incorrect result from INNER JOIN when column has a value of "WHITE"

[R Smith]

On 2019/03/22 5:30 PM, Richard Hipp wrote:


More recent versions of SQLite do issue a warning on the sqlite3_log
interface if you use a double-quoted string literal.  But not many
people look at warnings, it turns out.



I don't see these in my logs from the standard sqlite3_log interface 
using standard downloadable DLL from the sqlite.org site of version 
3.25.1 - is this addition even more recent or do I need to compile 
something in or such?


Or perhaps I'm not understanding what specific use case would trigger 
the log?



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

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[R Smith]

On 2019/03/21 2:31 AM, Steve Horvath wrote:

I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.

Of the four queries below, the second query should have returned the same
result set as the first query. As a side note, I also tried creating the
tables with no primary keys and got the same results. I also tried using
the "ON" syntax and got the same results.



"WHITE" refers to the column named "WHITE" while 'WHITE' would refer to 
the string of data 'White'. SQLite is very forgiving (for historical 
reasons) of mistakes like that so that if you pose the object id "WHTE" 
to the query planner and it doesn't find a column named "WHTE" it 
assumes you probably meant a string like 'whte', which is why your 
second example works.


We've been lobbying for an SQLite "Strict" mode which would never allow 
such shenanigans, but it's probably a rather huge undertaking.




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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[R Smith]

On 2019/02/18 11:24 AM, Clemens Ladisch wrote:

Rocky Ji wrote:

But everyone advices against nested select statements.

Who?

I've heard rumors that older version of the Oracle query optimizer did
worse with subqueries than with joins, but such advice is not necessarily
correct for SQLite.


+1

Whomever said to avoid nested or sub queries are lying to you - it's 
like saying "Use only left turns when driving, avoid right turns" - it's 
just silly, they both help to get you there.


That said, when you can achieve a result using only an outer query or a 
join that may utilize an index, then sure, you should prefer that over a 
sub query for efficiency purposes, but that in no way means to "avoid" 
them completely. They are part and parcel of SQL and very much optimized 
for (in modern DB engines) and often work faster and better.


Your advisors may have meant a specific older engine that had known 
issues with sub-selects or such.


I see you telling Keith that the schema wording doesn't matter and that 
the question is hypothetical, but have you actually run the schema you 
made? It doesn't work because, as Keith pointed out, those are invalid 
foreign keys. (It only runs if you set PRAGMA foreign_keys = 0; but that 
negates the purpose, it should be on).


Remake the schema, use ABCD if that suits you better, but at least make 
a schema that works, and restate the question. That way we can run the 
schema on our side, compose the queries that would answer your question 
without us having to spend half an hour first rewriting the schema into 
a working one (which then may well destroy the premise of your question).


Cheers,
Ryan

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

Re: [sqlite] Reading a table from inside a scalar function

[R Smith]

On 2019/02/18 12:06 PM, Arun - Siara Logics (cc) wrote:

Yes it is possible, but then your UDF is unlikely to be *Deterministic*.

By design, I intend to make sure that for any given input the function always 
returns the same value.
If the dependent rows are missing or change over time, then it would be an 
error condition.


What Dominique referred to is not so much the your intent, but an actual 
specification on a UDF (when added) to let the query planner know it is 
Deterministic or not, simply informing the QP that: "If you have 
computed me once for f(x) --> y then for every next same value of x you 
will always receive the same value of y, so that you can assume the 
output and need not call me again."


This is undesirable if the function needs to log something, for 
instance, or if the value of f(x) --> y over time can change for the 
same values of x. Non-deterministic functions are obviously slower in 
large queries, but there is nothing wrong with it, you CAN do it - 
Dominic simply pointed out that what you wish to do implies 
non-determinism (though you can force it otherwise if you can guarantee 
conformance and the consequences are acceptable, as it would seem from 
your explanation).




Is there an API to read a table row using RowID?  Or should I traverse the 
BTree pages using my own code? Thanks!


You should use a query and the standard API. This is pretty 
straight-forward and conforms to the most basic use of the SQLite API 
(no real need for examples). It probably goes without saying, but you 
need to obviously get and release all used API resources within your 
UDF, regardless of error conditions.


Note that in a transaction without WAL mode (and enabled/disabled 
READ_UNCOMMITTED) your query may or may not "see" data that is older, at 
least from before any current transaction(s) - which can be made to play 
exactly into your hand by controlling said journal mode. The question 
you need to answer is - will you be controlling the Journal mode?



Cheers!
Ryan

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

Re: [sqlite] Reading a table from inside a scalar function

[R Smith]

On 2019/02/18 1:02 PM, Dominique Devienne wrote:

On Mon, Feb 18, 2019 at 11:48 AM R Smith  wrote:


Note that in a transaction without WAL mode ... your query may or may not
"see" data that is older,


Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter.
If you are inside a transaction, you are inside it. And will see the
current rows for that TX,
whether you are inside the outer query, or the "inner" one (inside the
UDF). --DD


Thank you for saying Dominique, this is important. I neglected to state 
that it is inside another transaction, not it's own transaction.


To be clear, and to check if I do not have it wrong perhaps, let me ask 
here: If I start a new SELECT query (whether inside a UDF or not), while 
another transaction is going on in a DB with WAL and read_uncommitted 
set - can I or can I not "see" data changed by that transaction but not 
yet committed?


If not, I apologize, and is very interested in where then 
"read_uncommitted" becomes useful?



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

Re: [sqlite] Choosing one value from the group with priority

[R Smith]

On 2019/02/12 7:29 PM, Ivan Krylov wrote:
I can supply a list of source_ids in order of decreasing priority from 
an array in my application, though I lose cross-database portability 
(is there a portable way to pass an array to a parameterized query, 
though?)... 


There is no fully portable way to pass a parameterized array to a query, 
though nearly every RDBMS engine has /some/ way of doing it.


You could however construct query text to achieve the same, for example: 
Imagine a query that lists some values from some tables, and needs to 
match against the first bit of the Fibonacci sequence, which is simply 
an array of say [1,1,2,3,5,8,13,21], so if you join a subquery using a 
value constructed from the array of the form:

"JOIN (SELECT n1 AS y [UNION ALL SELECT n2] ... [UNION ALL SELECT nf]) AS s"
where s is the alias for the joined query and y is the alias for the 
array value.


SQL example:

SELECT x, ...
  FROM ...
  JOIN (SELECT 1 AS Par UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 13 UNION 
ALL SELECT 21) AS Fib

 WHERE x = Fib.Par


Note that there are far more efficient ways of doing this in most 
engines, such as using the VALUES () construct, but they all differ from 
engine to engine, whereas the above way is fully portable between all 
engines.


Note also that another fully portable and better method would be to 
construct first a TEMP table populated with the array values, and then 
simply join against that - but it does require an extra query, however, 
if you have any chance of re-using the query or using the same array 
more than once in other queries, then the TEMP table becomes exceedingly 
more efficient.



Cheers,
Ryan

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

Re: [sqlite] Tips for index creation

[R Smith]

On 2019/02/13 2:39 PM, Thomas Kurz wrote:

Hello,

I apologize right at the beginning, because this is a real noob question. But I 
don't have much experience with performance optimization and indexes, so I'm 
hoping for some useful hints what indexes to create.

I have queries like this:

SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 IS 
NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

Do I have to create three indexes, one for each column id1, id2, id3? Or is it 
better or even necessary to create only one index covering all three id columns?


The quickest will be a covering Index on a, b and c (in that specific 
order).




Do I need indexes for the parameter and value columns as well, and under which 
circumstances? (Only if I want to use a SELECT ... WHERE parameter = xy?)


Depends, the covering index on a, b, c, parameter, value will be very 
fast although somewhat larger (more space needed), but if the table only 
really contains those columns, the speed advantage will be negligible. 
However, if the table contains many columns, let's say 40+ columns, or 
perhaps other columns of which some of the values are rather wide (long 
strings, blobs, etc.) then singling out the 5 mentioned columns into a 
covering Index will definitely have a significant advantage (especially 
when row-count grows large).


A covering Index is essentially a copy of the table singling out the 
pertinent data, using up extra space but gaining faster access as a 
result.  The decision is always weighed on space cost vs. speed gain, 
and sometimes it's hard to find a definite answer without testing on a 
DB with enough real-world data.


So I guess the answer is: Try it, if it works better, keep it!

Cheers,
Ryan

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

Re: [sqlite] Choosing one value from the group with priority

[R Smith]

On 2019/02/12 1:13 PM, Ivan Krylov wrote:

Hi!

I have a table of some values obtained from different sources:

create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

//...//
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

which results in the following query plan:

QUERY PLAN
|--CO-ROUTINE 1
|  |--CO-ROUTINE 3
|  |  |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
|  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1

Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?



The Window function solution is good, but it can be done easily without 
it, resulting in a bit more efficient query.
Here is the query set recreated showing first your previous stated query 
and then an alternate. The semantics are made more legible using a CTE 
here, but can be done without it. The essential difference is that it 
uses a sub-query to obtain the minimum priority as opposed to 
introducing a sub-table sort.


  -- SQLite version 3.25.1  [ Release: 2018-09-18 ]  on SQLitespeed 
version 2.1.1.37.
  -- 



create table test (
id, source_id, value,
primary key(id, source_id)
);

insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

select * from test;

  --  id | source_id | value
  -- --- | - | 
  --  1  | 1 |  11
  --  1  | 2 |  12
  --  1  | 3 |  13
  --  2  | 1 |  21
  --  3  | 2 |  32


-- Original proposed Query:
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

  --  id  | value
  --  | 
  --   1  |  13
  --   2  |  21

EXPLAIN QUERY PLAN
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

  --  id  | parent | notused | detail
  --  | -- | --- | 


  --   2  |0   |0| CO-ROUTINE 1
  --   6  |2   |0| CO-ROUTINE 3
  --  10  |6   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)
  --  42  |6   |0| USE TEMP B-TREE FOR RIGHT PART 
OF ORDER BY

  --  58  |2   |0| SCAN SUBQUERY 3
  --  95  |0   |0| SCAN SUBQUERY 1



-- New proposed Query
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
  FROM test
)
SELECT id, value
  FROM AV AS AV1
 WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV 
AS AV2 WHERE AV2.id = AV1.id)

;

  --  id  | value
  --  | 
  --   1  |  13
  --   2  |  21


EXPLAIN QUERY PLAN
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
  FROM test
)
SELECT id, value
  FROM AV AS AV1
 WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV 
AS AV2 WHERE AV2.id = AV1.id)

;

  --  id  | parent | notused | detail
  --  | -- | --- | 

  --   3  |0   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)

  --  21  |0   |0| CORRELATED SCALAR SUBQUERY
  --  27  |   21   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)




  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.078s
  -- Total Script Query Time: 0d 00h 00m and 
00.016s

  -- Total Database Rows Changed: 5
  -- Total Virtual-Machine Steps: 600
  -- Last executed Item Index:7
  -- Last Script Error:
  -- 



  -- 2019-02-12 13:48:56.260  |  [Success]Script Success.
  -- 2019-02-12 13:48:56.260  |  [Success]Transaction Rolled back.
  -- ---  

Re: [sqlite] Min/Max and skip-scan optimizations

[R Smith]

On 2019/02/05 4:46 PM, Simon Slavin wrote:

On 5 Feb 2019, at 8:59am, Rowan Worth  wrote:



What is stopping sqlite's query planner from taking advantage of the index, 
which it has chosen to use for the query, to also satisfy the ORDER BY?

I suspect that, given the data in the table, the index supplied is not optimal 
for selecting the correct rows from the table.  SQLite may have decided that it 
needs to select on the contents of ts first, then source1.


And to add to this:

An Index is nothing magical and not a save-the-World-from-every-monster 
type of device (as newer DB programmers often think). It's an expensive 
add-on that provides an ordered binary lookup which, given enough bulk, 
will eventually win the efficiency race over the extra computation it 
adds. (The more bulk, the more win).
(Some DB programmers, when they see the words "table scan" in any Query 
plan, immediately feel as if they have somehow failed to correctly 
optimize the query. This is silly - a table scan is often the most 
optimal solution).


Add to that the fact that an SQLite TABLE is, in and of itself, nothing 
less than a covering Index with row_id as a key (or a custom key for 
WITHOUT ROWID tables), and as such it is a rather good Index and a 
mostly preferred Index by the query planner (because "using" any other 
index adds cycles plus an extra row_id lookup). Due to this, scanning 
the table is often more efficient than threading a lookup via another 
index into the query plan. Sometimes crafting a new temp BTree Index for 
(a) specific field(s) on a materialized set of data might also be judged 
faster than re-establishing links between said data and its original Index.


The method by which the query planner decides which other Index (if any) 
should be used involves a bit of game theory, typically looking at some 
ANALYZE result data along with with some tried and tested weights in the 
decision tree (which I'm not going into since A - It's not important, 
and B - I don't know enough of how SQLite does it). If the end score 
finds that there is no remarkable advantage to using a separate index, 
then it WILL opt to use the more-efficient table scan.


It might be that the adding of the "ORDER BY" simply pushes one such 
decision weight over the edge in this use case, and, once the table data 
evolved to be more complex or hefty, it may again turn to the Index.


To add to another poster's comment: Do not second-guess the 
Query-planner, leave it to its devices. You may even be able to 
construct a scenario where the specific use case causes the QP to choose 
an execution path that is slightly slower than an alternate one, but if 
it is looked at in the general case, then other similar query scenarios 
might again be faster with that chosen path. Further to this, if you 
construct a weird query now to force a path of execution with some gain, 
you possibly prohibit it from capitalizing on an even better improvement 
that might be inherent to the next SQLite update (possibly thanks to 
your very own report here).


If you can demonstrate a true degradation (one that slows down a 
significant time slice that trespasses on human-perceptible time) for a 
general query, an optimization will surely be considered, but this case, 
unless I've misunderstood the severity, does not seem to warrant that.


[PS: this is not a discouragement, it's great to hear of every possible 
quirk and make other users aware of a possible query scenario that might 
not be optimal - thanks for that, and I'm certain the devs would notice 
this, perhaps even get on fixing it right away, or maybe only keep it in 
the back of their minds for when the next round of query-planner 
refinement happens. I'm simply saying that there is possibly no 
satisfying answer to your question right now - best we can do is: 
"Sometimes the QP correctly evaluates the best path to be one that is 
not obviously best to us, or maybe even worse for a specific case, but 
typically better in the general case".]



Cheers,
Ryan


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

Re: [sqlite] Min/Max and skip-scan optimizations

[R Smith]

On 2019/02/06 12:12 AM, Gerlando Falauto wrote:


The use case involves retaining as much data as the storage can possibly
hold (so a bunch of gigabytes).
I could've just used directories and logfiles instead of abusing a
relational database but I just thought it would more convenient to issue a
query and use a cursor.


There is nothing wrong with using a database to store a list of values, 
as another poster pointed out, [insert high made-up number]% of schemata 
out there are basically just that, but I like Keith's suggestion, since 
you did decide to DB it, why not make it nicely relational too?



the table is often more efficient than threading a lookup via another
index into the query plan. Sometimes crafting a new temp BTree Index for
(a) specific field(s) on a materialized set of data might also be judged
faster than re-establishing links between said data and its original Index.


Do you think restoring the original primary key (instead of ROWID) and
dropping the index would make any difference?


I do think it would make a difference (almost any change would), but I 
am not sure it would make all the difference. I would however suggest, 
at the very least, to test this and see.






I pre-populated the table with a realistic use case scenario and ran
ANALYZE.
I'm not planning on using ANALYZE on the real system -- though I might
indeed pre-populate sqlite_stat1 with typical values as suggested in the
docs.


This is fine. I would ask - did your "test" data include a gigabyte or 
more data? The amount, cardinality and shape of the data are all most 
important for ANALYZE to provide good information.



If you can demonstrate a true degradation //...

Yes, in the worst case, adding the ORDER BY clause (2 vs.1, 4 vs.3) leads
to a perceivable degradation in terms of both seek time (several seconds
vs. milliseconds to get the first row) and occupied disk space.


I must have missed this, apologies, that is certainly a very true 
degradation. Note that the entire query delivery should be taken into 
consideration. The first row can often be delivered near instantaneous 
with following rows taking progressively longer. Very often the time it 
takes to deliver the first row is compensated by the time that is saved 
later along the subsequent rows. The QP takes this into consideration.


A good example is getting a set of data, say 100 rows, sorting it first 
and then just spitting it out from memory. The preparation (aka first 
row delivery) will take time, all the rest will be instant. Contrast 
that with a query that needs no sorting, it might produce rows as it 
scans the table, the first of which might appear instantly (since it's 
at the top of the table and satisfies the WHERE clause), but all the 
next qualifying rows might take a long while to produce depending on 
where they fall within the table. In the end the fully traversed cursor 
may take similar amounts of time.


The QP cannot know before-hand how many "hits" it would encounter, so 
has to use a basic pre-made guide and/or help from the ANALYZE data to 
best guess which route is better - and you can easily construct a 
non-usual set of data for which it will choose wrong every time, and for 
which "fixing" it will negatively affect more common sets of data.




As I already said, my use case *is* quite unusual. Definitely not something
you'd normally use a relational database for.


That does not matter - if the query planner can do better, it should - 
unless of course changing the decision tree will negatively affect 
another more widely used query case. (This is the hard part to establish.)



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

Re: [sqlite] Bug due to left join strength reduction optimization?

[R Smith]

On 2019/02/05 10:13 AM, Dominique Devienne wrote:

On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf  wrote:


sqlite> select ... from tab left join tab as tab2 on 0 ...


Can someone please educate me on this {{ on 0 }} join "condition" ?

I'm not following what the intended meaning is... Must have one, since OP
"expects 1 row with one column containing 0", despite a table with a single
1 row.


"on 0" is similar to "on FALSE" which simply means "don't join 
anything... ever" but since it is a left-join, it will still produce any 
rows forthcoming from the initial FROM table, /IF/ it clears the WHERE 
clause, which in this case it should because the WHERE condition is one 
that should always evaluate to TRUE.


If no rows are left-joined, the joined row-values can never have any 
other value than NULL, so the "tab2.id IS NOT NULL" must by definition 
always be FALSE (aka "0") - Which in turn means the WHERE condition of 
"c = 0" must always evaluate to TRUE, which means the 1 row from the 
base table MUST be output.


@OP:
As a matter of interest - what happens when the aliasing is taken out of 
the loop and the query changes to:


CREATE TABLE tab (id INT);
INSERT INTO tab VALUES (1);
SELECT 1
  FROM tab LEFT JOIN tab AS tab2 ON 0
  WHERE (tab2.id IS NOT NULL) = 0
;

I don't have that broken version currently, so can't test on my side, but I'm 
assuming your example is minimal and it works if anything is changed, which 
means it's likely the fault of the logic that checks the aliased value (unless 
the above query still fails, in which case my assumption is wrong and the above 
is a better test case).


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

Re: [sqlite] Option to control implicit casting

[R Smith]

On 2019/04/09 5:08 AM, Joshua Thomas Wise wrote:

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().



TLDR: We feel your pain, but it's probably not happening...

We have been forever lobbying for a "STRICT" mode in SQLite (search for 
"strict" in the archives, the hits are legion), something that would 
error out if you shove a string into an Integer declared column, not 
accept double-quotes around non-identifiers, force aggregate queries to 
use aggregate functions, start with Foreign_keys enabled... the list 
goes on. Actually the list doesn't go on that much, there's just a few 
things that break the SQL.


The reason this is regarded as broken is that in most cases in SQL 
(SQLite included) the people here, myself included, would advise you 
along the lines of "Do not try to think for the SQL engine, give the 
query and let it do its thing.' - which is the greatest advice in the 
spirit of RDBMS - but in SQLite's case this specific advice cannot 
always be given, because now in some cases it is up to you to 
specifically HAVE to think for the engine and not trust that it will do 
the thing you intended. You have to write extra code (or CHECK 
constraints) to just check that things are what they seem or have they 
morphed into something else? (like in #1 above).


I'm not sure your No.2 is a valid case - if you make a BLOB column and 
then try to read strings or string-functions from it, you should be 
punished for it. The problem is more if you declare a column as TEXT and 
then shove a BLOB in it, that should error out.


Don't get me wrong, we love the duck typing, we love the flexibility, 
and I for one love to be able to do quick data manipulations with the 
relaxed SQL on offer - but sometimes we are doing mission critical 
applications, or embedded things, these systems have no room for error 
and it's hard to always trust SQLite or design a bunch of code to 
second-guess the SQL in SQLite specifically. A STRICT mode would go such 
a long way to make SQLite 100% formidable.


Then again, it's already the most used DB in the World - it's not like 
they're battling for market share because of this little snag, and the 
problems that STRICT mode would solve are rather well-known and 
documented. As Dominique pointed out, the test harness is one of the 
finest in any industry and it would probably need a LOT of additions to 
test such a mode, so it's not a minor effort to implement.


One can still wish... :)

Cheers!
Ryan


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

Re: [sqlite] slow join, fast subselect

[R Smith]

On 2019/04/17 10:55 AM, Poor Yorick wrote:

I've used the following two test queries in a version of sqlite built against a
recent checkout of trunk, and also another recent version of sqlite.  a.ref is
indexed.  The subselect query is faster than the join query -- two orders of
magnitude faster on a larger dataset.  Is sqlite missing some easy optimisation
opportunity here?


select a.rowid
from a join b on a.rowid = b.rowid
where a.ref = $x


select a.rowid
from a,b
where a.ref = $x and a.rowid in (select rowid from b)



These queries have vastly different meanings and as such must have 
vastly different execution plans. I won't go into that because it's 
obvious, so I will just mention the assumptions.


For the query engine to optimize this according to your suggestion (i.e. 
for it to really do the second thing when you've asked for the first 
thing) it has to assume the following:


- 1. You do not need any of the fields from b in any part of the query 
(which IS the case here and can be deduced, but is an extremely small 
likelihood for JOINs in general queries),
- 2. there are no special Collations on either of the columns (again, 
this can be deduced here), and
- 3. it must assume that every b.rowid appears ONLY once in table b (I 
know this can be assumed for rowid's, but is something that cannot be 
easily known for any other field, where it could be true with or without 
a unique constraint).


And, these are only the obvious outsider-viewpoint assumptions, I have 
no insight in what other checks might be needed internally from the QP. 
This means that this optimization carries a cost deficit in the general 
case. i.e. Wasting CPU cycles "looking" for this optimization 
opportunity in the general case, considering the high unlikelihood of 
finding it viable, will slow down many more queries than it will help.


That said, this is the very kind of thing where we as engineering 
programmers or database queryers could optimize by asking our questions 
right, mind you - I am not advocating thinking for the Query Planner, 
but do state your question optimally. If you really want to know how 
many rabbits have been eaten by foxes, do not ask for a list of all 
rabbit names alphabetically and exclude every one that was NOT eaten by 
a fox, simply ask for the count of rabbits where eaten by fox = true.


In your above example you really wish to know all the a's which have an 
entry in b. The first query asks to join and list all b's found for 
every a (which works mathematically in this case by virtue of rowid 
uniqueness, but isn't the real question and forces a lot of "join" 
algorithm checking on the QP), the correct question is the second query: 
Show every a which can also be found in b. It releases the QP of a lot 
of responsibility and let's it follow a plan that is much faster.



Hope that makes sense :)

Ryan


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

Re: [sqlite] Corruption of database when renaming a table

[R Smith]

For a start, do not use single quotes for table names. In SQL, DB object 
identifiers can either be unquoted or contained in double-quotes. SQLite 
specifically even allows backticks or square brackets for compatibility, 
but nobody likes single quotes.


I.e. change this:

drop table 'fs_params_20291_27910';

To this:

drop table "fs_params_20291_27910";


Another thing SQL cares about is the case you use in names, but again SQLite 
allows referring to a table without needing to match the schema case. (Just 
putting this out there to satisfy my internal pedantry).

Lastly, you probably have a trigger on one of these tables you intend to 
rename, but the trigger has internally code referring to another table (or some 
such) causing the error. Could you please send the full schema of your DB?

using the sqlite3 CLI will output it on issuing: .fullschema

Or post a DB on a download site somewhere if it isn't sensitive.


Thanks,
Ryan

On 2019/03/15 3:29 PM, tjerzyko wrote:

I'm having corruption problem with a certain database file. You can download it 
here:
https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
I open it with sqlite3.exe and run the following script:

begin;
alter table fs_params rename to fs_params_20291_27910;
CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
drop table 'fs_params_20291_27910';
alter table file_locks rename to file_locks_27446_24559;
CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME TEXT, 
FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
drop table 'file_locks_27446_24559';
CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
alter table cam_in_file rename to cam_in_file_22705_10035;
end;

It throws the following error:
Error: near line 9: error in trigger T_FILE_BDELETE: no such table: 
main.file_locks_27446_24559

Probably my script is incorrect, but another thing worries me more. I open the 
database again and:

e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
AUDIO_IN_FILE  FS_ATTRIBUTES  REC_RECORDING_MODE
AUDIO_IN_FRAGMENT  FS_PARAMS  REC_WORKING_MODE
CAM_IN_FILEINTEGRITY_TEST STORAGE_BLOCK
CAM_IN_FRAGMENTLOOP_FRAGMENT  T_SCHEDULE
FILE   REC_LATEST_CONNECTION
FILE_LOCKS REC_LIST_TIMESTAMP
sqlite> select * from CAM_IN_FILE;
Error: no such table: CAM_IN_FILE
sqlite>

The table exists, but not quite. The database seems to be corrupted.

When I tried the same procedure on database created with newer SQLite version, 
there were no such problems.

Tomasz Jerzykowski


___
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] SETting a value to a field with multiple conditions

[R Smith]

Table-valued syntax is your friend:

  -- SQLite version 3.27.2  [ Release: 2019-02-25 ]  on SQLitespeed 
version 2.1.2.47.
  -- 



create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);

insert into t (a, b, c, d, e, idate) values
 ('p001', 'a', 1, 'n', 4, '2019-02-11')
,('p002', 'a', 1, 'n', 4, '2019-02-11')
,('p003', 'a', 2, 'n', 4, '2019-02-11')
,('p004', 'a', 2, 'y', 4, '2019-02-11')
,('p005', 'a', 3, 'y', 4, '2019-02-11')
,('p001', 'a', 4, 'n', 4, '2019-02-12')
,('p002', 'a', 5, 'n', 4, '2019-02-12')
,('p003', 'a', 6, 'n', 4, '2019-02-12')
,('p004', 'a', 7, 'y', 4, '2019-02-12')
,('p005', 'a', 8, 'y', 4, '2019-02-12')
,('p001', 'a', 3, 'n', 4, '2019-02-13')
,('p002', 'a', 4, 'n', 4, '2019-02-13')
,('p003', 'a', 5, 'n', 4, '2019-02-13')
,('p004', 'a', 6, 'y', 4, '2019-02-13')
,('p005', 'a', 7, 'y', 4, '2019-02-13')
;

UPDATE t AS tx SET b = 'z' WHERE (a,idate) = (SELECT ty.a,MAX(ty.idate) 
FROM t AS ty WHERE ty.a = tx.a GROUP BY ty.a);


select * from t;


  --   n  | a    |  b  |  c  |  d  |  e  | idate
  --  |  | --- | --- | --- | --- | --
  --   1  | p001 |  a  |  1  |  n  |  4  | 2019-02-11
  --   2  | p002 |  a  |  1  |  n  |  4  | 2019-02-11
  --   3  | p003 |  a  |  2  |  n  |  4  | 2019-02-11
  --   4  | p004 |  a  |  2  |  y  |  4  | 2019-02-11
  --   5  | p005 |  a  |  3  |  y  |  4  | 2019-02-11
  --   6  | p001 |  a  |  4  |  n  |  4  | 2019-02-12
  --   7  | p002 |  a  |  5  |  n  |  4  | 2019-02-12
  --   8  | p003 |  a  |  6  |  n  |  4  | 2019-02-12
  --   9  | p004 |  a  |  7  |  y  |  4  | 2019-02-12
  --  10  | p005 |  a  |  8  |  y  |  4  | 2019-02-12
  --  11  | p001 |  z  |  3  |  n  |  4  | 2019-02-13
  --  12  | p002 |  z  |  4  |  n  |  4  | 2019-02-13
  --  13  | p003 |  z  |  5  |  n  |  4  | 2019-02-13
  --  14  | p004 |  z  |  6  |  y  |  4  | 2019-02-13
  --  15  | p005 |  z  |  7  |  y  |  4  | 2019-02-13

  --    Item Stats:  Item No:   4 Query Size (Chars):  18
  -- Result Columns:    7 Result Rows: 15
  -- VM Work Steps: 141 Rows Modified:   0
  -- Full Query Time:   0d 00h 00m and 00.003s
  -- Query Result:  Success.
  -- 




Cheers,
Ryan


On 2019/05/15 4:22 PM, Jose Isaias Cabrera wrote:

Hi.  I know this has been probably asked before by someone, but imagine the 
following scenario:

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, 
'2019-02-13');

select * from t;

I would like to change field b to 'z' for all records in ('p001', 'p002', 
'p003', 'p004') but to the latest idate.  I know I can do multiple single 
statements, ie,

UPDATE t set b = 'z' WHERE a = 'p001' AND idate = (SELECT max(idate) FROM t 
WHERE a = 'p001');
...
UPDATE t set b = 'z' WHERE a = 'p004' AND idate = (SELECT max(idate) FROM t 
WHERE a = 'p004');

but is there a much nicer way of doing it in one call? I was thinking something 
like,

UPDATE t SET b = 'z' WHERE a IN ('p001', 'p002', 'p003', 'p004') AND idate = 
(SELECT max(idate) WHERE a = ?);

I don't know how to do the last part.  I was trying things like,

UPDATE t as tt set tt.b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 'p004') 
AND tt.idate = (SELECT max(idate) from t where a = tt.a);

This one gives errors out with,

Error: near ".": syntax error

It would be nice to know which . is the problem. :-) Any thoughts?  Thanks.

josé

Re: [sqlite] SETting a value to a field with multiple conditions

[R Smith]

I of course forgot to remove 'p005' from the list (luckily David 
didn't!), so the query should have been:


UPDATE t AS tx SET b = 'z' WHERE (a,idate) = (SELECT ty.a,MAX(ty.idate) 
FROM t AS ty WHERE ty.a = tx.a GROUP BY ty.a) AND a < 'p005';


which works well here (picking the minimal route), but your actual query 
may or may not work better with the IN ('p001', 'p002',...) method of 
checking.



Cheers!
Ryan

On 2019/05/15 4:22 PM, Jose Isaias Cabrera wrote:

Hi.  I know this has been probably asked before by someone, but imagine the 
following scenario//...



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

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

[R Smith]

On 2019/05/13 12:56 AM, Manuel Rigger wrote:

Hi everyone,

It seems that a table created in the temp database cannot have a parent
table that is created in the main database and vice versa:

PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 PRIMARY KEY);
CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFERENCES t0(c0));
INSERT INTO t1(c0) VALUES (1); -- no such table: temp.t0

Is this intended? It somehow seems so, since the parent table can also not
be prefixed by "temp." when declaring the foreign key in the above example.
However, I did not find a note in the documentation that this is not
supported. Would it make sense to describe this in the limitations at
https://www.sqlite.org/foreignkeys.html?



While the "why" of it has been handled by others, the question of "need 
it be documented" still remains, to which I can comment: Documenting the 
fact that FK relationships cannot be maintained across different 
database entities, trespasses on two documentation conventions:


A - Do not document the obvious (i.e. no point documenting the fact that 
your car needs all 4 wheels for correct operation)[1], and
B - Do not document the negative (i.e. Say what you CAN do [short list] 
rather than what you CAN'T [infinite list]) - unless it is an expected 
yet omitted behaviour (such as things that can normally be done in other 
databases, but not in this one)[2].




Cheers,
Ryan


[1] - Rant: There seems to be a senseless move to document exactly such 
silliness for fear of litigation these days, making modern documentation 
more and more a self-indemnification checklist by the manufacturer 
rather than a helpful description of the operation and functionality of 
the item. It used to be that the number 1 source of information about 
the vehicle/device you purchased was its manual (written by Engineers), 
now it's more TLDR; (edited by PR/Legal people) and for real information 
you simply pray there is a youtube video on the issue by another enthusiast.


[2] - I know MSSQL "allows" temp tables to have foreign keys specified, 
but it doesn't enforce the constraint, so it's nothing more than no-op 
syntactic sugar, perhaps in an effort to not have it choke on 
copy-pasted schemata.




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

Re: [sqlite] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

[R Smith]

Hi Bhavesh,

This is a common thing with 3rd party systems and engines (like SQlite) etc.

One day, you made a simple query which did not specify the output 
specifically (or the format, or the order, or such), BUT you liked the 
output as it was on that day, and then possibly made your program expect 
such output, depending on the form.


Later one day, the Engine changed, still giving exactly mathematically 
correct output, but perhaps changing the default format or some other 
thing the documentation either explicitly warned may change, or 
implicitly did not guarantee not to change, and your program now is no 
longer working.


The best advice here would be to tell you how to ask for exactly what 
you want, in a way that the system DOES promise to adhere to.


In your case, you could do:
SELECT printf('%0.12f', Rate) AS Rate FROM Customer;

Or whatever similar output format suits you best. You can then make your 
program depend on it, because you asked for it to be specifically so, 
and the Engine will always give it exactly so, no matter any other 
future changes to default formats.


See also:
https://sqlite.org/lang_corefunc.html#printf



Hope that helps,
Ryan


On 2019/06/06 1:48 PM, Bhavesh Patel wrote:

Hello SQLite Community Members,

I am facing an issue with SQLite latest version(3.28.0).

SQLITE  Return Exponential  value in the latest version(3.28.0) rather than 
integer number .

The old version (3.6.2) return the same integer  value which is inserted .

Below is the screenshots of my sample application which is shown the difference 
beween the sqlite 3.6.2 and sqlite 3.28.0.

Sqlite version: 3.28.0


Sqlite version: 3.6.2

The Return Value is different  in the both SQLite versions.

- I have downloaded latest SQLite version (3.28.0) from the below link:

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

- I have downloaded the SQLite version (3.6.2) from the below link:

https://www.sqlite.org/src/info/88c51b9f1579707a

Below is the step how I build the Latest  SQlite(3.28.0) Lib:-

-I downloaded the Source code “sqlite-amalgamation-328.zip” from the  link: 
https://www.sqlite.org/download.html

- After that, I build the Visual studio 2012 project name “SQLitelib” from the 
downloaded source.(the downloaded source placed in the Project directory.)

- Build the Libarary.

Below is the step how I build the Old  SQlite (3.6.2) Lib:-

-I downloaded the Zip file “SQLite-88c51b9f.zip” from the  link: 
https://www.sqlite.org/src/info/88c51b9f1579707a

- After that,I  make “sqlite3.c” from the downloaded source.

-I build the Visual studio 2012 project name “sqlite362” from the source.(the 
downloaded Zip file and source placed in the Project directory.)

- Build the Libarary.

Below is the step to Sqlite Test Application:-

-I create sample application in visual studio 2012 app name is “SQLiteCheck”.

-I placed the Both SQLite Lib (Sqlite 3.28.0 abd SQLite 3.6.2) placed in 
(ProjectDir)/ Externals folder.

- when  the Application Executes.

- Application creates database named  “Mydb.db”.

- it will create the table with one column by below SQL Query:

“CREATE TABLE customer(Rate NUMERIC(4) COLLATE BINARY);”

-The it will insert value '.439857' by below Query:

“INSERT into customer (Rate) VALUES ('.439857')”

-Then it will get the value by below select Query:

“Select Rate from customer”

-Now, Check for “Return value:” it is different for both SQLite versions.

___
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] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[R Smith]

On 2019/06/13 4:44 PM, Doug Currie wrote:


Except by the rules of IEEE (as I understand them)

-0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"


Except that 0.0 is also an approximation to zero, not "true zero."

Consider that 1/-0.0 is -inf whereas 1/0.0 is +int



I do not know if this is the result case in any of the programming 
languages, but in Mathematical terms that is just not true.


1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error 
out. Anything returning +Inf or -Inf is plain wrong.

I posit the same holds true for 1/-0.0



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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[R Smith]

On 2019/06/12 4:39 PM, Jonathan Brandmeyer wrote:

For an SQL engine, the next-best-thing to strict binary IEEE754 is not
sloppy binary IEEE754, its probably strict decimal IEEE754.


I very much agree on this point, however, unlike the -0.0/+0.0 change, 
changing to strict full IEEE754 conversion (with "-ve" etc.) WILL 
actually break many things due to backward-incompatibility.


I tend to agree with Simon on making a new IEEE754 conversion for when 
the need arise (maybe a sort of CAST target or collation rather than a 
column "Type") and perhaps leaving REAL be (with simply clear docs on 
how SQLIte's REAL works and diverges from IEEE754 in conversion).  
Especially since learning that, although the change is not slower by any 
significant margin, it's certainly not faster.


To surmise the posts so far: If it doesn't give us full IEE754 
conformance, it doesn't shorten the code-base, it's not mathematically 
important, it's not faster, and it might be confusing...  Why change?.


(There may of course be reasons internal to the project or the devs 
which I'm not privy to, the above is simply an opinion based on 
currently publicly known details)


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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[R Smith]

On 2019/06/12 6:04 PM, Simon Slavin wrote:

On 12 Jun 2019, at 4:35pm, R Smith  wrote:


(maybe a sort of CAST target or collation rather than a column "Type")

That's an interesting idea.  With a REAL value you get the same things when you 
print -0.0 and +0.0.  But if you CAST it to a new type of I754 then apply 
printf to that, you could get different text from the two values.



Indeed - although James makes an important point, the -0.0 of IEEE754 
isn't a useful feature, it's an artifact - a happenstance of an imposed 
rule, not an answer to a mathematical concept needing to be 
computerized. The CAST / Collation idea is still useful though for those 
specifically interested in the real IEEE754 bit representation (as 
Jonathan and Keith pointed out), which can even be improved to contain 
the rest of the IEEE754 features as you've mentioned in the 
conversion/text representation.



I now wonder if this is possible to implement as a simple User collation.


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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[R Smith]

On 2019/06/12 3:35 PM, Richard Hipp wrote:

IEEE754 floating point numbers have separate representations for +0.0
and -0.0.  As currently implemented, SQLite always display both
quantities as just "0.0".

Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
would that create unnecessary confusion?



1. Personally: I think all systems I'm involved in will be agnostic to 
this change, and without confusion.


2. Generally: We see many questions on the forum about IEEE754, but 
mostly it's to do with why X == (X-1).999n or why X == 
X.001... etc.
I have seen people ask about the -0.0 vs 0.0 support, but have not seen 
many people express confusion about it, or for the opposite in reply to it.


I will say that the go-to argument of people here (Devs included), when 
confronted about the small epsilon offsets, is: "Because we IEEE754" - a 
valid reason of course, but then it's probably best to adhere to it fully.


That said, it's been working like that flawlessly for years. Continuing 
is not a horrible option.


I'd ask: Is it any faster or slower?  Does changing get rid of unneeded 
code?  Is anything else gained from the change (other than proper 
IEEE754 adherence)?


In my mind these matter more towards the decision than "will it confuse 
some people" (at least with respect to this specific question).



Cheers,
Ryan

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

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

[R Smith]

On 2019/05/13 11:42 AM, Manuel Rigger wrote:

Hi Ryan,

I hope my question did not offend you. I didn't expect that the answer to
this question would be considered to be that obvious.


Goodness, I was not offended and apologies if my reply read in that way 
- it was just an honest account - and - if I were to stoop so low as to 
"take offense" at a question, I would never answer it publicly.


Please know that it is obvious to most Database people (er... what is a 
good word...? shall we say "Afficionados"?), but that in no way means 
that you *should* have known or that I (or most others here) would scoff 
at anyone not knowing this. I once did not know this, and now to me it 
was nothing but an opportunity to teach/relay that which I have been taught.


As to the question, let me see if I could entice your mind to see the 
obviousness with us: Try to imagine how you would program a database 
engine upon which constraints could be placed, the underlying methods or 
values of which may persist in separate files/schemata/tables which may 
all be transacted upon from alternate connections while they are not in 
view, or not accessible to the engine itself, but the engine is still 
expected to uphold the constraints.


I am hoping that after some thinking on the matter, either the 
obviousness would materialize for you, or possibly you will come up with 
a method that could change the face of RDBMS capabilities forever!


I'm hoping for the latter, but will take the former as a second prize. :)


Cheers!

Ryan


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

Re: [sqlite] SQL Features That SQLite Does Not Implement

[R Smith]

How we can anyone document functions that do not exist?, they are 
infinite and many of them (most) are not currently known to humanity. 
Also, if somewhere it said "SQLite cannot do sqr() it would lie - sure 
vanilla sqlite might not, but there are many ways in which it does.


!. Add-ons - there are many great sqlite add-ons (for lack of a better 
word) via externally run-time loadable extensions.

See: https://www.sqlite.org/loadext.html

You can add any of this easily to your sqlite, or if you compile your 
own, even add them to the source code. One great add-on is precisely the 
math library that supports all (and more) of the mentioned functions.


Example extension via code:
From this Page: https://www.sqlite.org/contrib

extension-functions.c 
 
(50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07


   Provide mathematical and string extension functions for SQL queries
   using the loadable extensions mechanism. Math: acos, asin, atan,
   atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos,
   sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign,
   sqrt, square, ceil, floor, pi. String: replicate, charindex,
   leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper,
   padl, padr, padc, strfilter. Aggregate: stdev, variance, mode,
   median, lower_quartile, upper_quartile.


Apart from that, you can add your own functions to sqlite (which may be 
hard if you are working through a wrapper, but real easy if you access 
the library or code directly).

See: https://www.sqlite.org/c3ref/create_function.html


Good luck and Happy SQLite-ing,
Ryan

On 2019/05/22 7:51 PM, sky5w...@gmail.com wrote:

I'm often scrambling to decide whether to do complicated queries in SQL or
my own code?
...Getting oh so close to a working query, only to fail at function not
defined?!!

Please add a note to the omitted page that many basic math functions are
NOT supported. (sqrt,mod,power,stdev,etc.)
https://www.sqlite.org/omitted.html
I am curious why these are not available when there are switches for much
larger search functions like FTS345?

...now reading how to build my own extensions...
Thanks for SQLite!
___
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] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[R Smith]

On 2019/05/23 1:26 PM, J. King wrote:

On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:


This is SQLite. Perhaps some of us could collaborate on a fork called
SQLbloat //..

I find this a little condescending. There's a lot of reasons to like SQLite, and the aspect that 
sways me more than others is not "lite", but "zeroconf".

I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not 
practical, and in the case of PHP, loadable extensions are disabled by default. 
I as a programmer am at the mercy of what distributions package---which is 
often SQLite in its default configuration, so a less-lite-but-still-zeroconf 
SQLessLite as the default configuration would be extremely valuable to me. At 
the same time, those working in tiny systems still have tons of compile-time 
options to keep things lean.

In short, I'm skeptical that the choices Hwaci have made about what to exclude are 
necessarily beyond reproach or discussion. Derogatory references to  "SQLbloat" 
really don't further the cause of honest discussion.


This is a misunderstanding of my intent, much as the OP misunderstood 
that I don't consider a function that could confirm the presence of 
another function to be bloat, I quite welcome that, and I most certainly 
do not think any decision is beyond reproach. The entire bloat argument 
is towards the extent of the libraries included automatically.


And to be clear, I don't think the Math lib would specifically be bloat, 
though it's an unneeded piece of added weight for my needs, but I will 
accept it happily if it makes most other people happy. But then, if we 
start with the math lib, what is next? Sure enough someone will come up 
with a very valid next bit to be added, and a next. I understand that 
Richard's decision on inclusion is not beyond reproach, but whose would be?


Put another way, let's say we do add some libraries automatically, 
pushing the weight up a nice bit, but they are not the libs you wanted, 
would you be happy and consoled? If not, how do you expect anyone else 
to be happy with your choice being the chosen implemented? Would you 
then rather go back to how it was before?


Lastly, it is very easy to add things to the base distro, but extremely 
hard to impossible to ever take it away again, which means one should 
only ever "add" with great caution.


Everybody's needs are different and it is impossible to satisfy all, so 
I maintain that providing the base SQL functions and having the options 
for added functionality relegated to every user's personal choice (with 
multiple ways of achieving it no less) is a good solution.


I do however think that having the function-list pragma in the base 
distro is needed. Understanding dependency shortcomings would then be as 
easy as a quick query, which is especially useful where SQLite is used 
through wrappers.



Cheers,
Ryan

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

Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[R Smith]

Ok, but you can't have it both ways. Either you want to write generic 
use-everywhere SQL, in which case you need to stick to the generic 
included-everywhere functions,


OR

You want to write specialized specific queries that use either your own 
or other UDF's, in which case you must be able to load your own (or 
other's) add-on libraries.


If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which 
returns the function result, unless it errors out, in which case it 
returns Zero, is good programming, then you are making a first-principle 
mistake. Because now you add a layer of complexity to the end-system. 
The result is Zero - Is this an error, or is this actually the result? 
Extra checks needed.


Errors should cause errors so that everyone know it's an error. 
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of 
thinking and a mistake often made by novice GUI programmers who value 
user-experience over truth.


Else what you are asking for is to have your bread buttered on both 
sides, which isn't feasible, and which isn't done in any RDBMS engine 
(though admittedly the big ones do include most general math functions 
as standard, but then they don't need to sometimes run on embedded devices).


This is SQLite. Perhaps some of us could collaborate on a fork called 
SQLbloat and put out standard libs/code/precompileds for versions of 
sqlite with everything - bbq sauce and all, for when you don't need Lite 
- then you can specify that the queries are for SQLbloat and they will 
run out the box directly and correctly.  Linux distro wars coming to an 
SQLite DB near you soon!



Cheers,
Ryan


On 2019/05/23 12:19 PM, Thomas Kurz wrote:

That doesn't make any difference. Then I could use the extensions-functions.c 
loadable module as well. My database has to work equally well no matter what 
dll and/or extension is used or not.

Regardless of my application, the problem stays the same when you open the 
database in any other viewer that doesn't have the extension.




- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Put your function into a loadable extension and load it during application 
startup so that it is always available to your code. This does not require 
checking the sqlite3.dll

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Just write a function that takes a function name, a default value and an 
unspecified number of arguments.

function_present(,[,])

The implementation will then check if the named function is available; If so, prepare, execute and 
return the result of "SELECT ()"
If not, just return .

This is probably going to make your queries run blindingly slow...

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement

Ok, thank you for that hint. But it is still very unconvenient. How can I 
define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

- Original Message -
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
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 Thomas Kurz
Sent: Wednesday, 22 May, 2019 22:19
To: SQLite mailing list
Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
I agree in that not every math function can be included by default.
My problem, however, is that I cannot know whether a user uses my
self-compiled version with 

Re: [sqlite] Does SQLITE ever optimize index creation based on another index?

[R Smith]

On 2019/04/30 2:10 AM, Deon Brewis wrote:

Given the SQL below, FooX is a covered index for x on Foo.

I want to create FooXB as a second index on x in Foo. Since 'x' is covered on 
FooX it should be cheaper to build FooXB from index FooX, than from table Foo. 
However, as far as I can tell from the from the opcodes of the index creation 
it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my understanding 
correct?


Not quite. This is a good example of something that "feels" like it 
should be better, just isn't.


Unless Foo(x) is a partial Index and the new index can somehow indicate 
that it has the same partiality as the original index (which it can't 
unless it's exactly equal, in which case, it's useless), there can be no 
advantage.


Keep in mind that, in SQLite, a table is nothing less than a covering 
Index itself with the row_id as the indexer (or the actual PK in the 
case of WITHOUT ROWID tables). There is no reason why it itself (being 
an Index) should be any slower to "walk" than any other Index, in fact a 
lookup via any other index will include an extra step (the lookup 
itself) that you don't have when walking the table index itself (aka 
doing a "table scan"). It's just better for anything where you access 
any field that are not in the existing index, and not worse for those 
that are.


There might be a small but real advantage if the field (that was indexed 
on) appeared at the end of very long list of fields or very large 
fields, i.e. hidden at the back end of the column list with really large 
(long-to-read) columns preceding it - meaning the existing Index would 
already have singled out that bit of data - but it's a very small 
likelihood and use-case though. (Meaning that it's unlikely for people 
to make multiple Indexes on the same field(s), so investing the effort 
and code-bloat in catering for the optimization it, which would only 
ever benefit it in the case where the column IS at the back of big other 
columns, would be of dubious benefit).




And if my understanding is correct, is there any scenarios in which I can 
coerce SQLITE to build a new index based on data in an existing index?


drop table Foo;
create table Foo(x text, y text, z text);

insert into Foo(x) values("elephant");
insert into Foo(x) values("cat");
insert into Foo(x) values("giraffe");
insert into Foo(x) values("dog");
insert into Foo(x) values("zebra");
insert into Foo(x) values("lion");
insert into Foo(x) values("panther");

create index FooX on Foo(x);
create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';



As an aside - if your INSERTs above was a "find the odd one out" puzzle, 
I vote that the answer would be "panther". :)



Cheers,

Ryan


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

Re: [sqlite] Getting the week of the month from strftime or date functions

[R Smith]

On 2019/05/07 3:07 PM, Jose Isaias Cabrera wrote:

Warren Young, on Monday, May 6, 2019 09:15 PM, wrote...
On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote:

someday, as John Lennon sang, "...the world will live as one." ;-)

Okay, but one *what*?  Serious question.

Yeah, if I have to explain it to you, then you miss the idea. :-) But here is 
something: you, one Warren Young, are composed of billions of living small 
living cells, all working to your well being. You are one. :-)

By the way, there will be a time when time will no longer be, but instead, 
we'll have eternity. :-) My personal believe, of course.


The spectacular width by which Warren's point was missed aside, I did 
chuckle at the irony of quoting a John Lennon song that advocates for no 
Religion with the very next post claiming adherence. :)


If time doesn't exist then, how will you know it is eternity?
(It's rhetorical, no answer required).


PS: It's Trillions of small cells, not Billions - around 38 Trillion in 
a reference 70Kg male, with almost equal numbers (around 30 Trillion 
more) non-human cells.

https://www.smithsonianmag.com/smart-news/there-are-372-trillion-cells-in-your-body-4941473/
https://journals.plos.org/plosbiology/article?id=10.1371/journal.pbio.1002533

PPS: Time is nothing but a measurement of the rate of progression, the 
progression of an irreversible sequence of events or states along a 
continuum. Progression rate is also relative to a frame of reference, 
and thus, so is time. This we can agree on: One day, time/progression 
will cease to be; in your frame of reference; for eternity. :)

https://en.wikipedia.org/wiki/Time


Cheers!
Ryan


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

Re: [sqlite] Custom collation of blobs

[R Smith]

To add to Dominique's suggestion, we use this approach a lot and have 
sort of standardized it internally. Of course 1NF dictates that this is 
not the real RDBMS way, but sometimes you need blobs because you just do.


I'm sure you already have figured out how to do it sans blob collations, 
and the question is only aimed at the collations and not needing 
alternate ideas, but on the off chance - here goes:


What we do is have blobs compressed if needed[1], then translate it to 
string using good old 3 to 4 translation (aka Base64)[2] which is rather 
efficient (much more so than bin --> Hex which is 1 to 2, doubling the 
size), then append an ante to the string "blob" that is a unique Key of 
the Blob which is also what it is sorted by (and a couple more 
characters that say a: whether it is compressed and b: the Blob version 
so that future software changes remain able to read the current blob 
architecture).


From there it's an easy step to make a standardized blob-storage thingy 
with pack/unpack to some structure, JSON, etc.  We even have these as 
UDFs, though I must say, in practice we never really use the UDF version 
since the content of the blob never really comes into play in SQL - if 
it does, we'd rather add real schema columns for that bit of the 
information.[3]


If sorting is all you need from all this, it's rather easy to also 
simply add another column to the table that contains the sort-by key and 
use that column whenever you query-sort it or compare it.


Lastly, you must already be aware, but just in case someone else is 
reading this: If you do have more than one column, place the Blob column 
at the end of the table definition.



Cheers,
Ryan

[1]: We start compressing if Len > 2560 bytes - we just found this 
number through running some research, a different figure might work for 
you. The compression algorithm varies for us between maximizing size 
saving vs. speed, and we can see a saving before 2560 bytes, but it 
seems for the general case, the timing of the total 
Base64.decode(compressed data)+LZ.decompress() starts being sporadically 
faster than just Base64.decompress(non-compressed data) times after 
about 3 thousand bytes, so even though a size saving  starts under 1K, a 
speed saving only starts around 3K+. Also, in case anyone wonders, 
Base64 compresses really badly - there is no point in first doing Base64 
then compressing, it's always a loss.
Mind you, this was long ago, perhaps I will find the code, modernize it 
and run tests again, some things may have changed.


[2]: As an aside, because of the only problem really being the null 
character in strings, we've designed another Base64-like translation 
that in stead of 8-bit to 6-bit (3x8bits --> 4x6bit) does 8bit to 7bit 
(7x8bit to 8x7bit) using a full 128-character palette plus a few control 
characters, which is not hard to find in a 256 character ASCII range 
(well, about 220-ish usable characters) and gives a great 7/8 size ratio 
(as opposed to 3/4) but it breaks UTF8 encoding (because of bit 7) so 
you must never load the stored strings through a UTF8 translation. In 
fact we've decided after some work on this that the size saving from 3/4 
to 7/8 (or put another way: 25% waste down to 12.5% waste) is just not 
worth the complication and non-standard translation. Perhaps one day 
when we have blobs in the 50-megabytes+, but then binding them as actual 
BLOBs and adding a couple of other control columns would be the better 
answer.


[3]: String storage has a hard limit of ~2Gigs in some systems, though 
you should have opted for another way of storage very long before you 
get to this limit.



On 2019/04/27 12:35 PM, Dominique Devienne wrote:

On Fri, Apr 26, 2019 at 7:36 PM Jens Alfke  wrote:


We are using SQLite blobs to store some structured values, and need
control over how they are collated in queries, i.e. memcmp is not the
correct ordering. We’ve registered a custom collating function, but
unfortunately it doesn’t get called. According to the docs, collating
functions are only used to compare strings, not any other data type. Is
there any way around this limitation?

The only workaround I can think of is to define a custom function that
converts a blob to a string and collate using those strings — e.g. `… ORDER
BY collatable_blob(b)`. But this requires expensive string conversions, and
it doesn’t work well with indexes.

It would be much cleaner and more efficient if there were a type of
collating function that operated on all data types.


You are hitting what is IMHO one of the last big remaining "hole" in
SQLite, which is its lack of User-Defined Types (UDTs) for persistent
columns.
(for transient values in function call chains, we have subtypes nowadays,
which is as close as a UDT as SQLite as).

Your blob obviously has internal structure, and while you're happy to store
it as an opaque blob, you'd like to have it sort differently.
You can of course work-around having a real UDT by 

Re: [sqlite] Limiting the result set size at each recursive step in a CTE

[R Smith]

On 2019/05/07 7:57 PM, Thomas Zimmermann wrote:

Hi!

Sometimes it is desirable to limit the size of the queue¹ in a 
recursive CTE//...



CREATE TABLE comment (
    comment_id INTEGER PRIMARY KEY,
    parent_comment_id INTEGER REFERENCES comment (comment_id),
    created_at INTEGER NOT NULL -- timestamp, bigger means newer
);
CREATE INDEX comment_hierarchy ON comment (parent_comment_id, 
created_at DESC);


WITH RECURSIVE //...


I would be very interested in a general solution that still allows for 
the adjacency list design,

but I'm open to denormalization. :)



It's tricky, but there is a solution now that Window-functions have 
joined the fray.


Essentially what we need is to first extract from the table a list of 
all the sub comments by parent comment, but while the query order is not 
helpful, we can partition by the parent-comment-ID's and number the rows 
(which we CAN apply a sort-order to thanks to the Window function 
methodology), so the first CTE does just that.


The next step is to list the origin rows (which have parent_id = NULL) 
and then amend to them every sub-comment belonging to them, but only 
where the created row-order ID is less than 100 (or whatever value you 
pick).


Lastly, we only use full sorting order in the very final query (outside 
the CTE's) according to main comment dates and sub-comment id's, which 
would make things fastest. This solution will work universally for all 
similar types of tree-queries.


While this compiles/runs, I have not been able to test this with data 
because you gave no data and I was too lazy to make up data, but I'm 
pretty sure it would work. If it doesn't, please send some data and 
expected outcome, then we can fix it.


WITH RECURSIVE
    comments_by_parent(subid, comment_id, parent_comment_id, 
created_at) AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY c.parent_comment_id 
ORDER BY c.created_at DESC) AS subid,

   c.comment_id, c.parent_comment_id, c.created_at
  FROM comment AS c
 WHERE c.parent_comment_id IS NOT NULL
),  sorted_comment(comment_id, created_at, sub_comment_id, 
sub_created_at, depth, idx) AS (

    SELECT comment_id, created_at, NULL, NULL, 0, 0
    FROM (SELECT comment_id, created_at
        FROM comment
       WHERE parent_comment_id IS NULL
       ORDER BY created_at DESC
       LIMIT 100
         )
    UNION ALL
    SELECT sc.comment_id, sc.created_at, cp.comment_id, 
cp.created_at, sc.depth + 1, cp.subid

  FROM sorted_comment AS sc
  JOIN comments_by_parent AS cp ON cp.parent_comment_id = 
sc.comment_id

 WHERE cp.subid < 100
)
SELECT comment_id, created_at, sub_comment_id, sub_created_at, depth, idx
  FROM sorted_comment
 ORDER BY created_at, comment_id, idx
;


Good luck!
Ryan




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

Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

[R Smith]

On 2019/06/23 8:14 AM, Ben Earhart wrote:

...that the person(s) that has no problem writing small, but solid, walls
of technical detail and drawing intricate circularly recursive syntax
diagrams which require multiple levels of detail to coherently represent,
can't be bothered to write example sql code for the top dozen things that
most surely cover better than the majority of real-world uses cases.



As Shawn pointed out, diagrams are scripted, but more importantly - 3 
things to consider:


A - SQLite is not SQL. SQLite provides an Engine to deal with SQL, but 
SQL is a thing unto itself and there are countless training, educational 
and example content on the net (see below for examples). SQLite often 
changes/updates to better follow the SQL standard, or rather, newer SQL 
technology. It would both be presumptuous and unsafe for SQLite to 
trespass on the education of SQL itself. (Even maintaining that would be 
a job for many).  A calculator manufacturer can show you how to enter a 
math function, but it's not in the business of teaching you how to do 
math, not because they don't want to, just because it isn't their 
authority and they shouldn't trespass on the domain of educational 
institutions.


B - The premise that "...most surely cover the majority of real-world 
use cases..." is just not true. SQL use cases and Query statements are 
literally infinite. For every query that was ever written on Earth, 
ten-fold more will be written in the near future, and infinitely more 
before the Sun explodes. What's the top 1% of infinity?  For SQLite to 
give examples of real-world use cases (as opposed to merely 
demonstrating internal specific methodology) would be like a car 
manufacturer (say Ford) suggesting driving routes to your nearest shop. 
There are many viable routes, it is not their domain and they would by 
no means be a better authority on it than a map book or google maps, or 
indeed your own knowledge of your area.


C - There are expert advisors who do this already, for whom it is their 
main function and they have supportive communities and the like. On this 
very forum, you can ask any SQL question and I promise someone will come 
up with great SQL to achieve just that. In fact, likely 5 someones will 
come up with different ways of achieving it in SQL.  The 
premise that SQL examples are hard to find seems perhaps more a result 
of  google's algorithm becoming an advertising monstrosity rather than a 
good search engine. 




Does anybody here know where such a thing might exist? I think sqlite3 is a
gem but, for whatever reason, I have had poor luck getting sqlite3 sql code
examples - just scraps here and there. I don't use it near as much as I
could simply because of difficulty getting examples for a few basic schema
patterns.


May I suggest one of my favourites: W3-Schools 
(https://www.w3schools.com/sql/default.asp) which has excellent basic 
tutorials on getting things done (more than just the dozen top real 
world examples).


Another, which is more SQLIte-oriented if you prefer, is: 
http://www.sqlitetutorial.net/


Also, from our own side, if you have a Windows machine or WINE 
somewhere, you can try the free sqlitespeed (https://sqlitespeed.com/) 
and during install tick the "Include example SQL" (I forget the exact 
caption, but that's the gist of it) which includes a whole lot of 
example scripts mostly gathered from this forum, but perhaps geared more 
towards doing creative things with SQLite specifically rather than 
general-purpose SQL such as W3-Schools would do.





Thanks,
Ben



Best of SQL luck mate!
Ryan


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

Re: [sqlite] slow join, fast subselect

[R Smith]

On 2019/04/17 1:23 PM, Poor Yorick wrote:


That's an apt and accessible description of the issue, but at the denotational
level the meanings of the queries are in fact identical under the conditions
you enumerated.  Ideally sqlite would notice and adjust its query plan
accordingly.


Ideally yes, but the heart of the matter, I would like to reiterate, is 
that the query in question has to be just so for the optimization to 
work, anything you change (using b.anything anywhere, having duplicate b 
values or in any way not using rowids or at least unique columns of some 
flavour, using a collation, etc.) would render the optimization 
opportunity void. The opportunity hangs by the tiniest of threads, or 
put another way: the decision tree + checks to arrive at this 
optimization opportunity is very long, and I doubt a similar query is 
encountered by the QP more than one in a few million times (with a 
strong possibility that that should read "in a few billion times") ever 
- and when it does come up, the programmer most probably would opt for 
the latter example since it more clearly describes the plot. (The fact 
that it is also faster is just lucky).


If on the other hand a situation presents itself where the obvious 
better plot description (i.e. the second query) was /slower/  - then the 
optimization (to rather do it like the faster first query) would be a 
more worthy cause.


The fact that we can engineer a query that happens to be equivalent 
mathematically and is slower does not sufficiently call for an effort to 
improve the planner. In fact, we can engineer many such queries, and 
these kinds of things come up quite regularly on the forum. To be sure, 
they often do get optimized IF the slower query is the one that is 
semantically more sensible and the effort to implement plus the added 
code-weight and cpu cycles are justified by the gain in the general case.




   If the cost of doing so doesn't justify the effort, that could be
documented.  As good as the sqlite documentation is, it currently lacks this
sort of higher-level guidance.


I agree, perhaps some general description might be useful, though it's 
hard to imagine it mentioning specific scenarios. Consider that the 
amount of semantically-different-but-functionally-equivalent-yet-slower 
queries that can be engineered must be legion (the forum produces new 
ones almost monthly). It's hard to fathom documenting all of them - plus 
some of them disappear with improvements over time.


Maybe you could volunteer a paragraph of documentation that would have 
adequately satisfied your question in this regard and in general - the 
devs often do amend documentation based on suggestions here.



Cheers,
Ryan

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

Re: [sqlite] slow join, fast subselect

[R Smith]

Also, let me just add (in case it sounded different) - We definitely do 
not wish to document the "why an optimization opportunity might not be 
feasible" in any way that would discourage anyone from submitting such a 
possible optimization opportunity. That would work against the axiomatic 
premise of this forum, sqlite and open source communities in general.



On 2019/04/17 1:23 PM, Poor Yorick wrote:





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

Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...

[R Smith]

On 2019/06/27 10:57 AM, Adolfo J. Millan wrote:

I must apologize, because I feel that muy comment has been misinterpreted, 
proably because my weak english.

...
  If this had been a technical or theoretical issue, I would probably have 
refrained from expressing my opinion, due to my lack of adequate preparation. 
But I think this thread turn around the complain of a beginner, who -I think- 
has his 2 cts. of reason.


I don't think anyone complained of the OP, we just tried to explain why 
the types of documentation he argued for isn't in sqlite, or that 
sqlite.org isn't the optimal place for it.


Since then some others have had a bit of backlash. I mean, for one, is 
it not enough that the people (who make sqlite) put endless hours into 
making one of the best RDBMS engines?, why must they also carry the 
torch in the SQL education department? For another, the sqlite devs 
probably understand SQL very well, but they won't be in any way more apt 
at educating this to (especially) beginners. In fact Richard has claimed 
so himself, and I imagine making sqlite takes up most of their time, so  
they typically do not write much user systems or get into contact with 
"real-world" query examples (perhaps some via the forum).


I like Warren's idea of a community documentation part, though in the 
current way SQLite docs are compiled and produced won't fit such an 
ideology - but perhaps we could set up an SQLite-SQL community Wiki on 
the side?


I am happy to host this, I'm sure others are too. I am however 
unconvinced it will get any real visitors/contributors, even with a 
direct link from the sqlite.org front-page. It's the premise that these 
things "are needed because they are scarce on the internet" that is 
demonstrably false.




That sadid, of course, D.R. Hipp has al the rigth to maintain his doc as 
criptic as the Dr. Stroustrup in his famous TC++PL book, and consequently, 
everyone who approaches must come properly cryed from home.


I don't think the docs are cryptic - they are perhaps less expansive and 
aims at getting the truth of a functionality across in the most succinct 
way - something that is very much appreciated by any expert user who has 
had to wade through pages of crud to find a bit of knowledge (which is 
the case in almost every blog). Sure enough there is a place for the 
hand-holdy step-by-step type children's books, and they really do exist 
(W3Schools and Tynker are great examples - I use it myself when learning 
a new thing), but a technical sqilte doc is no place for that.



Not a complaint, just my 2c.  :)


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

Re: [sqlite] insert: how to force application to provide value for int primary key?

[R Smith]

On 2019/09/10 2:28 PM, Dominique Devienne wrote:

On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski  wrote:


Yes, indeed works. Great, thank you!


Note though that it has performance implications perhaps.

This changes to physical structure of the table, to be stored as an index
basically.

So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.

It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.



I feel like there's some concepts here that are not perfectly defined in 
the above statements, or I am mistaken, so please allow me to add:


Any SQLite table is essentially itself a covering Index with mostly 
rowid as the indexed key, but in WITHOUT_ROWID tables, it takes whatever 
other primary key is offered as the indexed key (not specifying a PK is 
an error). There is no other difference, and no difference in 
computation and typically a net speed/size gain if your Primary Key is 
not exactly INTEGER. (This is the entire point of offering the 
WITHOUT_ROWID optimization).


I do not think it is "better" suited to specifically reads or writes - 
both can work better (depending on your suitability definition for 
"better"). It is however better suited to smaller data widths in the 
columns (especially for blobs).


If the above is not true, someone kindly point out to me the truth and 
the why of it - thanks!





So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.



That is interesting - could you elaborate on how exactly lots of inserts 
would be worse in WITHOUT_ROWID tables than in normal tables?*
Or point me to documentation or even a dev explaining it in a forum 
post, because I have definitely missed that very important bit of info.  
The larger column data thing I do get.  I really need to know how it 
"can" matter, because I use this setup near everywhere and so far been 
blissfully under the impression that it's never worse, and possibly a 
little better for non-integer key use-cases.



*[I get that a text key might have slower insertion into a B-Tree than 
an integer key might have into a B*-Tree, but if your PK needs to be 
text anyway, I don't see how adding it in a normal table (along with the 
normal row_id) could have better performance, unless I'm missing 
something important.]





My $0.02. --DD


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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[R Smith]

On 2019/06/14 4:23 AM, Richard Damon wrote:

On 6/13/19 10:51 AM, R Smith wrote:

On 2019/06/13 4:44 PM, Doug Currie wrote:

Except by the rules of IEEE (as I understand them)

-0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"


Except that 0.0 is also an approximation to zero, not "true zero."

Consider that 1/-0.0 is -inf whereas 1/0.0 is +int


I do not know if this is the result case in any of the programming
languages, but in Mathematical terms that is just not true.

1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error
out. Anything returning +Inf or -Inf is plain wrong.
I posit the same holds true for 1/-0.0

Yes, 1.0/0.0 is undefined in the Field of Real numbers, but IEEE isn't
the field of Real Numbers. First, as pointed out, it has limited
precision, but secondly it have values that are not in the field of Real
Numbers, namely NaN and +/-Inf.

Note, that with a computer, you need to do SOMETHING when asked for
1.0/0.0, it isn't good to just stop (and traps/exceptions are hard to
define for general compution systems), so defining the result is much
better than just defining that anything could happen. It could have been
defined as just a NaN, but having a special 'error' value for +Inf or
-Inf turns out to be very useful in some fields.


I wasn't advocating to do something weird when the value -0.0 exists in 
memory - the display of that is what the greater idea behind this thread 
is[**].


What I was objecting to, is claiming (in service of suggesting the 
use-case for -0.0), that the mathematical result of 1/-0.0 IS in fact 
"-Inf" and so computers should conform, when it simply isn't, it's an 
error and SHOULD be shown so. Neither is the mathematical result of 0/-1 
= -0.0. It simply isn't mathematically true (or rather, it isn't 
distinct from 0.0), and I maintain that any system that stores -0.0 as 
the result of the computation of 0/-1 is simply doing so by virtue of 
the computational method handling the sign-bit separate from the 
division and being able to store it like so by happenstance of IEEE754 
allowing -0.0 as a distinct value thanks to that same sign bit, and not 
because it ever was mathematically necessary to do so.


I'll be happy to eat my words if someone can produce a mathematical 
paper that argued for the inclusion of -0.0 in IEEE754 to serve a 
mathematical concept. It's a fault, not a feature.



[** As to the greater question of representation - In fact I'm now a bit 
on the fence about it. It isn't mathematical, but it does help represent 
true bit-data content. I'm happy with it both ways.]




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

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

[R Smith]

On 2019/09/20 2:49 PM, Fredrik Larsen wrote:

Hi Ryan

Nobody is proposing that QP should automagically add an index, I'm only
asking why the QP does not use already added index, that is specially added
for this specific case. I don't thinks this is a very "obscurest of
use-case" or to much to ask for, in fact, this is the expected behavior for
even the simplest SQL engines, and so especially sqlite.


Apologies if I was unclear, I'm not shouting at you for asking - this is 
not that kind of forum, and it's a perfectly good request.


To be clear, I did not mean you are asking for it to index, I am saying 
that it already (automagically) makes an index to use for itself in 
service of the group-by, which is grand because it also solves the 
forward ordering without the need of an explicit index.


I further tried to make the case that It only makes this index as a 
temporary index that cannot/will not currently be traversed in the 
reverse, which is where it falls short to your specific (yes, very 
obscure) use case.


However, my point was forged more towards you not depending on the Index 
it makes for anything, upon reading DD's post, but after re-reading your 
post, I see the question is not so much that you expect the behaviour to 
get a certain output, but that the output comes at the cost of speed 
where it should not carry any speed penalty.


In this regard, let me adjust my original statement more in the 
direction of ambivalence. The optimization should be implemented if 
easy. The use case is obscure, but definitely valid, and more 
importantly, I don't think there is another way to fix it. If, on the 
other hand, it is CPU heavy in some way, or IO heavy for very large 
selects, avoid it rather. I doubt more than 0.1% of all goup-by 
output is DESC ordered[1].



Cheers,
Ryan

[1] - That is a completely made up statistic (obviously) and I will 
happily abide by a higher figure if claimed, but that burden of proof is 
not on me. :)



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

Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

[R Smith]

On 2019/09/20 11:12 AM, Dominique Devienne wrote:


But who says the GROUP BY must return rows in ASCending order?

A lot of us "oldies" of this ML well know the order is arbitrary and
subject to change w/o an explicit ORDER BY.
So the GROUP BY is allowed, AFAIK, to return rows in DESCending order just
the same. And to do so efficiently
as Fredrik points out, since indexes (or indices, I never know) work


I had a post on here, about 7 years ago explaining how the correct 
English is indeed "Indices", same as Matrix --> Matrices and Vortex --> 
Vortices etc.  It was pointed out to me (correctly) that while we hold 
that use in describing the Index multiples on books or catalogues or 
such, when specifically talking about a Database, and because of naming 
rigidity in SQL language, it is perfectly acceptable and even preferred 
to use "Indexes" as the plural for "Index". I do it too now, like all 
teh cool kids. :)


An area of contention is that it hides the value of the word "Indexes" 
when used as a verb - as in

"What does he do here?"
"He indexes the cards".
Or present "Mary indexes the books" similar in meaning to present 
continuous "Mary is indexing the books".


But, that whole whinge is irrelevant, it's very clear from context which 
"Indexes" is meant.


The American-English Merriam-Webster seems quite more ok with this than 
some older English dictionaries, but it has evolved now and I do not 
hear anyone anymore insisting on "Indices".  My prediction is that this 
will also not remain a "Database-Only" shift. I'm already hearing people 
refer to "book Indexes".




equally well in both directions. In fact, it could
return rows in arbitrary / random order too!

The query-planner does see the ORDER BY that follows the GROUP BY after
all, so it could well decide
to group in DESCending order, thus avoiding the ordering completely, like
it already does for ASCending.
This would be a great optimisation, and from 30,000ft, it does indeed seem
like a "simple" one compared
to all the advanced optimisations already implements, as Fredrik mentioned.

I might even say that it looks like a "low-hanging-fruit", if I dared :).


Nothing wrong with your statement or assessment. I don't know how much 
effort or code addition this would require, I'm guessing quite little - 
but I would like to say that this is the very obscurest of use-cases 
reported, and if it does require any increase in CPU cycles for the 
normal case (which is how my stuff work) - I don't want it.


To be clear, the question (paraphrasing and shortening horribly) is 
something like this:


The QP will automagically use an Index on Group By -
Which is nice as a side-benefit I don't have to explicit an index, which 
saves my DB some space/effort,
But now I also want to use the side-benefit for reverse ordering, and it 
doesn't work

(But it works perfectly well if I DO explicitly declare the Index).

Now it seems fine to suggest the optimization, but the problem is that 
this requires possibly a lot of effort and (hopefully very little) 
additional code, to facilitate a shortcut upon a shortcut for a thing 
that is completely QP-specific, open to change in future if a better 
grouping algorithm presents itself, and not within hard documented 
design methodology.
It's probably not as smart an optimization as it would seem, and the 
advice should always remain: USE AND INDEX when you want stuff indexed 
and ordered. Don't rely on QP quirks for your business logic or speed of 
execution.


In other words, EVEN if the devs do add the quirk (perhaps it's really 
trivial), I would still say don't depend on it. Some future update may 
find a better way to group and change this method, and that day might 
slow down your code.



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

Re: [sqlite] New word to replace "serverless"

[R Smith]

I do not have a great suggestion to add, but to observe that the best 
suggestions I think are: NOT changing, (or if we have to) "Server-Free" 
or "Localized".


Especially when you consider the statement at the top of typical SQLite 
docs might read:


"SQLite is a self-contained, server-free, zero-configuration ... " - 
seems to me to make most sense without still trespassing on the new 
fad-name.




On 2020/01/28 12:18 am, Richard Hipp wrote:

For many years I have described SQLite as being "serverless", as a way


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

Re: [sqlite] Unexplained table bloat

[R Smith]

On 2020/01/13 12:25 AM, Tom Browder wrote:

On Sun, Jan 12, 2020 at 14:05 Keith Medcalf  wrote:

Close, but no banana. Every value has a type. A column may contain 
multiple values (as in one per row) 


Thanks, Keith.

I assume that is just for SQLite, or am I wrong again?



You are not wrong. This comes up from time to time and is always quite 
interesting.



As Keith explained, the Relational Model can be applied on most kinds of 
data-stores. SQLite's data store happens to allow per-value typing, and 
then only using "Duck typing" (https://en.wikipedia.org/wiki/Duck_typing).


While on the subject of SQLite peculiarities, there's a few to note if 
you are new to SQLite, like:
- You don't need any type for a column, but untyped columns are treated 
as having blob affinity, not text.
- A column declared as VARCHAR(30), or TEXT(30) or INT(11) for that 
matter, will be fully accepted, but there is no actual length 
constraint. You can put any length value in the column.
- NULL values are distinct from each other (in some cases) so that if A 
is NULL and B is NULL, then A = B returns FALSE (0) and A <> B also 
returns FALSE!. This is useful, but note that a Primary Key in SQLite 
without also having the NOT NULL constraint, will allow duplicate NULL 
values in the key. (the row-id alias being the exception).
- While on the point, the special type "INTEGER PRIMARY KEY" is an Alias 
for the internal index (row-id) of the table, and so doesn't allow NULLs.
- While on it still... Tables don't always have row-ids, and there is no 
easy check to know if it does, so unless you made the DB yourself, you 
can't safely just query the row-id.
- Since typing is per value, it is not an error in SQLite for Parent and 
Child Key columns in a Foreign-Key relationship to have different types 
- this can have some unexpected results!
- A datetime is a Numeric type expressed as text (ISO8601) and doesn't 
inherently know anything about time or time-zones. (The date-handling 
functions work amazingly well though).

- A column with the type STRING will have Integer affinity.
- A spelling error in your schema, such as CREATE TABLE t(id INT, name 
T3XT); will not be an error - SQLite will silently regard that column 
affinity as blob.
- Quotes are more or less ambivalent... CREATE TABLE t("id" int, [name] 
text, `age` [int]) is a perfectly valid schema statement, and Set a = 
"John" can mean different things based on whether there is a column 
named John or not.


I'm probably not remembering all of them now, but we've spent lots of 
time musing about it before (forum searches will probably provide a host 
of discussions, including much lobbying for a "strict" mode) and because 
of all that, there's a fun feature added in SQLitespeed that does 
Schema-checking and prints a list of warnings if it contains one or more 
of these SQLite quirks (including misspelled types, which helps me a 
lot), and on the SQLite site there's also a section on some of these 
peculiarities (https://www.sqlite.org/quirks.html).


I'm hoping someone else will add the quirks which I forgot about :)

We all came to love (mostly) and often use these quirks to some 
advantage, but it pays to be aware of them, especially coming from 
another DB architecture where typing and the like are more rigid.



Cheers!
Ryan



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

Re: [sqlite] Unexplained table bloat

[R Smith]

On 2020/01/13 12:24 PM, Dominique Devienne wrote:

On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf  wrote:

On Monday, 13 January, 2020 02:27, Dominique Devienne  
wrote:

I'd vote for a lengthof(col) that's always O(1) for both text and blob

So what should lengthof(something) return the number of bytes in the 'database 
encoding' or something else?

Bytes of course. Of the data stored, i.e. excluding the header byte
and encoded size (if any) from the file-format.
Basically the same as length() *except* for text values, resulting in
O(1) behavior. --DD



Please have mercy - my beautiful text-extracty queries will no longer work!

I'm OK with a second other function, perhaps called bytelength() that 
returns what you suggest, but length is mostly used in any text 
extracting or parsing along with with other functions like instr() and 
substr() etc. which all take character indexes based on positions of 
code-points and not of actual bytes.


Back in the day when all text was ANSI ASCII 8-byte Latin-English 
characters this was easy, but now it ain't.


That said, I thought that if you cast a string to BLOB and then query 
the length, like: SELECT length(CAST(firstname AS BLOB)) AS bytelen;
You essentially get what you asked for, but I've never used it, so am 
not sure.





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

Re: [sqlite] Unexplained table bloat

[R Smith]

On 2020/01/13 12:24 PM, Dominique Devienne wrote:
Bytes of course. Of the data stored, i.e. excluding the header byte 


I checked, I was apparently correct about the casting. This following 
extract from a DB I got from a forum member with Greek-to-Danish 
translations where I added length checks:


  -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on SQLitespeed 
version 2.1.3.11.
  -- 



SELECT Glose_ID, Græsk, Dansk, length(Dansk) AS len, length(CAST(Dansk 
AS BLOB)) AS bytelen

  FROM Gloser
 WHERE 1 LIMIT 20;


  --   Glose_ID   | Græsk  | 
Dansk    | len | bytelen
  --  | -- | 
 | --- | ---
  --   1  | απόδειξη η | 1. bevis  2. 
kvittering  |  23 |    23
  --   2  | δεικτική αντωνυμία | påpegende stedord, 
demonstrativ pronomen |  40 | 41
  --   3  | δεικτικός-ή-ό  | 
påpegende    | 9  |    10
  --   4  | καθόλου    | slet (ikke), overhovedet 
(ikke)  |  31 |    31
  --   5  | κώδικας ο  | 1. kode 2. 
lov   |  14 |    14
  --   6  | πλην   | (mat) minus; (præp + G) 
undtagen |  32 |    33
  --   7  | προσδιορίζω    | præcisere, fastsætte, bestemme 
(nøjagtigt)   |  42 |    45
  --   8  | προσδιορισμός ο    | fastsættelse, (nøjagtig) 
bestemmelse |  36 |    38
  --   9  | προσδιορισής ο | ?? (- RH) foremntlig samme 
betydning som προσδιορισμός   |  54 | 67
  --  10  | φακός ο    | 1. linse  2. glas, brilleglas  
3. lygte, lommelygte  |  51 |    51
  --  11  | βρώμικος-η-ο   | snavset, beskidt; (fig) 
lyssky, skummel  |  39 | 39
  --  12  | δε μου λες...  | sig mig engang… (egl “du 
fortæller mig ikke”)    |  45 |    52
  --  13  | δημοσιογράφος ο/η  | 
journalist   | 10 |    10
  --  14  | κέρμα το   | mønt, 
småmønt    |  13 |    16
  --  15  | κοπέλα η   | 
pige | 4  |    4
  --  16  | μαθήτρια η | elev, skoleelev 
(kvindelig)  |  27 |    27
  --  17  | μαθητής ο  | elev, skoleelev 
(mandlig)    |  25 |    25
  --  18  | μπουκάλι το    | 
flaske   | 6  |    6
  --  20  | ποτήρι το  | glas (Både glas og flaske er 
intetkøn som vin. Øl er hunkøn) |  60 |    64
  --  21  | ίσιος-α-ο  | 
lige | 4  |    4



Hope that format is not too messed up by my e-mailer.


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

Re: [sqlite] Next Release? Visual release timeline?

[R Smith]

On 2020/01/15 1:24 PM, Richard Hipp wrote:

On 1/15/20, Dominique Devienne  wrote:

I like Lua's way to graphically visualize releases at
https://www.lua.org/versions.html


Please send javascript that will generate such a graph, either as SVG
or as an HTML Canvas.

(1) For improved display on mobile, consider making the graph vertical
instead of horizontal.

(2) Assume the data is a JSON array of pairs.  The first element of
each pair is the release name (ex: "3.30.0") and the second element is
the time as a fractional year (ex: "2019.7775").


We'd like to submit this layout as an option:
https://sqlitespeed.com/sqlite_releases.html

Shown alongside the current list in simple form. Tried a few layouts, 
not all work as well (SQLite releases are much more dense than Lua), 
finally settled on the above, but left some options open.


It comes with some config structure in this form:

  var options = {
    parentId: 'versionHistoryGraph',
    width: 200,
    yearWidth: 100,
    heightPerYear: 300,
    heightPerVersion: 14, // needs to match the style for .version
    lineColor: "#22",
    yearBackground: "#EE",
    data: 
[["1.0",2000.6298197581566],["1.0.1",2000.6325576089437],["1.0.3",2000.6435090120922],["1.0.4",2000.659936116815],["1.0.5",2000.7064795801962],["1.0.8",2000.7502851927902],["1.0.9",2000.7749258498745],["1.0.10",2000.7804015514487],["1.0.12",2000.7968286561716],["1.0.14",2000.802304357746],["1.0.13",2000.802304357746],["1.0.15",2000.8132557608944],


...

["3.30.0",2019.7584987451517],["3.30.1",2019.7749258498745]]
  };

Note: The Release-Date array must be given Ascending, else an additional 
sort step in Java is needed, but I think SQLite is better at that.



I will send the full script directly via e-mail (not sure if the forum 
will allow the size), but it can of course also be directly copied from 
the above html.


Official statement:
We have solely created the content of that html page and this e-mail, 
for the purpose of using it in the sqlite public pages, or as they see 
fit, but it is free to all, and herewith donated to the public domain.



Cheers,
Davey Van Nes, Ryan Smith





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

Re: [sqlite] Not Null Constraint Issue?

[R Smith]

On 2020/01/16 12:47 am, Simon Slavin wrote:

On 15 Jan 2020, at 9:44pm, Justin Gielski  wrote:


*"database is locked release restore point sqlite"*

If there's nothing in your code that caused that to happen, then I would 
suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?


The database locking mode is set to NORMAL but the database is always
opened exclusively. Could this been a concurrency issue in which 2
connections hit the database at the exact same time?

SQLite is not meant to allow that, with the existance of the 
journal/shared-memory files acting as a mutex.  If it actually did happen, and 
you're not violating anything in the following document, then either you found 
a bug in SQLite, or you had hardware problems.


Yes, or a software glitch. Remember that the SQLite database engine is 
actual software library code running alongside your own code in the same 
process, which means that if your code causes a memory fault (for 
whatever reason), the accompanying SQLite code could easily be on the 
receiving end of the memory corruption and also no longer work as 
expected. This is one of the fundamental differences of having the 
Database Engine as part of your process vs. having a process elsewhere 
on a server that functions independently (and can check constraints 
independently).


This is not a typical thing in production though, more expected during 
debugging or development, but it can happen in production, especially 
having situations like the OP described with rogue database locks from 
"hung" processes, etc.


@Justin: I'd start debugging by your processes/code that were hanging, 
finding out why that happens, and how your code paths could end up in 
such a state would probably solve a lot of problems (including the 
sqlite one). That said, if the you can engineer a situation in which it 
happens repeatably, that would be very interesting and the devs (and the 
rest of us) would be very interested.



Cheers,
Ryan


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

Re: [sqlite] Capturing the changes in columns in a table

[R Smith]

On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:

Greetings!

Please observe the following,


create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);

insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');

...

p001|1|10|column b changed on 2019-02-12
p002|2|4|column c changed on 2019-02-12
p003|n|y|column d changed on 2019-02-12
sqlite>

What I would like is to cover all of the dates in the same command, so that the 
output is this,

p001|1|10|column b changed on 2019-02-12
p002|2|4|column c changed on 2019-02-12
p003|n|y|column d changed on 2019-02-12
p004|4|5|column b changed on 2019-02-13
p004|2|3|column c changed on 2019-02-13
p004|y|n|column d changed on 2019-02-13
p005|2|3|column c changed on 2019-02-13
p005|4|8|column e changed on 2019-02-13

Yes, I know I can get all the dates and build the SQL as I did above, but is 
there a way to do this in one call?  Thanks for the help.



Of course there is :)

Many questions are not clear though.
I assumed you are only interested in day-on-day changes, because in many 
days there can be many different values, if you measure them all against 
the last day, it will look like many changes between day X and the last 
day, when really you are probably interested only in the change from one 
day to the next day.


I also assumed that the days you will give as the input, let's call that 
min date and max date of the report, will refer to the days on which 
changed happened - which seems obvious, but the query must change if it 
is not the case.


Anyway, here is the SQL to achieve it. I made 2 queries, one in which it 
just gets the min and max from your data, and the other in which you can 
specify the min and max date. I guess the second one will be more what 
you want - the nice t hing is your calling program only needs to adjust 
those two values once, nowhere else:


(PS: I left the whole generated output in so you can see at the bottom, 
those errors are not errors, but just informing us that SQLite is 
creating indexes for those CTE tables, which is great and means this 
should be relatively fast  on large datasets even.)



  -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on SQLitespeed 
version 2.1.3.11.


  -- Script Items: 4  Parameter Count: 0
  -- 2020-01-14 00:08:17.875  |  [Info]   Script Initialized, 
Started executing...
  -- 



create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);

insert into t (a, b, c, d, e, idate) values
 ('p001', 1, 2, 'n', 4, '2019-02-11')
,('p002', 2, 2, 'n', 4, '2019-02-11')
,('p003', 3, 2, 'n', 4, '2019-02-11')
,('p004', 4, 2, 'y', 4, '2019-02-11')
,('p005', 5, 2, 'y', 4, '2019-02-11')
,('p001',10, 2, 'n', 4, '2019-02-12')
,('p002', 2, 4, 'n', 4, '2019-02-12')
,('p003', 3, 2, 'y', 4, '2019-02-12')
,('p004', 4, 2, 'y', 4, '2019-02-12')
,('p005', 5, 2, 'y', 4, '2019-02-12')
,('p001',10, 2, 'n', 4, '2019-02-13')
,('p002', 2, 4, 'n', 4, '2019-02-13')
,('p003', 3, 2, 'y', 4, '2019-02-13')
,('p004', 5, 3, 'n', 4, '2019-02-13')
,('p005', 5, 3, 'y', 8, '2019-02-13')
;

-- This version guesses the min and max dates...
WITH DSpan(minDate, maxDate) AS (
  SELECT MIN(idate), MAX(idate) FROM t GROUP BY idate
), DDays(dayOldDate, dayNewDate) AS (
  SELECT minDate, date(minDate,'+1 day') FROM DSpan
  UNION ALL
  SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE 
DDays.dayNewDate < DSpan.maxDate

), Chg(a, idate, col, oldVal, newVal) AS (
  SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b
    FROM DDays
    JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
    JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
   WHERE tNew.a = tOld.a AND tNew.b != tOld.b
UNION ALL
  SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c
    FROM DDays
    JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
    JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
   WHERE tNew.a = tOld.a AND tNew.c != tOld.c
UNION ALL
  SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d
    FROM DDays
    JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
    JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
   WHERE tNew.a = tOld.a AND tNew.d != tOld.d
UNION ALL
  SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e
    FROM DDays
    JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
    JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
   WHERE tNew.a = tOld.a AND tNew.e != tOld.e
)
SELECT Chg.a, Chg.oldVal, Chg.newVal,
   ('Column '||Chg.col||' changed on '||Chg.idate||' from 
'||Chg.oldVal||' to '||Chg.newVal||'.') AS Change

  FROM Chg
 ORDER BY Chg.a, Chg.idate
;


  -- a    | oldVal | newVal | Change
  --  | -- | -- | 
  -- p001 |    1   |   10   | Column b changed on 2019-02-12 from 1 to 10.
  -- p002 |    2   |    4   | Column c changed on 2019-02-12 from 2 to 4.
  -- p003 |    n   |    y   | Column d changed on 2019-02-12 from n to y.
  

Re: [sqlite] Capturing the changes in columns in a table

[R Smith]

On 2020/01/14 7:10 PM, David Raymond wrote:

A note and a question on subqueries. On reading:

select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as 
oldv,
...

My brain started yelling that that needed a "limit 1" on the subquery so that 
it would only return 1 row.

I looked in the SQLite docs though and it seems like I'm wrong.
https://www.sqlite.org/lang_expr.html
"The value of a subquery expression is the first row of the result from the enclosed 
SELECT statement."

Then to make sure my brain was remembering correctly I checked Postgres and 
found this.
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
"It is an error to use a query that returns more than one row or more than one 
column as a scalar subquery."


So my questions then are:

How is that handled by other databases?

Is that something worthy of being on the quirks page 
(https://www.sqlite.org/quirks.html), or is it just in the realm of non-quirky 
normal-level differences?



I cannot speak for ALL databases, but mostly PostGres, MySQL and MSSQL 
fails if violated only.  (I think MSSQL warns, depending on the version, 
when you prepare a non-limited sub-query).


Mostly though, they assume the Query programmer knows what (s)he is 
doing and will run without problem - that is, until the sub-query 
actually hits more than 1 row, at which point the current operation will 
fail with an error (something like: "Scalar Sub-Query may only return 1 
row").


SQLite seems to regard that if you ask for a scalar sub-query, you 
obviously mean to have just one row... so it returns just 1.


Both approaches have merit.
I always lean more towards "It's better to error out", and if it is a 
case of you just forgetting to place the LIMIT or TOP clause in your 
sub-query, then it's great that it errors out to help you rectify it 
NOW, like the client-server clade.


But,

I'm slightly more a fan of "what happens in dev must happen in 
production" - and in this case I'm leaning the sqlite way since, with 
the other servers, the disadvantage is that your dev server may well 
never produce more than one row, so always seem to be working, and then 
it fails once in production with more real data.


I'd rather have A - the Query error out on Prepare when it realizes the 
LIMIT/TOP clause is missing - or B - auto-recognizes the specific 
sub-query type and only returns one row (a-la-sqlite), but I don't think 
the "Accept it until the shape of the data makes it fail" is a great 
solution.



/2c

Ryan

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

Re: [sqlite] Capturing the changes in columns in a table

[R Smith]

On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:

R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​



​
Wow!  Thanks for this.  I had not thought about your questions.  My boss said, 
I need to know all the changes per project whenever it happened. So,... I will 
have to revise my thinking, but I have enough with your help to continue. I am 
going to have to reload SQLitespeed, and try it again. :-)  Thanks.​


A - Es un placer,

B - It's important to really understand how they want to see changes. 
Also I'm simply assuming (thanks to your example) that changes do not 
happen more frequently than once a day, and that the time of it is not 
important. If it is, the query will need to be adjusted.


C - I know you probably know this, but just in case it isn't 100% clear: 
there is nothing about the SQL I posted that requires SQLitespeed. It is 
simply the easiest for me to use and it outputs SQL+Results the way I 
like it (so feel free), but that query will work in any SQLite platform 
for any version of SQLite - after 3.8 that is (or 3.7... or whatever 
version introduced CTE's, my memory is suddenly failing).



Good luck with your quest!
Ryan


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

[sqlite] TO

[R Smith]

Hopefully the last of the silly questions...

The word "TO" is given as an SQLite Keyword, but I cannot find any 
reference to it being used anywhere in the SQL used by SQLite.


The search doesn't help (because the word TO is everywhere in text), so 
manually looking through CREATE TABLE / TRIGGER / etc. came up with nothing.


Anyone have an idea where the word TO is used in SQL in SQLite?


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

Re: [sqlite] TO

[R Smith]

On 2020/01/08 6:19 PM, Richard Hipp wrote:

On 1/8/20, R Smith  wrote:

Anyone have an idea where the word TO is used in SQL in SQLite?


alter table t1 rename TO t2;
rollback TO savepoint1;


So obvious...  My brain must be needing a break.


Thank you Richard and Tim!


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

Re: [sqlite] Capturing the changes in columns in a table

[R Smith]

Jose, I like Keith's version better using the Windowing functions 
assuming your version of SQLite is newer than 3.27 (or whenever Window 
functions were introduced, again my memory fails...)


Most importantly, the CTE query /requires/ changes be day-on-day to be 
seen, which is the case in your example, but might not be the real life 
case, whereas this window-function query of Keith will spot changes even 
if updates are logged days apart, or indeed happened on the same day.


You still need to make sure of a few things, but I would definitely use 
this query.



On 2020/01/14 1:51 AM, Keith Medcalf wrote:

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');

   select *
 from (
   select a,
  idate,
  'b' as variable,
  lag(b, 1) over (partition by a order by idate) as oldv,
  b as newv
 from t
union
   select a,
  idate,
  'c' as variable,
  lag(c, 1) over (partition by a order by idate) as oldv,
  c as newv
 from t
union
   select a,
  idate,
  'd' as variable,
  lag(d, 1) over (partition by a order by idate) as oldv,
  d as newv
 from t
union
   select a,
  idate,
  'e' as variable,
  lag(e, 1) over (partition by a order by idate) as oldv,
  e as newv
 from t
  )
where oldv <> newv;

a   idate   variableoldvnewv
--  --  --  --  --
p0012019-02-12  b   1   10
p0022019-02-12  c   2   4
p0032019-02-12  d   n   y
p0042019-02-13  b   4   5
p0042019-02-13  c   2   3
p0042019-02-13  d   y   n
p0052019-02-13  c   2   3
p0052019-02-13  e   4   8

Change "<>" to "IS NOT" to find out when their was no previous value


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

[sqlite] RTree function information

[R Smith]

I see three R*Tree functions in the function list compiled-in with 
latest binaries, namely:

rtreecheck(),
rtreedepth(), and
rtreenode().

While rtreecheck() is explained at the bottom of the R*Tree 
documentation page at:

https://sqlite.org/rtree.html#integrity_check_using_the_rtreecheck_sql_function

I cannot find any mention of the other two. When searching on the 
website using the standard search-box, both rtreenode and rtreedepth 
return Zero hits.


Might there be documentation hidden away somewhere? If so, would it be 
useful to be more visible?


Kindly let me know where I can find it, or perhaps a quick basic note on 
how those two functions work and are meant to be used.



Thank you kindly!
Ryan


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

Re: [sqlite] RTree function information

[R Smith]

On 2020/01/07 3:46 PM, Richard Hipp wrote:

On 1/7/20, R Smith  wrote:

I cannot find any mention of the other two.

Those other routines are for testing and debugging use only.  They
will (likely) be made inaccessible to normal application code in the
next release.



Thank you kindly.

May I assume the same of FTS5 functions:
fts5_decode,
fts5_decode_none,
fts5_expr,
fts5_expr_tcl,
fts5_fold, and
fts5_isalnum?

Since they also show up in the listing but I cannot find them in the 
normal docs.




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

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[R Smith]

On 2020/01/08 2:00 AM, Michael Kappert wrote:

-- Modify one parent entry of foreign key fk_t1_id
REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new');


If I understand correctly, the upsert should behave like UPDATE in the
examples above, but it behaves like a DELETE followed by INSERT instead?



You say "upsert" but the operation in question is not an "upsert", it's 
a REPLACE which is really a DELETE followed by an INSERT, which is why 
you are seeing the behaviour you noted.


To do an actual UPSERT in SQLite, use the UPSERT method, which will look 
somewhat like this for your example:


INSERT INTO t1(id, name) VALUES ('A', 'line 1-new')
  ON CONFLICT (id) DO UPDATE SET name = excluded.name

;


HTH and Good luck,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOTNULL

[R Smith]

I find the keyword NOTNULL listed among known SQLite keywords -
no. 88 on this page: https://sqlite.org/lang_keywords.html

But cannot find a single mention of it or place to use it in SQLite, nor 
get any hit on the sqlite.org search except in reference to the above list.


1. What is it for?

2. Does it have/need any documentation?


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

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[R Smith]

On 2020/01/08 1:10 PM, Simon Slavin wrote:

I advise you avoid the idea of UPSERT when dealing with SQLite (or better 
still, all SQL).  It is rarely implemented as a single operation, and you can 
get unexpected results with triggers and foreign key children.


I advise you to avoid the idea of driving a Bus to work (or better 
still, at all).  It is rarely easy to navigate narrow lanes and you can 
get unexpected results when trying to park it at the mall.


Rather use a bicycle.

If however your objective is to take 100 other people to work, you'll 
find the bus is a godsend - much like UPSERT.




Simon is quite correct, using a function without proper understanding, 
results can be somewhat unexpected.  Even with proper understanding of 
the SQL, it can still be unexpected, considering that different engines 
implement it differently. A shining example of this is "REPLACE" - 
almost everyone who posted about it here misunderstood what it really 
does[1], often causing the exact problems Simon warns about.


That said, I can't agree with advising against UPSERT.

There is no unknown magic going on with UPSERT (or any other fully 
supported SQL feature in SQLite), if you do need it, do read up properly 
on it (specific to the platform you will use it on) and it will work as 
advertised. (And in the rare case it doesn't, a bug report here usually 
remedies that in about a day).



Cheers,
Ryan

[1] - They often use REPLACE having come from a MySQL background, and 
then they often learn that even in MySQL it doesn't work as they had 
imagined.



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

Re: [sqlite] NOTNULL

[R Smith]

On 2020/01/08 1:23 PM, Simon Slavin wrote:


You can use NOTNULL as a condition.  It's the opposite of ISNULL.  You see it 
usually as a constraint, to ensure that a field has a value.


Thank you Simon - Do you perhaps have an example of this working in 
SQLite? I am not finding a way to make it work.




However, NOT NULL with a space works too, and that version seems to be more 
popular these days.


Yes, this I'm using in SQLite often.  I'm busy updating the 
documentation functions for the next release of sqlitespeed, needing to 
connect every keyword/function to its proper use in SQLite - which is 
why I've been posting these documentation-related questions.



Thanks for helping,
Ryan

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

Re: [sqlite] NOTNULL

[R Smith]

On 2020/01/08 2:03 PM, Keith Medcalf wrote:



1. What is it for?

It is a common misspelling of "IS NOT NULL" and means the same thing.

"ISNULL" is also a reserved word as it is a common misspelling of "IS NULL" and 
means the same thing.

You will note that ISNOTNULL is not a reserved word because apparently the 
common misspellings only omit one or the other of the spaces, butnotboth at the 
sametime.

Also, ISNOT is not a reserved word.  Apparently it is more common for the 
misspelling IS NOTNULL to be made than ISNOT NULL, even though ISNOT is the 
more logical inverse operator to IS.  (Perhaps this is because ISNOT could be 
spelled as iSnot leading to lawsuits from Apple ... :) )



LOL - I'm quoting you in the docs. :)


Thanks for clarifying,
Cheers!

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