Bug in table_info pragma

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

Bug in table_info pragma

J. King-3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a text default '' /* comment */ );
sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
'' /* comment */

I would expect it to print only the string delimiters.

--
J. King
_______________________________________________
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: Bug in table_info pragma

Simon Slavin-3
On 17 May 2019, at 11:55am, J. King <[hidden email]> wrote:

> I would expect it to print only the string delimiters.

I might expect it to print only the thing inside the delimiters, i.e. nothing.
_______________________________________________
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: Bug in table_info pragma

J. King-3
On 2019-05-17 06:59:58, "Simon Slavin" <[hidden email]> wrote:

>On 17 May 2019, at 11:55am, J. King <[hidden email]> wrote:
>
>>  I would expect it to print only the string delimiters.
>
>I might expect it to print only the thing inside the delimiters, i.e. nothing.

Then there would be no differentiating "default CURRENT_TIMESTAMP" from
"default 'CURRENT_TIMESTAMP'".

--
J. King
_______________________________________________
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: Bug in table_info pragma

Warren Young
In reply to this post by J. King-3
On May 17, 2019, at 4:55 AM, J. King <[hidden email]> wrote:

>
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t(a text default '' /* comment */ );
> sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
> '' /* comment */
>
> I would expect it to print only the string delimiters.

Isn’t this the mechanism behind the .schema shell command, which just gives a textual dump of the schema as input, including whitespace, commas, and no interpretation made on the types you give?

In other words, SQLite doesn’t “digest” your schema and spit out a clean version, it just tells you what you told it.
_______________________________________________
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: Bug in table_info pragma

J. King-3
On 2019-05-17 07:08:27, "Warren Young" <[hidden email]> wrote:

>On May 17, 2019, at 4:55 AM, J. King <[hidden email]> wrote:
>>
>>  SQLite version 3.28.0 2019-04-16 19:49:53
>>  Enter ".help" for usage hints.
>>  Connected to a transient in-memory database.
>>  Use ".open FILENAME" to reopen on a persistent database.
>>  sqlite> create table t(a text default '' /* comment */ );
>>  sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
>>  '' /* comment */
>>
>>  I would expect it to print only the string delimiters.
>
>Isn’t this the mechanism behind the .schema shell command, which just gives a textual dump of the schema as input, including whitespace, commas, and no interpretation made on the types you give?
>
>In other words, SQLite doesn’t “digest” your schema and spit out a clean version, it just tells you what you told it.

Perhaps I should have been clearer that this is a regression?

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a text default '' /* comment */ );
sqlite> pragma table_info(t);
0|a|text|0|''|0

--
J. King
_______________________________________________
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: Bug in table_info pragma

Simon Slavin-3
In reply to this post by J. King-3
On 17 May 2019, at 12:06pm, J. King <[hidden email]> wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, and store that string in the schema.

Neither of those are the result that programmers would normally want. I withdraw my suggestion.  Thanks for the heads-up.
_______________________________________________
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: Bug in table_info pragma

Nelson, Erik - 2
We need to get these into the hive database, if that's helpful
________________________________
From: Simon Slavin <[hidden email]>
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King <[hidden email]> wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, and store that string in the schema.

Neither of those are the result that programmers would normally want. I withdraw my suggestion.  Thanks for the heads-up.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c&s=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw&e=

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
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: Bug in table_info pragma

Nelson, Erik - 2
Please disregard, apologies for the noise.
________________________________
From: "Nelson, Erik - 2" <[hidden email]>
Sent: May 17, 2019 8:17 AM
To: [hidden email]
Subject: [External email from [hidden email]] Re: [sqlite] Bug in table_info pragma

We need to get these into the hive database, if that's helpful
________________________________
From: Simon Slavin <[hidden email]>
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King <[hidden email]> wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, and store that string in the schema.

Neither of those are the result that programmers would normally want. I withdraw my suggestion.  Thanks for the heads-up.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c&s=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw&e=

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=PJUZ-wgC3TvfU5DSVDpHM4-k2nn_x52ElGT8LAh017k&s=E1KU-A0zRaYOwwt9DyisbuiP0m-f_SaufevUhT-XhNo&e=

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
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: Bug in table_info pragma

Jose Isaias Cabrera-4
In reply to this post by J. King-3

J. King, on Friday, May 17, 2019 07:19 AM, wrote...
>Perhaps I should have been clearer that this is a regression?
>
>SQLite version 3.13.0 2016-05-18 10:57:30
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table t(a text default '' /* comment */ );
>sqlite> pragma table_info(t);
>0|a|text|0|''|0

I am not disagreeing with you about the bug.  That is a bug.  The default value should be an empty string ('').  But, it does works on the original intension,

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>  create table t(a text, b text default '' /* comment */ );
sqlite> insert into t (a) values ('hi');
sqlite> select * from t;
hi|

and further more,

sqlite> insert into t (a,b) values ('hi');
Error: 1 values for 2 columns
sqlite> create table t0(a text, b text default 'bye' /* comment */ );
sqlite> insert into t0 (a) values ('hi');
sqlite> select * from t0;
hi|bye

But you are right, it should not display,

sqlite> select dflt_value from pragma_table_info('t') where name = 'b';
'' /* comment */

and furthermore,

sqlite> select dflt_value from pragma_table_info('t0') where name = 'b';
'bye' /* comment */

Thanks.

josé
_______________________________________________
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: Bug in table_info pragma

Clemens Ladisch
In reply to this post by Simon Slavin-3
Simon Slavin wrote:
> If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to
> evaluate CURRENT_TIMESTAMP, find a string value like
> '2019-05-17 12:10:43', and store that string in the schema.

This keyword behaves magically.  ANSI SQL-92 says:
| The default value inserted in the column descriptor ... is as
| follows:
| Case:
| a) If the <default clause> contains NULL, then the null value.
| b) If the <default clause> contains a <literal>, then
|    Case:
|    i) If the subject data type is numeric, then the numeric value
|       of the <literal>.
| [...]
| d) If the <default clause> contains a <datetime value function>,
|    then the value of an implicit reference to the <datetime
|    value function>.


Regards,
Clemens
_______________________________________________
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: Bug in table_info pragma

Simon Slavin-3
On 17 May 2019, at 1:33pm, Clemens Ladisch <[hidden email]> wrote:

> This keyword behaves magically.

Mmmm.  In that case, to implement this properly you need to store a default-type flag alongside the default value.  Proposed values might be

0) No default specified, so use …
1) Fixed default specified, so use …
2) Magic keyword supplied, so evaluate …
_______________________________________________
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: Bug in table_info pragma

Clemens Ladisch
Simon Slavin wrote:
> On 17 May 2019, at 1:33pm, Clemens Ladisch <[hidden email]> wrote:
>> This keyword behaves magically.

... as far as the SQL standard is concerned.

> Mmmm.  In that case, to implement this properly you need to store
> a default-type flag alongside the default value.

The SQLite syntax diagrams treat it as "literal-value":
<https://www.sqlite.org/syntax/column-constraint.html>

SQLite's actual in-memory representation of default values is an expression
tree; _all_ DEFAULT expressions are evaluated lazily.

(SQL-92 does not allow arbitrary expressions as default values.)


Regards,
Clemens
_______________________________________________
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: Bug in table_info pragma

Jose Isaias Cabrera-4
In reply to this post by Jose Isaias Cabrera-4
Jose Isaias Cabrera, on Friday, May 17, 2019 08:28 AM, wrote...
>J. King, on Friday, May 17, 2019 07:19 AM, wrote...
>>Perhaps I should have been clearer that this is a regression?
>>

I know, overkill, but here is another look at it,

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0
   ...> (
   ...> a text,
   ...> b text default 'bye'
   ...> /* comment */
   ...> );
sqlite> create table t1
   ...> (
   ...> a text default 'hi', -- this is a comment for a
   ...> b text default 'bye', /* this is a comment for b */
   ...> c int default 0
   ...> /* this is a comment for c */
   ...> );
sqlite>
sqlite> create table t2
   ...> (
   ...> a text default 'hi', /* this is a comment for c */
   ...> b text default 'bye', /* this is a comment for b */
   ...> c INTEGER default 0
   ...> -- this is a comment for c
   ...> );
sqlite> .schema
CREATE TABLE t0
(
a text,
b text default 'bye'
/* comment */
);
CREATE TABLE t1
(
a text default 'hi', -- this is a comment for a
b text default 'bye', /* this is a comment for b */
c int default 0
/* this is a comment for c */
);
CREATE TABLE t2
(
a text default 'hi', /* this is a comment for c */
b text default 'bye', /* this is a comment for b */
c INTEGER default 0
-- this is a comment for c
);
sqlite> insert into t0 (a) values ('Hi');
sqlite> select * from t0;
Hi|bye
sqlite> insert into t1 (a) values ('Hi');
sqlite> select * from t1;
Hi|bye|0
sqlite> insert into t2 (a) values ('Hi');
sqlite> select * from t2;
Hi|bye|0
sqlite>
sqlite> select dflt_value from pragma_table_info('t1') where name = 'a';
'hi'
sqlite> select dflt_value from pragma_table_info('t1') where name = 'b';
'bye'
sqlite> select dflt_value from pragma_table_info('t1') where name = 'c';
0
/* this is a comment for c */
sqlite> select dflt_value from pragma_table_info('t2') where name = 'a';
'hi'
sqlite> select dflt_value from pragma_table_info('t2') where name = 'b';
'bye'
sqlite> select dflt_value from pragma_table_info('t2') where name = 'c';
0
-- this is a comment for c
sqlite>

Just found it interesting that in t1 and t2, column c, even though it is an INT, or INTEGER with default 0, and a new line, it still shows the comment. Yes, I know that they are all treated as text. :-)  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users