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
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
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
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
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
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
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
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
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
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
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
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
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:
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),
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
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
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
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
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
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
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);
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),
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)
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
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
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
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
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
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
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
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
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
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
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
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
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 --
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
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
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
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
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
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
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
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
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,
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
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
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
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
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 ?
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
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
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
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 /
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
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
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:
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
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
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?
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
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
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
701 - 763 of 763 matches
Mail list logo