Repository: matrixorigin/matrixone
Pull request #6047 (2022-10-27 14:15:32)

[Bug]: the cast from datetime to timestamp doesn't handle out of range situation #3459
fix unix_timestamp when out of range

## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue #3459

## What this PR does / why we need it:
fix unix_timestamp when out of range, should be null


Patch:
diff --git a/pkg/vectorize/unixtimestamp/unixtimestamp.go b/pkg/vectorize/unixtimestamp/unixtimestamp.go
index 1e895d742..4d7217001 100644
--- a/pkg/vectorize/unixtimestamp/unixtimestamp.go
+++ b/pkg/vectorize/unixtimestamp/unixtimestamp.go
@@ -18,6 +18,8 @@ import (
 	"github.com/matrixorigin/matrixone/pkg/container/types"
 )
 
+const int32_MAX int64 = 0x7fffffff
+
 var (
 	UnixTimestamp func([]types.Timestamp, []int64) []int64
 )
@@ -29,6 +31,9 @@ func init() {
 func unixTimestamp(xs []types.Timestamp, rs []int64) []int64 {
 	for i := range xs {
 		rs[i] = xs[i].Unix()
+		if rs[i] > int32_MAX {
+			rs[i] = -1
+		}
 	}
 	return rs
 }


Test patch:
diff --git a/pkg/sql/plan/function/builtin/multi/unix_timestamp_test.go b/pkg/sql/plan/function/builtin/multi/unix_timestamp_test.go
index ded318c04..d535e1eb9 100644
--- a/pkg/sql/plan/function/builtin/multi/unix_timestamp_test.go
+++ b/pkg/sql/plan/function/builtin/multi/unix_timestamp_test.go
@@ -31,6 +31,7 @@ func TestUnixTimestamp(t *testing.T) {
 	UnixtimeCase(t, types.T_int64, MustTimestamp(time.UTC, "2022-01-02 22:23:00"), 1641162180, false)
 	UnixtimeCase(t, types.T_int64, MustTimestamp(time.UTC, "2022-01-03 22:23:00"), 1641248580, false)
 	UnixtimeCase(t, types.T_int64, MustTimestamp(time.UTC, "2022-02-29 22:23:00"), 0, true)
+	UnixtimeCase(t, types.T_int64, MustTimestamp(time.UTC, "2050-02-29 22:23:00"), 0, true)
 }
 
 // func FromUnixTime(lv []*vector.Vector, proc *process.Process) (*vector.Vector, error)
diff --git a/test/distributed/result/function/func_datetime_unixtime.result b/test/distributed/result/function/func_datetime_unixtime.result
index 1864e0008..a2b1e7a6d 100644
--- a/test/distributed/result/function/func_datetime_unixtime.result
+++ b/test/distributed/result/function/func_datetime_unixtime.result
@@ -19,22 +19,22 @@ unix_timestamp(from_unixtime(2147483647))
 2147483647
 select unix_timestamp(from_unixtime(2147483648));
 unix_timestamp(from_unixtime(2147483648))
-2147483648
+null
 select unix_timestamp('2039-01-20 01:00:00');
 unix_timestamp(2039-01-20 01:00:00)
-2179098000
+null
 select unix_timestamp('1968-01-20 01:00:00');
 unix_timestamp(1968-01-20 01:00:00)
 null
 select unix_timestamp('2038-02-10 01:00:00');
 unix_timestamp(2038-02-10 01:00:00)
-2149376400
+null
 select unix_timestamp('1969-11-20 01:00:00');
 unix_timestamp(1969-11-20 01:00:00)
 null
 select unix_timestamp('2038-01-20 01:00:00');
 unix_timestamp(2038-01-20 01:00:00)
-2147562000
+null
 select unix_timestamp('1969-12-30 01:00:00');
 unix_timestamp(1969-12-30 01:00:00)
 null
@@ -46,7 +46,7 @@ unix_timestamp(1970-01-01 03:00:01)
 10801
 select unix_timestamp('2038-01-19 07:14:07');
 unix_timestamp(2038-01-19 07:14:07)
-2147498047
+null
 set time_zone="+01:00";
 select unix_timestamp('1970-01-01 01:00:00'),
 unix_timestamp('1970-01-01 01:00:01'),
@@ -54,7 +54,7 @@ unix_timestamp('2038-01-19 04:14:07'),
 unix_timestamp('2038-01-19 04:14:08'),
 unix_timestamp('2021-02-29 04:14:08');
 unix_timestamp(1970-01-01 01:00:00)    unix_timestamp(1970-01-01 01:00:01)    unix_timestamp(2038-01-19 04:14:07)    unix_timestamp(2038-01-19 04:14:08)    unix_timestamp(2021-02-29 04:14:08)
-null    1    2147483647    2147483648    null
+null    1    2147483647    null    null
 SET time_zone='+00:00';
 CREATE TABLE t1 (a DECIMAL(20,7));
 INSERT INTO t1 VALUES
@@ -112,66 +112,66 @@ from_unixtime(32536771199)
 3001-01-19 07:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
 unix_timestamp(3001-01-18 23:59:59)
-32536742399
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
 unix_timestamp(3001-01-19 00:00:00)
-32536742400
+null
 SET time_zone = "+00:00";
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
 unix_timestamp(3001-01-19 00:00:00)
-32536771200
+null
 SET time_zone = "+01:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-19 00:59:59
 SELECT UNIX_TIMESTAMP('3001-01-19 00:59:59');
 unix_timestamp(3001-01-19 00:59:59)
-32536771199
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 01:00:00');
 unix_timestamp(3001-01-19 01:00:00)
-32536771200
+null
 SET time_zone = "+09:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-19 08:59:59
 SELECT UNIX_TIMESTAMP("3001-01-19 08:59:59");
 unix_timestamp(3001-01-19 08:59:59)
-32536771199
+null
 SELECT UNIX_TIMESTAMP("3001-01-19 09:00:00");
 unix_timestamp(3001-01-19 09:00:00)
-32536771200
+null
 SET time_zone = "-01:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-18 22:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 22:59:59');
 unix_timestamp(3001-01-18 22:59:59)
-32536771199
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
 unix_timestamp(3001-01-18 23:59:59)
-32536774799
+null
 SET TIME_ZONE = "-07:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-18 16:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 15:59:59');
 unix_timestamp(3001-01-18 15:59:59)
-32536767599
+null
 SELECT UNIX_TIMESTAMP('3001-01-18 16:00:00');
 unix_timestamp(3001-01-18 16:00:00)
-32536767600
+null
 SELECT FROM_UNIXTIME(9223372036854775807);
 from_unixtime(9223372036854775807)
 null
diff --git a/test/result/function/func_datetime_unixtime.result b/test/result/function/func_datetime_unixtime.result
index 1864e0008..a2b1e7a6d 100644
--- a/test/result/function/func_datetime_unixtime.result
+++ b/test/result/function/func_datetime_unixtime.result
@@ -19,22 +19,22 @@ unix_timestamp(from_unixtime(2147483647))
 2147483647
 select unix_timestamp(from_unixtime(2147483648));
 unix_timestamp(from_unixtime(2147483648))
-2147483648
+null
 select unix_timestamp('2039-01-20 01:00:00');
 unix_timestamp(2039-01-20 01:00:00)
-2179098000
+null
 select unix_timestamp('1968-01-20 01:00:00');
 unix_timestamp(1968-01-20 01:00:00)
 null
 select unix_timestamp('2038-02-10 01:00:00');
 unix_timestamp(2038-02-10 01:00:00)
-2149376400
+null
 select unix_timestamp('1969-11-20 01:00:00');
 unix_timestamp(1969-11-20 01:00:00)
 null
 select unix_timestamp('2038-01-20 01:00:00');
 unix_timestamp(2038-01-20 01:00:00)
-2147562000
+null
 select unix_timestamp('1969-12-30 01:00:00');
 unix_timestamp(1969-12-30 01:00:00)
 null
@@ -46,7 +46,7 @@ unix_timestamp(1970-01-01 03:00:01)
 10801
 select unix_timestamp('2038-01-19 07:14:07');
 unix_timestamp(2038-01-19 07:14:07)
-2147498047
+null
 set time_zone="+01:00";
 select unix_timestamp('1970-01-01 01:00:00'),
 unix_timestamp('1970-01-01 01:00:01'),
@@ -54,7 +54,7 @@ unix_timestamp('2038-01-19 04:14:07'),
 unix_timestamp('2038-01-19 04:14:08'),
 unix_timestamp('2021-02-29 04:14:08');
 unix_timestamp(1970-01-01 01:00:00)    unix_timestamp(1970-01-01 01:00:01)    unix_timestamp(2038-01-19 04:14:07)    unix_timestamp(2038-01-19 04:14:08)    unix_timestamp(2021-02-29 04:14:08)
-null    1    2147483647    2147483648    null
+null    1    2147483647    null    null
 SET time_zone='+00:00';
 CREATE TABLE t1 (a DECIMAL(20,7));
 INSERT INTO t1 VALUES
@@ -112,66 +112,66 @@ from_unixtime(32536771199)
 3001-01-19 07:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
 unix_timestamp(3001-01-18 23:59:59)
-32536742399
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
 unix_timestamp(3001-01-19 00:00:00)
-32536742400
+null
 SET time_zone = "+00:00";
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
 unix_timestamp(3001-01-19 00:00:00)
-32536771200
+null
 SET time_zone = "+01:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-19 00:59:59
 SELECT UNIX_TIMESTAMP('3001-01-19 00:59:59');
 unix_timestamp(3001-01-19 00:59:59)
-32536771199
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-19 01:00:00');
 unix_timestamp(3001-01-19 01:00:00)
-32536771200
+null
 SET time_zone = "+09:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-19 08:59:59
 SELECT UNIX_TIMESTAMP("3001-01-19 08:59:59");
 unix_timestamp(3001-01-19 08:59:59)
-32536771199
+null
 SELECT UNIX_TIMESTAMP("3001-01-19 09:00:00");
 unix_timestamp(3001-01-19 09:00:00)
-32536771200
+null
 SET time_zone = "-01:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-18 22:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 22:59:59');
 unix_timestamp(3001-01-18 22:59:59)
-32536771199
+null
 SELECT FROM_UNIXTIME(32536771200);
 from_unixtime(32536771200)
 null
 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
 unix_timestamp(3001-01-18 23:59:59)
-32536774799
+null
 SET TIME_ZONE = "-07:00";
 SELECT FROM_UNIXTIME(32536771199);
 from_unixtime(32536771199)
 3001-01-18 16:59:59
 SELECT UNIX_TIMESTAMP('3001-01-18 15:59:59');
 unix_timestamp(3001-01-18 15:59:59)
-32536767599
+null
 SELECT UNIX_TIMESTAMP('3001-01-18 16:00:00');
 unix_timestamp(3001-01-18 16:00:00)
-32536767600
+null
 SELECT FROM_UNIXTIME(9223372036854775807);
 from_unixtime(9223372036854775807)
 null