Bug 3808 - Fractional seconds precision is broken for any precision larger than 3
Summary: Fractional seconds precision is broken for any precision larger than 3
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.25.15 (Dec2016-SP3)
Hardware: Other Windows
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-09-10 04:33 CEST by Dann Corbit
Modified: 2019-10-30 13:47 CET (History)
4 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Dann Corbit 2015-09-10 04:33:25 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:25.7) Gecko/20150824 Firefox/31.9 PaleMoon/25.7.0
Build Identifier: 

For timestamp and time columns (did not try any others) the precision specifier allows values from 0 to 7 (I checked the column definition from the columns system table) but no more than 3 digits of precision are stored.

sql>CREATE TABLE TT7 ( A TIMESTAMP(7));
operation successful (9.160ms)
sql>insert into TT7 values ('2015-12-12 12:30:59.1111111');
1 affected row (3.103ms)
sql>select * from TT7;
+----------------------------+
| a                          |
+============================+
| 2015-12-12 12:30:59.111000 |
+----------------------------+
1 tuple (1.554ms)

Reproducible: Always

Steps to Reproduce:
sql>CREATE TABLE TT7 ( A TIMESTAMP(7));
operation successful (9.160ms)
sql>insert into TT7 values ('2015-12-12 12:30:59.1111111');
1 affected row (3.103ms)
sql>select * from TT7;
+----------------------------+
| a                          |
+============================+
| 2015-12-12 12:30:59.111000 |
+----------------------------+
1 tuple (1.554ms)
Actual Results:  
Precision is truncated

Expected Results:  
Precision is correct (up to 1 digit for precision of 1 through 7 digits for precision of 7)

current_timestamp() time function is only accurate to seconds. (That's broken too)
Hence, it is not useful for testing functionality.
Comment 1 Dann Corbit 2015-09-10 04:41:13 CEST
Via SQL Server:
CREATE TABLE TT7 ( A datetime2(7));
insert into TT7 values ('2015-12-12 12:30:59.1111111');
select * from TT7;

Gives this result:
2015-12-12 12:30:59.1111111

Similarly for PostgreSQL:
CREATE TABLE TT7 ( A TIMESTAMP(7));
insert into TT7 values ('2015-12-12 12:30:59.1111111');
select * from TT7;

Gives this result (The allowed range of p is from 0 to 6 when eight-byte integer storage is used, or from 0 to 10 when floating-point storage is used. This server is using 8 byte storage):
"2015-12-12 12:30:59.111111"
Comment 2 Hannes Muehleisen cwiconfidential 2015-09-10 10:12:43 CEST
See also https://www.monetdb.org/bugzilla/show_bug.cgi?id=3807
Comment 3 MonetDB Mercurial Repository cwiconfidential 2015-10-14 19:02:13 CEST
Changeset c8cb4c4f476c, made by Niels Nes <niels@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c8cb4c4f476c

Changeset description:

	added test for fractional precision limitation bug 3808
Comment 4 Svetlin 2017-04-28 12:58:06 CEST
Reproduced on macOS Sierra as well
Comment 5 Svetlin 2017-04-28 12:58:33 CEST
Reproduced on macOS Sierra as well
Comment 6 Castro B 2019-10-14 05:13:17 CEST
Any estimation for this fix? It's kinda critical. ,https://www.maxvisits.com/
Comment 7 Pedro Ferreira 2019-10-14 15:17:33 CEST
For the upcoming Nov2019 release we now allow timestamps up to microsecond precision (i.e. 6 digits). However in the report, the compiler parses timestamp strings with 7 digits, which we must disallow.
Comment 8 Sjoerd Mullender cwiconfidential 2019-10-30 13:47:46 CET
(In reply to Pedro Ferreira from comment #7)
> For the upcoming Nov2019 release we now allow timestamps up to microsecond
> precision (i.e. 6 digits). However in the report, the compiler parses
> timestamp strings with 7 digits, which we must disallow.

Absolutely not.  We must allow parsing more than the specified precision, but we must do that correctly (I just fixed a bug in the upcoming release when the specified precision is less than 6).

What I can imagine is that we disallow specifying a type with a larger precision than what we actually support, i.e. no TIMESTAMP(7) but only up to TIMESTAMP(6).

Note that the value 7 in the sys.columns table (column type_digits) is not the maximum precision.  It's a system table and it needs specialized knowledge to interpret any value in any system table.  In this case, it's one more than the maximum precision.

The maximum precision is 6 in the upcoming Nov2019 release (it was 3 for older releases).