[sqlite] column type impact on index usage

I thought I understood that column types were effectively a hint to sqlite and didn't really have an effect on the semantics of queries. But I ran into this case wherein the column types of columns in tables being joined seems to determine whether an index is used or not. Here's my sample code.

Re: [sqlite] foreign key constraint failure

n table t2 gives the id of the t1 row which can not be deleted: > > SELECT id1 FROM t2 WHERE id = 456 > -- id1:123 > > This is row 123 of t1 which can not be deleted. > > Make sure to rollback the failed transaction, and restore foreign key > checks: > >

[sqlite] foreign key constraint failure

Is there a way to get sqlite to tell which foreign key constraint is causing a failure? Some kind of verbose mode? Thanks! sqlite> delete from t; Error: FOREIGN KEY constraint failed sqlite> ___ sqlite-users mailing list

[sqlite] values ?

My reading of https://sqlite.org/syntax/select-core.html makes me think that I should be able to issue something like values('foo'); and get a row with a single column whose value is 'foo'. But I get a syntax error. Probably obvious to the right people but what am I missing? sqlite>

Re: [sqlite] values ?

Argh. Yes, I was on 3.8.2. Thanks! On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp <d...@sqlite.org> wrote: > On 12/12/17, Mark Wagner <m...@google.com> wrote: > > My reading of https://sqlite.org/syntax/select-core.html makes me think > > that I should be able to

Re: [sqlite] values ?

or "where" or "order by", where would this > come in useful? > > On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner <m...@google.com> wrote: > > > Argh. Yes, I was on 3.8.2. Thanks! > > > > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp <d...@sq

Re: [sqlite] unique constraint

I am confused. Too much experimenting :( On Tue, May 15, 2018 at 4:55 PM Simon Slavin <slav...@bigfraud.org> wrote: > On 16 May 2018, at 12:25am, Mark Wagner <m...@google.com> wrote: > > > I'm wondering if > > someone can explain why this simple test of unique

[sqlite] unique constraint

OK, at the risk of exposing my lack of edification, I'm wondering if someone can explain why this simple test of unique column constraints doesn't work. At least it doesn't work as I expected it would (i.e. that the second insert would yield a unique constraint violation). create table t (k

Re: [sqlite] unique constraint

Thanks for the responses. Just a brain error. Not sure what I was thinking :) On Tue, May 15, 2018 at 6:55 PM R Smith <ryansmit...@gmail.com> wrote: > > On 2018/05/16 1:25 AM, Mark Wagner wrote: > > OK, at the risk of exposing my lack of edification... > > Som

Re: [sqlite] column types and constraints

Sorry, my typo (I had entered the corrected code). This: create table t1(x text non null); insert into t1(x) values(null); select * from t1; On Wed, Jun 27, 2018 at 6:14 PM Richard Hipp wrote: > On 6/27/18, Mark Wagner wrote: > > Thanks for all the good background. FWIW thi

Re: [sqlite] column types and constraints

Thanks for all the good background. FWIW this came up because someone had created a row with something like: (column_name non null). Needless to say, this created a column without a "not null" constraint. On Wed, Jun 27, 2018 at 5:02 PM Richard Hipp wrote: > On 6/27/18, Mark

[sqlite] column types and constraints

I recently pointed out that sqlite doesn't enforce type names and constraints when creating tables but I was unable to explain/justify this behavior. I'm sure this has come up before and there's a clear answer but I didn't find it easily. For example this is accepted without error: CREATE TABLE

[sqlite] question about covering index

Given the following schema: CREATE TABLE foo (_id integer primary key, x, y); CREATE INDEX i on foo(_id, x, y); And the following query sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y; I would have expected it (hoped?) that it would use the covering index for the

Re: [sqlite] question about covering index

rSort 1 49000 > >43 SorterData 1 16400 > >r[16]=data > >44 Column 4 0 14 00 r[14]=y > >45 Column 4 2 13 00 r[13]=x > >

Re: [sqlite] question about covering index

Slavin <slav...@bigfraud.org> wrote: > On 7 Feb 2018, at 12:43am, Mark Wagner <m...@google.com> wrote: > > > CREATE TABLE foo (_id integer primary key, x, y); > > CREATE INDEX i on foo(_id, x, y); > > > > And the following query > > > > sqlite

[sqlite] sqlidiff --schema question

I was surprised to see sqldiff --schema not report column constraints as schema differences. Or am I missing something? $ echo .schema | sqlite3 /tmp/f1.db CREATE TABLE t (foo text unique); $ echo .schema | sqlite3 /tmp/f2.db CREATE TABLE t (foo text); $ sqldiff --schema /tmp/f1.db /tmp/f2.db

Re: [sqlite] sqlidiff --schema question

Just pining on this in case anyone knows more... ? On Tue, Aug 7, 2018 at 2:00 PM Mark Wagner wrote: > > I was surprised to see sqldiff --schema not report column constraints as > schema differences. Or am I missing something? > > $ echo .schema | sqlite3 /tmp/f1.db > CREATE

Re: [sqlite] multiple table constraints

Thanks for the detailed response. I was really confused for a few minutes this morning noticing both forms in my code. :) On Wed, Mar 7, 2018 at 9:44 AM, Richard Hipp <d...@sqlite.org> wrote: > On 3/7/18, Mark Wagner <m...@google.com> wrote: > > > > e.g. both are ac

[sqlite] multiple table constraints

The syntax for multiple table constraints seems to specify a comma between each constraint but I noticed that no comma separator seems to be accepted as well. e.g. both are accepted CREATE TABLE foo(_id primary key, x, y, unique(x), unique(y)); CREATE TABLE foo(_id primary key, x, y, unique(x)

[sqlite] crash dropping table

I have a simple test case wherein I delete from a number of tables and then drop one of those tables. This crashes sqlite3. So something like this: begin; delete from x; delete from y; delete from z; drop x; << crashes here Sorry for my ignorance but is there a procedure for submitting bugs

Re: [sqlite] crash dropping table

18 at 9:56 AM Simon Slavin <slav...@bigfraud.org> wrote: > On 18 Apr 2018, at 4:47pm, Mark Wagner <m...@google.com> wrote: > > > I have a simple test case wherein I delete from a number of tables and > then > > drop one of those tables. This crashes sqlite3. &g

Re: [sqlite] pragma foreign_key_check

TS3_PARENTHESIS > ENABLE_FTS4 > ENABLE_FTS5 > ENABLE_JSON1 > ENABLE_LOAD_EXTENSION > ENABLE_LOCKING_STYLE=1 > ENABLE_MEMORY_MANAGEMENT > ENABLE_MEMSYS5 > ENABLE_PREUPDATE_HOOK > ENABLE_RBU > ENABLE_RTREE > ENABLE_SESSION > ENABLE_STAT4 > ENABLE_STMTVTAB > EXT

[sqlite] pragma foreign_key_check

The documentation for foreign_key_check says I should be receiving 4 columns per violation. I only seem to be getting 1. Am I doing it wrong? Perhaps it's a version issue? Thanks sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t (id int primary key, value); CREATE TABLE

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

When I saw this post I just assumed there wasn't a sufficient index to handle the select and the order by. Curious about the suggestion of adding + to the order by first term. On Thu, Mar 22, 2018 at 3:14 PM Simon Slavin wrote: > On 22 Mar 2018, at 10:09pm, Jonathan

Re: [sqlite] column types and constraints

Great explanation. Thanks. On Wed, Jun 27, 2018 at 7:43 PM Richard Hipp wrote: > On 6/27/18, Igor Tandetnik wrote: > > On 6/27/2018 9:14 PM, Richard Hipp wrote: > >> On 6/27/18, Mark Wagner wrote: > >>> Thanks for all the good background. FWIW this cam

Re: [sqlite] SQLite for datalogging - best practices

Going back to the comments from Dr. Hipp regarding WAL vs DELETE mode on F2FS devices, I just wanted to confirm my understanding. Given a device with F2FS and with sqlite compiled with SQLITE_ENABLE_BATCH_ATOMIC_WRITE, writes with DELETE mode will be considerably faster than with WAL mode. But a

[sqlite] downloading older versions

I'm trying to download older versions of sqlite to check the behavior of various bugs but I'm having trouble finding them. For example to get SQLite version 3.8.6.1 2017-07-21 03:23:38 I have tried: https://www.sqlite.org/2017/sqlite-tools-linux-x86-3080601.zip Should that have worked? Note

Re: [sqlite] how to

tains 11,12: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > WHERE PContent LIKE '%11,12%' > ; > >-- PSet | PContent | PCount >

[sqlite] how to

Given a table with two columns, A and B, with no constraints what would be the best way to query for those values of A such that there are corresponding values of B in a specified set. For example, given this data, below, and ignoring the primary key, I would want the following results: for p

[sqlite] is this possible

Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); I appear to be able to do this query: select 20 as q, * from t join s on (foo=q); But apparently I cannot do this: sqlite> select 20

[sqlite] why no unique columns on alter table

This is mainly for my curiosity. Is there any particular reason that one can't add a unique column on an alter table? With a default value of null they would all have unique values by default. Any insight into this would be great. Perhaps there' something obvious I'm missing. -- Mark