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

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>

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

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

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.

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?

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

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.

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)

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?

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

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