Line data Source code
1 : // Unit tests for the BigQuery datetime polyfill macros.
2 : //
3 : // Each test drives the macro directly against an in-process DuckDB
4 : // connection and exercises both the common path and the
5 : // BigQuery-specific edge case the wrapper exists to pin.
6 :
7 : #include <cstdint>
8 : #include <string>
9 :
10 : #include "absl/status/status.h"
11 : #include "backend/engine/duckdb/udf/registrar.h"
12 : #include "duckdb.h"
13 : #include "gtest/gtest.h"
14 :
15 : namespace bigquery_emulator {
16 : namespace backend {
17 : namespace engine {
18 : namespace duckdb {
19 : namespace udf {
20 : namespace {
21 :
22 : class DatetimeMacrosTest : public ::testing::Test {
23 : protected:
24 16 : void SetUp() override {
25 16 : ASSERT_EQ(::duckdb_open(nullptr, &db_), ::DuckDBSuccess);
26 16 : ASSERT_EQ(::duckdb_connect(db_, &conn_), ::DuckDBSuccess);
27 16 : absl::Status reg = RegisterAll(conn_);
28 32 : ASSERT_TRUE(reg.ok()) << reg;
29 16 : }
30 :
31 16 : void TearDown() override {
32 16 : if (conn_ != nullptr) ::duckdb_disconnect(&conn_);
33 16 : if (db_ != nullptr) ::duckdb_close(&db_);
34 16 : }
35 :
36 11 : int64_t RunInt64(const std::string& sql) {
37 11 : ::duckdb_result result;
38 11 : auto rc = ::duckdb_query(conn_, sql.c_str(), &result);
39 22 : EXPECT_EQ(rc, ::DuckDBSuccess) << "DuckDB rejected: "
40 22 : << (::duckdb_result_error(&result) == nullptr
41 22 : ? "(no error)"
42 22 : : ::duckdb_result_error(&result))
43 22 : << " (sql=" << sql << ")";
44 11 : int64_t v = ::duckdb_value_int64(&result, 0, 0);
45 11 : ::duckdb_destroy_result(&result);
46 11 : return v;
47 11 : }
48 :
49 6 : bool RunBool(const std::string& sql) {
50 6 : ::duckdb_result result;
51 6 : auto rc = ::duckdb_query(conn_, sql.c_str(), &result);
52 12 : EXPECT_EQ(rc, ::DuckDBSuccess) << "DuckDB rejected: " << sql;
53 6 : bool v = ::duckdb_value_boolean(&result, 0, 0);
54 6 : ::duckdb_destroy_result(&result);
55 6 : return v;
56 6 : }
57 :
58 : ::duckdb_database db_ = nullptr;
59 : ::duckdb_connection conn_ = nullptr;
60 : };
61 :
62 : // 2024-01-01 00:00:00 UTC -> 1704067200 seconds since epoch.
63 : constexpr int64_t kSec2024_01_01 = 1704067200;
64 : constexpr int64_t kMs2024_01_01 = 1704067200LL * 1000;
65 : constexpr int64_t kUs2024_01_01 = 1704067200LL * 1000 * 1000;
66 :
67 : // --- bq_unix_seconds ---------------------------------------------
68 :
69 1 : TEST_F(DatetimeMacrosTest, UnixSecondsWholeSecond) {
70 1 : EXPECT_EQ(
71 1 : RunInt64("SELECT bq_unix_seconds(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
72 1 : kSec2024_01_01);
73 1 : }
74 :
75 1 : TEST_F(DatetimeMacrosTest, UnixSecondsTruncatesSubsecond) {
76 : // Edge case pinned: BigQuery UNIX_SECONDS truncates higher
77 : // precision. A regression that switched the CAST to a rounding
78 : // form would surface here as 1704067201 (rounding .999 up to
79 : // the next second) instead of 1704067200.
80 1 : EXPECT_EQ(
81 1 : RunInt64(
82 1 : "SELECT bq_unix_seconds(TIMESTAMPTZ '2024-01-01 00:00:00.999+00')"),
83 1 : kSec2024_01_01);
84 1 : }
85 :
86 1 : TEST_F(DatetimeMacrosTest, UnixSecondsNullPropagation) {
87 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_seconds(NULL::TIMESTAMPTZ) IS NULL"));
88 1 : }
89 :
90 : // --- bq_unix_millis ----------------------------------------------
91 :
92 1 : TEST_F(DatetimeMacrosTest, UnixMillisWholeSecond) {
93 1 : EXPECT_EQ(
94 1 : RunInt64("SELECT bq_unix_millis(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
95 1 : kMs2024_01_01);
96 1 : }
97 :
98 1 : TEST_F(DatetimeMacrosTest, UnixMillisSubsecond) {
99 : // Fractional milliseconds. 2024-01-01 00:00:00.5 -> +500 ms.
100 1 : EXPECT_EQ(
101 1 : RunInt64("SELECT bq_unix_millis(TIMESTAMPTZ '2024-01-01 00:00:00.5+00')"),
102 1 : kMs2024_01_01 + 500);
103 1 : }
104 :
105 1 : TEST_F(DatetimeMacrosTest, UnixMillisNullPropagation) {
106 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_millis(NULL::TIMESTAMPTZ) IS NULL"));
107 1 : }
108 :
109 : // --- bq_unix_micros ----------------------------------------------
110 :
111 1 : TEST_F(DatetimeMacrosTest, UnixMicrosWholeSecond) {
112 1 : EXPECT_EQ(
113 1 : RunInt64("SELECT bq_unix_micros(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
114 1 : kUs2024_01_01);
115 1 : }
116 :
117 1 : TEST_F(DatetimeMacrosTest, UnixMicrosSubsecond) {
118 : // 2024-01-01 00:00:00.123456 -> +123456 microseconds.
119 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_micros(TIMESTAMPTZ "
120 1 : "'2024-01-01 00:00:00.123456+00')"),
121 1 : kUs2024_01_01 + 123456);
122 1 : }
123 :
124 1 : TEST_F(DatetimeMacrosTest, UnixMicrosNullPropagation) {
125 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_micros(NULL::TIMESTAMPTZ) IS NULL"));
126 1 : }
127 :
128 : // --- bq_unix_date ------------------------------------------------
129 :
130 1 : TEST_F(DatetimeMacrosTest, UnixDateEpochIsZero) {
131 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1970-01-01')"), 0);
132 1 : }
133 :
134 1 : TEST_F(DatetimeMacrosTest, UnixDatePreEpochIsNegative) {
135 : // Edge case pinned: pre-1970 dates return negative day counts.
136 : // BigQuery documents this explicitly. A regression that wrapped
137 : // the result in `ABS(...)` or `GREATEST(..., 0)` would surface
138 : // here.
139 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1969-12-31')"), -1);
140 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1969-12-01')"), -31);
141 1 : }
142 :
143 1 : TEST_F(DatetimeMacrosTest, UnixDateFutureDate) {
144 : // 2024-01-01 is 19723 days after 1970-01-01.
145 : // (54 years + 13 leap days = 19723 days.)
146 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '2024-01-01')"), 19723);
147 1 : }
148 :
149 1 : TEST_F(DatetimeMacrosTest, UnixDateNullPropagation) {
150 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_date(NULL::DATE) IS NULL"));
151 1 : }
152 :
153 1 : TEST_F(DatetimeMacrosTest, DateAddDay) {
154 1 : EXPECT_TRUE(RunBool(
155 1 : "SELECT bq_date_add(DATE '2020-09-22', 1, 3) = DATE '2020-09-23'"));
156 1 : }
157 :
158 1 : TEST_F(DatetimeMacrosTest, DateAddMonthEndSnap) {
159 1 : EXPECT_TRUE(RunBool(
160 1 : "SELECT bq_date_add(DATE '2024-01-31', 1, 2) = DATE '2024-02-29'"));
161 1 : }
162 :
163 1 : TEST_F(DatetimeMacrosTest, ExtractYear) {
164 1 : EXPECT_EQ(RunInt64("SELECT bq_extract(1, DATE '2020-06-15')"), 2020);
165 1 : }
166 :
167 : } // namespace
168 : } // namespace udf
169 : } // namespace duckdb
170 : } // namespace engine
171 : } // namespace backend
172 : } // namespace bigquery_emulator
|
