SQLite Forum

Issue with pragma table_info / data-type on views in 3.41
Login

Issue with pragma table_info / data-type on views in 3.41

(1.3) By beetlejuice (coleifer) on 2023-01-06 19:18:04 edited from 1.2 [source]

I'm seeing some failures in introspection library code that utilizes pragma table_info() to detect column types in views.

Example on 3.41 showing the b column changed from "datetime" to "num"(??):

sqlite> create table foo (a text, b datetime);                                           
sqlite> create view foo_view as select a, b from foo order by a;                         
sqlite> pragma table_info('foo');                                                        
0|a|TEXT|0||0
1|b|datetime|0||0
sqlite> pragma table_info('foo_view');
0|a|TEXT|0||0
1|b|NUM|0||0

Example of previously correct output:

sqlite> pragma table_info('foo_view');
0|a|text|0||0
1|b|datetime|0||0

I know datetime is not an official type,and the affinity rules designate datetime as being a numeric data-type, the behavior at the very least is a break from previous versions and somewhat surprising.

I would have expected the previous behavior -- the data-type I declared is what gets propagated back by the table_info pragma in both cases.

I see there are some changes related, possibly a regression here: https://www.sqlite.org/src/info/27655c9353620aa5 ?

(2.1) By Keith Medcalf (kmedcalf) on 2023-01-06 22:08:44 edited from 2.0 in reply to 1.3 [link] [source]

My version 3.41 (the current tip of trunk -- aka checkin [a6251d7289]) does the following:

SQLite version 3.41.0 2023-01-05 17:37:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.41.0 2023-01-05 17:37:44 286dbb743668e744f0a3aca054d64ab5849e9b866d8047e11fb9176df18b108f
zlib version 1.2.12
gcc-12.2.0
sqlite> create table foo (a text, b datetime);
sqlite> create view foo_view as select a, b from foo order by a;
sqlite> pragma table_info('foo');
┌─────┬──────┬────────────┬───────────┬──────┬─────────┬────────────┬────┬───────┬─────────┐
│ cid │ name │    type    │    aff    │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │
├─────┼──────┼────────────┼───────────┼──────┼─────────┼────────────┼────┼───────┼─────────┤
│ 0   │ 'a'  │ 'TEXT'     │ 'TEXT'    │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │
│ 1   │ 'b'  │ 'datetime' │ 'NUMERIC' │ NULL │ 0       │ NULL       │ 0  │ 0     │ 0       │
└─────┴──────┴────────────┴───────────┴──────┴─────────┴────────────┴────┴───────┴─────────┘

The behaviour of versions 3.39.0, 3.40.0 and 3.40.1 are all the same and maintain the "user specified type specification".

There is no version 3.41.0

(3) By Keith Medcalf (kmedcalf) on 2023-01-06 22:15:21 in reply to 1.3 [link] [source]

The nekkid tip of trunk does also preserves the user specified type sputtering:

SQLite version 3.41.0 2023-01-05 14:41:18
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.41.0 2023-01-05 14:41:18 a6251d72894f9c2e21fc6e91b1d2452a204952f5e1a94fd93835a47c7dfb9be3
msvc-1929
sqlite> create table foo (a text, b datetime);
sqlite> create view foo_view as select a, b from foo order by a;
sqlite> pragma table_info('foo');
┌─────┬──────┬────────────┬─────────┬────────────┬────┐
│ cid │ name │    type    │ notnull │ dflt_value │ pk │
├─────┼──────┼────────────┼─────────┼────────────┼────┤
│ 0   │ 'a'  │ 'TEXT'     │ 0       │ NULL       │ 0  │
│ 1   │ 'b'  │ 'datetime' │ 0       │ NULL       │ 0  │
└─────┴──────┴────────────┴─────────┴────────────┴────┘
sqlite>

(4) By beetlejuice (coleifer) on 2023-01-09 13:32:41 in reply to 3 [link] [source]

Keith, I think you are in too much of a hurry. Clearly from my title and my code, I showed that the issue is with the info returned for foo_view, not foo.

To be super clear, this is the issue:

sqlite> create table foo (a text, b datetime);
sqlite> create view foo_view as select a, b from foo order by a;
sqlite> pragma table_info('foo_view');
0|a|TEXT|0||0
1|b|NUM|0||0

(5) By Richard Hipp (drh) on 2023-01-09 13:37:52 in reply to 4 [link] [source]

The datatype for the foo_view.b column really is NUM. I think that the table_info pragma is working correctly.

(6.3) By beetlejuice (coleifer) on 2023-01-09 14:07:32 edited from 6.2 in reply to 5 [link] [source]

I understand, from an affinities perspective, why this is the case - especially as it pertains to the types propagated up from a query. However in previous versions, the user-specified type was reported for my view as well. I think it also is valid to say: Why, then, is NUM not also specified for the table foo itself, then?

Also, adding an explicit cast(b as datetime) to the view definition does not cause the user-defined type to be propagated -- this would seem to me to be at least the equivalent of what the table foo itself is doing.

(7) By Keith Medcalf (kmedcalf) on 2023-01-09 16:43:44 in reply to 4 [link] [source]

You are correct. This appears to only affect the bare parse, however, not when the view is used, which still propagates the user utterance.

SQLite version 3.41.0 2023-01-08 17:24:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo(x datetime text not null);
sqlite> create view foo_view as select x from foo;
sqlite> insert into foo values (datetime());
sqlite> pragma table_info(foo_view);
┌─────┬──────┬────────┬────────┬──────────┬─────────┬────────────┬────┬───────┬─────────┐
│ cid │ name │  type  │  aff   │   coll   │ notnull │ dflt_value │ pk │ rowid │ autoinc │
├─────┼──────┼────────┼────────┼──────────┼─────────┼────────────┼────┼───────┼─────────┤
│ 0   │ 'x'  │ 'TEXT' │ 'TEXT' │ 'BINARY' │ 0       │ NULL       │ 0  │ 0     │ 0       │
└─────┴──────┴────────┴────────┴──────────┴─────────┴────────────┴────┴───────┴─────────┘
sqlite> ^Z

Python 3.11.1 (tags/v3.11.1:a7a450f, Dec  6 2022, 19:58:39) [MSC v.1934 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mpsw
>>> db = mpsw.Connection()
>>> db.execute('create table foo(x datetime text not null)')
<mpsw.Cursor object at 0x0000016D22133CA0>
>>> db.execute('create view foo_view as select x from foo')
<mpsw.Cursor object at 0x0000016D226A4BA0>
>>> db.execute('insert into foo values (datetime())')
<mpsw.Cursor object at 0x0000016D22133CA0>
>>> cr = db.execute('select * from foo')
>>> next(cr)
MiniRow(x=datetime.datetime(2023, 1, 9, 16, 32, 29, 773000, tzinfo=datetime.timezone.utc))
>>> cr.description
(('x', 'datetime text', 'main', 'foo', 'x'),)
>>> cr.close()
>>> cr = db.execute('select * from foo_view')
>>> next(cr)
MiniRow(x=datetime.datetime(2023, 1, 9, 16, 32, 29, 773000, tzinfo=datetime.timezone.utc))
>>> cr.description
(('x', 'datetime text', 'main', 'foo', 'x'),)
>>> cr.close()
>>> db.close()
>>> ^Z

(8.1) By beetlejuice (coleifer) on 2023-01-09 16:57:31 edited from 8.0 in reply to 7 [link] [source]

I don't see any indication that your mpsw uses version 3.41.0, but assuming it does, my guess is that it uses the following API to get the column metadata: sqlite3_column_decltype. Editing because I mistakenly thought this API required -DENABLE_COLUMN_METADATA. We could try to use this, but it requires a cursor and query.

It'd be far simpler for the table_info pragma to behave the same in any case.

(9) By Keith Medcalf (kmedcalf) on 2023-01-09 17:19:04 in reply to 8.1 [link] [source]

Yes, mpsw wraps apsw which wraps sqlite3. It is using the current tip of trunk. And yes, the cursor.description is the apsw cursor.description_full which uses sqlite3_column_decltype to fetch the underlying column declaration.

Python 3.11.1 (tags/v3.11.1:a7a450f, Dec  6 2022, 19:58:39) [MSC v.1934 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mpsw
>>> mpsw.sqlitelibversion()
'3.41.0'
>>> mpsw.apswversion()
'3.40.0.0'
>>> mpsw.sqlite3_sourceid()
'2023-01-08 17:24:11 deda9fa827fc394b2c4dc3062f3128870f45260b6c119aef4b558060c1336f8c'
>>> ^Z

which corresponds to
=== 2023-01-08 ===
17:24:11 [deda9fa827] *MERGE* *CURRENT* *UNPUBLISHED* Merge changes from trunk (user: KMedcalf tags: private)
=== 2023-01-07 ===
22:28:00 [2da51d7e1b] Doc-only update, sqlite3_preupdate_hook() return (user: larrybr tags: trunk)

(10) By beetlejuice (coleifer) on 2023-01-09 18:00:16 in reply to 9 [link] [source]

Thanks for confirming that. Once again I think this is just inconsistent, @drh -- why does the table show one thing, the view show something else, and sqlite3_column_decltype something else?

(11.1) By beetlejuice (coleifer) on 2023-01-10 15:07:03 edited from 11.0 in reply to 5 [link] [source]

After reading Keith's comments below, I did some more digging. It appears that the following behavior is present in Sqlite 3.41 (upcoming) for the following schema:

create table foo (a text, b datetime);
create view foo_view as select a, b from foo;
create view foo_view2 as select a, cast(b as datetime) as b from foo;
  • pragma table_info('foo') reports foo.b having type datetime
  • pragma table_info('foo_view') reports foo_view.b having type num (!!)
  • pragma table_info('foo_view2') reports foo_view2.b having type num (!!)
  • sqlite3_column_decltype on a stmt selecting foo.b reports datetime
  • sqlite3_column_decltype on a stmt selecting foo_view.b reports datetime (!!)

My own opinion, as stated elsewhere, is that table_info('foo_view') should report the datetime data-type, else why is it preserved for foo? Similarly, why is the explicit cast thrown out for foo_view2?

Additionally, why should the column decltype differ from what is reported by the table info pragma?

(12) By Richard Hipp (drh) on 2023-01-10 19:59:16 in reply to 1.3 [link] [source]

Please try again with check-in 497a98363fd1ed07 or later and let me know whether or not this resolves your issue.

(13) By beetlejuice (coleifer) on 2023-01-10 20:52:02 in reply to 12 [link] [source]

Looks great on my side, thank you for your help and patience, drh.