sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Timothy J. Lee
sqlite 3.21.0, built from source on Scientific Linux 6
(which is derived from Red Hat Enterprise Linux 6).

configure options were:
--enable-threadsafe --enable-threads-override-locks --enable-load-extension

CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall -fno-strict-aliasing"

On i386:

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite>

On x86_64:

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9223372036854775807
sqlite>

This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6 e_expr-32.2.8
to fail on i386 (the output on x86_64 is the expected output).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Petr Kubat
Encountering this in Fedora as well while trying to package latest
3.21.0 version. Only on i386.

Additionally the test suite fails on some architectures (aarch64, ppc64,
s390x) when running the test case fts3expr5-1.5:

! fts3expr5-1.5 expected: [1 {invalid matchinfo blob passed to function rank()}]
! fts3expr5-1.5 got:      [1 {wrong number of arguments to function rank()}]


On 10/28/2017 12:43 AM, Timothy J. Lee wrote:

> sqlite 3.21.0, built from source on Scientific Linux 6
> (which is derived from Red Hat Enterprise Linux 6).
>
> configure options were:
> --enable-threadsafe --enable-threads-override-locks
> --enable-load-extension
>
> CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1
> -DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1
> -DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall
> -fno-strict-aliasing"
>
> On i386:
>
> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9.22337203685478e+18
> sqlite>
>
> On x86_64:
>
> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9223372036854775807
> sqlite>
>
> This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6
> e_expr-32.2.8
> to fail on i386 (the output on x86_64 is the expected output).
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Petr Kubat
Still present in 3.22.0:

! e_expr-32.2.5 expected: [integer 9223372036854775807]
! e_expr-32.2.5 got:      [real 9.22337203685478e+18]
! e_expr-32.2.6 expected: [integer 9223372036854775807]
! e_expr-32.2.6 got:      [real 9.22337203685478e+18]
! e_expr-32.2.8 expected: [integer 9000000000000000001 integer 9000000000000000001 integer 9000000000000000001 integer 9000000000000000001 integer 9000000000000000001 integer 9000000000000000001 integer 9223372036854775807 integer 9223372036854775807 integer 9223372036854775807 real 9.22337203685478e+18 real 9.22337203685478e+18 integer 9223372036854775807 integer 9223372036854775807 integer -5 integer -5]
! e_expr-32.2.8 got:      [integer 9000000000000000001 real 9.0e+18 real 9.0e+18 real 9.0e+18 integer 9000000000000000001 real 9.0e+18 integer 9223372036854775807 real 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18 integer -5 integer -5]

This seems to be happening whenever a string not ending with a number
('9000000000000000001x', '9000000000000000001 ',' 9000000000000000001.')
gets cast to NUMERIC.

Is there any more information I can provide to get this looked at?

On 11/01/2017 01:07 PM, Petr Kubat wrote:

> Encountering this in Fedora as well while trying to package latest
> 3.21.0 version. Only on i386.
>
> Additionally the test suite fails on some architectures (aarch64,
> ppc64, s390x) when running the test case fts3expr5-1.5:
>
> ! fts3expr5-1.5 expected: [1 {invalid matchinfo blob passed to
> function rank()}]
> ! fts3expr5-1.5 got:      [1 {wrong number of arguments to function
> rank()}]
>
>
> On 10/28/2017 12:43 AM, Timothy J. Lee wrote:
>> sqlite 3.21.0, built from source on Scientific Linux 6
>> (which is derived from Red Hat Enterprise Linux 6).
>>
>> configure options were:
>> --enable-threadsafe --enable-threads-override-locks
>> --enable-load-extension
>>
>> CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1
>> -DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1
>> -DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall
>> -fno-strict-aliasing"
>>
>> On i386:
>>
>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>> 9.22337203685478e+18
>> sqlite>
>>
>> On x86_64:
>>
>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>> 9223372036854775807
>> sqlite>
>>
>> This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6
>> e_expr-32.2.8
>> to fail on i386 (the output on x86_64 is the expected output).
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Richard Hipp-3
I'm still unable to reproduce this problem.  I've tried on every
32-bit platform I have at hand:

   * Ubuntu with -m32
   * Android
   * MacOS 10.6.8  with -m32
   * MinGW (32-bit) on Win7
   * MSVC (32-bit) on Win10

They all give the correct answer.   I'm sorry you are having problems.
But it is difficult for me to fix the problem if I am unable to
reproduce it.

On 1/23/18, Petr Kubat <[hidden email]> wrote:

> Still present in 3.22.0:
>
> ! e_expr-32.2.5 expected: [integer 9223372036854775807]
> ! e_expr-32.2.5 got:      [real 9.22337203685478e+18]
> ! e_expr-32.2.6 expected: [integer 9223372036854775807]
> ! e_expr-32.2.6 got:      [real 9.22337203685478e+18]
> ! e_expr-32.2.8 expected: [integer 9000000000000000001 integer
> 9000000000000000001 integer 9000000000000000001 integer 9000000000000000001
> integer 9000000000000000001 integer 9000000000000000001 integer
> 9223372036854775807 integer 9223372036854775807 integer 9223372036854775807
> real 9.22337203685478e+18 real 9.22337203685478e+18 integer
> 9223372036854775807 integer 9223372036854775807 integer -5 integer -5]
> ! e_expr-32.2.8 got:      [integer 9000000000000000001 real 9.0e+18 real
> 9.0e+18 real 9.0e+18 integer 9000000000000000001 real 9.0e+18 integer
> 9223372036854775807 real 9.22337203685478e+18 real 9.22337203685478e+18 real
> 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18
> real 9.22337203685478e+18 integer -5 integer -5]
>
> This seems to be happening whenever a string not ending with a number
> ('9000000000000000001x', '9000000000000000001 ',' 9000000000000000001.')
> gets cast to NUMERIC.
>
> Is there any more information I can provide to get this looked at?
>
> On 11/01/2017 01:07 PM, Petr Kubat wrote:
>> Encountering this in Fedora as well while trying to package latest
>> 3.21.0 version. Only on i386.
>>
>> Additionally the test suite fails on some architectures (aarch64,
>> ppc64, s390x) when running the test case fts3expr5-1.5:
>>
>> ! fts3expr5-1.5 expected: [1 {invalid matchinfo blob passed to
>> function rank()}]
>> ! fts3expr5-1.5 got:      [1 {wrong number of arguments to function
>> rank()}]
>>
>>
>> On 10/28/2017 12:43 AM, Timothy J. Lee wrote:
>>> sqlite 3.21.0, built from source on Scientific Linux 6
>>> (which is derived from Red Hat Enterprise Linux 6).
>>>
>>> configure options were:
>>> --enable-threadsafe --enable-threads-override-locks
>>> --enable-load-extension
>>>
>>> CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1
>>> -DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1
>>> -DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall
>>> -fno-strict-aliasing"
>>>
>>> On i386:
>>>
>>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>>> 9.22337203685478e+18
>>> sqlite>
>>>
>>> On x86_64:
>>>
>>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>>> 9223372036854775807
>>> sqlite>
>>>
>>> This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6
>>> e_expr-32.2.8
>>> to fail on i386 (the output on x86_64 is the expected output).
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Joseph R. Justice
On Jan 23, 2018 9:32 AM, "Richard Hipp" <[hidden email]> wrote:

I'm still unable to reproduce this problem.  I've tried on every
32-bit platform I have at hand:

   * Ubuntu with -m32
   * Android
   * MacOS 10.6.8  with -m32
   * MinGW (32-bit) on Win7
   * MSVC (32-bit) on Win10

They all give the correct answer.   I'm sorry you are having problems.
But it is difficult for me to fix the problem if I am unable to
reproduce it.


Is it possible Mr. Lee or Mr. Kubat could provide a guest login to Dr. Hipp
on a system exhibiting this problem, so that he might be able to
investigate it on a system known to be having the problem?



Joseph
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Ralf Junker
In reply to this post by Richard Hipp-3
On 23.01.2018 15:31, Richard Hipp wrote:

> I'm still unable to reproduce this problem.

sqlite3.exe from this ZIP:

   https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip

Running on Windows 7:

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
9223372036854775807

Notice the trailing white space which makes the difference.

Ralf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Hick Gunter
I think I may have found the problem:

The ToNumeric opcode calls sqlite3VdbeMemNumerify() which has (in 3.7.14.1) the following code:

    if( 0==sqlite3Atoi64(pMem->z, &pMem->u.i, pMem->n, pMem->enc) ){
      MemSetTypeFlag(pMem, MEM_Int);
    }else{
      pMem->r = sqlite3VdbeRealValue(pMem);
      MemSetTypeFlag(pMem, MEM_Real);
      sqlite3VdbeIntegerAffinity(pMem);
    }

So the result is an integer only oif the sqlite3Atoi64 function returns 0

This function hast he following head:

int sqlite3Atoi64(const char *zNum, i64 *pNum, int length, u8 enc){
  int incr = (enc==SQLITE_UTF8?1:2);
  u64 u = 0;
  int neg = 0; /* assume positive */
  int i;
  int c = 0;
  const char *zStart;
  const char *zEnd = zNum + length;
...

Where the superflous space at the end of the string causes the following condition to be TRUE

  if( (c!=0 && &zNum[i]<zEnd) || (i==0 && zStart==zNum) || i>19*incr ){
    /* zNum is empty or contains non-numeric text or is longer
    ** than 19 digits (thus guaranteeing that it is too large) */
    return 1;

The conversion/counting loop inbetween needs to set zEnd to the last character scanned to fix this.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ralf Junker
Gesendet: Dienstag, 23. Jänner 2018 16:22
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

On 23.01.2018 15:31, Richard Hipp wrote:

> I'm still unable to reproduce this problem.

sqlite3.exe from this ZIP:

   https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip

Running on Windows 7:

SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
9223372036854775807

Notice the trailing white space which makes the difference.

Ralf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Jim Callahan
In reply to this post by Ralf Junker
What locale?
The locale setting may influence character to numeric conversions at the C
language library level.

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9.22337203685478e+18
> sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
> 9223372036854775807
> Notice the trailing white space which makes the difference.


Although U.S. dollar users are used to the currency symbol to the left of
the digits; in some countries
the currency symbol is to the right of the digits and sometimes there is a
space between the digits and the currency symbol.

Currencies are often represented as decimals (except when accounting
systems use integer pennies) and so a trailing space
in some or all locales may trigger an assumption of either a monetary or
floating point value.

The locale could impact something as low level as an atoi() or atol() C
conversion functions.
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rtref/itol.htm#itol

"These members are 1 if the currency_symbol or int_curr_symbol strings
should precede the value of a monetary amount, or 0 if the strings should
follow the value. The p_cs_precedes and int_p_cs_precedes members apply to
positive amounts (or zero), and the n_cs_precedes and int_n_cs_precedes members
apply to negative amounts."
...
"These members are 1 if a space should appear between the currency_symbol
 or int_curr_symbol strings and the amount, or 0 if no space should appear.
The p_sep_by_space and int_p_sep_by_space members apply to positive amounts
(or zero), and the n_sep_by_space and int_n_sep_by_space members apply to
negative amounts."
https://www.gnu.org/savannah-checkouts/gnu/libc/manual/html_node/Currency-Symbol.html#Currency-Symbol

"In many European countries such as France, Germany, Greece, Scandinavian
countries, the symbol is usually placed after the amount (e.g., 20,50 €)."
Note space between amount and Euro symbol.
https://en.wikipedia.org/wiki/Currency_symbol

Scientific Linux is used at CERN and ETHZ and other European facilities?

So, my guess would be that the space after the number, plus certain locales
would reproduce the issue.


Jim Callahan
Callahan Data Science LLC
Orlando, FL



On Tue, Jan 23, 2018 at 10:22 AM, Ralf Junker <[hidden email]> wrote:

> On 23.01.2018 15:31, Richard Hipp wrote:
>
> I'm still unable to reproduce this problem.
>>
>
> sqlite3.exe from this ZIP:
>
>   https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip
>
> Running on Windows 7:
>
> SQLite version 3.22.0 2018-01-22 18:45:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
> 9.22337203685478e+18
> sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
> 9223372036854775807
>
> Notice the trailing white space which makes the difference.
>
> Ralf
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Richard Hipp-3
In reply to this post by Petr Kubat
On 1/23/18, Petr Kubat <[hidden email]> wrote:
> Still present in 3.22.0:
>
> ! e_expr-32.2.5 expected: [integer 9223372036854775807]
> ! e_expr-32.2.5 got:      [real 9.22337203685478e+18]
>
> Is there any more information I can provide to get this looked at?

Ralf provided the information I needed, which was a method of
reproducing the problem.  It is now fixed on trunk.  See
https://www.sqlite.org/src/info/1b02731962c21bb0 for the patch.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Tony Papadimitriou
In reply to this post by Ralf Junker
If it helps, I can reproduce with the mentioned binary on Win7 but I cannot
with my own compiled version (using MSVC).

-----Original Message-----
From: Ralf Junker

On 23.01.2018 15:31, Richard Hipp wrote:

> I'm still unable to reproduce this problem.

sqlite3.exe from this ZIP:

   https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip

Running on Windows 7:

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
9223372036854775807

Notice the trailing white space which makes the difference.

Ralf

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

Petr Kubat
In reply to this post by Richard Hipp-3
Thanks for the patch! Tests on i686 are green when included.

There are still some failures for other architectures but I will make
separate threads for these once I investigate.

Petr

On 01/23/2018 06:35 PM, Richard Hipp wrote:

> On 1/23/18, Petr Kubat <[hidden email]> wrote:
>> Still present in 3.22.0:
>>
>> ! e_expr-32.2.5 expected: [integer 9223372036854775807]
>> ! e_expr-32.2.5 got:      [real 9.22337203685478e+18]
>>
>> Is there any more information I can provide to get this looked at?
> Ralf provided the information I needed, which was a method of
> reproducing the problem.  It is now fixed on trunk.  See
> https://www.sqlite.org/src/info/1b02731962c21bb0 for the patch.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users