On 04/05/2016 01:19 PM, Richard Hipp wrote: > On 4/4/16, Mike Bayer <classic at zzzcomputing.com> 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 this limitation). I will remove the tests from my suite that have for many years expected SQLite to behave this way, and possibly put a note in my own documentation pointing to SQLite's explanation, and the problem is solved. That part of the userbase who relied upon this behavior would be negatively impacted, however, as they'd now have to solve their problem in a different way and would experience sudden behavioral changes on an upgrade from 3.11 to 3.12. >
