Hi,
creating an R-tree table with what looks like a table constraint results
in an inconsistent number of columns, with funny results:
> create virtual table t using rtree(id, x1, x2, y1, check(1));
> insert into t default values;
> select * from t;
1|0.0|0.0|1.74906711200709e-38
Regards,
Hi,
the R-tree documentation says:
| Attempts to insert something other than an integer into the first
| column, or something other than a numeric value into the other
| columns, will result in an error.
This is not actually true:
> create virtual table t using rtree(id, x1, x2);
> insert into
On 2015-10-02 07:28 PM, Bart Smissaert wrote:
>> if any single column in those two rows is NULL.
> OK, I got it and that will make it even less likely that this situation
> will occur.
> I think we got this exhausted now.
Sure - but don't be dismayed though, every opportunity for optimization
On 2015-10-02 05:41 PM, Bart Smissaert wrote:
>> you're just throwing random terms around and hoping something sticks.
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3] TEXT,
>
> Keep at it.
Will do.
RBS
On Fri, Oct 2, 2015 at 6:46 PM, R.Smith wrote:
>
>
> On 2015-10-02 07:28 PM, Bart Smissaert wrote:
>
>> if any single column in those two rows is NULL.
>>>
>> OK, I got it and that will make it even less likely that this situation
>> will occur.
>> I think we got
No, whether the column contains a null is irrelevant. It is whether the column
CAN contain a null.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Bart Smissaert
> Sent: Friday, 2
You are making an error.
sqlite> create table x(a, b, c, d);
sqlite> create unique index y on x(a,b,c,d);
sqlite> insert into x values(1,2,3,null);
sqlite> insert into x values(1,2,3,null);
sqlite> insert into x values(1,2,3,null);
sqlite> select * from x;
1|2|3|
1|2|3|
1|2|3|
sqlite> select
> if any single column in those two rows is NULL.
OK, I got it and that will make it even less likely that this situation
will occur.
I think we got this exhausted now.
RBS
On Fri, Oct 2, 2015 at 6:24 PM, Richard Hipp wrote:
> On 10/2/15, Bart Smissaert wrote:
> > Do you mean that is only
Yes, I can see that there is no point in SQLite having this optimization.
Of course I can do this optimization myself (just run select count(*) from
table, instead of using distinct) if I know all conditions are met. This
wasn't anything practical I needed, just a fleeting thought.
RBS
On Fri,
OK, thanks.
Do you mean that is only valid if there are no rows where all columns are
NULL?
In any case, I can see that this optimization (in SQLite) is just not worth
the trouble.
RBS
On Fri, Oct 2, 2015 at 6:02 PM, Richard Hipp wrote:
> On 10/2/15, Bart Smissaert wrote:
> >
> > Unless I am
On 2 Oct 2015, at 4:41pm, Bart Smissaert wrote:
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3] TEXT,
> [FIELD4] REAL)
>
> and we have a unique index on all 4 fields:
>
> CREATE UNIQUE INDEX IDX1
> ON TABLE1
> (FIELD1, FIELD2, FIELD3, FIELD4)
> you're just throwing random terms around and hoping something sticks.
Not sure where you got that idea from, but let me explain better:
Say we have a table:
CREATE TABLE TABLE1
([FIELD1] INTEGER,
[FIELD2] TEXT,
[FIELD3] TEXT,
[FIELD4] REAL)
and we have a unique index on all 4 fields:
CREATE
I'm using an archaic version, I know, but busy_timeout doesn't seem to
work. It doesn't return the value I set. When was it added?
sqlite3 temp.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> PRAGMA busy_timeout=3;
sqlite> PRAGMA busy_timeout;
sqlite>
On 2015-10-02 10:05 AM, Bart Smissaert wrote:
> Noticed that if I have table with a unique index on all fields, counting
> all rows is still a lot faster
> (about a factor 10 on my particular test table) than counting distinct rows.
> Could maybe an optimization be added to SQLite to speed this
It is faster because if it knows there is no where or join or whatever row
limiting condition and it also knows there is
a unique index on all fields it can simply do select count(rowid) from
table1 and not do any count distinct.
RBS
On Fri, Oct 2, 2015 at 3:51 PM, Scott Hess wrote:
> On Fri,
> The Uniqueness of the output depends on which fields are included, JOINs,
UNIONs, etc. etc.
I am not talking about that situation. I am only referring to a situation
where you want to count all
rows in a table. I know it will be uncommon to have an index on all fields
and this is not really a
Counting all rows vs counting distinct rows is a very different task.
In simple terms
For all rows the process is
read every leaf page in the index
query the cell count field (number of records)
add cell count to the to the total count
repeat for the remaining leaf pages
For distinct records
On 2 Oct 2015, at 9:05am, Bart Smissaert wrote:
> Noticed that if I have table with a unique index on all fields, counting
> all rows is still a lot faster
> (about a factor 10 on my particular test table) than counting distinct rows.
> Could maybe an optimization be added to SQLite to speed
I'm using an archaic version, I know, but busy_timeout doesn't seem to
work. It doesn't return the value I set. When was it added?
sqlite3 temp.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> PRAGMA busy_timeout=3;
sqlite> PRAGMA busy_timeout;
sqlite>
On 10/2/15, Bart Smissaert wrote:
> Do you mean that is only valid if there are no rows where all columns are
> NULL?
No, I mean that two rows can be identical (not distinct) event if
there is a unique index on all columns, if any single column in those
two rows is NULL. Example:
SQLite
>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every operation
> on any
On 10/2/15, Bart Smissaert wrote:
>
> Unless I am mistaken here this is done with a SQL like this:
>
> SELECT COUNT(*) AS UNIQUE_ROWS
> FROM (SELECT DISTINCT * FROM TABLE1)
>
> But if we take advantage of the fact that this table has a unique index on
> all the fields of the table
> we can simply
On Fri, Oct 2, 2015 at 9:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3]
On Fri, Oct 2, 2015 at 9:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3]
On Fri, Oct 2, 2015 at 8:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
AFAICT this is the first posting where you said "I want to count all the
unique rows of this
I think the doc is right. I overcame the problem by using a construct like:
SELECT field1, field2? WHERE PKEY IN (SELECT PKEY ? WHERE OFFSET n LIMIT
m)
That executes a sub query.
But your solution looks actually better, as it is:
SELECT * FROM (SELECT field1, field2? WHERE OFFSET n LIMIT m)
Thanks. I know about the technique your mentioned, but the point is not about
the use of offset or not. The same issue will happen but using a key.
See my other reply above.
Noticed that if I have table with a unique index on all fields, counting
all rows is still a lot faster
(about a factor 10 on my particular test table) than counting distinct rows.
Could maybe an optimization be added to SQLite to speed this up, taking
advantage of the fact that there is a unique
Why does any of that matter? SELECT COUNT(*) FROM table; already knows all
of that information.
If you have a question about why one query is faster/slower than another
query given one schema versus another schema, then post representative
schema and queries. Right now you're just throwing
On Fri, Oct 2, 2015 at 7:43 AM, Bart Smissaert
wrote:
> > The Uniqueness of the output depends on which fields are included, JOINs,
> UNIONs, etc. etc.
>
> I am not talking about that situation. I am only referring to a situation
> where you want to count all
> rows in a table. I know it will be
On 10/01/2015 10:32 PM, Andrew Cunningham wrote:
> I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY
> violation occurs, SQLite will continue to run, but performance slows down
> by about 1000x. Breaking into the code shows that it seems to be
> continually re-reading the
On the following page: https://www.sqlite.org/c3ref/funclist.html
The following items:
*sqlite3_backup_finish
*sqlite3_backup_init
*sqlite3_backup_pagecount
*sqlite3_backup_remaining
*sqlite3_backup_step
Have incorrect anchors assigned to them in the HTML page code.
32 matches
Mail list logo
