Default Values Pragma bug

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Default Values Pragma bug

R Smith-2
This is a small niggle, perhaps not worthy of the term "bug".

The Default values when specified in a CREATE TABLE statement will
include C-style commented text in the output of the pragma table_info()
(or its t.v.f. derivative) while the actual default value handling will
parse it out.

Minimal demonstration:

   -- SQLite version 3.24.0  [ Release: 2018-06-04 ]  on SQLitespeed
version 2.1.1.16.
   --
================================================================================================

CREATE TABLE t(
   a INTEGER PRIMARY KEY /* This is the PK */,
   b INT DEFAULT 10      /* The Int val   */,
   c TEXT DEFAULT 'Ten'  /* The Text val */
);

INSERT INTO t DEFAULT VALUES;

SELECT * FROM t;

   --       a      |       b      |  c
   -- ------------ | ------------ | ---
   --       1      |      10      | Ten   <-- Correct


PRAGMA table_info(t);

   -- cid | name | type    | notnull | dflt_value                  |  pk
   -- --- | ---- | ------- | ------- | --------------------------- | ---
   --  0  |   a  | INTEGER |    0 |                             |  1
   --  1  |   b  | INT     |    0    | 10      /* The Int val   */ |  0 <--
   --  2  |   c  | TEXT    |    0    | 'Ten'  /* The Text val */   | 
0   <-- Not correct


SELECT name, dflt_value FROM pragma_table_info('t');

   -- name | dflt_value
   -- ---- | ---------------------------
   --   a  |
   --   b  | 10      /* The Int val   */  <--
   --   c  | 'Ten'  /* The Text val */    <-- Not correct

DROP TABLE t;

   --
------------------------------------------------------------------------------------------------

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Default Values Pragma bug

E.Pasma
Hello Ryan,

Your already moderate complaint needs further moderation
After reading  https://www.sqlite.org/lang_createtable.html#dfltval <https://www.sqlite.org/lang_createtable.html#dfltval> I see that the default value may be a function name (when written inside parenthesis) or a special name like current_date.
So it is not just a constant and needs parsing anyway.
This may make it better acceptable as it is, with comments included in the output of pragma table_info().

E. Pasma

PS there is a small typing mistake in above document:
..constant if it does contains
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users