mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-13 00:32:56 +00:00
314f2704d4
* chore: update datafusion * update sqlness case of time.sql Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: adjust range query partition * fix: hisogram incorrect result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: ignore filter pushdown temporarily Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: update limit sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: histogram with wrong distribution Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: update negative ordinal sqlness case Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * feat: bump df to cd7a00b Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * resolve conflicts * ignore test_range_filter Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix promql exec panic * fix "select count(*)" exec error * re-enable the "test_range_filter" test since the filter push down seems not necessary to be removed * fix: range query schema error * update sqlness results Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * resolve conflicts * update datafusion, again * fix pyo3 compile error, and update some sqlness results * update decimal sqlness cases Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix: promql literal * fix udaf tests * fix filter pushdown sqlness tests * fix?: test_cast * fix: rspy test fail due to datafusion `sin` signature change * rebase main to see if there are any failed tests * debug ci * debug ci * debug ci * enforce input partition Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * debug ci * fix ci * fix ci * debug ci * debug ci * debug ci * fix sqlness * feat: do not return error while creating a filter * chore: remove array from error * chore: replace todo with unimplemented * Update src/flow/clippy.toml Co-authored-by: Yingwen <realevenyag@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com> Co-authored-by: Ruihang Xia <waynestxia@gmail.com> Co-authored-by: WUJingdi <taylor-lagrange@qq.com> Co-authored-by: discord9 <discord9@163.com> Co-authored-by: evenyag <realevenyag@gmail.com> Co-authored-by: tison <wander4096@gmail.com>
221 lines
12 KiB
Plaintext
221 lines
12 KiB
Plaintext
--- date_add ---
|
|
SELECT date_add('2023-12-06 07:39:46.222'::TIMESTAMP_MS, INTERVAL '5 day');
|
|
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| date_add(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),IntervalMonthDayNano("92233720368547758080")) |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-11T07:39:46.222 |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_add('2023-12-06 07:39:46.222'::TIMESTAMP_MS, '5 day');
|
|
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| date_add(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),Utf8("5 day")) |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-11T07:39:46.222 |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_add('2023-12-06'::DATE, INTERVAL '3 month 5 day');
|
|
|
|
+-------------------------------------------------------------------------------------+
|
|
| date_add(Utf8("2023-12-06"),IntervalMonthDayNano("237684487635026733149179609088")) |
|
|
+-------------------------------------------------------------------------------------+
|
|
| 2024-03-11 |
|
|
+-------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_add('2023-12-06'::DATE, '3 month 5 day');
|
|
|
|
+----------------------------------------------------+
|
|
| date_add(Utf8("2023-12-06"),Utf8("3 month 5 day")) |
|
|
+----------------------------------------------------+
|
|
| 2024-03-11 |
|
|
+----------------------------------------------------+
|
|
|
|
--- date_sub ---
|
|
SELECT date_sub('2023-12-06 07:39:46.222'::TIMESTAMP_MS, INTERVAL '5 day');
|
|
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| date_sub(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),IntervalMonthDayNano("92233720368547758080")) |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-01T07:39:46.222 |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_sub('2023-12-06 07:39:46.222'::TIMESTAMP_MS, '5 day');
|
|
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| date_sub(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),Utf8("5 day")) |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-01T07:39:46.222 |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_sub('2023-12-06'::DATE, INTERVAL '3 month 5 day');
|
|
|
|
+-------------------------------------------------------------------------------------+
|
|
| date_sub(Utf8("2023-12-06"),IntervalMonthDayNano("237684487635026733149179609088")) |
|
|
+-------------------------------------------------------------------------------------+
|
|
| 2023-09-01 |
|
|
+-------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_sub('2023-12-06'::DATE, '3 month 5 day');
|
|
|
|
+----------------------------------------------------+
|
|
| date_sub(Utf8("2023-12-06"),Utf8("3 month 5 day")) |
|
|
+----------------------------------------------------+
|
|
| 2023-09-01 |
|
|
+----------------------------------------------------+
|
|
|
|
--- date_format ---
|
|
SELECT date_format('2023-12-06 07:39:46.222'::TIMESTAMP_MS, '%Y-%m-%d %H:%M:%S:%3f');
|
|
|
|
+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| date_format(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),Utf8("%Y-%m-%d %H:%M:%S:%3f")) |
|
|
+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-06 07:39:46:222 |
|
|
+-----------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT date_format('2023-12-06 07:39:46.222'::TIMESTAMP_S, '%Y-%m-%d %H:%M:%S:%3f');
|
|
|
|
+------------------------------------------------------------------------------------------------------------------------+
|
|
| date_format(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Second, None)")),Utf8("%Y-%m-%d %H:%M:%S:%3f")) |
|
|
+------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-06 07:39:46:000 |
|
|
+------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
--- datetime not supported yet ---
|
|
SELECT date_format('2023-12-06 07:39:46.222'::DATETIME, '%Y-%m-%d %H:%M:%S:%3f');
|
|
|
|
Error: 3000(PlanQuery), Failed to plan SQL: This feature is not implemented: Unsupported SQL type Datetime(None)
|
|
|
|
SELECT date_format('2023-12-06'::DATE, '%m-%d');
|
|
|
|
+-----------------------------------------------+
|
|
| date_format(Utf8("2023-12-06"),Utf8("%m-%d")) |
|
|
+-----------------------------------------------+
|
|
| 12-06 |
|
|
+-----------------------------------------------+
|
|
|
|
--- test date functions with table rows ---
|
|
CREATE TABLE dates(d DATE, ts timestamp time index);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO dates VALUES ('1992-01-01'::DATE, 1);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO dates VALUES ('1993-12-30'::DATE, 2);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO dates VALUES ('2023-12-06'::DATE, 3);
|
|
|
|
Affected Rows: 1
|
|
|
|
--- date_add ---
|
|
SELECT date_add(d, INTERVAL '1 year 2 month 3 day') from dates;
|
|
|
|
+---------------------------------------------------------------------------+
|
|
| date_add(dates.d,IntervalMonthDayNano("1109194275255040958530743959552")) |
|
|
+---------------------------------------------------------------------------+
|
|
| 1993-03-04 |
|
|
| 1995-03-03 |
|
|
| 2025-02-09 |
|
|
+---------------------------------------------------------------------------+
|
|
|
|
SELECT date_add(d, '1 year 2 month 3 day') from dates;
|
|
|
|
+------------------------------------------------+
|
|
| date_add(dates.d,Utf8("1 year 2 month 3 day")) |
|
|
+------------------------------------------------+
|
|
| 1993-03-04 |
|
|
| 1995-03-03 |
|
|
| 2025-02-09 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT date_add(ts, INTERVAL '1 year 2 month 3 day') from dates;
|
|
|
|
+----------------------------------------------------------------------------+
|
|
| date_add(dates.ts,IntervalMonthDayNano("1109194275255040958530743959552")) |
|
|
+----------------------------------------------------------------------------+
|
|
| 1971-03-04T00:00:00.001 |
|
|
| 1971-03-04T00:00:00.002 |
|
|
| 1971-03-04T00:00:00.003 |
|
|
+----------------------------------------------------------------------------+
|
|
|
|
SELECT date_add(ts, '1 year 2 month 3 day') from dates;
|
|
|
|
+-------------------------------------------------+
|
|
| date_add(dates.ts,Utf8("1 year 2 month 3 day")) |
|
|
+-------------------------------------------------+
|
|
| 1971-03-04T00:00:00.001 |
|
|
| 1971-03-04T00:00:00.002 |
|
|
| 1971-03-04T00:00:00.003 |
|
|
+-------------------------------------------------+
|
|
|
|
--- date_sub ---
|
|
SELECT date_sub(d, INTERVAL '1 year 2 month 3 day') from dates;
|
|
|
|
+---------------------------------------------------------------------------+
|
|
| date_sub(dates.d,IntervalMonthDayNano("1109194275255040958530743959552")) |
|
|
+---------------------------------------------------------------------------+
|
|
| 1990-10-29 |
|
|
| 1992-10-27 |
|
|
| 2022-10-03 |
|
|
+---------------------------------------------------------------------------+
|
|
|
|
SELECT date_sub(d, '1 year 2 month 3 day') from dates;
|
|
|
|
+------------------------------------------------+
|
|
| date_sub(dates.d,Utf8("1 year 2 month 3 day")) |
|
|
+------------------------------------------------+
|
|
| 1990-10-29 |
|
|
| 1992-10-27 |
|
|
| 2022-10-03 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT date_sub(ts, INTERVAL '1 year 2 month 3 day') from dates;
|
|
|
|
+----------------------------------------------------------------------------+
|
|
| date_sub(dates.ts,IntervalMonthDayNano("1109194275255040958530743959552")) |
|
|
+----------------------------------------------------------------------------+
|
|
| 1968-10-29T00:00:00.001 |
|
|
| 1968-10-29T00:00:00.002 |
|
|
| 1968-10-29T00:00:00.003 |
|
|
+----------------------------------------------------------------------------+
|
|
|
|
SELECT date_sub(ts, '1 year 2 month 3 day') from dates;
|
|
|
|
+-------------------------------------------------+
|
|
| date_sub(dates.ts,Utf8("1 year 2 month 3 day")) |
|
|
+-------------------------------------------------+
|
|
| 1968-10-29T00:00:00.001 |
|
|
| 1968-10-29T00:00:00.002 |
|
|
| 1968-10-29T00:00:00.003 |
|
|
+-------------------------------------------------+
|
|
|
|
--- date_format ---
|
|
SELECT date_format(d, '%Y-%m-%d %H:%M:%S:%3f') from dates;
|
|
|
|
+----------------------------------------------------+
|
|
| date_format(dates.d,Utf8("%Y-%m-%d %H:%M:%S:%3f")) |
|
|
+----------------------------------------------------+
|
|
| 1992-01-01 00:00:00:000 |
|
|
| 1993-12-30 00:00:00:000 |
|
|
| 2023-12-06 00:00:00:000 |
|
|
+----------------------------------------------------+
|
|
|
|
SELECT date_format(ts, '%Y-%m-%d %H:%M:%S:%3f') from dates;
|
|
|
|
+-----------------------------------------------------+
|
|
| date_format(dates.ts,Utf8("%Y-%m-%d %H:%M:%S:%3f")) |
|
|
+-----------------------------------------------------+
|
|
| 1970-01-01 00:00:00:001 |
|
|
| 1970-01-01 00:00:00:002 |
|
|
| 1970-01-01 00:00:00:003 |
|
|
+-----------------------------------------------------+
|
|
|
|
DROP TABLE dates;
|
|
|
|
Affected Rows: 0
|
|
|