[Sqlalchemy-tickets] Issue #4370: sqlite 3.25.x docs/tutorial.rst doctests fail (zzzeek/sqlalchemy)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 4370: sqlite 3.25.x docs/tutorial.rst doctests fail
https://bitbucket.org/zzzeek/sqlalchemy/issues/4370/sqlite-325x-docs-tutorialrst-doctests-fail

Andreas Rammhold:

While bumping SQLAlchemy (& sqlite) in the NixOS Package repository (https://github.com/NixOS/nixpkgs/pull/46852) we have been seeing one particular test (`DocTest.test_core`) failing whenever we test with sqlite >= 3.25.  Succeeding the tests is a mandatory step for the package to be bumped.

SQLAlchemy version: 1.2.13

SQLite version: 2.35.3

### Steps to reproduce

After checking out https://github.com/NixOS/nixpkgs/pull/46852 and building it using `nix-build -I nixpkgs=. -A python3Package.sqlalchemy` the following error will appear after some time:

```
=================================== FAILURES ===================================
______________________________ DocTest.test_core _______________________________
Traceback (most recent call last):
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 86, in test_core
    self._run_doctest("core/tutorial.rst")
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 80, in _run_doctest
    self._run_doctest_for_content(fname, content)
  File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 69, in _run_doctest_for_content
    assert not runner.failures
AssertionError: assert not 2
 +  where 2 = <doctest.DocTestRunner object at 0x7fffeb84db70>.failures
----------------------------- Captured stdout call -----------------------------
2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses")
2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,861 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	PRIMARY KEY (id)
)

2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE addresses (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	email_address VARCHAR NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)

2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,864 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,867 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname) VALUES (?, ?)
2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine ('jack', 'Jack Jones')
2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,869 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine (2, 'wendy', 'Wendy Williams')
2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,880 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine ((1, 'ja...@ya...'), (1, 'ja...@ms...'), (2, 'ww...@ww...'), (2, 'we...@ao...'))
2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname 
FROM users
2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine SELECT users.name, users.fullname 
FROM users
2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses
2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses 
WHERE users.id = addresses.user_id
2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,916 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,917 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine ('m', 'z', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine SELECT users.id, addresses.id, users.id, users.name, addresses.email_address AS email FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine ()
2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine ('%@aol.com', '%@msn.com')
2018-11-15 00:58:09,932 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS anon_1 
FROM users, addresses 
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
2018-11-15 00:58:09,933 INFO sqlalchemy.engine.base.Engine (', ', '%@aol.com', '%@msn.com')
2018-11-15 00:58:09,935 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses 
FROM addresses ORDER BY num_addresses
2018-11-15 00:58:09,936 INFO sqlalchemy.engine.base.Engine ()
**********************************************************************
File "core/tutorial.rst", line 1037, in core/tutorial.rst
Failed example:
    conn.execute(stmt).fetchall()
Expected:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses
    FROM addresses ORDER BY num_addresses
    ()
    [(2, 4)]
Got:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses 
    FROM addresses ORDER BY num_addresses
    ()
    [(1, 4)]
2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses 
FROM addresses ORDER BY num_addresses DESC
2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine ()
**********************************************************************
File "core/tutorial.rst", line 1054, in core/tutorial.rst
Failed example:
    conn.execute(stmt).fetchall()
Expected:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses
    FROM addresses ORDER BY num_addresses DESC
    ()
    [(2, 4)]
Got:
    SELECT addresses.user_id, count(addresses.id) AS num_addresses 
    FROM addresses ORDER BY num_addresses DESC
    ()
    [(1, 4)]
```

The full log can be retrieved here:
https://gist.githubusercontent.com/andir/b4b7f1ef355899c7972bb5ead67afb0f/raw

Looking at the documentation for this doctest one of the queries that is executed origins from the following lines:

```
>>> from sqlalchemy import func
>>> stmt = select([
...         addresses.c.user_id,
...         func.count(addresses.c.id).label('num_addresses')]).\
...         order_by("num_addresses")
```

The difference in output is in the first element of the first tuple. It should represent `addresses.user_id`. Given that there is no ordering, grouping further restriction of the value of that field I'd guess that any value could be presented here. It could probably be explained as "undefined behavior"? In that case the assumption of the test is wrong and should allow for any value from the set of available values (1 or 2).

We believe this could be a bug within the testsuite.