Test failures with SQLite 3.37.0+ due to column affinity case

[simonw]

These three tests are failing on my local machine:

FAILED tests/test_internals_database.py::test_table_column_details[facetable-expected0] - AssertionError: assert [Column(cid=0, name='pk', type='INTEGER', no...
FAILED tests/test_internals_database.py::test_table_column_details[sortable-expected1] - AssertionError: assert [Column(cid=0, name='pk1', type='varchar(30)'...
FAILED tests/test_table_html.py::test_sort_links - AssertionError: assert [{'a_href': None,\n  'attrs': {'class': ['col-Link'],\n            'data-column': '...

I ran pytest --lf -vv and the output had things like this in it:

E         -  Column(cid=1, name='created', type='text', notnull=0, default_value=None, is_pk=0, hidden=0),
E         ?                                      ^^^^
E         +  Column(cid=1, name='created', type='TEXT', notnull=0, default_value=None, is_pk=0, hidden=0),
...
E            {'a_href': '/fixtures/sortable?_sort=sortable_with_nulls_2',
E             'attrs': {'class': ['col-sortable_with_nulls_2'],
E                       'data-column': 'sortable_with_nulls_2',
E                       'data-column-not-null': '0',
E         -             'data-column-type': 'real',
E         ?                                  ^^^^
E         +             'data-column-type': 'REAL',
E         ?                                  ^^^^

Something is causing column types to come back in uppercase where previously they were lowercase.

[simonw]

My best guess is that this is an undocumented change in SQLite 3.38 - I get that test failure with that SQLite version.

[simonw]

Had a look through the commits in sqlite/sqlite@version-3.37.2...version-3.38.0 but couldn't see anything obvious that might have caused this.

Really wish I had a good mechanism for running the test suite against different SQLite versions!

May have to revisit this old trick: https://til.simonwillison.net/sqlite/ld-preload

[simonw]

OK, using that trick worked for testing this:

docker run -it -p 8001:8001 ubuntu

Then inside that container:

apt-get install -y python3 build-essential tcl wget python3-pip git python3.8-venv

For each version of SQLite I wanted to test I needed to figure out the tarball URL - for example, for 3.38.0 I navigated to https://www.sqlite.org/src/timeline?t=version-3.38.0 and clicked the "checkin" link and copied the tarball link:
https://www.sqlite.org/src/tarball/40fa792d/SQLite-40fa792d.tar.gz

Then to build it (the CPPFLAGS took some trial and error):

cd /tmp
wget https://www.sqlite.org/src/tarball/40fa792d/SQLite-40fa792d.tar.gz
tar -xzvf SQLite-40fa792d.tar.gz
cd SQLite-40fa792d
CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE=1" ./configure
make

Then to test with Datasette:

cd /tmp
git clone https://github.com/simonw/datasette
cd datasette
python3 -m venv venv
source venv/bin/activate
pip install wheel # So bdist_wheel works in next step
pip install -e '.[test]'
LD_PRELOAD=/tmp/SQLite-40fa792d/.libs/libsqlite3.so pytest

After some trial and error I proved that those tests passed with 3.36.0:

cd /tmp
wget https://www.sqlite.org/src/tarball/5c9a6c06/SQLite-5c9a6c06.tar.gz
tar -xzvf SQLite-5c9a6c06.tar.gz
cd SQLite-5c9a6c06
CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE=1" ./configure
make
cd /tmp/datasette
LD_PRELOAD=/tmp/SQLite-5c9a6c06/.libs/libsqlite3.so pytest tests/test_internals_database.py

BUT failed with 3.37.0:

# 3.37.0
cd /tmp
wget https://www.sqlite.org/src/tarball/bd41822c/SQLite-bd41822c.tar.gz
tar -xzvf SQLite-bd41822c.tar.gz
cd SQLite-bd41822c
CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE=1" ./configure
make
cd /tmp/datasette
LD_PRELOAD=/tmp/SQLite-bd41822c/.libs/libsqlite3.so pytest tests/test_internals_database.py

[simonw]

I thought I'd need to introduce https://dirty-equals.helpmanual.io/types/string/ to help write tests for this, but I think I've found a good alternative that doesn't need a new dependency.

[simonw]

Updated this TIL with extra patterns I figured out: https://til.simonwillison.net/sqlite/ld-preload