Date: 2020-07-22 18:23:54 +0200
From: jpastuszek
To: SQL devs <>
Version: 11.37.7 (Jun2020)
CC: @PedroTadim

Last updated: 2020-10-19 11:06:19 +0200

Comment 27933

Date: 2020-07-22 18:23:54 +0200
From: jpastuszek

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

Wrong result for "INTERVAL '1' HOUR / 2.0".

Reproducible: Always

Steps to Reproduce:

SELECT INTERVAL '1' HOUR / 2, INTERVAL '1' HOUR / 2.0, INTERVAL '1' HOUR * 1000 / 2000, INTERVAL '1' HOUR * 1000.0 / 2000.0;

Actual Results:

1800.000|180.000|1800.000|1800.000|

Expected Results:

1800.000|1800.000|1800.000|1800.000|

Comment 27938

Date: 2020-07-24 14:38:07 +0200
From: jpastuszek

Also probably related:

SELECT INTERVAL '1' HOUR * CAST(1000 AS DOUBLE);

Gives:

SQL Error [22000]: TypeException:user.s66_24[7]:'calc.' undefined in: X_9:lng := calc.(X_6:lng, X_8:dbl);

And less minified gives different error:

 SELECT
     CAST(INTERVAL '1' HOUR * CAST((1000 / 5040000) AS DOUBLE) AS BIGINT) AS chunk,
     name AS terms
 FROM sys.functions
 GROUP BY chunk, terms

SQL Error [42000]: Error in optimizer multiplex: TypeException:user.s84_24[19]:'bat.append' undefined in: bat.append(X_91:bat[:lng], X_97:dbl);

Comment 27941

Date: 2020-07-24 20:56:19 +0200
From: @PedroTadim

The second issue was found by SQLancer and it is fixed on default by disabling addition between intervals and other numeric types.

Comment 27967

Date: 2020-07-30 11:40:39 +0200
From: jpastuszek

Also this is wrong:

 SELECT INTERVAL '1' HOUR * 1000 / 1800000; -- 2.000
 SELECT INTERVAL '1' HOUR / INTERVAL '1800' SECOND; -- 0.002

Comment 27969

Date: 2020-08-04 10:11:22 +0200
From: @PedroTadim

SELECT INTERVAL '1' HOUR / INTERVAL '1800' SECOND;

This is fixed on typing branch by disabling division between intervals. It will be available on the next feature release.