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

2019-04-03 Thread 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

Re: [sqlite] Feature request, sqlite3_stmt_action

2019-03-28 Thread 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

Re: [sqlite] export tables

2019-04-08 Thread 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

Re: [sqlite] What is the most commonly used file extension for sqlite3 database files?

2019-03-23 Thread 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

Re: [sqlite] Equiv stmts, different explain plans

2019-03-05 Thread 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

Re: [sqlite] INSERTing from another table data

2019-03-19 Thread 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

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

2019-03-22 Thread 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

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

2019-03-22 Thread 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

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

2019-02-18 Thread 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

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

2019-02-18 Thread 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

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

2019-02-18 Thread 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

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

2019-02-12 Thread 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

Re: [sqlite] Tips for index creation

2019-02-13 Thread 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:

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

2019-02-12 Thread 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),

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

2019-02-05 Thread 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

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

2019-02-06 Thread 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

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

2019-02-05 Thread 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

Re: [sqlite] Option to control implicit casting

2019-04-09 Thread 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

Re: [sqlite] slow join, fast subselect

2019-04-17 Thread 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

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

2019-03-15 Thread 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

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

2019-05-15 Thread 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);

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

2019-05-15 Thread 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),

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

2019-05-13 Thread 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)

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

2019-06-06 Thread 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

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

2019-06-13 Thread 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

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

2019-06-12 Thread 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

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

2019-06-12 Thread 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

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

2019-06-12 Thread 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

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

2019-05-13 Thread 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

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

2019-05-22 Thread 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

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

2019-05-23 Thread 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

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

2019-05-23 Thread 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

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

2019-04-30 Thread 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

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

2019-05-07 Thread 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

Re: [sqlite] Custom collation of blobs

2019-04-27 Thread 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

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

2019-05-08 Thread 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 --

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

2019-06-26 Thread 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

Re: [sqlite] slow join, fast subselect

2019-04-17 Thread 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

Re: [sqlite] slow join, fast subselect

2019-04-17 Thread 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

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

2019-06-27 Thread 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

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

2019-09-10 Thread 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

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

2019-06-14 Thread 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 approxim

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

2019-09-20 Thread 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

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

2019-09-20 Thread 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

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

2020-01-28 Thread 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,

Re: [sqlite] Unexplained table bloat

2020-01-12 Thread 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

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread 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

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread 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

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

2020-01-15 Thread 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

Re: [sqlite] Not Null Constraint Issue?

2020-01-15 Thread 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 ?

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

2020-01-13 Thread 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

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

2020-01-14 Thread 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

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

2020-01-13 Thread 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

[sqlite] TO

2020-01-08 Thread 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 /

Re: [sqlite] TO

2020-01-08 Thread 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

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

2020-01-13 Thread 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

[sqlite] RTree function information

2020-01-07 Thread 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:

Re: [sqlite] RTree function information

2020-01-07 Thread 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

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

2020-01-07 Thread 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

[sqlite] NOTNULL

2020-01-08 Thread 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?

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

2020-01-08 Thread 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

Re: [sqlite] NOTNULL

2020-01-08 Thread 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

Re: [sqlite] NOTNULL

2020-01-08 Thread 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

<    3   4   5   6   7   8