[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/06/2016 10:33 AM, Richard Hipp wrote: > On 4/4/16, Mike Bayer wrote: >> The "type" column in PRAGMA table_info() is now a blank string when the >> target object is a view in 3.12.0. In 3.11.0 and prior versions, the >> typing information is returned, >> > > I think the problem is fixed by this checkin: > https://www.sqlite.org/src/info/fb555c3c2af7f5e6 > > Please confirm. Can confirm my user's issue is resolved by version 3.12.1 thanks for the quick turnaround! >
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-06 16:33, Richard Hipp wrote: > On 4/4/16, Mike Bayer wrote: >> The "type" column in PRAGMA table_info() is now a blank string when the >> target object is a view in 3.12.0. In 3.11.0 and prior versions, the >> typing information is returned, > I think the problem is fixed by this checkin: > https://www.sqlite.org/src/info/fb555c3c2af7f5e6 Perfunctory test showed that it works fine with plain 3.12.0 ([e9bb4cf4] Version 3.12.0 (user: drh, tags: trunk, release, version-3.12.0)) --- does not require intermediate check--ins. It goes even farther then 3.11.x because it keeps column's declared type when view's column name has changed (CREATE VIEW v(newname) ...). Thank you. -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On Wed, 6 Apr 2016 06:13:01 +
Hick Gunter wrote:
> You are hopefully aware of the fact that SQLite associates type with
> the actual values and not the containers(columns) used to hold these
> values? This means that a data object of any type may be
> held/returned in a column, irrespective of the declared type (which,
> for expressions, is NULL).
Yes, but some of us are careful to include CHECK constraints to enforce
type-checking. Even when not that careful, many follow the practice of
restricting each column to a single type.
> What would your date routine do with the string 12.17.9.17.15?
It would never see it. Problem solved by prevention:
sqlite> create table T(t TEXT not NULL
check (strftime('%m', t) is not NULL));
sqlite> insert into T values ('2016-04-06');
sqlite> insert into T values ('2016-14-06');
Error: CHECK constraint failed: T
sqlite> insert into T values ('12.17.9.17.15');
Error: CHECK constraint failed: T
--jkl
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On Tue, 5 Apr 2016 13:19:50 -0400
Richard Hipp wrote:
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
It should report it as for a table, with values consistent with how
SQLite will treat the column.
A fundamental rule for views is that -- for purposes of SELECT -- they
*are* tables. The fact that tables have physical representation in the
database and views are derived is, er, immaterial to the relational
algebra implemented by the DBMS.
--jkl
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
+1 for INFORMATION SCHEMA ! > Wed Apr 06 2016 04:18:10 PM CEST from "Michael Schlenker" > Subject: Re: [sqlite] regression in 3.12.0 vs. >3.11.0,column type information in PRAGMA missing > > > btw. would be nice to have an INFORMATION SCHEMA style view for this > info instead of a pragma. > > Michael > >
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hi, Am 06.04.2016 um 15:00 schrieb Cezary H. Noweta: > Hello, > > On 2016-04-06 09:43, Darren Duncan wrote: >> On 2016-04-05 10:19 AM, Richard Hipp wrote: > >>> It seems to me that the most consistent answer is that the "type" of >>> columns in a VIEW should always be an empty string. > >> That's only consistent if you do the same thing with base tables. > > Non--consistency: [snip] > > Column's affinity is still propagated: > > sqlite> CREATE TABLE b2 AS SELECT * FROM bv; > sqlite> PRAGMA table_info(b2); > 0|a|NUM|0||0 > > so the point is that ``PRAGMA table_info'' stopped displaying column's > affinity in case of views. > > On the other side, views are not tables so a consistency does not > require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not > displaying an affinity in case of expressions, too: > > === SHELL 3.11.x === > sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a; > sqlite> PRAGMA table_info(av2); > 0|CAST(+a AS NUMERIC)||0||0 > > so the decision was to remove that view's info at all. we have been bitten by this case, e.g. aggregate and expression not giving any meaningful info for views. But this change to PRAGMA table_info() throws out the child with the bathwater in a way. E.g. we currently try to find the types for a view in a multistage process: 1. Inspect PRAGMA table_info() 2. Look in a special hint table that states the type explicitly via configuration (e.g. for views with aggregate functions) 3. Select a row with LIMIT 1 and look at the result (fails if the view is empty). With the old behaviour, the case 1 would find a reliable result 99% of the time (for our schema). Now we either have to add additional configuration just for SQLite (because similar code works fine for Oracle and MS SQL Server), or hope that case 3 works. btw. would be nice to have an INFORMATION SCHEMA style view for this info instead of a pragma. Michael -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321 -- Michael Schlenker Senior Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Stra?e 1-3 Fax:+49 (421) 20153-41 28359 Bremen E-Mail: michael.schlenker at contact-software.com http://www.contact-software.com/ Registered office: Bremen, Germany Managing directors: Karl Heinz Zachries, Ralf Holtgrefe Court of register: Amtsgericht Bremen HRB 1321
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-06 09:43, Darren Duncan wrote: > On 2016-04-05 10:19 AM, Richard Hipp wrote: >> It seems to me that the most consistent answer is that the "type" of >> columns in a VIEW should always be an empty string. > That's only consistent if you do the same thing with base tables. Non--consistency: sqlite> CREATE TABLE a (a DECIMAL(10)); sqlite> CREATE TABLE b AS SELECT * FROM a; sqlite> CREATE VIEW bv AS SELECT * FROM a; sqlite> PRAGMA table_info(b); 0|a|NUM|0||0 sqlite> PRAGMA table_info(bv); 0|a||0||0 Info on column's affinity (besides if it is a declared one --- DECIMAL, FLOAT --- or a real one --- NUMERIC, REAL) is still useful, when comparison is made. Column's affinity is still propagated: sqlite> CREATE TABLE b2 AS SELECT * FROM bv; sqlite> PRAGMA table_info(b2); 0|a|NUM|0||0 so the point is that ``PRAGMA table_info'' stopped displaying column's affinity in case of views. On the other side, views are not tables so a consistency does not require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not displaying an affinity in case of expressions, too: === SHELL 3.11.x === sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a; sqlite> PRAGMA table_info(av2); 0|CAST(+a AS NUMERIC)||0||0 so the decision was to remove that view's info at all. Now, we know that this behavior was changed by an arbitrary, intentional decision. We can debate about correctitude of that decision (there are pros and cons), however more important thing is to describe the fact in the documentation: http://sqlite.org/pragma.html#pragma_table_info: ``The table named in the table_info pragma can also be a view.'' --- in case of views only column's id and name are displayed. > Alternately, you can exploit the explicit column list that is optional > in a CREATE VIEW: > >CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2; Syntax error. Did you mean: CREATE VIEW v3 (foo) AS SELECT CAST(w+x+y+z AS NUMERIC) FROM t2; -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 2016-04-06 6:00 AM, Cezary H. Noweta wrote: > On 2016-04-06 09:43, Darren Duncan wrote: >> On 2016-04-05 10:19 AM, Richard Hipp wrote: > >>> It seems to me that the most consistent answer is that the "type" of >>> columns in a VIEW should always be an empty string. > >> That's only consistent if you do the same thing with base tables. > > Non--consistency: > > sqlite> CREATE TABLE a (a DECIMAL(10)); > sqlite> CREATE TABLE b AS SELECT * FROM a; > sqlite> CREATE VIEW bv AS SELECT * FROM a; > sqlite> PRAGMA table_info(b); > 0|a|NUM|0||0 > sqlite> PRAGMA table_info(bv); > 0|a||0||0 > > Info on column's affinity (besides if it is a declared one --- DECIMAL, FLOAT > --- or a real one --- NUMERIC, REAL) is still useful, when comparison is made. > > Column's affinity is still propagated: > > sqlite> CREATE TABLE b2 AS SELECT * FROM bv; > sqlite> PRAGMA table_info(b2); > 0|a|NUM|0||0 > > so the point is that ``PRAGMA table_info'' stopped displaying column's > affinity > in case of views. I agree with you in the sense that CREATE TABLE AS SELECT and CREATE VIEW AS SELECT with the same SELECT should produce results with identical type info. > > >> Alternately, you can exploit the explicit column list that is optional >> in a CREATE VIEW: >> >>CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2; > > Syntax error. Did you mean: > > CREATE VIEW v3 (foo) AS SELECT CAST(w+x+y+z AS NUMERIC) FROM t2; I actually meant what I said, wherein the column list is declared using the same syntax as one would use for a CREATE TABLE, meaning with types. However, your use of explicit CAST syntax is possibly a better solution in one sense, in that the SELECT itself is completely unambiguous to human readers what the result type is. It also fits right in with what I think is the best solution, that column type info just is derived from the SELECT expression. -- Darren Duncan
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 4/4/16, Mike Bayer wrote: > The "type" column in PRAGMA table_info() is now a blank string when the > target object is a view in 3.12.0. In 3.11.0 and prior versions, the > typing information is returned, > I think the problem is fixed by this checkin: https://www.sqlite.org/src/info/fb555c3c2af7f5e6 Please confirm. -- D. Richard Hipp drh at sqlite.org
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/06/2016 02:13 AM, Hick Gunter wrote:
> You are hopefully aware of the fact that SQLite associates type with the
> actual values and not the containers(columns) used to hold these values? This
> means that a data object of any type may be held/returned in a column,
> irrespective of the declared type (which, for expressions, is NULL).
>
> What would your date routine do with the string 12.17.9.17.15?
it would raise an exception on the invalid date format, not any
different from the string->date converters that take effect when you
enter a date on a web form.
Even though SQLite allows any kind of data to be stored in any row
regardless of type affinity, in practice, applications typically remain
faithful to the type of data they plan to store within columns. That is,
even though SQLite has a dynamic per-row typing model, most of the world
very much wants it to behave as a drop-in for MySQL or Postgresql and
their applications assume schemas have fixed datatypes just like all
other relational databases.
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Mike
> Bayer
> Gesendet: Dienstag, 05. April 2016 21:46
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: Re: [sqlite] regression in 3.12.0 vs. 3.11.0, column type
> information in PRAGMA missing
>
>
>
> On 04/05/2016 01:19 PM, Richard Hipp wrote:
>> On 4/4/16, Mike Bayer wrote:
>>> The "type" column in PRAGMA table_info() is now a blank string when
>>> the target object is a view in 3.12.0. In 3.11.0 and prior versions,
>>> the typing information is returned,
>>>
>>
>> This could easily be considered a bug fix rather than a regression.
>> Please explain why you think it is important to know the "type" of a
>> column in a view?
>
> As others have noted, in the application space we often use these names as
> suggestions for how data from such a column is to be handled once
> transmitted outside of the SQLite layer. The most prominent example is
> date values, where we apply converters on both sides of the data to convert
> between language-specific date objects and a string representation on the
> SQLite side.
>
> For example, here is the Python standard library SQLite database adapter:
>
> https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types
>
> In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES,
> which indicates "parse the name of the declared type delivered by SQLite
> within a result set, in order to apply a converter". This specific
> implementation is parsing the type affinity as delivered in the result set,
> so is not impacted by this change. However, other database abstraction
> systems rely upon the use of so-called "table metadata"
> gathered up front about tables and views in order to know about the datatypes
> that are expected from particular column names; on SQLite this
> relies upon "pragma table_info()" to collect that information.If
> it's no longer present, such systems would require the user to explicitly
> state datatypes in the case of views on the application side, or to be
> modified to rely upon typing information when a result set is received rather
> than based on the schema of the constructs themselves.
>But like in so many other cases, database-agnostic systems are designed
> around the way all other relational databases behave, which in this area is
> that of the "fixed type per-column" model where there's never been an issue
> knowing the types that will be received from a table or view. That SQLite
> also delivers these fields via "pragma table_info()" just made it that much
> more possible for abstraction layers to emulate similar behavior in SQLite.
>
>
>
>>
>> There are further inconsistencies here. Example:
>>
>> CREATE TABLE t1(x INTEGER);
>> CREATE VIEW v1 AS SELECT x FROM t1;
>> CREATE VIEW v2(x) AS SELECT x FROM t1;
>> PRAGMA table_info('v1');
>> PRAGMA table_info('v2');
>>
>> As of version 3.12.0, the two pragma's give the same answer. but in
>> version 3.11.0, they were different. Which of the two answers
>> returned by 3.11.0 is correct?
>
> Looking from the perspective of the consuming application, ultimately the
> INTEGER affinity value is what's delivered so in an ideal world people would
> expect the answer to be INTEGER.
>
> However I would reiterate the point of my original email, which is that if
> this chang
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
You are hopefully aware of the fact that SQLite associates type with the actual
values and not the containers(columns) used to hold these values? This means
that a data object of any type may be held/returned in a column, irrespective
of the declared type (which, for expressions, is NULL).
What would your date routine do with the string 12.17.9.17.15?
-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Mike
Bayer
Gesendet: Dienstag, 05. April 2016 21:46
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] regression in 3.12.0 vs. 3.11.0, column type information
in PRAGMA missing
On 04/05/2016 01:19 PM, Richard Hipp wrote:
> On 4/4/16, Mike Bayer wrote:
>> The "type" column in PRAGMA table_info() is now a blank string when
>> the target object is a view in 3.12.0. In 3.11.0 and prior versions,
>> the typing information is returned,
>>
>
> This could easily be considered a bug fix rather than a regression.
> Please explain why you think it is important to know the "type" of a
> column in a view?
As others have noted, in the application space we often use these names as
suggestions for how data from such a column is to be handled once
transmitted outside of the SQLite layer. The most prominent example is
date values, where we apply converters on both sides of the data to convert
between language-specific date objects and a string representation on the
SQLite side.
For example, here is the Python standard library SQLite database adapter:
https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types
In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES, which
indicates "parse the name of the declared type delivered by SQLite within a
result set, in order to apply a converter". This specific implementation is
parsing the type affinity as delivered in the result set, so is not impacted by
this change. However, other database abstraction systems rely upon the use of
so-called "table metadata"
gathered up front about tables and views in order to know about the datatypes
that are expected from particular column names; on SQLite this
relies upon "pragma table_info()" to collect that information.If
it's no longer present, such systems would require the user to explicitly state
datatypes in the case of views on the application side, or to be modified to
rely upon typing information when a result set is received rather than based on
the schema of the constructs themselves.
But like in so many other cases, database-agnostic systems are designed
around the way all other relational databases behave, which in this area is
that of the "fixed type per-column" model where there's never been an issue
knowing the types that will be received from a table or view. That SQLite also
delivers these fields via "pragma table_info()" just made it that much more
possible for abstraction layers to emulate similar behavior in SQLite.
>
> There are further inconsistencies here. Example:
>
> CREATE TABLE t1(x INTEGER);
> CREATE VIEW v1 AS SELECT x FROM t1;
> CREATE VIEW v2(x) AS SELECT x FROM t1;
> PRAGMA table_info('v1');
> PRAGMA table_info('v2');
>
> As of version 3.12.0, the two pragma's give the same answer. but in
> version 3.11.0, they were different. Which of the two answers
> returned by 3.11.0 is correct?
Looking from the perspective of the consuming application, ultimately the
INTEGER affinity value is what's delivered so in an ideal world people would
expect the answer to be INTEGER.
However I would reiterate the point of my original email, which is that if this
change in behavior is intentional, it should be documented in the change notes
at http://sqlite.org/releaselog/3_12_0.html ; I've looked through every line
item and see none that suggest a change in how PRAGMA behaves with regards to
views. I also notice that there's now a category of change called "Potentially
Disruptive Change"; I hope to illustrate here that whether or not the 3.11 or
3.12 behavior is chosen, this change is definitely "potentially disruptive" and
should be noted
as such.Or even that this area of behavior could be explicitly
described at http://sqlite.org/datatype3.html ("type affinities do not transfer
to views at the view definition level").
>
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
In the case of views propagating type affinities at the definition lev
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 2016-04-05 10:19 AM, Richard Hipp wrote:
> This could easily be considered a bug fix rather than a regression.
> Please explain why you think it is important to know the "type" of a
> column in a view?
One should be able to treat a view the same as a base table, not even having to
know whether a table is one type or the other, when it doesn't matter. Type
information should be as readily available, or not, for a view as a base table.
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
The answer to that question is, the same type as the type that "+" results in
given those arguments. Either the result of that summation is of some
particular data type, eg NUMERIC, in which case use that, or summing different
numeric types is illegal, in which case trying to use the view would fail.
> It seems to me that the most consistent answer is that the "type" of
> columns in a VIEW should always be an empty string.
That's only consistent if you do the same thing with base tables.
Alternately, you can exploit the explicit column list that is optional in a
CREATE VIEW:
CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2;
So if a VIEW definition uses that syntax, that's what the returned column type
is; otherwise the returned column type is the empty string.
Personally, I think using the expression value type is the best though, and
works without any schema changes.
-- Darren Duncan
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello,
On 2016-04-05 19:19, Richard Hipp wrote:
> Please explain why you think it is important to know the "type" of a
> column in a view?
For the same reason as it is important in case of a table. Both could be
consistent.
> There are further inconsistencies here. Example:
>
> CREATE TABLE t1(x INTEGER);
> CREATE VIEW v1 AS SELECT x FROM t1;
> CREATE VIEW v2(x) AS SELECT x FROM t1;
> PRAGMA table_info('v1');
> PRAGMA table_info('v2');
>
> As of version 3.12.0, the two pragma's give the same answer. but in
> version 3.11.0, they were different. Which of the two answers
> returned by 3.11.0 is correct?
IMHO, INTEGER. ``v2(x)'' changes nothing but the name of the column. It
has no impact on the column's affinity.
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
Opposite to the above: w+x+y+z loses affinity so it would be displayed
as an empty string.
> It seems to me that the most consistent answer is that the "type" of
> columns in a VIEW should always be an empty string.
If so, Mike's postulate is arguable. This fact could be mentioned in a
description of ``table_info'' PRAGMA: in case of views only id and a
name of a column is meaningful.
-- best regards
Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
If this change also applies to?sqlite3_table_column_metadata()'s behavior with
views, then I would consider it a real negative. ?Currently I can get my
declaration types using that call, which are hints on how to interpret the
values in the databases I create, from simple views which is convenient.
?Otherwise I have to use a number of less exact fallback methods to interpret
the values (such as reading the first row, or running queries against the
column, etc.).
Peter
On Tuesday, April 5, 2016 10:19 AM, Richard Hipp wrote:
On 4/4/16, Mike Bayer wrote:
> The "type" column in PRAGMA table_info() is now a blank string when the
> target object is a view in 3.12.0. In 3.11.0 and prior versions, the
> typing information is returned,
>
This could easily be considered a bug fix rather than a regression.
Please explain why you think it is important to know the "type" of a
column in a view?
There are further inconsistencies here.? Example:
? ? CREATE TABLE t1(x INTEGER);
? ? CREATE VIEW v1 AS SELECT x FROM t1;
? ? CREATE VIEW v2(x) AS SELECT x FROM t1;
? ? PRAGMA table_info('v1');
? ? PRAGMA table_info('v2');
As of version 3.12.0, the two pragma's give the same answer. but in
version 3.11.0, they were different.? Which of the two answers
returned by 3.11.0 is correct?
Or, consider this situation:
? ? CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
? ? CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
What should "PRAGMA table_info('v3')" report as the column type?
It seems to me that the most consistent answer is that the "type" of
columns in a VIEW should always be an empty string.
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-05 17:05, Mike Bayer wrote: > OK...so *that* is a change. Is *that* by design, and if so should that > be described here http://sqlite.org/releaselog/3_12_0.html ? I hope that is by an accident. While info about ``notnull'' and ``defaultvalue'' (which is not carried in 3.11.x too) is not meaningful in case of read-only views, then ``affinity'' info is still important (for comparision). > Otherwise if this is an unexpected regression then I hope a ticket can > be filed. +1. -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-04 22:14, Mike Bayer wrote: > if it is by design that views no longer carry typing information, this > is a major behavioral change and no mention of it occurs in the release > notes for 3.12.0, so at the very least this change should be documented > if expected. This is not true. Column's affinity is still propagated to a view, though it is not displayed by ``PRAGMA table_info''. Try: sqlite> INSERT INTO users VALUES (2); sqlite> SELECT * FROM users_v WHERE user_id > '1'; 2 If an affinity was not carried then the above ``SELECT'' would return an empty set, while it returns ``2'' (as in SQLite 3.11.x). IMHO, this described by you behavior can be considered as a bug in ``PRAGMA table_info'', which takes column's affinity from ``CREATE TABLE'' command only as for now. -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/05/2016 01:19 PM, Richard Hipp wrote:
> On 4/4/16, Mike Bayer wrote:
>> The "type" column in PRAGMA table_info() is now a blank string when the
>> target object is a view in 3.12.0. In 3.11.0 and prior versions, the
>> typing information is returned,
>>
>
> This could easily be considered a bug fix rather than a regression.
> Please explain why you think it is important to know the "type" of a
> column in a view?
As others have noted, in the application space we often use these names
as suggestions for how data from such a column is to be handled once
transmitted outside of the SQLite layer. The most prominent example is
date values, where we apply converters on both sides of the data to
convert between language-specific date objects and a string
representation on the SQLite side.
For example, here is the Python standard library SQLite database adapter:
https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types
In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES,
which indicates "parse the name of the declared type delivered by SQLite
within a result set, in order to apply a converter". This specific
implementation is parsing the type affinity as delivered in the result
set, so is not impacted by this change. However, other database
abstraction systems rely upon the use of so-called "table metadata"
gathered up front about tables and views in order to know about the
datatypes that are expected from particular column names; on SQLite this
relies upon "pragma table_info()" to collect that information.If
it's no longer present, such systems would require the user to
explicitly state datatypes in the case of views on the application side,
or to be modified to rely upon typing information when a result set is
received rather than based on the schema of the constructs themselves.
But like in so many other cases, database-agnostic systems are
designed around the way all other relational databases behave, which in
this area is that of the "fixed type per-column" model where there's
never been an issue knowing the types that will be received from a table
or view. That SQLite also delivers these fields via "pragma
table_info()" just made it that much more possible for abstraction
layers to emulate similar behavior in SQLite.
>
> There are further inconsistencies here. Example:
>
> CREATE TABLE t1(x INTEGER);
> CREATE VIEW v1 AS SELECT x FROM t1;
> CREATE VIEW v2(x) AS SELECT x FROM t1;
> PRAGMA table_info('v1');
> PRAGMA table_info('v2');
>
> As of version 3.12.0, the two pragma's give the same answer. but in
> version 3.11.0, they were different. Which of the two answers
> returned by 3.11.0 is correct?
Looking from the perspective of the consuming application, ultimately
the INTEGER affinity value is what's delivered so in an ideal world
people would expect the answer to be INTEGER.
However I would reiterate the point of my original email, which is that
if this change in behavior is intentional, it should be documented in
the change notes at http://sqlite.org/releaselog/3_12_0.html ; I've
looked through every line item and see none that suggest a change in how
PRAGMA behaves with regards to views. I also notice that there's now a
category of change called "Potentially Disruptive Change"; I hope to
illustrate here that whether or not the 3.11 or 3.12 behavior is chosen,
this change is definitely "potentially disruptive" and should be noted
as such.Or even that this area of behavior could be explicitly
described at http://sqlite.org/datatype3.html ("type affinities do not
transfer to views at the view definition level").
>
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
In the case of views propagating type affinities at the definition
level, ideally we'd take the summation of the type affinities themselves
assuming each column is populated with a type-compliant data value and
return that, which in this case would be NUMERIC, since all math
operators coerce their values to NUMERIC first.
>
> It seems to me that the most consistent answer is that the "type" of
> columns in a VIEW should always be an empty string.
So to give some background where I'm coming from on this, I don't
actually need SQLite to behave either way, I'm the creator and
maintainer of one of many database abstraction layers that provides a
SQLite translation layer and it's *my* users who will come to me with
this behavioral change (and in fact they already have which is how I
know about this). I only need the change and/or the "correct" behavior
to be defined (that is, is part of SQLite's test suite and won't keep
changing across releases) and documented (so I can point my users to
SQLite's own instructions about
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 4/4/16, Mike Bayer wrote:
> The "type" column in PRAGMA table_info() is now a blank string when the
> target object is a view in 3.12.0. In 3.11.0 and prior versions, the
> typing information is returned,
>
This could easily be considered a bug fix rather than a regression.
Please explain why you think it is important to know the "type" of a
column in a view?
There are further inconsistencies here. Example:
CREATE TABLE t1(x INTEGER);
CREATE VIEW v1 AS SELECT x FROM t1;
CREATE VIEW v2(x) AS SELECT x FROM t1;
PRAGMA table_info('v1');
PRAGMA table_info('v2');
As of version 3.12.0, the two pragma's give the same answer. but in
version 3.11.0, they were different. Which of the two answers
returned by 3.11.0 is correct?
Or, consider this situation:
CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
What should "PRAGMA table_info('v3')" report as the column type?
It seems to me that the most consistent answer is that the "type" of
columns in a VIEW should always be an empty string.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/05/2016 10:43 AM, Cezary H. Noweta wrote: > > IMHO, this described by you behavior can be considered as a bug in > ``PRAGMA table_info'', which takes column's affinity from ``CREATE > TABLE'' command only as for now. OK...so *that* is a change. Is *that* by design, and if so should that be described here http://sqlite.org/releaselog/3_12_0.html ? Otherwise if this is an unexpected regression then I hope a ticket can be filed. > > -- best regards > > Cezary H. Noweta > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
The "type" column in PRAGMA table_info() is now a blank string when the
target object is a view in 3.12.0. In 3.11.0 and prior versions, the
typing information is returned,
Version 3.11.0:
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (
...> user_id INTEGER NOT NULL
...> );
sqlite> CREATE VIEW users_v AS SELECT * FROM users;
sqlite> PRAGMA table_info("users_v");
0|user_id|INTEGER|0||0
Version 3.12.0:
SQLite version 3.12.0 2016-03-29 10:14:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (
user_id INTEGER NOT NULL
)
...>...>...> ;
sqlite> CREATE VIEW users_v AS SELECT * FROM users;
sqlite> PRAGMA table_info("users_v");
0|user_id||0||0
if it is by design that views no longer carry typing information, this
is a major behavioral change and no mention of it occurs in the release
notes for 3.12.0, so at the very least this change should be documented
if expected.
