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 a
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
| 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 LI
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 val
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
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 t
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
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
$
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
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
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
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
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 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
I am confused. Too much experimenting :(
On Tue, May 15, 2018 at 4:55 PM Simon Slavin wrote:
> On 16 May 2018, at 12:25am, Mark Wagner wrote:
>
> > I'm wondering if
> > someone can explain why this simple test of unique column constraints
> > doesn't wo
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 integ
8, 2018 at 9:56 AM Simon Slavin wrote:
> On 18 Apr 2018, at 4:47pm, Mark Wagner 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:
>
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 fo
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
ESIS
> 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
>
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 t
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 wrote:
> On 3/7/18, Mark Wagner wrote:
> >
> > e.g. both are accepted
> >
> > CREATE TABLE foo(_i
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) un
orterSort 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:48 PM, Simon Slavin wrote:
> On 7 Feb 2018, at 12:43am, Mark Wagner 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 * FRO
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
no option for "where" or "order by", where would this
> come in useful?
>
> On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner wrote:
>
> > Argh. Yes, I was on 3.8.2. Thanks!
> >
> > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp wrote:
> >
>
Argh. Yes, I was on 3.8.2. Thanks!
On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp wrote:
> On 12/12/17, Mark Wagner 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'); a
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'
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
>
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.sqli
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.
31 matches
Mail list logo