SQLite Forum

Math functions for arguments outside the domain should raise exception according to SQL Standard
Login

Math functions for arguments outside the domain should raise exception according to SQL Standard

(1.1) By tarest on 2021-03-17 14:13:23 edited from 1.0 [link] [source]

I compared SQLite implementation of the math functions to the SQL Standard and other RDBMS, and I found one issue.

The SQRT(-1) and LN(-1) functions return NULL, but SQL Standard says they should raise an error. It is implemented according to the SQL Standard in PostgreSQL, Oracle and SQL Server.

If <natural logarithm> is specified, then let V be the value of the simply contained <numeric value expression>.

Case:

  1. If V is the null value, then the result is the null value.
  2. If V is 0 (zero) or negative, then an exception condition is raised: data exception — invalid argument for natural logarithm.
  3. Otherwise, the result is the natural logarithm of V.

Source: page 282 in "Part 2: Foundation" of SQL:2011 draft. Note: I have access only to SQL:2011 draft. It is available in https://www.wiscorp.com/sql20nn.zip archive linked on the https://www.wiscorp.com/SQLStandards.html page.

SQLite returns NULL also for example for ACOS(2), so there can be similar issue, however it isn't described by the SQL:2011 draft.

Is there a reason why SQLite returns NULL instead of raising an error?

(2) By Richard Hipp (drh) on 2021-03-16 22:45:27 in reply to 1.0 [link] [source]

Is there a reason why SQLite return NULL instead of raising an error?

You can catch a NULL using coalesce() or IS NULL, deal with it, and keep processing. But there is no way to catch an exception.

(3) By Keith Medcalf (kmedcalf) on 2021-03-17 20:15:20 in reply to 1.1 [link] [source]

The SQL standard is technically incorrect (or is geared for someone with a primary school education only).

There is an answer to the functions SQRT(-1) and LN(-1). Those answers are not, however, real numbers -- they are what are called "imaginary numbers".

Imaginary numbers cannot be represented as "real numbers" and SQLite3 does not do arithmetic using complex numbers (well, actually it does, the complex part is just always 0j).

Since the answer to SQRT(-1) and LN(-1) have a non-zero imaginary part and the real part is zero (SQRT(-1) -> 1j; LN(-1) -> 3.141592653589793j (which you will note is PI)) these results cannot be represented as real numbers even though the answer does, in fact, exist.

The mathematically correct answer when that answer must be expressed in the real number domain is a NaN (not a number in the real domain). That result is represented by SQLite3 as a NULL (which happens to have the same propagation qualities as a NaN).

So in short, SQLite3 is correct and the SQL standard (and those things which follow that standard) are incorrect.

(5) By Keith Medcalf (kmedcalf) on 2021-03-17 20:22:44 in reply to 3 [link] [source]

That is LN(-1)/1j == pi

(6) By TripeHound on 2021-03-17 23:48:01 in reply to 5 [link] [source]

Or:

 LN(-1)
-------- = PI
SQRT(-1)

(7) By Larry Brasfield (larrybr) on 2021-03-18 00:23:25 in reply to 3 [link] [source]

The SQL standard is technically incorrect (or is geared for someone with a primary school education only).

I would add: "and overly paternalistic". Who is to say it's an error? The answer is simple: No real number. NULL is the closest approximation to that.

SQRT(-1) -> 1j; LN(-1) -> 3.141592653589793j

I think you have revealed yourself as being trained in electrical engineering. Mathematics folks say 'i' rather than 'j'.

... the answer does, in fact, exist.

Only in an imaginary way. If somebody adjusts your bank balance by that amount, you need to be thinking about doing business elsewhere.

(8) By Keith Medcalf (kmedcalf) on 2021-03-18 01:18:46 in reply to 7 [link] [source]

I think you have revealed yourself as being trained in electrical engineering. Mathematics folks say 'i' rather than 'j'.

LOL. Systems Engineering as in Process Control. In either case the glass is twice as big as it needs to be :)

(9) By Richard Damon (RichardDamon) on 2021-03-18 02:02:22 in reply to 3 [link] [source]

Actually, the question is which definition of 'the reals' is it using. For the true mathematical reals, sqrt(-1) is a domain error, so an error message would be appropriate.

Only when you extend the reals to add the Not-a-Number value, can you include that in the possible results. Since SQL predates the IEEE-754 definition for floating-point representations, which is as far as I know the origin of the NAN value, at least as far as a general definition. it isn't surprising that it adopted some conventions that disagree with the later standard.

(4) By Keith Medcalf (kmedcalf) on 2021-03-17 20:18:55 in reply to 1.1 [link] [source]

ACOS(2) -> -1.3169578969248166j which would also be a NaN when expressed as a real number.

(10) By anonymous on 2021-03-18 17:10:28 in reply to 1.1 [link] [source]

This discussion is going nowhere. And the simple reason is that both sides of the argument are correct.

Yes, the SQL standard says that an invalid argument should raise an exception. Is this 'incorrect'? Is this 'overly paternalistic'? Perhaps, but it is the standard.

Yes, you can catch a NULL using coalesce(). But dealing with it? If you expect NULL then there is no problem, but getting NULL when you do not expect this is the equivalent of 'one of the parameters handed to this function is wrong; good luck figuring out which one and why'.

I was facing a similar decision a while ago when writing an extension for calendar calculations: do I raise an exception or is NULL better? Some of the functions involved have four parameters - getting NULL as a result was unworkable. The solution? Do not make a decision.

The extension now checks for an environment variable SQLITE3_TCAL_ERRORS during initialisation. If this variable exists any function in this extension when confronted with a bad parameter raises an exception with a detailed message telling which parameter was wrong and why. If this variable does not exist the same error results in a NULL.

I suggest a similar solution for this problem (maybe using a pragma). As the currect behavior is returning NULL that should be the default and for those of us that require the SQL standard (or are still debugging a query) that would also be possible.

(11) By David Raymond (dvdraymond) on 2021-03-18 17:52:43 in reply to 10 [link] [source]

How about adding 2 argument versions where the second argument is an onError argument which explicitly states what you want it to do in that case?

That would put it in the hands of the query writer as opposed to being in the hands of the system owner in the environment variable version. Which is better I guess would be debatable.

(12) By anonymous on 2021-03-18 17:56:14 in reply to 11 [source]

Then a pragma would be a better solution.

The drawback of a two-argument version is that it requires changing a query or view to debug.

(13) By Stephan (stephancb) on 2021-03-19 06:09:49 in reply to 10 [link] [source]

Computing systems like Matlab, Python, Julia, ... do raise an exception for expressions like log(-1) even if returning a complex value in principle would be an option (one needs to use log(-1+0i) to avoid the exception). But these systems do also provide mechanisms to catch exceptions. The SQL standard is short-sighted in the sense that if raising exceptions is specified then also an SQL catch mechanism should have been provided. Therefore I find a non-compliance of Sqlite here acceptable.

Mathematical analytic continuation is not very useful if the value in question represents a real world parameter such as a density or temperature. When measuring such parameters, impossible negative values can occur for various reasons (instrumental offsets, noise, ...) and must be dealt with in the data processing. A good strategy is to check the value before functions like log are called, and then conditionally do something appropriate.

(14) By anonymous on 2021-03-19 13:58:45 in reply to 13 [link] [source]

The SQL standard is short-sighted in the sense that if raising exceptions is specified then also an SQL catch mechanism should have been provided.

Not really. Every host-language I have used for SQL (COBOL, PHP, C, Python, AutoIt3, Java) has ways to catch exceptions. But ... in order for a host-language to correctly handle a caught exception it needs information and a NULL is just not enough.

The one place where a missing SQL catch mechanism is an issue is an abused CLI. Abused? Yes. As its name implies the purpose of the CLI is to interactively execute SQL statements. That means that for every statement and every exception there is someone at the keyboard. Now you can use or abuse the CLI to execute complex multi-statement SQL, but with said CLI lacking any flow-control that is not ideal (there is a limited flow-control when setting .bail on which exits a batch CLI after an exception). So similar to host languages SQL exception handling should be done by the CLI, not by SQL.

A good strategy is to check the value before functions like log are called, and then conditionally do something appropriate.

Exactly! Cleanup the data before pushing it into any function with restrictions on its parameters. And if the input has not been cleaned let it throw an exception. An exception should be a hint to check your data - sweeping bad input under the carpet by quietly handling NULL is a bad idea.