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 22 : void SetUp() override {
25 22 : ASSERT_EQ(::duckdb_open(nullptr, &db_), ::DuckDBSuccess);
26 22 : ASSERT_EQ(::duckdb_connect(db_, &conn_), ::DuckDBSuccess);
27 22 : absl::Status reg = RegisterAll(conn_);
28 44 : ASSERT_TRUE(reg.ok()) << reg;
29 22 : }
30 :
31 22 : void TearDown() override {
32 22 : if (conn_ != nullptr) ::duckdb_disconnect(&conn_);
33 22 : if (db_ != nullptr) ::duckdb_close(&db_);
34 22 : }
35 :
36 15 : int64_t RunInt64(const std::string& sql) {
37 15 : ::duckdb_result result;
38 15 : auto rc = ::duckdb_query(conn_, sql.c_str(), &result);
39 30 : EXPECT_EQ(rc, ::DuckDBSuccess) << "DuckDB rejected: "
40 30 : << (::duckdb_result_error(&result) == nullptr
41 30 : ? "(no error)"
42 30 : : ::duckdb_result_error(&result))
43 30 : << " (sql=" << sql << ")";
44 15 : int64_t v = ::duckdb_value_int64(&result, 0, 0);
45 15 : ::duckdb_destroy_result(&result);
46 15 : return v;
47 15 : }
48 :
49 10 : bool RunBool(const std::string& sql) {
50 10 : ::duckdb_result result;
51 10 : auto rc = ::duckdb_query(conn_, sql.c_str(), &result);
52 20 : EXPECT_EQ(rc, ::DuckDBSuccess) << "DuckDB rejected: " << sql;
53 10 : bool v = ::duckdb_value_boolean(&result, 0, 0);
54 10 : ::duckdb_destroy_result(&result);
55 10 : return v;
56 10 : }
57 :
58 : // Statement with no interesting result (e.g. SET TimeZone).
59 1 : void RunOk(const std::string& sql) {
60 1 : ::duckdb_result result;
61 1 : auto rc = ::duckdb_query(conn_, sql.c_str(), &result);
62 2 : EXPECT_EQ(rc, ::DuckDBSuccess) << "DuckDB rejected: " << sql;
63 1 : ::duckdb_destroy_result(&result);
64 1 : }
65 :
66 : ::duckdb_database db_ = nullptr;
67 : ::duckdb_connection conn_ = nullptr;
68 : };
69 :
70 : // 2024-01-01 00:00:00 UTC -> 1704067200 seconds since epoch.
71 : constexpr int64_t kSec2024_01_01 = 1704067200;
72 : constexpr int64_t kMs2024_01_01 = 1704067200LL * 1000;
73 : constexpr int64_t kUs2024_01_01 = 1704067200LL * 1000 * 1000;
74 :
75 : // --- bq_unix_seconds ---------------------------------------------
76 :
77 1 : TEST_F(DatetimeMacrosTest, UnixSecondsWholeSecond) {
78 1 : EXPECT_EQ(
79 1 : RunInt64("SELECT bq_unix_seconds(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
80 1 : kSec2024_01_01);
81 1 : }
82 :
83 1 : TEST_F(DatetimeMacrosTest, UnixSecondsTruncatesSubsecond) {
84 : // Edge case pinned: BigQuery UNIX_SECONDS truncates higher
85 : // precision. A regression that switched the CAST to a rounding
86 : // form would surface here as 1704067201 (rounding .999 up to
87 : // the next second) instead of 1704067200.
88 1 : EXPECT_EQ(
89 1 : RunInt64(
90 1 : "SELECT bq_unix_seconds(TIMESTAMPTZ '2024-01-01 00:00:00.999+00')"),
91 1 : kSec2024_01_01);
92 1 : }
93 :
94 1 : TEST_F(DatetimeMacrosTest, UnixSecondsNullPropagation) {
95 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_seconds(NULL::TIMESTAMPTZ) IS NULL"));
96 1 : }
97 :
98 : // --- bq_unix_millis ----------------------------------------------
99 :
100 1 : TEST_F(DatetimeMacrosTest, UnixMillisWholeSecond) {
101 1 : EXPECT_EQ(
102 1 : RunInt64("SELECT bq_unix_millis(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
103 1 : kMs2024_01_01);
104 1 : }
105 :
106 1 : TEST_F(DatetimeMacrosTest, UnixMillisSubsecond) {
107 : // Fractional milliseconds. 2024-01-01 00:00:00.5 -> +500 ms.
108 1 : EXPECT_EQ(
109 1 : RunInt64("SELECT bq_unix_millis(TIMESTAMPTZ '2024-01-01 00:00:00.5+00')"),
110 1 : kMs2024_01_01 + 500);
111 1 : }
112 :
113 1 : TEST_F(DatetimeMacrosTest, UnixMillisNullPropagation) {
114 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_millis(NULL::TIMESTAMPTZ) IS NULL"));
115 1 : }
116 :
117 : // --- bq_unix_micros ----------------------------------------------
118 :
119 1 : TEST_F(DatetimeMacrosTest, UnixMicrosWholeSecond) {
120 1 : EXPECT_EQ(
121 1 : RunInt64("SELECT bq_unix_micros(TIMESTAMPTZ '2024-01-01 00:00:00+00')"),
122 1 : kUs2024_01_01);
123 1 : }
124 :
125 1 : TEST_F(DatetimeMacrosTest, UnixMicrosSubsecond) {
126 : // 2024-01-01 00:00:00.123456 -> +123456 microseconds.
127 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_micros(TIMESTAMPTZ "
128 1 : "'2024-01-01 00:00:00.123456+00')"),
129 1 : kUs2024_01_01 + 123456);
130 1 : }
131 :
132 1 : TEST_F(DatetimeMacrosTest, UnixMicrosNullPropagation) {
133 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_micros(NULL::TIMESTAMPTZ) IS NULL"));
134 1 : }
135 :
136 : // --- bq_unix_date ------------------------------------------------
137 :
138 1 : TEST_F(DatetimeMacrosTest, UnixDateEpochIsZero) {
139 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1970-01-01')"), 0);
140 1 : }
141 :
142 1 : TEST_F(DatetimeMacrosTest, UnixDatePreEpochIsNegative) {
143 : // Edge case pinned: pre-1970 dates return negative day counts.
144 : // BigQuery documents this explicitly. A regression that wrapped
145 : // the result in `ABS(...)` or `GREATEST(..., 0)` would surface
146 : // here.
147 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1969-12-31')"), -1);
148 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '1969-12-01')"), -31);
149 1 : }
150 :
151 1 : TEST_F(DatetimeMacrosTest, UnixDateFutureDate) {
152 : // 2024-01-01 is 19723 days after 1970-01-01.
153 : // (54 years + 13 leap days = 19723 days.)
154 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_date(DATE '2024-01-01')"), 19723);
155 1 : }
156 :
157 1 : TEST_F(DatetimeMacrosTest, UnixDateNullPropagation) {
158 1 : EXPECT_TRUE(RunBool("SELECT bq_unix_date(NULL::DATE) IS NULL"));
159 1 : }
160 :
161 1 : TEST_F(DatetimeMacrosTest, DateAddDay) {
162 1 : EXPECT_TRUE(RunBool(
163 1 : "SELECT bq_date_add(DATE '2020-09-22', 1, 3) = DATE '2020-09-23'"));
164 1 : }
165 :
166 1 : TEST_F(DatetimeMacrosTest, DateAddMonthEndSnap) {
167 1 : EXPECT_TRUE(RunBool(
168 1 : "SELECT bq_date_add(DATE '2024-01-31', 1, 2) = DATE '2024-02-29'"));
169 1 : }
170 :
171 1 : TEST_F(DatetimeMacrosTest, ExtractYear) {
172 1 : EXPECT_EQ(RunInt64("SELECT bq_extract(1, DATE '2020-06-15')"), 2020);
173 1 : }
174 :
175 : // --- bq_timestamp_add / bq_timestamp_sub -------------------------
176 :
177 1 : TEST_F(DatetimeMacrosTest, TimestampAddSevenDaysLiteral) {
178 : // Same shape the conformance fixture exercises: n=7, part=DAY.
179 1 : EXPECT_TRUE(RunBool(
180 1 : "SELECT CAST(bq_timestamp_add(TIMESTAMPTZ '2024-03-09 12:00:00+00', "
181 1 : "7, 3) AS VARCHAR) = '2024-03-16 12:00:00+00'"));
182 1 : EXPECT_TRUE(RunBool(
183 1 : "SELECT typeof(bq_timestamp_add(TIMESTAMPTZ '2024-03-09 12:00:00+00', "
184 1 : "7, 3)) = 'TIMESTAMP WITH TIME ZONE'"));
185 1 : }
186 :
187 1 : TEST_F(DatetimeMacrosTest, TimestampAddDayIsExactly24hAcrossDst) {
188 : // Edge case pinned: BigQuery TIMESTAMP_ADD(t, INTERVAL 1 DAY) adds
189 : // exactly 86400 seconds. DuckDB's calendar-day interval on
190 : // TIMESTAMPTZ would add "one local day" (23h across the 2024-03-10
191 : // US spring-forward) in a non-UTC session zone; the macro's
192 : // to_microseconds arithmetic must not.
193 1 : RunOk("SET TimeZone='America/Los_Angeles'");
194 1 : EXPECT_EQ(RunInt64("SELECT epoch_us(bq_timestamp_add("
195 1 : "TIMESTAMPTZ '2024-03-09 12:00:00-08', 1, 3)) - "
196 1 : "epoch_us(TIMESTAMPTZ '2024-03-09 12:00:00-08')"),
197 1 : int64_t{86400} * 1000 * 1000);
198 1 : }
199 :
200 1 : TEST_F(DatetimeMacrosTest, TimestampAddMicrosecondGranularity) {
201 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_micros(bq_timestamp_add("
202 1 : "TIMESTAMPTZ '2024-01-01 00:00:00+00', 123456, 11))"),
203 1 : kUs2024_01_01 + 123456);
204 1 : }
205 :
206 1 : TEST_F(DatetimeMacrosTest, TimestampAddNegativeAmountSubtracts) {
207 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_seconds(bq_timestamp_add("
208 1 : "TIMESTAMPTZ '2024-01-01 00:00:00+00', -2, 7))"),
209 1 : kSec2024_01_01 - 2 * 3600);
210 1 : }
211 :
212 1 : TEST_F(DatetimeMacrosTest, TimestampSubMirrorsAdd) {
213 1 : EXPECT_EQ(RunInt64("SELECT bq_unix_seconds(bq_timestamp_sub("
214 1 : "TIMESTAMPTZ '2024-01-01 00:00:00+00', 90, 8))"),
215 1 : kSec2024_01_01 - 90 * 60);
216 1 : }
217 :
218 1 : TEST_F(DatetimeMacrosTest, TimestampAddNullPropagation) {
219 1 : EXPECT_TRUE(
220 1 : RunBool("SELECT bq_timestamp_add(NULL::TIMESTAMPTZ, 1, 3) IS NULL"));
221 1 : EXPECT_TRUE(
222 1 : RunBool("SELECT bq_timestamp_add(TIMESTAMPTZ '2024-01-01 00:00:00+00', "
223 1 : "NULL, 3) IS NULL"));
224 1 : }
225 :
226 : } // namespace
227 : } // namespace udf
228 : } // namespace duckdb
229 : } // namespace engine
230 : } // namespace backend
231 : } // namespace bigquery_emulator
|
