test_sqlite: CheckFuncDeterministic() fails with SQLite 3.32

[vstinner]

BPO	40784
Nosy	@vstinner, @benjaminp, @encukou, @berkerpeksag, @miss-islington, @erlend-aasland
PRs	bpo-40784: Fix sqlite3 deterministic test #20448 [3.8] bpo-40784: Fix sqlite3 deterministic test (GH-20448) #20512 [3.9] bpo-40784: Fix sqlite3 deterministic test (GH-20448) #20513

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2020-05-28.23:28:09.959>
created_at = <Date 2020-05-26.17:33:00.064>
labels = ['3.8', 'type-bug', 'tests', '3.9', '3.10']
title = 'test_sqlite: CheckFuncDeterministic() fails with SQLite 3.32'
updated_at = <Date 2020-05-29.12:46:55.830>
user = 'https://github.com/vstinner'

bugs.python.org fields:

activity = <Date 2020-05-29.12:46:55.830>
actor = 'miss-islington'
assignee = 'none'
closed = True
closed_date = <Date 2020-05-28.23:28:09.959>
closer = 'berker.peksag'
components = ['Tests']
creation = <Date 2020-05-26.17:33:00.064>
creator = 'vstinner'
dependencies = []
files = []
hgrepos = []
issue_num = 40784
keywords = ['patch']
message_count = 9.0
messages = ['370022', '370024', '370042', '370050', '370061', '370272', '370305', '370307', '370308']
nosy_count = 7.0
nosy_names = ['ghaering', 'vstinner', 'benjamin.peterson', 'petr.viktorin', 'berker.peksag', 'miss-islington', 'erlendaasland']
pr_nums = ['20448', '20512', '20513']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue40784'
versions = ['Python 3.8', 'Python 3.9', 'Python 3.10']

[vstinner]

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

[vstinner]

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.

[benjaminp]

This seems like it's testing an implementation detail.

[encukou]

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

[erlend-aasland]

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

[berkerpeksag]

New changeset c610d97 by Erlend Egeberg Aasland in branch 'master':
bpo-40784: Fix sqlite3 deterministic test (GH-20448)
c610d97

[vstinner]

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