Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Inconsistent behavior between dbl (SQL double) and flt (SQL real) data types and across platforms #3696

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Inconsistent behavior between dbl (SQL double) and flt (SQL real) data types and across platforms #3696

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2015-04-02 17:37:48 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)

Last updated: 2015-05-07 12:38:10 +0200

Comment 20758

Date: 2015-04-02 17:37:48 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:36.0) Gecko/20100101 Firefox/36.0
Build Identifier:

A double accepts conversion of special values such as 'Inf', 'Infinity', 'inf', 'infinity' where a float (SQL real) does not accept these (return an error). However the negative infinity ('-Inf', '-Infinity', '-inf', '-infinity') is accepted for both data types. A strange inconsistency.

Also on MS Windows for a float (SQL real) the special values 'NaN', '-NaN', 'Inf', 'Infinity', 'inf', 'infinity', '-Inf', '-Infinity', '-inf', '-infinity' are NOT accepted at all resulting in different MonetDB behavior across platforms.
See for instance http://monetdb.cwi.nl/testweb/web/showtestoutput.php?serial=55220:750c51b86faf&target=Mic-Windows7-x86_64-installer&module=sql&test=sql%2Ftest%2Fpg_regress%2Ffloat4&which=out

Also it appears that conversions resulting in overflow (such as '1e+39' or '-1e+39') for a float (SQL real) is a accepted on MS Windows where it is not accepted on other platforms. This difference should also not occur.

Also it appears that string representation of Infinity is different on different platforms. On Linux it is 'inf' or '-inf', On GNU-Solaris-sparcv9-dbfarm and GNU-Solaris-sparc-dbfarm and GNU-OpenIndiana-x86_64 it is 'Inf' or '-Inf', so with a capital I. This output difference should also be harmonised across all supported platforms.

Reproducible: Always

Steps to Reproduce:

  1. Start mserver5 (MonetDB v11.19.9 (Oct2014-SP2))
  2. Start mclient (or a JDBC client)
  3. Execute SQL commands:
    CREATE TABLE DOUBLE_TBL(x double);
    INSERT INTO DOUBLE_TBL(x) VALUES ('NaN');
    SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
    -- lists 1 row with null as double and as string value (in JDBC)

INSERT INTO DOUBLE_TBL(x) VALUES ('-NaN');
SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
-- lists 2 rows with null as double and as string value (in JDBC)

INSERT INTO DOUBLE_TBL(x) VALUES ('Inf');
INSERT INTO DOUBLE_TBL(x) VALUES ('Infinity');
INSERT INTO DOUBLE_TBL(x) VALUES ('inf');
INSERT INTO DOUBLE_TBL(x) VALUES ('infinity');
SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
-- lists 6 rows. last 4 return 'inf' as string value, o as double value (in JDBC)

INSERT INTO DOUBLE_TBL(x) VALUES ('-Inf');
INSERT INTO DOUBLE_TBL(x) VALUES ('-Infinity');
INSERT INTO DOUBLE_TBL(x) VALUES ('-inf');
INSERT INTO DOUBLE_TBL(x) VALUES ('-infinity');
SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
-- lists 10 rows. last 4 return '-inf' as string value, o as double value (in JDBC)

CREATE TABLE REAL_TBL(x real);
INSERT INTO REAL_TBL(x) VALUES ('NaN');
SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
-- lists 1 row with null as double and as string value (in JDBC)

INSERT INTO REAL_TBL(x) VALUES ('-NaN');
SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
-- lists 2 rows with null as double and as string value (in JDBC)

INSERT INTO REAL_TBL(x) VALUES ('Inf');
-- Error: conversion of string 'Inf' to type flt failed. SQLState: 22018 ErrorCode: 0
INSERT INTO REAL_TBL(x) VALUES ('Infinity');
-- Error: conversion of string 'Infinity' to type flt failed. SQLState: 22018 ErrorCode: 0
INSERT INTO REAL_TBL(x) VALUES ('inf');
-- Error: conversion of string 'inf' to type flt failed. SQLState: 22018 ErrorCode: 0
INSERT INTO REAL_TBL(x) VALUES ('infinity');
-- Error: conversion of string 'infinity' to type flt failed. SQLState: 22018 ErrorCode: 0
SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
-- lists 2 instead of 6 rows.

INSERT INTO REAL_TBL(x) VALUES ('-Inf');
INSERT INTO REAL_TBL(x) VALUES ('-Infinity');
INSERT INTO REAL_TBL(x) VALUES ('-inf');
INSERT INTO REAL_TBL(x) VALUES ('-infinity');
SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
-- lists 6 rows. last 4 return '-inf' as string value, o as double value (in JDBC)

-- test overflow for real
INSERT INTO REAL_TBL(x) VALUES ('1e+39');
-- this should result in: Error: conversion of string '1e+39' to type flt failed. SQLState: 22018 ErrorCode: 0
INSERT INTO REAL_TBL(x) VALUES ('-1e+39');
-- this should result in: Error: conversion of string '-1e+39' to type flt failed. SQLState: 22018 ErrorCode: 0

-- cleanup
DROP TABLE DOUBLE_TBL;
DROP TABLE REAL_TBL;

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.19.9 (Oct2014-SP2), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE DOUBLE_TBL(x double);
operation successful (1.499ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('NaN');
1 affected row (0.714ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
+--------------------------+-------+
| x | x_str |
+==========================+=======+
| null | null |
+--------------------------+-------+
1 tuple (1.150ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('-NaN');
1 affected row (0.519ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
+--------------------------+-------+
| x | x_str |
+==========================+=======+
| null | null |
| null | null |
+--------------------------+-------+
2 tuples (0.365ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('Inf');
1 affected row (0.308ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('Infinity');
1 affected row (0.519ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('inf');
1 affected row (0.401ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('infinity');
1 affected row (0.376ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
+--------------------------+-------+
| x | x_str |
+==========================+=======+
| null | null |
| null | null |
| inf | inf |
| inf | inf |
| inf | inf |
| inf | inf |
+--------------------------+-------+
6 tuples (0.487ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('-Inf');
1 affected row (0.292ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('-Infinity');
1 affected row (0.395ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('-inf');
1 affected row (0.279ms)
sql>INSERT INTO DOUBLE_TBL(x) VALUES ('-infinity');
1 affected row (0.257ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM DOUBLE_TBL;
+--------------------------+-------+
| x | x_str |
+==========================+=======+
| null | null |
| null | null |
| inf | inf |
| inf | inf |
| inf | inf |
| inf | inf |
| -inf | -inf |
| -inf | -inf |
| -inf | -inf |
| -inf | -inf |
+--------------------------+-------+
10 tuples (0.302ms)
sql>
sql>CREATE TABLE REAL_TBL(x real);
operation successful (1.570ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('NaN');
1 affected row (0.533ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
+-----------------+-------+
| x | x_str |
+=================+=======+
| null | null |
+-----------------+-------+
1 tuple (1.671ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('-NaN');
1 affected row (0.449ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
+-----------------+-------+
| x | x_str |
+=================+=======+
| null | null |
| null | null |
+-----------------+-------+
2 tuples (0.333ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('Inf');
conversion of string 'Inf' to type flt failed.
sql>INSERT INTO REAL_TBL(x) VALUES ('Infinity');
conversion of string 'Infinity' to type flt failed.
sql>INSERT INTO REAL_TBL(x) VALUES ('inf');
conversion of string 'inf' to type flt failed.
sql>INSERT INTO REAL_TBL(x) VALUES ('infinity');
conversion of string 'infinity' to type flt failed.
sql>SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
+-----------------+-------+
| x | x_str |
+=================+=======+
| null | null |
| null | null |
+-----------------+-------+
2 tuples (0.547ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('-Inf');
1 affected row (0.380ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('-Infinity');
1 affected row (0.370ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('-inf');
1 affected row (0.384ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('-infinity');
1 affected row (0.288ms)
sql>SELECT x, cast(x as varchar(30)) as x_str FROM REAL_TBL;
+-----------------+-------+
| x | x_str |
+=================+=======+
| null | null |
| null | null |
| -inf | -inf |
| -inf | -inf |
| -inf | -inf |
| -inf | -inf |
+-----------------+-------+
6 tuples (0.302ms)
sql>INSERT INTO REAL_TBL(x) VALUES ('1e+39');
conversion of string '1e+39' to type flt failed.
sql>INSERT INTO REAL_TBL(x) VALUES ('-1e+39');
conversion of string '-1e+39' to type flt failed.
sql>
sql>DROP TABLE DOUBLE_TBL;
operation successful (1.169ms)
sql>DROP TABLE REAL_TBL;
operation successful (0.899ms)
sql>

Expected Results:

Scanning and conversion of special values ('Nan', 'Inf', '-Inf') and overflow values to a double or a float (SQL real) should be treated in a uniform way and the same across platforms.

Also the string output should be consistent across platform for these special double and float values.

See also tests in sql/test/pg_regress/float4.sql and sql/test/pg_regress/float8.sql

In gdk/gdk_atoms.c the implementation of fltFromStr() should be more similar to dblFromStr().

Comment 20776

Date: 2015-04-10 14:23:49 +0200
From: MonetDB Mercurial Repository <>

Changeset 61ee04c1073b made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=61ee04c1073b

Changeset description:

Don't accept strings like NaN or Infinite when converting to floating point.
The SQL standard says (ISO_9075_02_Foundation_2011_E, 6.13, General
Rules 9, b) that when converting a string to an approximate number,
the string must conform to the rules for a "signed numeric literal"
which is an optional sign, digits, period, more digits, exponent (with
some optional bits).  But definitely not a string such as "NaN".  This
fixes bug #3696.

Comment 20791

Date: 2015-04-14 10:42:11 +0200
From: @sjoerdmullender

Fixed with changesets 61ee04c1073b and 9c80de069c44

Comment 20800

Date: 2015-04-16 12:47:00 +0200
From: MonetDB Mercurial Repository <>

Changeset 9dc8e2433dc3 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9dc8e2433dc3

Changeset description:

With fix for bug #3696 the special values 'nan', 'Inf', 'Infinity', 'inf', 'infinity' are no longer accepted for floating point data types.
Updated the float4 and float8 outputs.
@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working normal SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team