More strange things:
SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 order by x desc;
9, 2
8, 3
7, 4
SELECT x, 0+sequence() AS y FROM t1 WHERE y>0 AND y<99 order by x desc;
9, 6
8, 6
7, 6
Seems that this optimization a little bit broken...
--
Sent from: http://sqlite.1065341.n5.nabbl
Well, behaviour of deterministic function is really strange.
SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55 AND y NOT IN
(56,57,58) AND y NOT LIKE 'abc%' AND y%10==2 order by x desc
(Example from alias.test, I have declared sequence() with
SQLITE_DETERMINISTIC flag)
Trace for the q
Jens Alfke-2 wrote
> First off, you didn’t register the function as deterministic, so SQLite
> has to assume it can return a different result every time it’s called,
> even with the same arguments. That immediately prevents the kind of
> optimization you wanted.
I guess, it doesn't really matter w
I have noticed strange behaviour of user functions. Consider following
example:
#include
#include
#include "sqlite3.h"
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i0
AND y<100;", callback, 0, &zErrMsg);
sqlite3_close(db);
What is the timeline of this feature? Will it be implemented in the nearest
future? And what optimizations doesn't also work on without rowid tables? Is
the query planner good at optimizing stuff on without rowid tables at all?
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_
sqlite> create table t1(a primary key, b) without rowid;
sqlite> create table t2(a primary key, b) without rowid;
sqlite> explain query plan select * from t1, t2 where t1.b = t2.b;
0|0|0|SCAN TABLE t1
0|1|1|SCAN TABLE t2
sqlite> create table t3(a primary key, b);
sqlite> create table t4(a primary k
Richard Hipp-3 wrote
> See https://www.sqlite.org/fileformat.html#the_sqlite_stat4_table
Thanks, I have already read it, but there is no explanation about this:
korablev wrote
> statistics in the second row seems to be wrong: there are 0 rows which are
> less than 1(for the reason that
DROP TABLE IF EXISTS t1;
create table t1(id primary key, a, b) without rowid;
create index t1a on t1(a);
insert into t1 values(1, 1, 2), (2, 1, 3);
analyze t1;
select * from sqlite_stat4 where idx = 't1a';
t1|t1a|2 1|0 0|0 0|
t1|t1a|2 1|0 1|0 1|
Firstly, there is no blob values for s
Any thoughts concerning this issue? This output is really confusing when
trying to understand query plan...
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite
CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
INSERT INTO t1 VALUES(1, 1, 1);
EXPLAIN QUERY PLAN SELECT min(c) FROM t1;
This query emits: "SEARCH TABLE t1 USING PRIMARY KEY". However, it is not
really SEARCH, but SCAN TABLE. It can be seen from opcodes for VM. (Indeed,
we should traverse thr
Hmm, I got this:
SQLite version 3.21.0 2017-10-15 22:16:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test_db1
sqlite> CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
sqlite> WITH RECURSIVE
Thanks, I really appreciate so fast responce. However, example above still
doesn't work: planner prefers index 'a' instead of TABLE SCAN for WITHOUT
ROWID table, when range of query covers full table.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
Consider the following example:
CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
WITH RECURSIVE
cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000)
INSERT INTO t1(a,b,c) SELECT x, x,x FROM cnt;
CREATE INDEX t1b ON t1(b);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT * FROM t1 WHERE b B
13 matches
Mail list logo
