Hi. I'm a php-src contributor.

A php-src issue reported something like this: https://github.com/php/php-src/issues/12076

Please see the comment for the issue for details, but when I execute the insert statement via php, the value of the REAL type differs from the expected value.

This only happens on version 3.43 in a 32bit environment.

Also, if you bind a value to insert using a prepared statement, the expected value will be inserted.

It seems to me that the last binary digit in the internal representation of floating point numbers is not rounded correctly.

It is not yet clear whether this is a problem caused by php, but considering that it is possible to operate properly with previous versions of SQLITE, I thought that it might be caused by SQLITE 3.43, so I reported it.

Thank you.

(Edited only for spelling and IEEE standard cite.)

Looking through the link to see what this "bug" is about, I see that what is said, essentially, is "This PHP test suddenly fails on x86 arch." It says nothing regarding why this is a SQLite bug. There is an inference, (which I see as likely correct), that the PHP test failure arises from a recent change in SQLite behavior.

There has been a change in how, precisely, character sequences representing floating point numbers are converted to the internal, IEEE-754 representation of the "double" floating point values. The current code is considered by the SQLite developers to be more correct and has been made more consistent across platforms.

Given these facts, I submit that those who see a PHP "bug" consider very carefully whether the PHP test suite which shows such remains correct. It is not sufficient to say "It's a SQLite bug because we changed nothing."

If somebody wants to claim that SQLite is producing incorrect conversions from textual FP representation to internal binary FP representation, that claim should be supported with a short script which the SQLite CLI can execute and which demonstrates the problem. And "the problem", to be considered a SQLite bug, will have to be more than "Versions before 3.42 did this while version 3.42 does thus." It will need to be along the lines of "This textual input does not round-trip." or "This textual input produces this binary which, per IEEE-754 conventions, is incorrect."

BTW, it is a huge (and likely ignored) ask to suggest that somebody on the SQLite dev team should load up a PHP implementation development environment and tools so that this PHP test anomaly can be investigated. If an alleged SQLite bug cannot be cast as a misbehavior of SQLite-provided source with the SQLite-provided CLI, it is very unlikely to be considered a SQLite bug.

Thank you.

I will try to verify a little more whether it can be reproduced with sqlite alone or not.

I didn't mean to ask you to prepare a php development environment. I'm not familiar with sqlite internals, but I had a feeling someone familiar with this issue might find something. I'm sorry if I misled you. And I am grateful for the useful information I received from you.

By the way, I think that the internal representation of floating point numbers is generally IEEE754, but does it use IEEE488?

I have never heard of IEEE488 as a standard related to floating point numbers, so if so, I will study it more.

does it use IEEE488?

No, he's plainly just experienced a wetware hash collision. :)

Our Larry must have some electrical engineering in his background…

Thank you. Apparently I don't have to go to the bookstore to look for reference books on electrical engineering.

I tried it earlier with ruby ​​under the same conditions, but it did not reproduce. Assuming that the problem is caused by php, I will continue investigating.

Thank you!

does it use IEEE488?

As I recall, without going to look it up, that is a standard that covers an instrument control bus which was once prevalent. I hopefully attribute that mis-reference¹ to the late hour and work on another project which seems to have activated the EE in me.

Fixed now.

1. ^{^} More accurately: The notion I need not look it up, yet again.

I didn't mean to ask you to prepare a php development environment.

I should not have intimated otherwise. Please understand that my posts sometimes bring out points which are directed to folks other than the nominal OP. With the benefit of a night's sleep, I would rephrase that point to avoid suggesting an unreasonable demand.

do you have any plans to support specifying the FPU's computational accuracy to 53 bits

I do not understand what you are asking, exactly. But if I am guessing correctly at your meaning, I think SQLite already does this using code like this:

if( sizeof(long double)>8 ){
  /* CPU has high-precision floating point */
} else {
  /* CPU has only IEEE-754 64-bit binary floating point support */
}

The test is normally only true on x86 and x64 processors. SQLite uses different text-to-float and float-to-text conversion logic depending on the answer. The x86/x64 logic runs faster.

I don't know what the FNSTCW and FLDCW opcodes do on x64, but my guess is that they disable the high-precision floating point hardware, causing all computations to be done in ordinary IEEE 754 binary-64. Since the source code still sees "sizeof(long double)" as 10 or 16, it does not know that you have disabled the high-precision floating point, and so it chooses the wrong algorithm.

The SQLITE_TESTCTRL_USELONGDOUBLE sqlite3_test_control() can be used to change algorithms on-the-fly. But that interface is specifically for testing purposes only. You should not use it in your application. The behavior of that interface is subject to change without notice.

I am sorry you are having trouble with floating point. Please read about Floating Point Numbers in the documentation, and especially the first sentence of section 1.1:

SQLite promises to preserve the 15 most significant digits of a floating point value.

It seems to me that you are complaining that SQLite is not preserving the 16-th significant digit on a text-to-float or float-to-text conversion. But that is just a guess, because you have not yet provided us with a test case.

It seems to me that the last binary digit in the internal representation of floating point numbers is not rounded correctly.

Is this a difference that is only observable when converting values from a string? Does it occur when using a bound variable (with the value bound as a double precision float, not a string) to insert the value?

The conversion of values between strings and floats is full of hazards, most of which generate about 1 ULP of error sometimes. (I very much doubt that SQLite will ever set flags in the floating point control word. That route lies towards warring with other libraries.)

Is this a difference that is only observable when converting values from a string? Does it occur when using a bound variable (with the value bound as a double precision float, not a string) to insert the value?

As you said, binding the float value will give you accurate results. Problems will occur if you use a statement that is all strings that are never bound, or if you intentionally bind with a string type.

And although it's a statement with no practicality, I get 0 when I run something like this:

SELECT ? == CAST((SELECT CAST(? as TEXT)) as REAL);

Alas, the code required to get that right is surprisingly large. Loss-free conversion between floating point values and strings (in either direction) is difficult to get right, and tends to require things like multi-precision arithmetic libraries (though fortunately not arbitrary precision) as there are some horrible edge cases.

I think it's entirely acceptable for SQLite to punt the problem to the C library, at least with default build options. (It'd be lovely to be able to plug in custom converters so that language bindings that have access to something better can use it. I know that Tcl is one such language. Python may also be; I've not looked there.)

I think it's entirely acceptable for SQLite to punt the problem to the C library

Except it doesn't. TCL punts to the C library but SQLite implements its own text-to-float and float-to-text conversions. There are multiple reasons for this:

• C-library conversions vary by LOCALE.

• Different C-library implementations give differing answers, especially on older and obscure platforms.

• C-library conversion routines do not work with UTF-16 (or at least not that I am aware of).

• The SQLite reimplementation of these routines is almost 4x faster than the GNU C Library versions on my Ubuntu desktop.

The downside of the SQLite home-grown conversion routines is that they are slightly less precise than the GNU versions.

Tcl definitely does not punt formatting of doubles as strings, but it might punt parsing. Kevin Kenny wrote the formatter and its test suite. (Before anyone thinks that means the code's easy, it's 5000 lines of code and necessitated adding a bignum handling library. I forget the compiled size, but I suspect it is too large for SQLite.) This means that Tcl strongly has the property that any floating point number can be converted to a string and back to obtain the exact same floating point representation, and also the property that the string representation of that value is the shortest such (decimal) representation.

I remember a kerfuffle a decade or so ago when someone found a fault in the glibc implementation that turned string parsing into a denial-of-service attack. Kevin just said "oh, I forgot that exact case in the test suite; it works, but I'd not put it in because I had cases one ULP away from it".

Hi, Donal, Richard. Thank you for your replies.

First of all, I apologize for my poor English. English is difficult for Japanese.

Next, to the main topic, this phenomenon has been seen since sqlite3.43.0. I'm not familiar with the internals of sqlite yet, so I'm having trouble understanding why the latest version can't do what the previous version did.

I'm guessing this is a BC-breaking change as a result, but I'm curious if there are any plans to restore the previous behavior.

I need to know sqlite's official policy on this issue first. Otherwise, I can't even suggest a fix for the php driver.

I'm guessing this is a BC-breaking change as a result

You have not yet shown us the problem. There is nothing we can do to address your problem unless you tell us precisely what your problem is. Please provide an example that we can run that demonstrates a change in behavior that you think needs to be fixed. Ideally, your demonstration would be in the form of an SQL script that we can run using the sqlite3 command-line interface.

Since the FPU control word is involved, it is difficult to provide test cases in a simple interactive shell. I'm wondering if there is any way to do this. The easiest way I can come up with at the moment is to provide C like in some previous posts.

If you don't need it, please skip reading from here.

In my environment, I have two i386/Alpine docker containers, one built with Sqlite 3.43 and the other with 3.42.

First, create a table to use for testing.

CREATE TABLE TEST (id INT, env TEXT, value REAL);

Then, prepare the following C and compile it with gcc. This is for 3.43:

#include <stdio.h>

#include <sqlite3.h>

int main() {

    unsigned int oldcw, cw;
    __asm__ __volatile__ ("fnstcw %0" : "=m" (*&oldcw)); \
    cw = (oldcw & ~0x100) | 0x200; \
    __asm__ __volatile__ ("fldcw %0" : : "m" (*&cw)); \

    sqlite3* db;
    sqlite3_open("sq", &db);

    sqlite3_exec(
        db,
        "INSERT INTO test VALUES (1, '32bit 3.43.0', 3.14);",
        NULL,
        NULL,
        NULL
    );

    sqlite3_close(db);

    return 0;

}

For Sqlite3.42, the SQL statement is slightly rewritten, and the rest is exactly the same:

INSERT INTO test VALUES (2, '32bit 3.42.0', 3.14);

Compile and run each of these. After the two C inserts are completed, check the record from the interactive shell. It doesn't matter which container you use, as the interactive shell will produce the same results when started in either container.

sqlite> SELECT * FROM test;
1|32bit 3.43.0|3.14
2|32bit 3.42.0|3.14

Compare values.

sqlite> SELECT (SELECT value from test where id = 1) == (SELECT value from test where id = 2);
0

You can see that different values ​​were inserted in 3.43 and 3.42.

In addition, 3.43.0:

sqlite> SELECT (SELECT value from test where id = 1) == 3.14;
0

sqlite> SELECT (SELECT value from test where id = 1) == 3.1399999999999997;
1

3.42.0:

sqlite> SELECT (SELECT value from test where id = 2) == 3.14;
1

sqlite> SELECT (SELECT value from test where id = 2) == 3.1399999999999997;
0

Questions:

1. Do I understand correctly that you cannot reproduce the problem without the use of in-line ASM?

2. What result do you get from "SELECT *, ieee754(value) FROM test;".

Do I understand correctly that you cannot reproduce the problem without the use of in-line ASM?

Yes, I can't think of any other way.

What result do you get from "SELECT *, ieee754(value) FROM test;".

The results are as follows:

sqlite> SELECT *, ieee754(value) FROM test;
1|32bit 3.43.0|3.14|ieee754(3535325707485839,-50)
2|32bit 3.42.0|3.14|ieee754(7070651414971679,-51)

I can't think of any other way [other than to use in-line assembly to reduce the precision of "long double" computations in the floating point hardware].

This is not something I want to fix. I call this a misuse of SQLite. I call it a bug in your PHP test.

To recap: SQLite uses a test of the form "sizeof(long double)>8" to determine if long double type supports high-precision floating point values. If this test shows that the CPU does support high-precision long doubl computation, then SQLite uses a faster algorithm for doing text-to-binary floating point conversions. If the CPU does not support high-precision floating point, then SQLite uses a slower algorithm that gives good results even with ordinary IEEE 756 binary-64 computations.

Your in-line assembly is messing up this test by configuring the FPU to use lower-precision binary-64 even on a long double. That leads to minor inaccuracies, as you have observed.

Possible solutions that you can employ:

1. Do not use in-line assembly to artificially lower the precision of computations involving "long double".

2. Run "sqlite3_test_control(SQLITE_TESTCTRL_USELONGDOUBLE,0);" to prevent SQLite from using "long double" and to force it to use the slower algorithm that gives full accuracy with ordinary binary-64.

Note that #2 works in SQLite 3.43.0, but it is not a supported interface and is not recommended. You really should be doing #1.

By the way, when I changed the FPU control word in the same way, I got the same result on 64bitOS.

The reason why I took the trouble to prepare a 32-bit environment was simply because 32-bit OS was the only one that could be reproduced with php.

Definitely looks like a 1 ULP error in the floating point number parser.

If we compare the bit patterns for the two numbers you're using a test case (I'm using Tcl where I know the numerics are right because I know they're tested and validated; you may try replicating these things in your favourite language) then we see:

% proc bitPattern {value} {
    # Exact bit pattern depends on machine architecture
    binary scan [binary format d $value] B* x; set x
}
% bitPattern 3.14
0001111110000101111010110101000110111000000111100000100101000000
% bitPattern 3.1399999999999997
0001111010000101111010110101000110111000000111100000100101000000
#      ^ Here is the only difference

There is a single bit difference in the least significant digit of the mantissa. That's a 1-ULP difference, and shows that you are comparing very fine differences indeed. It's a relatively common level of error in numeric algorithms, especially those optimized for speed. (Note that this is a 1 in about 10¹⁶ error. In virtually all values derived from physical quantities, this is accuracy you'll never practically attain.)

In production code, the suggested change is to use bound parameters of queries to avoid conversion to or from strings. That doesn't help with your test cases, but it does suggest that moving floats in or out of SQLite is best not done via strings, just as you should also do with longer strings or BLOBs.

Using the CLI, we see similar:

sqlite> .mode qbox
sqlite> select ieee754_to_blob(3.14);
┌───────────────────────┐
│ ieee754_to_blob(3.14) │
├───────────────────────┤
│ x'40091eb851eb851f'   │
└───────────────────────┘
sqlite> select ieee754_to_blob(3.1399999999999997);
┌─────────────────────────────────────┐
│ ieee754_to_blob(3.1399999999999997) │
├─────────────────────────────────────┤
│ x'40091eb851eb851e'                 │
└─────────────────────────────────────┘

But the conversion looks good to me (since I have not modified floating point hardware using assembly instructions, as Saki has):

sqlite> select decimal(3.14);
┌─────────────────────────────────────────────────────────┐
│                      decimal(3.14)                      │
├─────────────────────────────────────────────────────────┤
│ '3.140000000000000124344978758017532527446746826171875' │
└─────────────────────────────────────────────────────────┘
sqlite> select decimal(3.1399999999999997);
┌────────────────────────────────────────────────────────┐
│              decimal(3.1399999999999997)               │
├────────────────────────────────────────────────────────┤
│ '3.13999999999999968025576890795491635799407958984375' │
└────────────────────────────────────────────────────────┘

Thank you both for validating this.

ieee754_to_blob

I didn't know this, so it was a learning experience.

Perhaps this is a problem that has surfaced because, unlike other databases, Sqlite uses direct API calls (in other words, it does not use socket communication, etc.).

Anyway,

Do not use in-line assembly to artificially lower the precision of computations involving "long double".

You really should be doing #1.

Considering the information you provided, I think this is correct. Thank you very much for the alternative suggestion too.

I thought that change #1 was impossible, at least at the patch version level, so #2, which has fewer side effects, is very helpful.

I didn't know this

This is an extension, bundled with the shell (by default?).

It's always there with current code.

I don't know how to close this thread...

I don't know how to close this thread...

There is no need. If you're satisfied with the responses, and you have acquired your answer, this thread served its purpose. However, someone else may ask another question based on one of the answers or post, and it could continue. But, the original intention of your post was fulfilled.

There is no need. If you're satisfied with the responses, and you have acquired your answer, this thread served its purpose. However, someone else may ask another question based on one of the answers or post, and it could continue. But, the original intention of your post was fulfilled.

I see. Thank you!

Hi. I would like to ask you again about this issue.

I reproduced the problem using inline assembly, but there are APIs with similar functionality on major platforms.

There's also _controlfp_s on Windows, fpsetprec on FreeBSD and _FPU_SETCW on Linux.

Is it against the philosophy of SQLite to consider cases where APIs like these are used?

I would assume that such settings are the proper responsibility of the application that embeds the SQLite library, and that the sqlite shell program won't set them and will get whatever the OS believes to be a suitable default choice. (To do otherwise is to have the potential for warring between different libraries over the FP control word setting, and would lead to things needing to be ultra-careful when resuming processing to obtain the next row of a result set. Yuck!) You are free to create programs that have other choices.

You can always accurately move a double into a query as a bound parameter and extract one from a result set, so places where exact handling of floating point values is actually critical already have simple workarounds.

I see, I understood your point.

If so, why isn't this mentioned in the release notes? There is no mention in the release notes that the changes have been made to ignore the FPU control word.

Since the user experience is changing, if it is an intentional specification change, shouldn't it be made public?

I think that changes in the user experience that are not mentioned in the release notes are generally called side effects or bug, but in this case, it is not a side effect, an intended change, right?

Please download the newly updated SQLite source code at check-in 9a854b919667e0e6 and rerun your test cases using that new check-in. Report back whether or not that check-in resolves your issue.

Thanks for the code fix! I tried immediately.

Sorry for the hard to see results, but all tests are working as expected.

Test by C

#include <stdio.h>

#include <sqlite3.h>

int main() {

unsigned int oldcw, cw;

__asm__ __volatile__ ("fnstcw %0" : "=m" (*&oldcw)); \

cw = (oldcw & ~0x100) | 0x200; \

__asm__ __volatile__ ("fldcw %0" : : "m" (*&cw)); \

sqlite3* db;
sqlite3_open("sq", &db);

sqlite3_exec(
    db,
    "INSERT INTO test VALUES (1, '32bit 3.43.x', 3.14);",
    NULL,
    NULL,
    NULL
);

sqlite3_close(db);

return 0;

}

sql insert by sqlite3 client:

INSERT INTO test VALUES (2, '32bit 3.43.x', 3.14);

results:

/var/www/html # php82 read.php

array(3) {

["id"]=>

int(1)

["env"]=>

string(12) "32bit 3.43.x"

["value"]=>

float(3.14)

}

array(3) {

["id"]=>

int(2)

["env"]=>

string(12) "32bit 3.43.x"

["value"]=>

float(3.14)

}

Test by php (cast)

<?php

$db = new SQLite3('sq');

$s = $db->prepare('SELECT ? == CAST((SELECT CAST(? as TEXT)) as REAL);');

$s->bindValue(1, 3.14);

$s->bindValue(2, 3.14);

$r = $s->execute();

var_dump($r->fetchArray(SQLITE3_ASSOC));

result:

/var/www/html # php82 cast.php

array(1) {

["? == CAST((SELECT CAST(? as TEXT)) as REAL)"]=>

int(1)

}

Test by php (write)

<?php

$db = new SQLite3('sq');

$db->exec("INSERT INTO test VALUES (3, '32bit php 3.43.x', 3.14);");

$db->close();

result:

array(3) {

["id"]=>

int(3)

["env"]=>

string(16) "32bit php 3.43.x"

["value"]=>

float(3.14)

}