classification
Title: test_sqlite: CheckFuncDeterministic() fails with SQLite 3.32
Type: behavior Stage: resolved
Components: Tests Versions: Python 3.10, Python 3.9, Python 3.8
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: Nosy List: benjamin.peterson, berker.peksag, erlendaasland, ghaering, miss-islington, petr.viktorin, vstinner
Priority: normal Keywords: patch

Created on 2020-05-26 17:33 by vstinner, last changed 2020-05-29 12:46 by miss-islington. This issue is now closed.

Pull Requests
URL Status Linked Edit
PR 20448 merged erlendaasland, 2020-05-27 10:23
PR 20512 merged miss-islington, 2020-05-29 12:26
PR 20513 merged miss-islington, 2020-05-29 12:26
Messages (9)
msg370022 - (view) Author: STINNER Victor (vstinner) * (Python committer) Date: 2020-05-26 17:32
With SQLite 3.32, test_sqlite fails with:

FAIL: CheckFuncDeterministic (sqlite3.test.userfunctions.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/builddir/build/BUILD/Python-3.9.0b1/Lib/sqlite3/test/userfunctions.py", line 290, in CheckFuncDeterministic
    self.assertEqual(mock.call_count, 1)
AssertionError: 2 != 1

This test defines a "deterministic" function and ensures that calling it twice in SQLite with only call the underyling Python function only once.

Copy of the test:

    @unittest.skipIf(sqlite.sqlite_version_info < (3, 8, 3), "deterministic parameter not supported")
    def CheckFuncDeterministic(self):
        mock = unittest.mock.Mock(return_value=None)
        self.con.create_function("deterministic", 0, mock, deterministic=True)
        self.con.execute("select deterministic() = deterministic()")
        self.assertEqual(mock.call_count, 1)

In pysqlite_connection_create_function() of Modules/_sqlite/connection.c, determistic=1 sets the following flag:

        flags |= SQLITE_DETERMINISTIC;

This flag is documented as:
"A deterministic function always gives the same answer when it has the same inputs."

* https://www.sqlite.org/deterministic.html
* https://www.sqlite.org/c3ref/c_deterministic.html

"SELECT 1 WHERE deterministic() = deterministic()" query also calls the mock twice.

Running "SELECT deterministic()" query twice also calls the mock twice.

It seems like SQLite 3.32 behaves differently.

Fedora issue: https://bugzilla.redhat.com/show_bug.cgi?id=1839826
msg370024 - (view) Author: STINNER Victor (vstinner) * (Python committer) Date: 2020-05-26 17:35
Oh, I also tried with a function taking one argument and then call it twice by running "SELECT deterministic(1)" query twice: again, the mock is also called twice.
msg370042 - (view) Author: Benjamin Peterson (benjamin.peterson) * (Python committer) Date: 2020-05-27 02:59
This seems like it's testing an implementation detail.
msg370050 - (view) Author: Petr Viktorin (petr.viktorin) * (Python committer) Date: 2020-05-27 06:53
Indeed. The SQLite documentation talks about the limitations that non-deterministic functions have, not about deterministic functions being memoized: https://www.sqlite.org/deterministic.html

The flag would be better tested e.g. with a CHECK constraint: https://www.sqlite.org/lang_createtable.html#ckconst
msg370061 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-05-27 10:23
Hi, folks. I took the liberty to create a PR for this; hope you don't mind. I've used partial indices to test deterministic behaviour.

https://www.sqlite.org/partialindex.html
msg370272 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2020-05-28 23:27
New changeset c610d970f5373b143bf5f5900d4645e6a90fb460 by Erlend Egeberg Aasland in branch 'master':
bpo-40784: Fix sqlite3 deterministic test (GH-20448)
https://github.com/python/cpython/commit/c610d970f5373b143bf5f5900d4645e6a90fb460
msg370305 - (view) Author: STINNER Victor (vstinner) * (Python committer) Date: 2020-05-29 12:28
Python 3.8 and Python 3.9 are also affected, I created backports.

Python 3.7 is not affected: deterministic parameter was added to Python 3.8.
https://docs.python.org/dev/library/sqlite3.html#sqlite3.Connection.create_function
msg370307 - (view) Author: miss-islington (miss-islington) Date: 2020-05-29 12:46
New changeset 00a240bf7f95bbd220f1cfbf9eb58484a5f9681a by Miss Islington (bot) in branch '3.8':
bpo-40784: Fix sqlite3 deterministic test (GH-20448)
https://github.com/python/cpython/commit/00a240bf7f95bbd220f1cfbf9eb58484a5f9681a
msg370308 - (view) Author: miss-islington (miss-islington) Date: 2020-05-29 12:46
New changeset 8fcc1474ef5d819c144309e048e71e6013544063 by Miss Islington (bot) in branch '3.9':
bpo-40784: Fix sqlite3 deterministic test (GH-20448)
https://github.com/python/cpython/commit/8fcc1474ef5d819c144309e048e71e6013544063
History
Date User Action Args
2020-05-29 12:46:55miss-islingtonsetmessages: + msg370308
2020-05-29 12:46:37miss-islingtonsetmessages: + msg370307
2020-05-29 12:28:26vstinnersetmessages: + msg370305
versions: + Python 3.8, Python 3.9
2020-05-29 12:26:41miss-islingtonsetpull_requests: + pull_request19757
2020-05-29 12:26:31miss-islingtonsetnosy: + miss-islington

pull_requests: + pull_request19756
2020-05-28 23:28:09berker.peksagsetstatus: open -> closed
type: behavior
resolution: fixed
stage: patch review -> resolved
2020-05-28 23:27:40berker.peksagsetnosy: + berker.peksag
messages: + msg370272
2020-05-27 10:23:55erlendaaslandsetmessages: + msg370061
2020-05-27 10:23:01erlendaaslandsetkeywords: + patch
stage: patch review
pull_requests: + pull_request19702
2020-05-27 09:00:13erlendaaslandsetnosy: + erlendaasland
2020-05-27 06:53:24petr.viktorinsetnosy: + petr.viktorin
messages: + msg370050
2020-05-27 02:59:50benjamin.petersonsetnosy: + benjamin.peterson
messages: + msg370042
2020-05-26 17:35:21vstinnersetmessages: + msg370024
2020-05-26 17:33:00vstinnercreate