GeezerVison

The World Wide Web, restored to 800 pixels of glory.

GeezerVison is showing github.com — [SQL] Date and interval arithmetic follow the SQL standard · feldera/feldera@ca12040

File tree

Expand file treeCollapse file tree

Open diff view settings

Filter options

crates/sqllib/src
- casts.rs
docs.feldera.com/docs
- changelog.md
- sql
  - casts.md
  - datetime.md
python/tests/runtime_aggtest
- aggregate_tests2
  - test_atbl_interval.py
- arithmetic_tests
scripts
- validate-demos.sh
sql-to-dbsp-compiler/SQL-compiler/src
- main/java/org/dbsp/sqlCompiler
  - compiler
    - backend/rust
      - ToRustInnerVisitor.java
    - frontend
      - ExpressionCompiler.java
      - calciteCompiler
        ConvertletTable.java
        FelderaRexBuilder.java
        optimizer
        CalciteOptimizer.java
        RexOptimize.java
        TableFunctionReduceExpressionsRule.java
  - ir/type
    - IsIntervalType.java
    - primitive
      - DBSPTypeLongInterval.java
      - DBSPTypeShortInterval.java
- test/java/org/dbsp/sqlCompiler/compiler/sql
  - postgres
    - PostgresIntervalTests.java
  - quidem
    - MiscTests.java
  - simple

Expand file treeCollapse file tree

Open diff view settings

Collapse file

`‎crates/sqllib/src/casts.rs‎`

Copy file name to clipboardExpand all lines: crates/sqllib/src/casts.rs

+126-6Lines changed: 126 additions & 6 deletions

Original file line number	Diff line number	Diff line change
`@@ -1923,12 +1923,19 @@ pub fn cast_to_ShortInterval_DAYS_i64(value: i64) -> SqlResult<ShortInterval> {`
`1923`	`1923`	`}`
`1924`	`1924`	`}`
`1925`	`1925`
	`1926`	+/// Converts `value` units of `multiplier` microseconds to a `ShortInterval`. A unit other than the
	`1927`	+/// second holds no fraction, so `value` is truncated toward zero first.
`1926`	`1928`	`fn convert_to_ShortInterval_SqlDecimal<const P: usize, const S: usize>(`
`1927`	`1929`	`value: SqlDecimal<P, S>,`
`1928`	`1930`	`multiplier: i64,`
`1929`	`1931`	`kind: &'static str,`
`1930`	`1932`	`) -> SqlResult<ShortInterval> {`
`1931`		`- let dd = DynamicDecimal::from(value);`
	`1933`	`+ let units = if multiplier == MICROS_PER_SECOND as i64 {`
	`1934`	`+ value`
	`1935`	`+ } else {`
	`1936`	`+ value.trunc()`
	`1937`	`+ };`
	`1938`	`+ let dd = DynamicDecimal::from(units);`
`1932`	`1939`	`let mul = DynamicDecimal::from(multiplier);`
`1933`	`1940`	`dd.checked_mul(&mul)`
`1934`	`1941`	`.and_then(\|val\| i64::try_from(val).ok())`
`@@ -1980,7 +1987,7 @@ pub fn cast_to_ShortInterval_HOURS_i64(value: i64) -> SqlResult<ShortInterval> {`
`1980`	`1987`	`pub fn cast_to_ShortInterval_HOURS_SqlDecimal<const P: usize, const S: usize>(`
`1981`	`1988`	`value: SqlDecimal<P, S>,`
`1982`	`1989`	`) -> SqlResult<ShortInterval> {`
`1983`		`- convert_to_ShortInterval_SqlDecimal::<P, S>(value, 3600i64 * 1000 * 1000, "DAYS")`
	`1990`	`+ convert_to_ShortInterval_SqlDecimal::<P, S>(value, 3600i64 * 1000 * 1000, "HOURS")`
`1984`	`1991`	`}`
`1985`	`1992`
`1986`	`1993`	`#[doc(hidden)]`
`@@ -2488,20 +2495,72 @@ cast_function!(LongInterval_YEARS, LongInterval, s, SqlString);`
`2488`	`2495`	`cast_function!(LongInterval_YEARS_TO_MONTHS, LongInterval, s, SqlString);`
`2489`	`2496`	`cast_function!(LongInterval_MONTHS, LongInterval, s, SqlString);`
`2490`	`2497`
	`2498`	`+// A cast between interval types truncates the value toward zero to the last field of the`
	`2499`	`+// target type.`
	`2500`	`+`
	`2501`	`+#[doc(hidden)]`
	`2502`	`+#[inline]`
	`2503`	`+pub fn cast_to_LongInterval_YEARS_LongInterval(value: LongInterval) -> SqlResult<LongInterval> {`
	`2504`	`+ Ok(LongInterval::from_months(value.months() / 12 * 12))`
	`2505`	`+}`
	`2506`	`+`
`2491`	`2507`	`#[doc(hidden)]`
`2492`	`2508`	`#[inline]`
`2493`		`-pub fn cast_to_LongInterval_LongInterval(value: LongInterval) -> SqlResult<LongInterval> {`
	`2509`	`+pub fn cast_to_LongInterval_MONTHS_LongInterval(value: LongInterval) -> SqlResult<LongInterval> {`
`2494`	`2510`	`Ok(value)`
`2495`	`2511`	`}`
`2496`	`2512`
`2497`	`2513`	`#[doc(hidden)]`
`2498`	`2514`	`#[inline]`
`2499`		`-pub fn cast_to_ShortInterval_ShortInterval(value: ShortInterval) -> SqlResult<ShortInterval> {`
	`2515`	`+pub fn cast_to_LongInterval_YEARS_TO_MONTHS_LongInterval(`
	`2516`	`+ value: LongInterval,`
	`2517`	`+) -> SqlResult<LongInterval> {`
`2500`	`2518`	`Ok(value)`
`2501`	`2519`	`}`
`2502`	`2520`
`2503`		`-cast_function!(LongInterval, LongInterval, LongInterval, LongInterval);`
`2504`		`-cast_function!(ShortInterval, ShortInterval, ShortInterval, ShortInterval);`
	`2521`	`+cast_function!(LongInterval_YEARS, LongInterval, LongInterval, LongInterval);`
	`2522`	`+cast_function!(`
	`2523`	`+ LongInterval_MONTHS,`
	`2524`	`+ LongInterval,`
	`2525`	`+ LongInterval,`
	`2526`	`+ LongInterval`
	`2527`	`+);`
	`2528`	`+cast_function!(`
	`2529`	`+ LongInterval_YEARS_TO_MONTHS,`
	`2530`	`+ LongInterval,`
	`2531`	`+ LongInterval,`
	`2532`	`+ LongInterval`
	`2533`	`+);`
	`2534`	`+`
	`2535`	+/// Defines the cast of a short interval to the type with `units`, whose last field is `unit`
	`2536`	`+/// microseconds long.`
	`2537`	`+macro_rules! cast_to_short_interval {`
	`2538`	`+ ($units: ident, $unit: expr) => {`
	`2539`	`+ ::paste::paste! {`
	`2540`	`+ #[doc(hidden)]`
	`2541`	`+ #[inline]`
	`2542`	`+ pub fn [<cast_to_ShortInterval_ $units _ShortInterval>](`
	`2543`	`+ value: ShortInterval,`
	`2544`	`+ ) -> SqlResult<ShortInterval> {`
	`2545`	`+ let unit = $unit as i64;`
	`2546`	`+ Ok(ShortInterval::from_microseconds(value.microseconds() / unit * unit))`
	`2547`	`+ }`
	`2548`	`+`
	`2549`	`+ cast_function!([<ShortInterval_ $units>], ShortInterval, ShortInterval, ShortInterval);`
	`2550`	`+ }`
	`2551`	`+ };`
	`2552`	`+}`
	`2553`	`+`
	`2554`	`+cast_to_short_interval!(DAYS, MICROS_PER_DAY);`
	`2555`	`+cast_to_short_interval!(HOURS, MICROS_PER_HOUR);`
	`2556`	`+cast_to_short_interval!(DAYS_TO_HOURS, MICROS_PER_HOUR);`
	`2557`	`+cast_to_short_interval!(MINUTES, MICROS_PER_MINUTE);`
	`2558`	`+cast_to_short_interval!(HOURS_TO_MINUTES, MICROS_PER_MINUTE);`
	`2559`	`+cast_to_short_interval!(DAYS_TO_MINUTES, MICROS_PER_MINUTE);`
	`2560`	`+cast_to_short_interval!(SECONDS, 1);`
	`2561`	`+cast_to_short_interval!(DAYS_TO_SECONDS, 1);`
	`2562`	`+cast_to_short_interval!(HOURS_TO_SECONDS, 1);`
	`2563`	`+cast_to_short_interval!(MINUTES_TO_SECONDS, 1);`
`2505`	`2564`
`2506`	`2565`	`static DAYS_TO_HOURS: LazyLock<Regex> =`
`2507`	`2566`	`LazyLock::new(\|\| Regex::new(r"^([+-])?(\d+)\s+(\d{1,2})$").unwrap());`
`@@ -4072,6 +4131,67 @@ mod tests {`
`4072`	`4131`	`limit_or_size_string,`
`4073`	`4132`	`};`
`4074`	`4133`
	`4134`	`+ #[test]`
	`4135`	`+ fn interval_casts_truncate() {`
	`4136`	`+ use crate::{`
	`4137`	`+ cast_to_LongInterval_MONTHS_LongInterval, cast_to_LongInterval_YEARS_LongInterval,`
	`4138`	`+ cast_to_ShortInterval_DAYS_ShortInterval,`
	`4139`	`+ cast_to_ShortInterval_HOURS_TO_MINUTES_ShortInterval,`
	`4140`	`+ cast_to_ShortInterval_SECONDS_ShortInterval,`
	`4141`	`+ };`
	`4142`	`+`
	`4143`	`+ let months = LongInterval::from_months;`
	`4144`	`+ assert_eq!(`
	`4145`	`+ cast_to_LongInterval_YEARS_LongInterval(months(19)).unwrap(),`
	`4146`	`+ months(12)`
	`4147`	`+ );`
	`4148`	`+ assert_eq!(`
	`4149`	`+ cast_to_LongInterval_YEARS_LongInterval(months(-19)).unwrap(),`
	`4150`	`+ months(-12)`
	`4151`	`+ );`
	`4152`	`+ assert_eq!(`
	`4153`	`+ cast_to_LongInterval_YEARS_LongInterval(months(i32::MIN)).unwrap(),`
	`4154`	`+ months(-178956970 * 12)`
	`4155`	`+ );`
	`4156`	`+ assert_eq!(`
	`4157`	`+ cast_to_LongInterval_MONTHS_LongInterval(months(19)).unwrap(),`
	`4158`	`+ months(19)`
	`4159`	`+ );`
	`4160`	`+`
	`4161`	`+ let hours = \|h: i64\| ShortInterval::from_microseconds(h * 3_600_000_000);`
	`4162`	`+ let micros = ShortInterval::from_microseconds;`
	`4163`	`+ assert_eq!(`
	`4164`	`+ cast_to_ShortInterval_DAYS_ShortInterval(hours(-25)).unwrap(),`
	`4165`	`+ hours(-24)`
	`4166`	`+ );`
	`4167`	`+ assert_eq!(`
	`4168`	`+ cast_to_ShortInterval_HOURS_TO_MINUTES_ShortInterval(micros(90_500_000)).unwrap(),`
	`4169`	`+ micros(60_000_000)`
	`4170`	`+ );`
	`4171`	`+ assert_eq!(`
	`4172`	`+ cast_to_ShortInterval_SECONDS_ShortInterval(micros(1_500_001)).unwrap(),`
	`4173`	`+ micros(1_500_001)`
	`4174`	`+ );`
	`4175`	`+ }`
	`4176`	`+`
	`4177`	`+ #[test]`
	`4178`	`+ fn decimal_to_short_interval_truncates() {`
	`4179`	`+ use crate::{`
	`4180`	`+ cast_to_ShortInterval_DAYS_SqlDecimal, cast_to_ShortInterval_SECONDS_SqlDecimal,`
	`4181`	`+ };`
	`4182`	`+`
	`4183`	`+ let value = SqlDecimal::<5, 2>::new(-150, 2).unwrap();`
	`4184`	`+ // A day holds no fraction; a second does`
	`4185`	`+ assert_eq!(`
	`4186`	`+ cast_to_ShortInterval_DAYS_SqlDecimal(value).unwrap(),`
	`4187`	`+ ShortInterval::from_microseconds(-86_400_000_000)`
	`4188`	`+ );`
	`4189`	`+ assert_eq!(`
	`4190`	`+ cast_to_ShortInterval_SECONDS_SqlDecimal(value).unwrap(),`
	`4191`	`+ ShortInterval::from_microseconds(-1_500_000)`
	`4192`	`+ );`
	`4193`	`+ }`
	`4194`	`+`
`4075`	`4195`	`#[test]`
`4076`	`4196`	`fn date_casts_out_of_range() {`
`4077`	`4197`	`// An out-of-range result is an error, not a panic, so SAFE_CAST produces NULL`

Collapse file

`‎docs.feldera.com/docs/changelog.md‎`

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/changelog.md

+27Lines changed: 27 additions & 0 deletions

Display the source diff
Display the rich diff

Original file line number	Diff line number	Diff line change
`@@ -42,6 +42,33 @@ Source edition can be found on github.`
`42`	`42`	`storage_max`. A pipeline stored with such values still loads. See
`43`	`43`	`[Storage limits](/pipelines/configuration#storage-limits).`
`44`	`44`
	`45`	`+- Incompatible change (SQL): date arithmetic now matches the SQL`
	`46`	`+ standard. An interval holds whole numbers in every field except`
	`47`	+ `SECOND`, and an operation that produces an interval truncates the value
	`48`	`+ toward zero to the last field of its type (see`
	`49`	`+ [the interval types](/sql/datetime#the-interval-types)):`
	`50`	`+ - A cast between interval types:`
	`51`	+ `CAST(INTERVAL '19' MONTH AS INTERVAL YEAR)` is now `INTERVAL '1' YEAR`
	`52`	+ instead of 19 months, so `DATE '2020-01-01' + CAST(INTERVAL '19' MONTH
	`53`	+ AS INTERVAL YEAR)` is now 2021-01-01 instead of 2021-08-01.
	`54`	+ `CAST(INTERVAL '25' HOUR AS INTERVAL DAY)` is now `INTERVAL '1' DAY`
	`55`	`+ instead of 25 hours.`
	`56`	+ - A cast of a number with a fraction: `CAST(1.5 AS INTERVAL YEAR)` is
	`57`	+ now `INTERVAL '1' YEAR` instead of 18 months, and
	`58`	+ `CAST(-1.5 AS INTERVAL DAY)` is now `INTERVAL '-1' DAY` instead of
	`59`	`+ -36 hours.`
	`60`	`+ - The product or the quotient of an interval and a number, which has`
	`61`	+ the type of the interval: `INTERVAL '1' YEAR * 1.5` is now
	`62`	+ `INTERVAL '1' YEAR` instead of 18 months, and `INTERVAL '1' DAY / 2`
	`63`	+ is now `INTERVAL '0' DAY` instead of 12 hours.
	`64`	`+ - The difference of two dates, times or timestamps, which has the type`
	`65`	`+ of its qualifier and counts the boundaries of its last field between`
	`66`	+ the two values: `(DATE '2021-01-01' - DATE '2020-12-31') YEAR` is now
	`67`	+ `INTERVAL '1' YEAR` instead of 0 months, and
	`68`	+ `(DATE '2021-03-01' - DATE '2020-01-01') YEAR` is now
	`69`	+ `INTERVAL '1' YEAR` instead of 14 months. `TIMESTAMPDIFF` does not
	`70`	`+ change.`
	`71`	`+`
`45`	`72`	`## v0.372.0`
`46`	`73`
`47`	`74`	`- Behavior change (non-fault-tolerant Kafka output): temporary delivery`

Collapse file

`‎docs.feldera.com/docs/sql/casts.md‎`

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/sql/casts.md

+4Lines changed: 4 additions & 0 deletions

Display the source diff
Display the rich diff

Original file line number	Diff line number	Diff line change
@@ -37,6 +37,10 @@ interval expressed in the interval's unit. For example, `CAST(INTERVAL
`37`	`37`	'10' DAY AS BIGINT)` returns 10, and `CAST(INTERVAL '10.6' SECONDS AS
`38`	`38`	INTEGER)` returns 10. Long intervals use years or months as the unit;
`39`	`39`	`short intervals use days, hours, minutes, or seconds.`
	`40`	+A cast of a number to an interval type other than `SECOND` truncates
	`41`	+the number to an integer first: `CAST(1.5 AS INTERVAL YEAR)` is
	`42`	+`INTERVAL '1' YEAR`, and `CAST(-1.5 AS INTERVAL DAY)` is `INTERVAL
	`43`	+'-1' DAY`.
`40`	`44`
`41`	`45`	Casts of strings to numeric types, `BOOLEAN`, `DATE`, `TIME`,
`42`	`46`	`TIMESTAMP`, and `TIMESTAMP WITH TIME ZONE` first remove the

Collapse file

`‎docs.feldera.com/docs/sql/datetime.md‎`

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/sql/datetime.md

+25Lines changed: 25 additions & 0 deletions

Display the source diff
Display the rich diff

Original file line number	Diff line number	Diff line change
`@@ -291,6 +291,31 @@ most about 106 million days (about 292,000 years), positive or`
`291`	`291`	`negative. A long interval can be at most about 179 million years,`
`292`	`292`	`positive or negative.`
`293`	`293`
	`294`	`+As the SQL standard specifies, an interval value holds whole numbers`
	`295`	+in every field except `SECOND`: an `INTERVAL YEAR` holds a whole number of years, and an `INTERVAL DAY
	`296`	+TO HOUR` holds whole days and hours. An operation that produces an
	`297`	`+interval truncates the value toward zero to the last field of the`
	`298`	`+result type:`
	`299`	`+`
	`300`	+- a cast between interval types: `CAST(INTERVAL '19' MONTH AS INTERVAL
	`301`	+ YEAR)` is `INTERVAL '1' YEAR`, and `CAST(INTERVAL '-25' HOUR AS
	`302`	+ INTERVAL DAY)` is `INTERVAL '-1' DAY`;
	`303`	`+- the product or the quotient of an interval and a number, which has`
	`304`	+ the type of the interval: `INTERVAL '1' YEAR * 1.5` is `INTERVAL '1'
	`305`	+ YEAR`, and `INTERVAL '1' DAY / 2` is `INTERVAL '0' DAY`;
	`306`	`+- the difference of two dates, times or timestamps, which has the type`
	`307`	`+ of its qualifier, and counts the boundaries of its last field between`
	`308`	+ the two values: `(DATE '2021-01-01' - DATE '2020-12-31') YEAR` is
	`309`	+ `INTERVAL '1' YEAR`, and `(TIMESTAMP '2020-01-02 01:00:00' - TIMESTAMP
	`310`	+ '2020-01-01 23:00:00') DAY` is `INTERVAL '1' DAY`. `TIMESTAMPDIFF`
	`311`	+ counts the elapsed units instead: `TIMESTAMPDIFF(YEAR, DATE
	`312`	+ '2020-12-31', DATE '2021-01-01')` is 0;
	`313`	+- a cast of a number: `CAST(-1.5 AS INTERVAL DAY)` is `INTERVAL '-1'
	`314`	+ DAY`.
	`315`	`+`
	`316`	`+Seconds can have fractions, with the standard's default precision of`
	`317`	+microseconds: `INTERVAL '1' SECOND * 1.5` is `INTERVAL '1.5' SECOND`.
	`318`	`+`
`294`	`319`	`### Interval literals`
`295`	`320`
`296`	`321`	`Interval literals (constant values) can be written using the following`

Collapse file

`‎python/tests/runtime_aggtest/aggregate_tests2/test_atbl_interval.py‎`

Copy file name to clipboardExpand all lines: python/tests/runtime_aggtest/aggregate_tests2/test_atbl_interval.py

+15-14Lines changed: 15 additions & 14 deletions

Original file line number	Diff line number	Diff line change
`@@ -250,13 +250,14 @@ class aggtst_atbl_interval_months_res(TstView):`
`250`	`250`	`"""Interval months converted to varchar"""`
`251`	`251`
`252`	`252`	`def __init__(self):`
	`253`	`+ # Validated on HSQLDB`
`253`	`254`	`self.data = [`
`254`	`255`	`{`
`255`	`256`	`"id": 0,`
`256`	`257`	`"f_c1": "+605",`
`257`	`258`	`"f_c2": "-605",`
`258`		`- "f_c3": "-33",`
`259`		`- "f_c4": "+33",`
	`259`	`+ "f_c3": "-34",`
	`260`	`+ "f_c4": "+34",`
`260`	`261`	`"f_c5": "-639",`
`261`	`262`	`"f_c6": "+639",`
`262`	`263`	`},`
`@@ -266,35 +267,35 @@ def __init__(self):`
`266`	`267`	`"f_c2": "+121",`
`267`	`268`	`"f_c3": "-64",`
`268`	`269`	`"f_c4": "+64",`
`269`		`- "f_c5": "+56",`
`270`		`- "f_c6": "-56",`
	`270`	`+ "f_c5": "+57",`
	`271`	`+ "f_c6": "-57",`
`271`	`272`	`},`
`272`	`273`	`{`
`273`	`274`	`"id": 1,`
`274`		`- "f_c1": "+120",`
`275`		`- "f_c2": "-120",`
`276`		`- "f_c3": "+87",`
`277`		`- "f_c4": "-87",`
	`275`	`+ "f_c1": "+121",`
	`276`	`+ "f_c2": "-121",`
	`277`	`+ "f_c3": "+88",`
	`278`	`+ "f_c4": "-88",`
`278`	`279`	`"f_c5": "-33",`
`279`	`280`	`"f_c6": "+33",`
`280`	`281`	`},`
`281`	`282`	`{`
`282`	`283`	`"id": 1,`
`283`	`284`	`"f_c1": "+605",`
`284`	`285`	`"f_c2": "-605",`
`285`		`- "f_c3": "-16",`
`286`		`- "f_c4": "+16",`
	`286`	`+ "f_c3": "-17",`
	`287`	`+ "f_c4": "+17",`
`287`	`288`	`"f_c5": "-622",`
`288`	`289`	`"f_c6": "+622",`
`289`	`290`	`},`
`290`	`291`	`{`
`291`	`292`	`"id": 1,`
`292`		`- "f_c1": "-557",`
`293`		`- "f_c2": "+557",`
	`293`	`+ "f_c1": "-558",`
	`294`	`+ "f_c2": "+558",`
`294`	`295`	`"f_c3": "-217",`
`295`	`296`	`"f_c4": "+217",`
`296`		`- "f_c5": "+340",`
`297`		`- "f_c6": "-340",`
	`297`	`+ "f_c5": "+341",`
	`298`	`+ "f_c6": "-341",`
`298`	`299`	`},`
`299`	`300`	`]`
`300`	`301`

0 commit comments

Comments

0 (0)

You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.