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

Temporal extraction glitches #3425

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

Temporal extraction glitches #3425

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

Comments

@monetdb-team
Copy link

Date: 2014-01-29 10:53:46 +0100
From: @mlkersten
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2014-02-20 15:03:02 +0100

Comment 19509

Date: 2014-01-29 10:53:46 +0100
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:26.0) Gecko/20100101 Firefox/26.0
Build Identifier:

The extraction of hour and month from a timestamp seems incorrect.

Reproducible: Always

Steps to Reproduce:

select now(), extract(second from now());
select now(), extract(minute from now());
select now(), extract(hour from now());
select now(), extract(month from now());
select now(), extract(year from now());

Actual Results:

sql>select now(), extract(second from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:26.000000+01:00 | 26.000 |
+----------------------------------+-------------------+
1 tuple (0.672ms)
sql>select now(), extract(minute from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:48.000000+01:00 | 48 |
+----------------------------------+-------------------+
1 tuple (0.897ms)
sql>select now(), extract(hour from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:58.000000+01:00 | 1 |
+----------------------------------+-------------------+
1 tuple (0.797ms)
sql>select now(), extract(month from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:19.000000+01:00 | 3 |
+----------------------------------+-------------------+
1 tuple (1.017ms)
sql>select now(), extract(year from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:29.000000+01:00 | 2014 |
+----------------------------------+-------------------+
1 tuple (0.832ms)

Expected Results:

sql>select now(), extract(second from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:26.000000+01:00 | 26.000 |
+----------------------------------+-------------------+
1 tuple (0.672ms)
sql>select now(), extract(minute from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:48.000000+01:00 | 48 |
+----------------------------------+-------------------+
1 tuple (0.897ms)
sql>select now(), extract(hour from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:58.000000+01:00 | 10 |
+----------------------------------+-------------------+
1 tuple (0.797ms)
sql>select now(), extract(month from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:19.000000+01:00 | 1 |
+----------------------------------+-------------------+
1 tuple (1.017ms)
sql>select now(), extract(year from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:29.000000+01:00 | 2014 |
+----------------------------------+-------------------+
1 tuple (0.832ms)

Comment 19510

Date: 2014-01-29 13:10:23 +0100
From: @mlkersten

sql>explain select now(), extract(day from now());
+----------------------------------------------------------------------------+
| mal |
+============================================================================+
| function user.s3_1{autoCommit=true}():void; |
| X_2 := mtime.current_timestamp(); |
| X_3 := mtime.current_timestamp(); |
| X_5 := mtime.timestamp_add_msec_interval(X_3,3600000000:lng); |
| X_6 := mtime.day(X_5); |
| X_7 := sql.resultSet(2,1,X_2); |
| sql.rsColumn(X_7,".L1","L1","timestamptz",7,0,X_2); |
| sql.rsColumn(X_7,".L1","current_timestamp","int",32,0,X_6); |
| X_19 := io.stdout(); |
| sql.exportResult(X_19,X_7); |
| end s3_1; |
| querylog.define("select now(), extract(day from now());","default_pipe") |
| optimizer.mitosis() |
| optimizer.dataflow() |
+----------------------------------------------------------------------------+
sql>declare ts timestamp;
operation successful (0.657ms)
sql>set ts = now();
operation successful (0.645ms)
sql>explain select now(), extract(day from ts);
+---------------------------------------------------------------------------------+
| mal |
+=================================================================================+
| function user.s7_1{autoCommit=true}():void; |
| X_2 := sql.mvc(); |
| X_3 := mtime.current_timestamp(); |
| X_5:timestamp := sql.getVariable(X_2,"ts"); |
| X_6 := mtime.day(X_5); |
| X_7 := sql.resultSet(2,1,X_3); |
| sql.rsColumn(X_7,".L1","L1","timestamptz",7,0,X_3); |
| sql.rsColumn(X_7,".L1","day_single_value","int",32,0,X_6); |
| X_20 := io.stdout(); |
| sql.exportResult(X_20,X_7); |
| end s7_1; |
| querylog.define("explain select now(), extract(day from ts);","default_pipe") |
| optimizer.mitosis() |
| optimizer.dataflow() |
+---------------------------------------------------------------------------------+
14 tuples (1.308ms)

Comment 19520

Date: 2014-02-03 15:57:57 +0100
From: @sjoerdmullender

(In reply to comment 1)

sql>explain select now(), extract(day from now());
[...]
| X_5 := mtime.timestamp_add_msec_interval(X_3,3600000000:lng);

I think the value 3600000000 is a factor 1000 too high. Where does it come from?

Comment 19533

Date: 2014-02-05 09:33:28 +0100
From: @njnes

that (incorrectly) comes from the second_interval. I have a patch.

Comment 19534

Date: 2014-02-05 09:44:39 +0100
From: @njnes

extended the test ./BugTracker-2011/Tests/extract_seconds.Bug-2793.sql

Comment 19543

Date: 2014-02-05 13:00:13 +0100
From: MonetDB Mercurial Repository <>

Changeset be0051b026da made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

approved output for bug #3427 (also use auto commit mode)
fixed bug #3430, ie check if local temporary tables are stored in the 'tmp' schema
fixed bug #3428, don't overwrite the statement number, but use a temporary structure to store the
NotNil select statements. Solves the problem that the sum(a), sub(a*b) uses the a after nil
removal in the a*b.

fixed bug #3425, correctly add the timezone with the proper type (ie second interval (ie with scale 3))

Comment 19619

Date: 2014-02-20 15:03:02 +0100
From: @sjoerdmullender

Jan2014 has been released.

@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
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants