commit 5f7ebc55cc484a241bb1aab7a1af03908b98c0d7
from: Sergey Bronnikov <sergeyb@tarantool.org>
date: Tue Sep 17 07:52:26 2024 UTC

sql: forbid non-integer values in datetime

The patch forbids using non-integer values in datetime's `:set()`
for `year`, `month`, `day`, `hour`, `min`, `sec`, `usec`, `msec`,
`nsec` and `tzoffset` keys. `timestamp` can be double, and integer
values allowed in timestamp if `nsec`, `usec`, or `msecs`
provided. An error will be raised when a value of incorrect type
is passed.

Fixes #10391

@TarantoolBot document
Title: Update types of datetime values passed to SQL's `CAST();`

`CAST` can accept only integer values for `year`, `month`, `day`,
`hour`, `min`, `sec`, `usec`, `msec`, `nsec` and `tzoffset`.
`timestamp` can be integer or double.

(cherry picked from commit f57be571b5e4cc8d57c7e97c15b52df37ad6f12c)

commit - 55d197aff4433b5fb2a28464e718ce2625c0614e
commit + 5f7ebc55cc484a241bb1aab7a1af03908b98c0d7
blob - /dev/null
blob + ad1d46553852af85f5a0bf5a2fcd5d370a33a26a (mode 644)
--- /dev/null
+++ changelogs/unreleased/gh-10391-forbid-non-integers-sql-datetime.md
@@ -0,0 +1,3 @@
+## bugfix/datetime
+
+- Forbid non-integers in SQL's `CAST({}) AS datetime` (gh-10391).
blob - a66ed101cab29fbd0ecfa8108187eef628fbfb90
blob + 08a6ee5ed45a601d2995e7fb537d6fbcd060ee6a
--- src/lib/core/datetime.c
+++ src/lib/core/datetime.c
@@ -47,6 +47,12 @@ local_secs(const struct datetime *date)
 	return (int64_t)date->epoch + date->tzoffset * 60;
 }
 
+static int
+is_integer(double num)
+{
+	return roundf(num) == num;
+}
+
 /**
  * Resolve tzindex encoded timezone from @sa date using Olson facilities.
  * @param[in] epoch decode input epoch time (in seconds).
@@ -1052,6 +1058,18 @@ map_field_to_dt_field(struct dt_fields *fields, const 
 static int
 datetime_from_fields(struct datetime *dt, const struct dt_fields *fields)
 {
+	if (!is_integer(fields->year) ||
+	    !is_integer(fields->month) ||
+	    !is_integer(fields->day) ||
+	    !is_integer(fields->hour) ||
+	    !is_integer(fields->min) ||
+	    !is_integer(fields->sec) ||
+	    !is_integer(fields->msec) ||
+	    !is_integer(fields->usec) ||
+	    !is_integer(fields->nsec) ||
+	    !is_integer(fields->tzoffset))
+		return -1;
+
 	if (fields->count_usec > 1)
 		return -1;
 	double nsec = fields->msec * 1000000 + fields->usec * 1000 +
blob - 81ba24c8a90398085e103c5924c74f8033bad9f5
blob + 6df4ecc73fa2ac25b690433a4dec0a7d8231e28a
--- test/sql-luatest/datetime_test.lua
+++ test/sql-luatest/datetime_test.lua
@@ -2514,11 +2514,42 @@ g.test_datetime_33_3 = function()
         local dt = require('datetime')
         local dt1 = dt.new({year = 1})
         local sql = [[SELECT CAST({'year': 1.1} AS DATETIME);]]
-        t.assert_equals(box.execute(sql).rows, {{dt1}})
+        local _, err, res
+        _, err = box.execute(sql)
+        res = [[Type mismatch: can not convert ]]..
+              [[map({"year": 1.1}) to datetime]]
+        t.assert_equals(err.message, res)
 
         sql = [[SELECT CAST({'year': 1.1e0} AS DATETIME);]]
-        t.assert_equals(box.execute(sql).rows, {{dt1}})
+        _, err = box.execute(sql)
+        res = [[Type mismatch: can not convert ]]..
+              [[map({"year": 1.1}) to datetime]]
+        t.assert_equals(err.message, res)
 
+        -- The timestamp value should be integer if the fractional
+        -- part for the last second is set via the nsec.
+        sql = [[SELECT CAST({'timestamp': 1.1, 'nsec': 1} AS DATETIME);]]
+        _, err = box.execute(sql)
+        res = [[Type mismatch: can not convert ]]..
+              [[map({"timestamp": 1.1, "nsec": 1}) to datetime]]
+        t.assert_equals(err.message, res)
+
+        -- The timestamp value should be integer if the fractional
+        -- part for the last second is set via the usec.
+        sql = [[SELECT CAST({'timestamp': 1.1, 'usec': 1} AS DATETIME);]]
+        _, err = box.execute(sql)
+        res = [[Type mismatch: can not convert ]]..
+              [[map({"timestamp": 1.1, "usec": 1}) to datetime]]
+        t.assert_equals(err.message, res)
+
+        -- The timestamp value should be integer if the fractional
+        -- part for the last second is set via the msec.
+        sql = [[SELECT CAST({'timestamp': 1.1, 'msec': 1} AS DATETIME);]]
+        _, err = box.execute(sql)
+        res = [[Type mismatch: can not convert ]]..
+              [[map({"timestamp": 1.1, "msec": 1}) to datetime]]
+        t.assert_equals(err.message, res)
+
         sql = [[SELECT CAST({'year': 1} AS DATETIME);]]
         t.assert_equals(box.execute(sql).rows, {{dt1}})
     end)