SQLite Forum

The SQLITE_DETERMINISTIC flag isn't work on 3.32.3 release

The SQLITE_DETERMINISTIC flag isn't work on 3.32.3 release

(1) By Yiting on 2020-07-15 01:42:58 [link] [source]

I tested SQLite's deterministic parameter by running python 3.8's unit test and found that this parameter had stopped working well on the latest release 3.32.3.

I debugged the issue and found sqlite3VdbeAddFunctionCall() had been called twice and my callback function had been called twice too.

This issue can be reproduced on VxWorks and Ubuntu 14.04.6.

(2) By Richard Hipp (drh) on 2020-07-15 01:47:20 in reply to 1 [link] [source]

What about it isn't working? Can you provide a test case to demonstrate the malfunction?

(3.1) By Yiting on 2020-07-15 02:30:24 edited from 3.0 in reply to 2 [link] [source]

Sorry I may not have described the issue clearly, because I didn't test SQLite 3.32.3 directly. I just ran a Python 3.8 unit test case.

./python3 -m unittest -v sqlite3.test.userfunctions.FunctionTests.CheckFuncDeterministic

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)

The result is mock.call_count is equal to 2.

(4) By Richard Hipp (drh) on 2020-07-15 02:29:47 in reply to 3.0 [source]

I think this was discussed previously (2020-05-27) at e79a299b3ffa87e41c5b2b3d. The problem is in the Python test, apparently, not in SQLite.

"DETERMINISTIC" means that the output of the function is always determined by its parameters. This permits the SQLite to optimize out multiple calls to the same function, but it does not require that. The python test seems to be assuming that the optimization is a requirement.

The behavior changed slightly in order to address 3c9eadd2a6ba0aa5 bug.

(5) By TripeHound on 2020-07-15 08:45:13 in reply to 4 [link] [source]

There was another similar discussion at bc6ed1f746. As Richard says, DETERMINISTIC doesn't mean SQLite will only call a function once, just that it is allowed to assume that every call (with the same parameters) will return the same value (which may result in a reduced number of calls).