include built-in Math functions in SQLite to 3.35.0 of march 2021

[BigStone]

BPO	42686
Nosy	@rhettinger, @pfmoore, @tjguk, @stevendaprano, @ambv, @zware, @zooba, @pablogsal, @miss-islington, @erlend-aasland
PRs	bpo-42686: Enable SQLite math functions in Windows build #24053 [3.10] bpo-42686: Enable SQLite math functions in Windows build (GH-24053) #25892

^{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 2021-05-04.13:22:39.360>
created_at = <Date 2020-12-19.15:32:41.568>
labels = ['type-feature', 'library', 'OS-windows']
title = 'include built-in Math functions in SQLite to 3.35.0 of march 2021'
updated_at = <Date 2021-07-18.15:30:48.143>
user = 'https://bugs.python.org/BigStone'

bugs.python.org fields:

activity = <Date 2021-07-18.15:30:48.143>
actor = 'pablogsal'
assignee = 'none'
closed = True
closed_date = <Date 2021-05-04.13:22:39.360>
closer = 'lukasz.langa'
components = ['Library (Lib)', 'Windows']
creation = <Date 2020-12-19.15:32:41.568>
creator = 'Big Stone'
dependencies = []
files = []
hgrepos = []
issue_num = 42686
keywords = ['patch']
message_count = 15.0
messages = ['383383', '383386', '383503', '383506', '383552', '383555', '383556', '384095', '384137', '384193', '384204', '386624', '388686', '392891', '392892']
nosy_count = 11.0
nosy_names = ['rhettinger', 'paul.moore', 'tim.golden', 'steven.daprano', 'lukasz.langa', 'zach.ware', 'steve.dower', 'Big Stone', 'pablogsal', 'miss-islington', 'erlendaasland']
pr_nums = ['24053', '25892']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue42686'
versions = []

[BigStone]

SQlite-3.35.0 of mach 2021 will have Built-In Math Functions option.

https://sqlite.org/draft/releaselog/3_35_0.html

Would it be possible to have it activated in the following versions update of Python ? It's pretty usefull for some basic statistics

[stevendaprano]

As far as I can tell, every one of those are already available in Python.

https://docs.python.org/3/library/math.html

[erlend-aasland]

I see no reason not to build SQLite with -DSQLITE_ENABLE_MATH_FUNCTIONS for the Windows and macOS builds. Why take the detour through the Python library when you can use the built-in SQLite functions?

The change is trivial: Add -DSQLITE_ENABLE_MATH_FUNCTIONS to PCbuild/sqlite3.vcxproj and Mac/BuildScript/build-installer.py.

[erlend-aasland]

Seems like the math functions will be included by default anyways, so we'll have to explicitly exclude them if we don't want them.

https://www.sqlite.org/draft/compile.html#enable_math_functions:
"This option [SQLITE_ENABLE_MATH_FUNCTIONS] is automatically added to the Makefile by the configure script on unix platforms, unless the --disable-math option is used."

[stevendaprano]

It is probably harmless to enable the option, especially if it will be
the SQLite default, but why would you do your maths computations in sql,
using the limited set of functions available, when you could do them in
Python, with a much larger set of available functions and better
debugging facilities?

[rhettinger]

but why would you do your maths computations in sql?

There are number of reasons it might be more convenient in SQL. For example, it could be handy to create views measured in degrees instead of radians. That might make where clauses in queries a little easier to read.

Seems like the math functions will be included by default anyways

So, can this be closed?

[erlend-aasland]

So, can this be closed?

For macOS, the SQLITE_ENABLE_MATH_FUNCTIONS option is enabled by default, but I'm not sure about the Windows build. The SQLite docs says:
"This option is also included on Windows builds using the "Makefile.msc" makefile for nmake."

[erlend-aasland]

Using SQLite 3.35.0 nightly build, building Python using PCbuild/build.bat and PCBuild\pcbuild.sln with VS 2017 (does this reflect how the Windows team build the installers?), I can confirm that math functions are _not_ included by default. I added SQLITE_ENABLE_MATH_FUNCTIONS to PCbuild/sqlite3.vcxproj, but then VS 2017 fails building sqlite:

1>------ Rebuild All started: Project: sqlite3, Configuration: Debug Win32 ------
1>sqlite3.c
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [5]'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [8]'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [6]'
1>Done building project "sqlite3.vcxproj" -- FAILED.
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========

(Note, I've overwritten the files in the externals\sqlite-3.33.0.0 directory with the nightly build.)

[erlend-aasland]

FYI, I've opened a thread on the SQLite Forum: https://sqlite.org/forum/forumpost/721645dc36

[BigStone]

it seems resolved now, at least with VS 2019

[erlend-aasland]

Yes, R. Hipp has fixed this here: https://www2.sqlite.org/cgi/src/info/e5d7209e118a8453
We still need to modify PCbuild in order to build the SQLite library with math functions from 3.35.0 and onwards.

[erlend-aasland]

Steve, I've put up a PR that prepares the Windows build for this. Would you mind taking a look at it?

[erlend-aasland]

See bpo-43492 for upgrading the macOS & Windows installers to SQLite 3.35.0.

[ambv]

New changeset 4bb0a81 by Miss Islington (bot) in branch '3.10':
bpo-42686: Enable SQLite math functions in Windows build (GH-24053) (bpo-25892)
4bb0a81

[ambv]

Merged into 3.10 and 3.11.