This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 75588fe61 Minor: Port some timestamp tests to sqllogictests (#5804)
75588fe61 is described below
commit 75588fe611a3d375adb411cd70b818ad1c3d4ab9
Author: Andrew Lamb <[email protected]>
AuthorDate: Fri Mar 31 16:32:01 2023 +0200
Minor: Port some timestamp tests to sqllogictests (#5804)
---
datafusion/core/tests/sql/timestamp.rs | 375 ---------------------
.../tests/sqllogictests/test_files/timestamps.slt | 215 +++++++++++-
2 files changed, 210 insertions(+), 380 deletions(-)
diff --git a/datafusion/core/tests/sql/timestamp.rs
b/datafusion/core/tests/sql/timestamp.rs
index e3c2ef6df..e62465203 100644
--- a/datafusion/core/tests/sql/timestamp.rs
+++ b/datafusion/core/tests/sql/timestamp.rs
@@ -20,381 +20,6 @@ use datafusion::from_slice::FromSlice;
use datafusion_common::ScalarValue;
use std::ops::Add;
-#[tokio::test]
-async fn query_cast_timestamp_millis() -> Result<()> {
- let ctx = SessionContext::new();
-
- let t1_schema = Arc::new(Schema::new(vec![Field::new("ts",
DataType::Int64, true)]));
- let t1_data = RecordBatch::try_new(
- t1_schema.clone(),
- vec![Arc::new(Int64Array::from(vec![
- 1235865600000,
- 1235865660000,
- 1238544000000,
- ]))],
- )?;
- ctx.register_batch("t1", t1_data)?;
-
- let sql = "SELECT to_timestamp_millis(ts) FROM t1 LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+--------------------------+",
- "| totimestampmillis(t1.ts) |",
- "+--------------------------+",
- "| 2009-03-01T00:00:00 |",
- "| 2009-03-01T00:01:00 |",
- "| 2009-04-01T00:00:00 |",
- "+--------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_micros() -> Result<()> {
- let ctx = SessionContext::new();
-
- let t1_schema = Arc::new(Schema::new(vec![Field::new("ts",
DataType::Int64, true)]));
- let t1_data = RecordBatch::try_new(
- t1_schema.clone(),
- vec![Arc::new(Int64Array::from(vec![
- 1235865600000000,
- 1235865660000000,
- 1238544000000000,
- ]))],
- )?;
- ctx.register_batch("t1", t1_data)?;
-
- let sql = "SELECT to_timestamp_micros(ts) FROM t1 LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+--------------------------+",
- "| totimestampmicros(t1.ts) |",
- "+--------------------------+",
- "| 2009-03-01T00:00:00 |",
- "| 2009-03-01T00:01:00 |",
- "| 2009-04-01T00:00:00 |",
- "+--------------------------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_seconds() -> Result<()> {
- let ctx = SessionContext::new();
-
- let t1_schema = Arc::new(Schema::new(vec![Field::new("ts",
DataType::Int64, true)]));
- let t1_data = RecordBatch::try_new(
- t1_schema.clone(),
- vec![Arc::new(Int64Array::from(vec![
- 1235865600, 1235865660, 1238544000,
- ]))],
- )?;
- ctx.register_batch("t1", t1_data)?;
-
- let sql = "SELECT to_timestamp_seconds(ts) FROM t1 LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+---------------------------+",
- "| totimestampseconds(t1.ts) |",
- "+---------------------------+",
- "| 2009-03-01T00:00:00 |",
- "| 2009-03-01T00:01:00 |",
- "| 2009-04-01T00:00:00 |",
- "+---------------------------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_nanos_to_others() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table("ts_data", make_timestamp_nano_table()?)?;
-
- // Original column is nanos, convert to millis and check timestamp
- let sql = "SELECT to_timestamp_millis(ts) FROM ts_data LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-------------------------------+",
- "| totimestampmillis(ts_data.ts) |",
- "+-------------------------------+",
- "| 2020-09-08T13:42:29.190 |",
- "| 2020-09-08T12:42:29.190 |",
- "| 2020-09-08T11:42:29.190 |",
- "+-------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT to_timestamp_micros(ts) FROM ts_data LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-------------------------------+",
- "| totimestampmicros(ts_data.ts) |",
- "+-------------------------------+",
- "| 2020-09-08T13:42:29.190855 |",
- "| 2020-09-08T12:42:29.190855 |",
- "| 2020-09-08T11:42:29.190855 |",
- "+-------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT to_timestamp_seconds(ts) FROM ts_data LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------------------------+",
- "| totimestampseconds(ts_data.ts) |",
- "+--------------------------------+",
- "| 2020-09-08T13:42:29 |",
- "| 2020-09-08T12:42:29 |",
- "| 2020-09-08T11:42:29 |",
- "+--------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_seconds_to_others() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table("ts_secs",
make_timestamp_table::<TimestampSecondType>()?)?;
-
- // Original column is seconds, convert to millis and check timestamp
- let sql = "SELECT to_timestamp_millis(ts) FROM ts_secs LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------------------------+",
- "| totimestampmillis(ts_secs.ts) |",
- "+-------------------------------+",
- "| 2020-09-08T13:42:29 |",
- "| 2020-09-08T12:42:29 |",
- "| 2020-09-08T11:42:29 |",
- "+-------------------------------+",
- ];
-
- assert_batches_eq!(expected, &actual);
-
- // Original column is seconds, convert to micros and check timestamp
- let sql = "SELECT to_timestamp_micros(ts) FROM ts_secs LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------------------------+",
- "| totimestampmicros(ts_secs.ts) |",
- "+-------------------------------+",
- "| 2020-09-08T13:42:29 |",
- "| 2020-09-08T12:42:29 |",
- "| 2020-09-08T11:42:29 |",
- "+-------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // to nanos
- let sql = "SELECT to_timestamp(ts) FROM ts_secs LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------------------+",
- "| totimestamp(ts_secs.ts) |",
- "+-------------------------+",
- "| 2020-09-08T13:42:29 |",
- "| 2020-09-08T12:42:29 |",
- "| 2020-09-08T11:42:29 |",
- "+-------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_micros_to_others() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table(
- "ts_micros",
- make_timestamp_table::<TimestampMicrosecondType>()?,
- )?;
-
- // Original column is micros, convert to millis and check timestamp
- let sql = "SELECT to_timestamp_millis(ts) FROM ts_micros LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------------------+",
- "| totimestampmillis(ts_micros.ts) |",
- "+---------------------------------+",
- "| 2020-09-08T13:42:29.190 |",
- "| 2020-09-08T12:42:29.190 |",
- "| 2020-09-08T11:42:29.190 |",
- "+---------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // Original column is micros, convert to seconds and check timestamp
- let sql = "SELECT to_timestamp_seconds(ts) FROM ts_micros LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----------------------------------+",
- "| totimestampseconds(ts_micros.ts) |",
- "+----------------------------------+",
- "| 2020-09-08T13:42:29 |",
- "| 2020-09-08T12:42:29 |",
- "| 2020-09-08T11:42:29 |",
- "+----------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // Original column is micros, convert to nanos and check timestamp
- let sql = "SELECT to_timestamp(ts) FROM ts_micros LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----------------------------+",
- "| totimestamp(ts_micros.ts) |",
- "+----------------------------+",
- "| 2020-09-08T13:42:29.190855 |",
- "| 2020-09-08T12:42:29.190855 |",
- "| 2020-09-08T11:42:29.190855 |",
- "+----------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_cast_timestamp_from_unixtime() -> Result<()> {
- let ctx = SessionContext::new();
-
- let t1_schema = Arc::new(Schema::new(vec![Field::new("ts",
DataType::Int64, true)]));
- let t1_data = RecordBatch::try_new(
- t1_schema.clone(),
- vec![Arc::new(Int64Array::from(vec![
- 1235865600, 1235865660, 1238544000,
- ]))],
- )?;
- ctx.register_batch("t1", t1_data)?;
-
- let sql = "SELECT from_unixtime(ts) FROM t1 LIMIT 3";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+---------------------+",
- "| fromunixtime(t1.ts) |",
- "+---------------------+",
- "| 2009-03-01T00:00:00 |",
- "| 2009-03-01T00:01:00 |",
- "| 2009-04-01T00:00:00 |",
- "+---------------------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn to_timestamp() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table("ts_data", make_timestamp_nano_table()?)?;
-
- let sql = "SELECT COUNT(*) FROM ts_data where ts >
to_timestamp('2020-09-08T12:00:00+00:00')";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 2 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn to_timestamp_millis() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table(
- "ts_data",
- make_timestamp_table::<TimestampMillisecondType>()?,
- )?;
-
- let sql = "SELECT COUNT(*) FROM ts_data where ts >
to_timestamp_millis('2020-09-08T12:00:00+00:00')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 2 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn to_timestamp_micros() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table(
- "ts_data",
- make_timestamp_table::<TimestampMicrosecondType>()?,
- )?;
-
- let sql = "SELECT COUNT(*) FROM ts_data where ts >
to_timestamp_micros('2020-09-08T12:00:00+00:00')";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 2 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn to_timestamp_seconds() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table("ts_data",
make_timestamp_table::<TimestampSecondType>()?)?;
-
- let sql = "SELECT COUNT(*) FROM ts_data where ts >
to_timestamp_seconds('2020-09-08T12:00:00+00:00')";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 2 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn from_unixtime() -> Result<()> {
- let ctx = SessionContext::new();
- ctx.register_table("ts_data",
make_timestamp_table::<TimestampSecondType>()?)?;
-
- let sql = "SELECT COUNT(*) FROM ts_data where ts >
from_unixtime(1599566400)"; // '2020-09-08T12:00:00+00:00'
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 2 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
#[tokio::test]
async fn count_distinct_timestamps() -> Result<()> {
let ctx = SessionContext::new();
diff --git a/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
b/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
index 5f5ea4e61..952c501b4 100644
--- a/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
@@ -68,9 +68,214 @@ select * from foo where ts != '2000-02-01T00:00:00';
statement ok
drop table foo;
-###
+##########
+## to_timestamp tests
+##########
+
+statement ok
+create table t1(ts bigint) as VALUES
+ (1235865600000),
+ (1235865660000),
+ (1238544000000);
+
+
+# query_cast_timestamp_millis
+query P
+SELECT to_timestamp_millis(ts) FROM t1 LIMIT 3
+----
+2009-03-01T00:00:00
+2009-03-01T00:01:00
+2009-04-01T00:00:00
+
+# query_cast_timestamp_micros
+
+query P
+SELECT to_timestamp_micros(ts * 1000) FROM t1 LIMIT 3
+----
+2009-03-01T00:00:00
+2009-03-01T00:01:00
+2009-04-01T00:00:00
+
+# query_cast_timestamp_seconds
+
+query P
+SELECT to_timestamp_seconds(ts / 1000) FROM t1 LIMIT 3
+----
+2009-03-01T00:00:00
+2009-03-01T00:01:00
+2009-04-01T00:00:00
+
+statement error DataFusion error: Execution error: Table 'ts' doesn't exist\.
+drop table ts;
+
+# Create timestamp tables with different precisions but the same logical values
+
+statement ok
+create table ts_data(ts bigint, value int) as values
+ (1599572549190855000, 1),
+ (1599568949190855000, 2),
+ (1599565349190855000, 3);
+
+statement ok
+create table ts_data_nanos as select arrow_cast(ts, 'Timestamp(Nanosecond,
None)') as ts, value from ts_data;
+
+statement ok
+create table ts_data_micros as select arrow_cast(ts / 1000,
'Timestamp(Microsecond, None)') as ts, value from ts_data;
+
+statement ok
+create table ts_data_millis as select arrow_cast(ts / 1000000,
'Timestamp(Millisecond, None)') as ts, value from ts_data;
+
+statement ok
+create table ts_data_secs as select arrow_cast(ts / 1000000000,
'Timestamp(Second, None)') as ts, value from ts_data;
+
+
+
+# query_cast_timestamp_nanos_to_others
+
+query P
+SELECT to_timestamp_micros(ts / 1000) FROM ts_data LIMIT 3
+----
+2020-09-08T13:42:29.190855
+2020-09-08T12:42:29.190855
+2020-09-08T11:42:29.190855
+
+query P
+SELECT to_timestamp_millis(ts / 1000000) FROM ts_data LIMIT 3
+----
+2020-09-08T13:42:29.190
+2020-09-08T12:42:29.190
+2020-09-08T11:42:29.190
+
+
+query P
+SELECT to_timestamp_seconds(ts / 1000000000) FROM ts_data LIMIT 3
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+# query_cast_timestamp_seconds_to_others
+
+# Original column is seconds, convert to millis and check timestamp
+query P
+SELECT to_timestamp_millis(ts) FROM ts_data_secs LIMIT 3
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+# Original column is seconds, convert to micros and check timestamp
+query P
+SELECT to_timestamp_micros(ts) FROM ts_data_secs LIMIT 3
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+# to nanos
+query P
+SELECT to_timestamp(ts) FROM ts_data_secs LIMIT 3
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+
+# query_cast_timestamp_micros_to_others
+
+# Original column is micros, convert to millis and check timestamp
+query P
+SELECT to_timestamp_millis(ts) FROM ts_data_micros LIMIT 3
+----
+2020-09-08T13:42:29.190
+2020-09-08T12:42:29.190
+2020-09-08T11:42:29.190
+
+
+# Original column is micros, convert to seconds and check timestamp
+query P
+SELECT to_timestamp_seconds(ts) FROM ts_data_micros LIMIT 3
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+
+# Original column is micros, convert to nanos and check timestamp
+
+query P
+SELECT to_timestamp(ts) FROM ts_data_micros LIMIT 3
+----
+2020-09-08T13:42:29.190855
+2020-09-08T12:42:29.190855
+2020-09-08T11:42:29.190855
+
+# query_cast_timestamp_from_unixtime
+
+
+query P
+SELECT from_unixtime(ts / 1000000000) FROM ts_data LIMIT 3;
+----
+2020-09-08T13:42:29
+2020-09-08T12:42:29
+2020-09-08T11:42:29
+
+# to_timestamp
+
+query I
+SELECT COUNT(*) FROM ts_data_nanos where ts >
to_timestamp('2020-09-08T12:00:00+00:00')
+----
+2
+
+# to_timestamp_millis
+
+query I
+SELECT COUNT(*) FROM ts_data_millis where ts >
to_timestamp_millis('2020-09-08T12:00:00+00:00')
+----
+2
+
+# to_timestamp_micros
+
+query I
+SELECT COUNT(*) FROM ts_data_micros where ts >
to_timestamp_micros('2020-09-08T12:00:00+00:00')
+----
+2
+
+# to_timestamp_seconds
+
+query I
+SELECT COUNT(*) FROM ts_data_secs where ts >
to_timestamp_seconds('2020-09-08T12:00:00+00:00')
+----
+2
+
+# from_unixtime
+
+# 1599566400 is '2020-09-08T12:00:00+00:00'
+query I
+SELECT COUNT(*) FROM ts_data_secs where ts > from_unixtime(1599566400)
+----
+2
+
+statement ok
+drop table ts_data
+
+statement ok
+drop table ts_data_nanos
+
+statement ok
+drop table ts_data_micros
+
+statement ok
+drop table ts_data_millis
+
+statement ok
+drop table ts_data_secs
+
+
+
+##########
## test date_bin function
-###
+##########
query P
SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z',
TIMESTAMP '1970-01-01T00:00:00Z')
----
@@ -263,7 +468,7 @@ SELECT INTERVAL '8' MONTH +
'2000-01-01T00:00:00'::timestamp;
# Interval columns are created with timestamp subtraction in subquery since
they are not supported yet
statement ok
-create table foo (val int, ts1 timestamp, ts2 timestamp) as values
+create table foo (val int, ts1 timestamp, ts2 timestamp) as values
(1, '2023-03-15T15:00:20.000000123'::timestamp,
'2023-01-20T23:00:00.000000099'::timestamp),
(2, '2023-02-28T12:01:55.000123456'::timestamp,
'2000-02-23T11:00:00.123000001'::timestamp),
(3, '2033-11-02T23:22:13.000123456'::timestamp,
'1990-03-01T00:00:00.333000001'::timestamp),
@@ -355,7 +560,7 @@ FROM foo;
# Interval - Timestamp => error
statement error DataFusion error: Error during planning:
Interval\(MonthDayNano\) - Timestamp\(Nanosecond, None\) can't be evaluated
because there isn't a common type to coerce the types to
-SELECT subq.interval1 - subq.ts1
+SELECT subq.interval1 - subq.ts1
FROM (
SELECT ts1,
ts1 - ts2 AS interval1
@@ -367,7 +572,7 @@ drop table foo;
# timestamptz to utf8 conversion
query BBBB
-SELECT
+SELECT
'2000-01-01T00:00:00'::timestamp::timestamptz = '2000-01-01T00:00:00',
'2000-01-01T00:00:00'::timestamp::timestamptz != '2000-01-01T00:00:00',
'2000-01-01T00:00:00'::timestamp::timestamptz >= '2000-01-01T00:00:00',
