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 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
On 6/27/2018 10: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. It's not mentioned here though: https://sqlite.org/syntax/column-constraint.html -- Igor Tandetnik ___ 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
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
Re: [sqlite] column types and constraints
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"? -- Igor Tandetnik ___ 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
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
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] insert or replace performance with self "references" column
On Wed, Jun 27, 2018, 7:47 PM Keith Medcalf wrote: > > If you give the parent column a proper affinity (ie, integer) do you get > "happiness making" results? > > --- > 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 Allen > >Sent: Wednesday, 27 June, 2018 16:20 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: [sqlite] insert or replace performance with self > >"references" column > > > >I have a table with an additional index and a query: > > > >"create table Transactions (Id integer primary key not null, Parent > >references Transactions(id), Body varchar);" > > > >"create index Parent_Index on Transactions (Parent);" > > > >EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, > >Body) values (?1, ?2, ?3); > >23 0 0 SCAN TABLE Transactions > >43 0 0 SCAN TABLE Transactions > > > >The double SCAN TABLE seems to have something to do with both the > >"references" column and the "or replace" statement. If I remove > >either, then the SCAN goes away. > > > >Questions: > > > >- Is my syntax for the " insert or replace" statement correct, and > >will it do what I expect (insert a new row with a new auto-generated > >Id if Id is NULL, insert a new row with the given Id if Id is not > >NULL > >and no row exists with that Id, or update the existing row with the > >given Id if Id is not NULL and a row exists with that Id)? > > > >- Is sqlite really doing one or two table scans to perform the > >"insert > >or replace" with a "references" self column? > > > >- If so, is there a way to eliminate this (other than removing either > >the "references" or the "or replace")? > > > >Thanks much. > >___ > >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] 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 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
Re: [sqlite] column types and constraints
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
Re: [sqlite] column types and constraints
On 6/27/2018 6:56 PM, 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. https://sqlite.org/datatype3.html SQLite attempts to be maximally compatible with a wide variety of database engines, and those use all kinds of naming conventions for their data types. SQLite itself doesn't really have column data types - at least, not in the traditional sense. It has a concept of type affinity. To this end, SQLite accepts any sequence of names, optionally followed by one or two numbers in parentheses, as a valid column type: https://sqlite.org/syntax/type-name.html . These names and numbers are largely ignored, except to the extent that a column type affinity is gleaned from them via a simple substring match. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace performance with self "references" column
If you give the parent column a proper affinity (ie, integer) do you get "happiness making" results? --- 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 Allen >Sent: Wednesday, 27 June, 2018 16:20 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] insert or replace performance with self >"references" column > >I have a table with an additional index and a query: > >"create table Transactions (Id integer primary key not null, Parent >references Transactions(id), Body varchar);" > >"create index Parent_Index on Transactions (Parent);" > >EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, >Body) values (?1, ?2, ?3); >23 0 0 SCAN TABLE Transactions >43 0 0 SCAN TABLE Transactions > >The double SCAN TABLE seems to have something to do with both the >"references" column and the "or replace" statement. If I remove >either, then the SCAN goes away. > >Questions: > >- Is my syntax for the " insert or replace" statement correct, and >will it do what I expect (insert a new row with a new auto-generated >Id if Id is NULL, insert a new row with the given Id if Id is not >NULL >and no row exists with that Id, or update the existing row with the >given Id if Id is not NULL and a row exists with that Id)? > >- Is sqlite really doing one or two table scans to perform the >"insert >or replace" with a "references" self column? > >- If so, is there a way to eliminate this (other than removing either >the "references" or the "or replace")? > >Thanks much. >___ >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
In the current tip of trunk it pretends the unknown tokens are surrounded by double-quotes. Until you interpose a non type keyword ... at which point the parser stops "eating your junk as the type declaration" and resumes the grammar .. sqlite> create table x(x happy days); sqlite> pragma table_info(x); 0|x|happy days|0||0 sqlite> create table y(x happy not days); Error: near "days": syntax error sqlite> create table y(x happy integer days); sqlite> pragma table_info(y); 0|x|happy integer days|0||0 sqlite> create table z(x happy dumbledorf the wood elf integer days); sqlite> pragma table_info(z); 0|x|happy dumbledorf the wood elf integer days|0||0 Presumably this is so you can do things like: create table x(x datetime text); -- the affinity of the column is text rather than create table x(x "datetime text"); -- the affinity of the column is text However, AS does not "eat your junk" and you have to use quotes to embed spaces in the column/type string ... sqlite> select x as dingbat french fries from x; Error: near "french": syntax error sqlite> select x as "dingbat french fries" from x; 1 --- 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 Simon Slavin >Sent: Wednesday, 27 June, 2018 17:07 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On 27 Jun 2018, at 11:56pm, 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. > >The usual answer for these things is backward compatiibility. The >bug existed for so long before it was spotted that SQLite3 must >continue to support it. Roll on SQLite4. > >> For example this is accepted without error: CREATE TABLE bar2 (x >happy >> days); > >You can analyse what's actually happening: > >SQLite version 3.22.0 2017-12-05 15:00:17 >sqlite> CREATE TABLE bar2 (x happy days); >sqlite> .headers on >sqlite> .mode column >sqlite> PRAGMA table_info(bar2); >cid nametypenotnull dflt_value pk >-- -- -- -- -- - >- >0 x happy >days 0 0 > >The interpretation is that the first two words are a columnname and >type, and the third word is a column ID, which is not useful. And >there's some sort of formatting bug in the CLI. :-( > >So don't do that. > >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] column types and constraints
On 27 Jun 2018, at 11:56pm, 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. The usual answer for these things is backward compatiibility. The bug existed for so long before it was spotted that SQLite3 must continue to support it. Roll on SQLite4. > For example this is accepted without error: CREATE TABLE bar2 (x happy > days); You can analyse what's actually happening: SQLite version 3.22.0 2017-12-05 15:00:17 sqlite> CREATE TABLE bar2 (x happy days); sqlite> .headers on sqlite> .mode column sqlite> PRAGMA table_info(bar2); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 x happy days 0 0 The interpretation is that the first two words are a columnname and type, and the third word is a column ID, which is not useful. And there's some sort of formatting bug in the CLI. :-( So don't do that. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 bar2 (x happy days); -- Mark ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert or replace performance with self "references" column
I have a table with an additional index and a query: "create table Transactions (Id integer primary key not null, Parent references Transactions(id), Body varchar);" "create index Parent_Index on Transactions (Parent);" EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) values (?1, ?2, ?3); 23 0 0 SCAN TABLE Transactions 43 0 0 SCAN TABLE Transactions The double SCAN TABLE seems to have something to do with both the "references" column and the "or replace" statement. If I remove either, then the SCAN goes away. Questions: - Is my syntax for the " insert or replace" statement correct, and will it do what I expect (insert a new row with a new auto-generated Id if Id is NULL, insert a new row with the given Id if Id is not NULL and no row exists with that Id, or update the existing row with the given Id if Id is not NULL and a row exists with that Id)? - Is sqlite really doing one or two table scans to perform the "insert or replace" with a "references" self column? - If so, is there a way to eliminate this (other than removing either the "references" or the "or replace")? Thanks much. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
