[sqlite] is this possible

[Mark Wagner]

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 as q, * from v;
Error: no such column: q

It's interesting because it allows me to define the view and at that point
it knows nothing about q so I would have assumed it could be "supplied"
later.

Is this just how it is or perhaps my syntax is off?  Or maybe I'm just
confused.

Just curious.

Thanks!

-- Mark
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] why no unique columns on alter table

[Mark Wagner]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to

[Mark Wagner]

Prior to my original message I was playing around with group concat (even
though I know the order is considered arbitrary) but found I couldn't put a
where clause on that column.

Is there anything wrong with this code?

sqlite> select c, group_concat(p) as P from t group by c;
c   P
--  --
1   10,11,12
2   11,12
3   10

BUT...

select c, group_concat(p) as P from t where P='10,11,12' group by c ;


select c, group_concat(p) as P from t where P='10' group by c ;


On Tue, Dec 4, 2018 at 6:04 AM R Smith  wrote:

> I've mixed up the adding orders to make sure they have no affect on the
> outcome - and in the final results examples I've left all the columns so
> you can see what is going on, but you of course need only one of the
> columns in your desired output.
>
>
> CREATE TABLE t (id integer primary key, c, p);
> INSERT INTO t VALUES(1,  1,11);
> INSERT INTO t VALUES(2,  1,12);
> INSERT INTO t VALUES(3,  1,10);
> INSERT INTO t VALUES(4,  2,11);
> INSERT INTO t VALUES(5,  2,12);
> INSERT INTO t VALUES(6,  3,10);
>
> -- Base Query:
> 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
> ;
>
>-- PSet | PContent | PCount
>--  |  | --
>--   1  | 10,11,12 |3
>--   2  |   11,12  |2
>--   3  |10|1
>
>
> -- Example one - finding the set that contains all of 10,11,12 and
> nothing else:
> 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 = '10,11,12'
> ;
>
>-- PSet |   PContent   |PCount
>--  |  | 
>--   1  |   10,11,12   |   3
>
>
> -- Example two: Finding any set that contains 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
>--  |  | --
>--   1  | 10,11,12 |3
>--   2  |   11,12  |2
>
>
>
> On 2018/12/04 6:17 AM, Mark Wagner wrote:
> > 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 values 10,11,12 ==> 1
> > for p values 11,12 ==> 2
> > for p values 10 ==> 3
> >
> > For all other "input" we should get no result/null/whatever.
> >
> > CREATE TABLE t (id integer primary key, c, p);
> >
> > INSERT INTO t VALUES(1,  1,10);
> >
> > INSERT INTO t VALUES(2,  1,11);
> >
> > INSERT INTO t VALUES(3,  1,12);
> >
> > INSERT INTO t VALUES(4,  2,11);
> >
> > INSERT INTO t VALUES(5,  2,12);
> >
> > INSERT INTO t VALUES(6,  3,10);
> >
> >
> > For all other "input" we should get no result/null/whatever.
> >
> > I can concoct a query based on the "input" like this but it seems like
> > there must be a better way?
> >
> > SELECT DISTINCT c as C FROM t WHERE
> >   EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
> > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
> > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)
> >
> > AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to

[Mark Wagner]

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 values 10,11,12 ==> 1
for p values 11,12 ==> 2
for p values 10 ==> 3

For all other "input" we should get no result/null/whatever.

CREATE TABLE t (id integer primary key, c, p);

INSERT INTO t VALUES(1,  1,10);

INSERT INTO t VALUES(2,  1,11);

INSERT INTO t VALUES(3,  1,12);

INSERT INTO t VALUES(4,  2,11);

INSERT INTO t VALUES(5,  2,12);

INSERT INTO t VALUES(6,  3,10);


For all other "input" we should get no result/null/whatever.

I can concoct a query based on the "input" like this but it seems like
there must be a better way?

SELECT DISTINCT c as C FROM t WHERE
 EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)

AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite for datalogging - best practices

[Mark Wagner]

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 relatively long lived transaction that contains a significant amount
of computation with lots of reads and writes would still block reads on
other threads.  So WAL could still be the better choice in some
circumstances -- even with F2FS since reads can be happening in parallel.
Am I missing something?

On Mon, Oct 29, 2018 at 8:58 PM Keith Medcalf  wrote:

>
> If you don't mind me asking, what sort of data are you collecting?
> Are you the master (ie, scanning) or a slave (getting async data pushed to
> you).
> Are you "compressing" the returned data (storing only changes exceeding
> the deadband) or are you storing every value (or is the source instrument
> doing compression)?
>
> I presume you need to store the TimeStamp, Point, Value and Confidence.
> What is the data rate (# Points and Frequency)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] downloading older versions

[Mark Wagner]

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 that I've tried various "year"
components on the path as well.

-- Mark
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlidiff --schema question

[Mark Wagner]

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 TABLE t (foo text unique);
>
> $ echo .schema | sqlite3 /tmp/f2.db
> CREATE TABLE t (foo text);
>
> $ sqldiff --schema /tmp/f1.db /tmp/f2.db
> $
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlidiff --schema question

[Mark Wagner]

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
$
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Mark Wagner]

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 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.
> >>
> >> It should have.  I get an error when I type:
> >
> > Note the typo: "non null" where "not null" was meant. This creates a
> column
> > with type "non". I'm not sure why "null" is accepted though - no path
> > through syntax diagram seems to allow it at that spot. Perhaps there's an
> > undocumented column constraint "NULL", to complement "NOT NULL"?
>
> "NULL" without the "NOT" is a valid constraint.  So the datatype is
> "NON" and it has a "NULL" constraint, meaning is able to hold NULL
> (the default).
>
> This works on PosgreSQL, MySQL, and Oracle, for example:
>
>  CREATE TABLE t1(x INT NULL);
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Mark Wagner]

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 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.
>
> It should have.  I get an error when I type:
>
> CREATE TABLE t1(x NOT NULL);
> INSERT INTO t1(x) VALUES(NULL);
>
> I think something else must be going on.  Do you have an exact copy of
> what "someone" typed?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column types and constraints

[Mark Wagner]

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 Wagner  wrote:
> > 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 bar2 (x happy
> > days);
>
> In the early days of SQLite, the goal was to get it to parse the
> CREATE TABLE statements of as many different SQL engines as possible.
> I looked at the supported datatypes of contemporary engines, and they
> were all different.  So to maximize compatibility, I made the decision
> to mostly ignore the "type" and accept any sequence of identifiers as
> the type.  The actual type used it computed according to the following
> rules, in order:
>
> (1) If the type name contains "INT" then use INTEGER
> (2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT
> (3) If the type name contains "BLOB" then use BLOB
> (4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL
> (5) Otherwise use NUMERIC
>
> Those rules are defined here:
> https://www.sqlite.org/datatype3.html#affname
>
> This flexible type-name arrangement works because SQLite is very
> forgiving about you putting non-proscribed values into columns - it
> tries to convert if it can do so without loss of information but if it
> cannot do a reversible type conversion it simply stores whatever you
> give it.  Hence if you store a string '3456' into an INT column, it
> converts the string into an integer, but if you store a string 'xyzzy'
> in an INT column it will actually store the string value.
>
> After the above decisions were made, SQLite became the most widely
> used database engine on the planet and over a trillion SQLite database
> files got created, and now we need to stick with that original idea
> lest we cause compatibility issues for all that legacy.
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] column types and constraints

[Mark Wagner]

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 bar2 (x happy
days);

-- Mark
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Mark Wagner]

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...
>
> Sometimes you pay the price for taking a risk. :)
>
> > create table t (k integer primary key, s text UNIQUE);
>
> Did you by any chance assume the "UNIQUE" constraint to cover both the
> fields k and s? It applies of course only to s here. If you wanted it to
> apply to both, the schema should read:
> create table t(k integer primary key, s text, unique(k,s) );
>
> (I'm just trying to guess at what caused the confusion - even if you do
> change to this schema, the uniqueness constraint will pass even better
> than before, since the primary key is by definition unique and
> automatically increments, so any key in which it appears MUST also be
> unique for every value in it, even if you added 'bar' ten times in a
> row, it will never fail.)
>
> > insert into t (s) values ("foo");
> > insert into t (s) values ("bar");
> > select * from t;
> > k   s
> > --  --
> > 1   foo
> > 2   bar
>
> foo and bar here (which should be quoted like 'foo' and 'bar' by the
> way, double-quotes are for identifiers) are very unique, so cannot
> possibly constitute a uniqueness violation.
>
> >
> > Adding to my confusion is this:
> >
> > insert into t values (3, "bar");
> > Error: UNIQUE constraint failed: t.s
>
> whereas 'bar' here definitely is not unique (there is already a 'bar' in
> there) and thus fails.
>
> I've always found the higher level languages to be slightly less than
> intuitive, since they try to "read like English", except the language is
> nuanced and often what something seems to mean in English is not what it
> really means in the computed sense. That means that a statement fragment
> like UNIQUE needs as much documentation as, for instance, the lower
> level assembly language MOV command, if not more, because MOV left you
> with zero assumptions, you had to check the docs to see what it did, and
> even then, it delightfully did the very minimum.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique constraint

[Mark Wagner]

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 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 integer primary key, s text UNIQUE);
> > insert into t (s) values ("foo");
> > insert into t (s) values ("bar");
>
> You appear to be confused.  How does the second INSERT violate the UNIQUE
> constraint ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unique constraint

[Mark Wagner]

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 integer primary key, s text UNIQUE);
insert into t (s) values ("foo");
insert into t (s) values ("bar");
select * from t;
k   s
--  --
1   foo
2   bar

Adding to my confusion is this:

insert into t values (3, "bar");
Error: UNIQUE constraint failed: t.s

3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] crash dropping table

[Mark Wagner]

I will try to produce a repro case that I can share out (the database I was
testing on has lots of personal data that I will first try to delete).
Thanks for the responses.

@David Note that those weren't the precise commands (i.e. drop command was
"drop table x;").



On Wed, Apr 18, 2018 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.
> >
> > 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 for
> > things like this?
>
> Can you make the same problem happen in the SQLite CLI tool ?  If so,
> please post a full script here.  If not, there's a possibility that the
> problem is somewhere in your own software.
>
> If it's in your software, are you checking the return values from the
> first four commands in your list ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] crash dropping table

[Mark Wagner]

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 for
things like this?

SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-7.3.0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Mark Wagner]

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 Moules 
> wrote:
>
> > Sure; I didn't include them because the only difference is the last
> line, and that just seems to be the standard "ordering" line. I figured the
> explain was more useful as a lot has changed in that.
>
> I find EXPLAIN QUERY PLANs easier to read (probably unlike the development
> team who understand things at the level of EXPLAIN).
>
> The situation as you describe it in the above post is that your query gets
> /slower/ when you add an ORDER BY clause.  This is not expected, and does
> not suggest anything wrong with SQLite.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma foreign_key_check

[Mark Wagner]

That's what I get for just typing in to sqlite3.  Yes, with the unique
constraint it works.  Thanks!


On Sat, Mar 17, 2018 at 12:53 PM Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Nor does there appear to be "column names" ... and this with the current
> tip of the trunk.
>
> SQLite version 3.23.0 2018-03-16 07:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> PRAGMA foreign_keys=OFF;
> sqlite> BEGIN TRANSACTION;
> sqlite> CREATE TABLE t (id int primary key, value);
> sqlite> CREATE TABLE t1 (id integer primary key, v references t(value));
> sqlite> INSERT INTO t1 VALUES(1,1);
> sqlite> COMMIT;
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma foreign_key_check(t1);
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma foreign_key_check(t);
> sqlite> .head on
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> .mode col
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> .head on
> sqlite> select * from pragma_foreign_key_check;
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> select * from pragma_foreign_key_check();
> Error: foreign key mismatch - "t1" referencing "t"
> sqlite> pragma compile_options;
> compile_options
> -
> ALLOW_COVERING_INDEX_SCAN
> ALLOW_URI_AUTHORITY
> COMPILER=gcc-7.2.0
> DEFAULT_CACHE_SIZE=262144
> DEFAULT_FOREIGN_KEYS
> DEFAULT_MMAP_SIZE=0
> DEFAULT_PAGE_SIZE=4096
> DEFAULT_PROXYDIR_PERMISSI
> DEFAULT_RECURSIVE_TRIGGER
> DEFAULT_WAL_AUTOCHECKPOIN
> DEFAULT_WAL_SYNCHRONOUS=1
> ENABLE_8_3_NAMES=1
> ENABLE_API_ARMOR
> ENABLE_COLUMN_METADATA
> ENABLE_COSTMULT
> ENABLE_CURSOR_HINTS
> ENABLE_DBSTAT_VTAB
> ENABLE_FTS3
> ENABLE_FTS3_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
> EXTRA_INIT=core_init
> HAVE_ISNAN
> LIKE_DOESNT_MATCH_BLOBS
> MAX_ATTACHED=15
> SOUNDEX
> STAT4_SAMPLES=64
> TEMP_STORE=2
> THREADSAFE=1
> USE_URI
> WIN32_MALLOC
> sqlite>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner
> >Sent: Saturday, 17 March, 2018 12:51
> >To: SQLite mailing list
> >Subject: [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 t1 (id integer primary key, v references t(value));
> >INSERT INTO t1 VALUES(1,1);
> >COMMIT;
> >sqlite>
> >sqlite> pragma foreign_key_check;
> >Error: foreign key mismatch - "t1" referencing "t"
> >sqlite>
> >sqlite> pragma foreign_key_check(t1);
> >Error: foreign key mismatch - "t1" referencing "t"
> >sqlite>
> >sqlite> .version
> >SQLite 3.20.1 2017-08-24 16:21:36
> >8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma foreign_key_check

[Mark Wagner]

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 t1 (id integer primary key, v references t(value));
INSERT INTO t1 VALUES(1,1);
COMMIT;
sqlite>
sqlite> pragma foreign_key_check;
Error: foreign key mismatch - "t1" referencing "t"
sqlite>
sqlite> pragma foreign_key_check(t1);
Error: foreign key mismatch - "t1" referencing "t"
sqlite>
sqlite> .version
SQLite 3.20.1 2017-08-24 16:21:36
8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple table constraints

[Mark Wagner]

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 accepted
> >
> > CREATE TABLE foo(_id primary key, x, y, unique(x), unique(y));
> > CREATE TABLE foo(_id primary key, x, y, unique(x) unique(y));
> >
> > Just curious if this is some historical artifact or if there's some
> > difference between the two that I'm not aware of.
>
> This appears to be an historical artifact.
>
> A quick spot-check shows that both forms are accepted and appear to
> work going back to SQLite version 3.0.0 (2004-06-18).  But this is not
> something that has been part of our test suite, so you should strive
> to use only the first (correct) form.
>
> I would fix this parser problem, except there are literally over a
> trillion SQLite database files in circulation, and even if only 0.001%
> of those use the incorrect second form, that still means millions of
> database files out there that would break if we "fix" it.  Hence, I
> won't document the second form as valid syntax, but I will add test
> cases to make sure the second form continues to be accepted, to ensure
> future compatibility.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multiple table constraints

[Mark Wagner]

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) unique(y));

Just curious if this is some historical artifact or if there's some
difference between the two that I'm not aware of.

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about covering index

[Mark Wagner]

gt; >31Return 6 0 000
> >32IfPos  4 34000  if r[4]>0
> >then r[4]-=0, goto 34; Groupby result generator entry point
> >33Return 6 0 000
> >34Copy   1 12100
> >r[12..13]=r[1..2]
> >35Copy   3 11000  r[11]=r[3]
> >36MakeRecord 113 15   00
> >r[15]=mkrec(r[11..13])
> >37SorterInsert   1 15113  00  key=r[15]
> >38Return 6 0 000  end groupby
> >result generator
> >39Null   0 1 300
> >r[1..3]=NULL
> >40Return 7 0 000
> >41OpenPseudo 4 16500  5 columns
> >in r[16]
> >42SorterSort 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
> >46  Column 4 1 12   00  r[12]=_id
> >47  ResultRow  123 000
> >output=r[12..14]
> >48SorterNext 1 43000
> >49Halt   0 0 000
> >50Transaction0 0 4 0  01
> >usesStmtJournal=0
> >51Goto   0 1 000
> >s
> >
> >
> >---
> >The fact that there's a Highway to Hell but only a Stairway to Heaven
> >says a lot about anticipated traffic volume.
> >
> >
> >>-Original Message-
> >>From: sqlite-users [mailto:sqlite-users-
> >>boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner
> >>Sent: Tuesday, 6 February, 2018 17:44
> >>To: SQLite mailing list
> >>Subject: [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 order by.  Any clue why it doesn't or what I could do
> >differently
> >>to
> >>get it to use an index for the selection, the grouping, and the
> >>ordering?
> >>
> >>selectid = 0
> >>   order = 0
> >>from = 0
> >>  detail = SCAN TABLE foo
> >>
> >>selectid = 0
> >>   order = 0
> >>from = 0
> >>  detail = USE TEMP B-TREE FOR ORDER BY
> >>___
> >>sqlite-users mailing list
> >>sqlite-users@mailinglists.sqlite.org
> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about covering index

[Mark Wagner]

OK, I oversimplified trying to make it easier.

The real query has a join so I'm aggregating some of the columns.  But this
test case seemed to show the issue.  I could show something closer to what
I'm really doing if that explanation isn't sufficient.



On Tue, Feb 6, 2018 at 4:48 PM, Simon 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> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER
> > BY y;
>
> Why are you grouping on the primary key ?  Primary key values must be, by
> definition, unique.  Grouping by a unique value means every group has one
> entry.
>
> There's a similar problem with the index you created.  Since the primary
> key is first, there's no point in having the x and y in the index.
> Therefore there's no point in having the index since it just duplicates the
> primary key index for the table.
>
> I suspect that SQLite is acting weird because you fed it with weird things.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] question about covering index

[Mark Wagner]

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 order by.  Any clue why it doesn't or what I could do differently to
get it to use an index for the selection, the grouping, and the ordering?

selectid = 0
   order = 0
from = 0
  detail = SCAN TABLE foo

selectid = 0
   order = 0
from = 0
  detail = USE TEMP B-TREE FOR ORDER BY
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Mark Wagner]

My use case was this.  For some given input, find which of those input
values do not have corresponding rows in a given table.

In other words something like this (but values seemed easier).


select '1' as x union select '2' as x union select '3 as x where x not in
(select id from foo);

Picture the 1,2,3 as some form of input which requires further processing
if we don't have rows for them.

Perhaps there's a better way to do this that I'm not thinking of.


On Tue, Dec 12, 2017 at 11:07 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> Nifty... but... With no option for "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...@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 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?
> > >
> > > It probably means you are using an older version of SQLite.  The
> > > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Mark Wagner]

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 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?
>
> It probably means you are using an older version of SQLite.  The
> syntax you describe as introduced in version 3.8.3 (2014-02-03).
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] values ?

[Mark Wagner]

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> values('foo', 'bar');
Error: near "values": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key constraint failure

[Mark Wagner]

Thanks for the clever ideas.  In my case I figured it out by hand (it was a
trigger which was inserting a row with a foreign key into another table
that no longer existed).  But I will make use of those strategies in the
future.


On Tue, May 9, 2017 at 11:54 PM, Gwendal Roué <gwendal.r...@gmail.com>
wrote:

> There is a way, but it requires some effort:
>
> First let's define a schema that reproduces your error:
>
> CREATE TABLE t1 (
>   id INTEGER PRIMARY KEY);
> CREATE TABLE t2 (
>   id INTEGER PRIMARY KEY,
>   id1 INTEGER REFERENCES t1(id) ON DELETE RESTRICT);
> INSERT INTO t1 (id) VALUES (123);
> INSERT INTO t2 (id, id1) VALUES (456, 123);
>
> -- error: FOREIGN KEY constraint failed
> DELETE FROM t1
>
> OK, error is reproduced.
>
> Now you want to know which foreign key has failed:
>
> PRAGMA foreign_keys = OFF;
> BEGIN TRANSACTION;
> DELETE FROM t1 -- no error this time
> PRAGMA foreign_key_check
> -- table:"t2" rowid:456 parent:"t1" fkid:0
>
> This means that row 456 of table t2 has a broken foreign to table t1.
>
> If you want to know which row in t1 can not be deleted:
>
> PRAGMA foreign_key_list(t2)
> -- id:0 seq:0 table:"t1" from:"id1" to:"id" on_update:"NO ACTION"
> on_delete:"RESTRICT" match:"NONE"
>
> OK so id1 in 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:
>
> ROLLBACK
> PRAGMA foreign_keys = ON
>
> Gwendal Roué
>
> > Le 10 mai 2017 à 06:57, Mark Wagner <m...@google.com> a écrit :
> >
> > 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-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign key constraint failure

[Mark Wagner]

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-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] column type impact on index usage

[Mark Wagner]

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.  Note that in the case when the columns are both
integer an index is used and when one is integer and one is not specified
no index is used.

Any thoughts on this?

Thanks.

-- Mark

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seq (value integer);
CREATE TABLE bar (value integer, unique(value));
CREATE TABLE bar1 (value, unique(value));
COMMIT;
sqlite>
sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar1 ON
seq.value = bar1.value;
0|0|0|SCAN TABLE seq
0|1|1|SCAN TABLE bar1
sqlite>
sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar ON
seq.value = bar.value;
0|0|0|SCAN TABLE seq
0|1|1|SEARCH TABLE bar USING COVERING INDEX sqlite_autoindex_bar_1 (value=?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users