* This file is part of the oGRAC project.
* Copyright (c) 2024 Huawei Technologies Co.,Ltd.
*
* oGRAC is licensed under Mulan PSL v2.
* You can use this software according to the terms and conditions of the Mulan PSL v2.
* You may obtain a copy of Mulan PSL v2 at:
*
* http://license.coscl.org.cn/MulanPSL2
*
* THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
* EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
* MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
* See the Mulan PSL v2 for more details.
* -------------------------------------------------------------------------
*
* func_date.c
*
*
* IDENTIFICATION
* src/ogsql/function/func_date.c
*
* -------------------------------------------------------------------------
*/
#include "func_date.h"
#include "srv_instance.h"
static inline bool32 sql_is_last_month_day(date_detail_t *data_detail)
{
return (data_detail->day == CM_MONTH_DAYS(data_detail->year, data_detail->mon));
}
static bool32 sql_get_datetime_prec_from_var(const variant_t *var, int32 *precision)
{
int32 prec;
switch ((int32)var->type) {
case OG_TYPE_INTEGER:
*precision = var->v_int;
return OG_TRUE;
case OG_TYPE_UINT32:
if (var->v_uint32 > (uint32)OG_MAX_INT32) {
return OG_FALSE;
}
*precision = (int32)var->v_uint32;
return OG_TRUE;
case OG_TYPE_BIGINT:
if (var->v_bigint < (int64)OG_MIN_INT32 || var->v_bigint > (int64)OG_MAX_INT32) {
return OG_FALSE;
}
*precision = (int32)var->v_bigint;
return OG_TRUE;
case OG_TYPE_NUMBER:
case OG_TYPE_DECIMAL:
case OG_TYPE_NUMBER3:
if (!cm_dec_is_integer(&var->v_dec)) {
return OG_FALSE;
}
if (cm_dec_to_int32(&var->v_dec, &prec, ROUND_TRUNC) != OG_SUCCESS) {
cm_reset_error();
return OG_FALSE;
}
*precision = prec;
return OG_TRUE;
default:
return OG_FALSE;
}
}
static status_t sql_func_add_months_core(int32 temp_add_months, date_detail_t *time_desc)
{
int32 year = time_desc->year;
int32 month = time_desc->mon;
bool32 last_mon_day;
int32 add_months = temp_add_months;
last_mon_day = sql_is_last_month_day(time_desc);
year += (add_months / 12);
add_months %= 12;
month += add_months;
if (month > 12) {
year++;
month -= 12;
} else if (month <= 0) {
year--;
month += 12;
}
if (!CM_IS_VALID_YEAR(year)) {
OG_THROW_ERROR(ERR_TYPE_OVERFLOW, "DATETIME");
return OG_ERROR;
}
time_desc->year = (uint16)year;
time_desc->mon = (uint8)month;
if (last_mon_day || (time_desc->day > CM_MONTH_DAYS(time_desc->year, time_desc->mon))) {
time_desc->day = (uint8)CM_MONTH_DAYS(time_desc->year, time_desc->mon);
}
return OG_SUCCESS;
}
status_t sql_func_add_months(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t date_var;
variant_t month_var;
date_detail_t time_desc;
CM_POINTER2(func, res);
expr_tree_t *arg1 = func->argument;
CM_POINTER(arg1);
SQL_EXEC_FUNC_ARG_EX(arg1, &date_var, res);
if (var_as_date(SESSION_NLS(stmt), &date_var) != OG_SUCCESS) {
cm_set_error_loc(arg1->loc);
return OG_ERROR;
}
expr_tree_t *arg2 = arg1->next;
CM_POINTER(arg2);
SQL_EXEC_FUNC_ARG_EX(arg2, &month_var, res);
if (var_as_floor_integer(&month_var) != OG_SUCCESS) {
cm_set_error_loc(arg2->loc);
return OG_ERROR;
}
cm_decode_date(date_var.v_date, &time_desc);
if (sql_func_add_months_core(month_var.v_int, &time_desc) != OG_SUCCESS) {
cm_set_error_loc(func->loc);
return OG_ERROR;
}
res->is_null = OG_FALSE;
res->type = date_var.type;
res->v_date = cm_encode_date(&time_desc);
return OG_SUCCESS;
}
status_t sql_verify_add_months(sql_verifier_t *verif, expr_node_t *func)
{
CM_POINTER2(verif, func);
if (sql_verify_func_node(verif, func, 2, 2, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *date_arg = func->argument;
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
expr_tree_t *mon_arg = date_arg->next;
if (!sql_match_numeric_type(TREE_DATATYPE(mon_arg))) {
OG_SRC_ERROR_REQUIRE_NUMERIC(mon_arg->loc, TREE_DATATYPE(mon_arg));
return OG_ERROR;
}
sql_infer_func_optmz_mode(verif, func);
if (OG_IS_STRING_TYPE(TREE_DATATYPE(date_arg)) && OG_IS_WEAK_NUMERIC_TYPE(TREE_DATATYPE(mon_arg)) &&
NODE_IS_OPTMZ_CONST(func)) {
sql_add_first_exec_node(verif, func);
}
func->datatype = OG_TYPE_DATE;
func->size = OG_DATE_SIZE;
return OG_SUCCESS;
}
status_t sql_verify_current_timestamp(sql_verifier_t *verif, expr_node_t *func)
{
int32 precision = OG_DEFAULT_DATETIME_PRECISION;
source_location_t loc;
CM_POINTER2(verif, func);
OG_RETURN_IFERR(sql_verify_func_node(verif, func, 0, 1, OG_INVALID_ID32));
expr_tree_t *arg = func->argument;
if (arg != NULL) {
if (OG_IS_UNKNOWN_TYPE(TREE_DATATYPE(arg)) && TREE_IS_BINDING_PARAM(arg)) {
precision = OG_DEFAULT_DATETIME_PRECISION;
} else if (!TREE_IS_CONST(arg) || !sql_get_datetime_prec_from_var(&arg->root->value, &precision)) {
OG_SRC_THROW_ERROR(arg->loc, ERR_INVALID_FUNC_PARAMS, "integer argument required");
return OG_ERROR;
}
}
loc = (arg != NULL) ? arg->loc : func->loc;
if (precision < OG_MIN_DATETIME_PRECISION || precision > OG_MAX_DATETIME_PRECISION) {
OG_SRC_THROW_ERROR_EX(loc, ERR_SQL_SYNTAX_ERROR, "fraction must between %d and %d. ",
OG_MIN_DATETIME_PRECISION, OG_MAX_DATETIME_PRECISION);
return OG_ERROR;
}
if (verif->stmt->session->call_version >= CS_VERSION_8) {
func->datatype = OG_TYPE_TIMESTAMP_TZ;
func->size = OG_TIMESTAMP_TZ_SIZE;
} else {
func->datatype = OG_TYPE_TIMESTAMP_TZ_FAKE;
func->size = OG_TIMESTAMP_SIZE;
}
verif->stmt->context->unsinkable = OG_TRUE;
func->precision = (uint8)precision;
sql_add_first_exec_node(verif, func);
return OG_SUCCESS;
}
status_t sql_func_current_timestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
int32 prec = OG_MAX_DATETIME_PRECISION;
variant_t var;
CM_POINTER3(stmt, func, res);
expr_tree_t *arg = func->argument;
if (arg != NULL) {
SQL_EXEC_FUNC_ARG_EX(arg, &var, res);
if (!sql_get_datetime_prec_from_var(&var, &prec)) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAMS, "integer argument required");
return OG_ERROR;
}
}
if (prec < OG_MIN_DATETIME_PRECISION || prec > OG_MAX_DATETIME_PRECISION) {
OG_SRC_THROW_ERROR_EX(arg->loc, ERR_SQL_SYNTAX_ERROR, "fraction must between %d and %d. ",
OG_MIN_DATETIME_PRECISION, OG_MAX_DATETIME_PRECISION);
return OG_ERROR;
}
SQL_GET_STMT_SYSTIMESTAMP(stmt, res);
if (stmt->session->call_version >= CS_VERSION_8) {
res->type = OG_TYPE_TIMESTAMP_TZ;
res->v_tstamp_tz.tstamp =
cm_adjust_date_between_two_tzs(stmt->v_systimestamp, g_timer()->tz, sql_get_session_timezone(stmt));
res->v_tstamp_tz.tz_offset = sql_get_session_timezone(stmt);
} else {
res->type = OG_TYPE_TIMESTAMP_TZ_FAKE;
}
res->is_null = OG_FALSE;
return cm_adjust_timestamp_tz(&res->v_tstamp_tz, prec);
}
static status_t sql_func_extract_date(interval_unit_t unit, variant_t date_var, variant_t *res)
{
date_t v_date = date_var.v_date;
dec8_t dec;
date_detail_t dt;
cm_decode_date(v_date, &dt);
res->type = OG_TYPE_INTEGER;
res->is_null = OG_FALSE;
switch (unit) {
case IU_YEAR:
res->v_int = dt.year;
break;
case IU_MONTH:
res->v_int = dt.mon;
break;
case IU_DAY:
res->v_int = dt.day;
break;
case IU_HOUR:
res->v_int = dt.hour;
break;
case IU_MINUTE:
res->v_int = dt.min;
break;
case IU_SECOND:
res->v_bigint =
(int64)dt.sec * MICROSECS_PER_SECOND + (int64)dt.millisec * MILLISECS_PER_SECOND + dt.microsec;
cm_int64_to_dec(res->v_bigint, &dec);
(void)cm_dec_div_int64(&dec, MICROSECS_PER_SECOND, &res->v_dec);
res->type = OG_TYPE_NUMBER;
break;
case IU_TZ_HOUR:
res->v_int = TIMEZONE_GET_HOUR(date_var.v_tstamp_tz.tz_offset);
break;
case IU_TZ_MINUTE:
res->v_int = TIMEZONE_GET_SIGN_MINUTE(date_var.v_tstamp_tz.tz_offset);
break;
default:
OG_THROW_ERROR(ERR_INVALID_FUNC_PARAMS, "invalid extract field for extract source");
return OG_ERROR;
}
return OG_SUCCESS;
}
static status_t sql_func_extract_interval(interval_unit_t unit, variant_t *itvl_var, variant_t *res)
{
dec8_t dec;
interval_detail_t dt;
res->type = OG_TYPE_INTEGER;
res->is_null = OG_FALSE;
if (itvl_var->type == OG_TYPE_INTERVAL_YM) {
cm_decode_yminterval(itvl_var->v_itvl_ym, &dt);
if (unit == IU_YEAR) {
res->v_int = dt.year;
} else if (unit == IU_MONTH) {
res->v_int = dt.mon;
} else {
OG_THROW_ERROR(ERR_INVALID_FUNC_PARAMS, "invalid extract field for extract source");
return OG_ERROR;
}
} else {
cm_decode_dsinterval(itvl_var->v_itvl_ds, &dt);
switch (unit) {
case IU_DAY:
res->v_int = dt.day;
break;
case IU_HOUR:
res->v_int = dt.hour;
break;
case IU_MINUTE:
res->v_int = dt.min;
break;
case IU_SECOND:
res->v_bigint = (int64)dt.sec * MICROSECS_PER_SECOND + dt.fsec;
cm_int64_to_dec(res->v_bigint, &dec);
OG_RETURN_IFERR(cm_dec_div_int64(&dec, MICROSECS_PER_SECOND, &res->v_dec));
if (dt.is_neg) {
cm_dec_negate(&res->v_dec);
}
res->type = OG_TYPE_NUMBER;
return OG_SUCCESS;
default:
OG_THROW_ERROR(ERR_INVALID_FUNC_PARAMS, "invalid extract field for extract source");
return OG_ERROR;
}
}
if (dt.is_neg) {
res->v_int = -res->v_int;
}
return OG_SUCCESS;
}
status_t sql_func_extract(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
expr_tree_t *arg_unit = NULL;
expr_tree_t *arg_date = NULL;
variant_t unit_var;
variant_t date_var;
CM_POINTER2(func, res);
arg_unit = func->argument;
CM_POINTER(arg_unit);
SQL_EXEC_FUNC_ARG_EX(arg_unit, &unit_var, res);
arg_date = arg_unit->next;
CM_POINTER(arg_date);
SQL_EXEC_FUNC_ARG_EX(arg_date, &date_var, res);
if (sql_match_interval_type(date_var.type)) {
return sql_func_extract_interval(unit_var.v_itvl_unit_id, &date_var, res);
} else if (var_as_timestamp_flex(&date_var) == OG_SUCCESS) {
return sql_func_extract_date(unit_var.v_itvl_unit_id, date_var, res);
} else {
cm_set_error_loc(arg_date->loc);
return OG_ERROR;
}
}
static status_t sql_verify_datetime_unit(sql_verifier_t *verif, expr_node_t *unit_node)
{
word_t word;
word.text = unit_node->word.column.name;
if (!lex_match_datetime_unit(&word)) {
OG_SRC_THROW_ERROR(word.text.loc, ERR_INVALID_FUNC_PARAMS, "datetime unit expected");
return OG_ERROR;
}
unit_node->type = EXPR_NODE_CONST;
unit_node->datatype = OG_TYPE_ITVL_UNIT;
unit_node->value.type = OG_TYPE_ITVL_UNIT;
unit_node->value.v_itvl_unit_id = word.id;
SQL_SET_OPTMZ_MODE(unit_node, OPTIMIZE_AS_CONST);
return OG_SUCCESS;
}
status_t sql_verify_extract(sql_verifier_t *verif, expr_node_t *func)
{
expr_tree_t *unit_arg = NULL;
expr_tree_t *date_arg = NULL;
CM_POINTER2(verif, func);
unit_arg = func->argument;
if (unit_arg == NULL || unit_arg->next == NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 2, 2);
return OG_ERROR;
}
OG_RETURN_IFERR(sql_verify_datetime_unit(verif, unit_arg->root));
date_arg = unit_arg->next;
if (date_arg->next != NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 2, 2);
return OG_ERROR;
}
OG_RETURN_IFERR(sql_verify_expr_node(verif, date_arg->root));
if (!(sql_match_interval_type(TREE_DATATYPE(date_arg)) || sql_match_datetime_type(TREE_DATATYPE(date_arg)))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
func->datatype = (EXPR_VALUE(int32, unit_arg) == IU_SECOND) ? OG_TYPE_NUMBER : OG_TYPE_INTEGER;
func->size = (func->datatype == OG_TYPE_INTEGER) ? sizeof(int32) : sizeof(dec8_t);
return OG_SUCCESS;
}
status_t sql_func_from_tz(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t date_var;
variant_t tz_var;
timezone_info_t tz_info;
CM_POINTER3(stmt, func, res);
expr_tree_t *arg_date = func->argument;
CM_POINTER(arg_date);
sql_exec_expr(stmt, arg_date, &date_var);
SQL_CHECK_COLUMN_VAR(&date_var, res);
if (date_var.is_null) {
SQL_SET_NULL_VAR(res);
return OG_SUCCESS;
}
sql_keep_stack_variant(stmt, &date_var);
if (date_var.type != OG_TYPE_TIMESTAMP) {
OG_SET_ERROR_MISMATCH(OG_TYPE_TIMESTAMP, date_var.type);
return OG_ERROR;
}
SQL_EXEC_FUNC_ARG_EX(arg_date->next, &tz_var, res);
sql_keep_stack_variant(stmt, &tz_var);
if (!OG_IS_STRING_TYPE(tz_var.type)) {
OG_SET_ERROR_MISMATCH(OG_TYPE_CHAR, tz_var.type);
return OG_ERROR;
}
if (cm_text2tzoffset(&tz_var.v_text, &tz_info) != OG_SUCCESS) {
return OG_ERROR;
}
res->is_null = OG_FALSE;
res->type = OG_TYPE_TIMESTAMP_TZ;
res->v_tstamp_tz.tstamp = date_var.v_tstamp;
res->v_tstamp_tz.tz_offset = tz_info;
return OG_SUCCESS;
}
status_t sql_verify_from_tz(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
expr_tree_t *arg = func->argument;
OG_RETURN_IFERR(sql_verify_func_node(verf, func, 2, 2, OG_INVALID_ID32));
if (arg->root->datatype != OG_TYPE_TIMESTAMP && arg->root->datatype != OG_TYPE_UNKNOWN) {
OG_SET_ERROR_MISMATCH(OG_TYPE_TIMESTAMP, arg->root->datatype);
return OG_ERROR;
}
arg = arg->next;
if (!OG_IS_STRING_TYPE(arg->root->datatype) && arg->root->datatype != OG_TYPE_UNKNOWN) {
OG_SET_ERROR_MISMATCH(OG_TYPE_CHAR, arg->root->datatype);
return OG_ERROR;
}
func->precision = OG_DEFAULT_DATETIME_PRECISION;
func->datatype = OG_TYPE_TIMESTAMP_TZ;
func->size = sizeof(timestamp_tz_t);
return OG_SUCCESS;
}
status_t sql_verify_from_unixtime(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
if (sql_verify_func_node(verf, func, 1, 2, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
const expr_tree_t *arg = func->argument;
if (!sql_match_numeric_type(TREE_DATATYPE(arg))) {
OG_SRC_ERROR_REQUIRE_NUMERIC(arg->loc, TREE_DATATYPE(arg));
return OG_ERROR;
}
arg = arg->next;
if (arg != NULL && !sql_match_string_type(TREE_DATATYPE(arg))) {
OG_SRC_ERROR_REQUIRE_STRING(arg->loc, TREE_DATATYPE(arg));
return OG_ERROR;
}
if (arg != NULL) {
func->datatype = OG_TYPE_VARCHAR;
} else {
func->datatype = OG_TYPE_TIMESTAMP;
}
func->precision = OG_MAX_DATETIME_PRECISION;
func->size = cm_get_datatype_strlen(func->argument->root->datatype, func->argument->root->size);
return OG_SUCCESS;
}
status_t sql_func_from_unixtime(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t unix_ts_var;
variant_t fmt_var;
timestamp_t tmp_tstamp;
expr_tree_t *arg1 = func->argument;
SQL_EXEC_FUNC_ARG_EX(arg1, &unix_ts_var, res);
OG_RETURN_IFERR(var_as_decimal(&unix_ts_var));
if (OG_SUCCESS != var_to_unix_timestamp(&unix_ts_var.v_dec, &res->v_tstamp, SESSION_TIME_ZONE(stmt->session))) {
return OG_ERROR;
}
expr_tree_t *arg2 = arg1->next;
if (arg2 == NULL) {
res->type = OG_TYPE_TIMESTAMP;
} else {
tmp_tstamp = res->v_tstamp;
SQL_EXEC_FUNC_ARG_EX(arg2, &fmt_var, res);
OG_RETURN_IFERR(sql_push(stmt, OG_MAX_NUMBER_LEN, (void **)&res->v_text.str));
res->v_text.len = 0;
OG_RETURN_IFERR(cm_timestamp2text(tmp_tstamp, &fmt_var.v_text, &res->v_text, OG_MAX_NUMBER_LEN));
res->type = OG_TYPE_VARCHAR;
}
res->is_null = OG_FALSE;
return OG_SUCCESS;
}
status_t sql_verify_utcdate(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
OG_RETURN_IFERR(sql_verify_func_node(verf, func, 0, 0, OG_INVALID_ID32));
func->datatype = OG_TYPE_TIMESTAMP_TZ;
func->size = sizeof(timestamp_tz_t);
func->precision = OG_DEFAULT_DATETIME_PRECISION;
sql_add_first_exec_node(verf, func);
return OG_SUCCESS;
}
status_t sql_func_utcdate(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
int32 prec = OG_DEFAULT_DATETIME_PRECISION;
date_t dt_utc_now = CM_UNIX_EPOCH;
timeval_t tv;
(void)cm_gettimeofday(&tv);
dt_utc_now += ((int64)tv.tv_sec * MICROSECS_PER_SECOND + tv.tv_usec);
res->v_tstamp_tz.tstamp = (timestamp_t)dt_utc_now;
res->v_tstamp_tz.tz_offset = 0;
res->is_null = OG_FALSE;
res->type = OG_TYPE_TIMESTAMP_TZ;
return cm_adjust_timestamp_tz(&res->v_tstamp_tz, prec);
}
status_t sql_func_last_day(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t date_var;
date_detail_t time_desc;
CM_POINTER2(func, res);
expr_tree_t *arg = func->argument;
CM_POINTER(arg);
SQL_EXEC_FUNC_ARG_EX(arg, &date_var, res);
if (var_as_date(SESSION_NLS(stmt), &date_var) != OG_SUCCESS) {
cm_set_error_loc(arg->loc);
return OG_ERROR;
}
cm_decode_date(date_var.v_date, &time_desc);
time_desc.day = (uint8)CM_MONTH_DAYS(time_desc.year, time_desc.mon);
res->is_null = OG_FALSE;
res->type = date_var.type;
res->v_date = cm_encode_date(&time_desc);
return OG_SUCCESS;
}
status_t sql_verify_last_day(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
if (sql_verify_func_node(verf, func, 1, 1, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *date_arg = func->argument;
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
func->datatype = OG_TYPE_DATE;
func->size = OG_DATE_SIZE;
return OG_SUCCESS;
}
status_t sql_verify_localtimestamp(sql_verifier_t *verf, expr_node_t *func)
{
int32 precision = OG_DEFAULT_DATETIME_PRECISION;
source_location_t loc;
CM_POINTER2(verf, func);
OG_RETURN_IFERR(sql_verify_func_node(verf, func, 0, 1, OG_INVALID_ID32));
expr_tree_t *arg = func->argument;
if (arg != NULL) {
if (OG_IS_UNKNOWN_TYPE(TREE_DATATYPE(arg)) && TREE_IS_BINDING_PARAM(arg)) {
precision = OG_DEFAULT_DATETIME_PRECISION;
} else if (!TREE_IS_CONST(arg) || !sql_get_datetime_prec_from_var(&arg->root->value, &precision)) {
OG_SRC_THROW_ERROR(arg->loc, ERR_INVALID_FUNC_PARAMS, "integer argument required");
return OG_ERROR;
}
}
loc = (arg != NULL) ? arg->loc : func->loc;
if (precision < OG_MIN_DATETIME_PRECISION || precision > OG_MAX_DATETIME_PRECISION) {
OG_SRC_THROW_ERROR_EX(loc, ERR_SQL_SYNTAX_ERROR, "integer argument must between %d and %d. ",
OG_MIN_DATETIME_PRECISION, OG_MAX_DATETIME_PRECISION);
return OG_ERROR;
}
verf->stmt->context->unsinkable = OG_TRUE;
func->datatype = OG_TYPE_TIMESTAMP;
func->size = OG_TIMESTAMP_SIZE;
func->precision = (uint8)precision;
sql_add_first_exec_node(verf, func);
return OG_SUCCESS;
}
status_t sql_func_localtimestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
int32 prec = OG_MAX_DATETIME_PRECISION;
variant_t var;
CM_POINTER3(stmt, func, res);
expr_tree_t *arg = func->argument;
if (arg != NULL) {
SQL_EXEC_FUNC_ARG_EX(arg, &var, res);
if (!sql_get_datetime_prec_from_var(&var, &prec)) {
OG_SRC_THROW_ERROR(arg->loc, ERR_INVALID_FUNC_PARAMS, "integer argument required");
return OG_ERROR;
}
}
if (prec < OG_MIN_DATETIME_PRECISION || prec > OG_MAX_DATETIME_PRECISION) {
OG_SRC_THROW_ERROR_EX(arg->loc, ERR_SQL_SYNTAX_ERROR, "integer argument must between %d and %d. ",
OG_MIN_DATETIME_PRECISION, OG_MAX_DATETIME_PRECISION);
return OG_ERROR;
}
SQL_GET_STMT_SYSTIMESTAMP(stmt, res);
res->type = OG_TYPE_TIMESTAMP;
res->is_null = OG_FALSE;
res->v_tstamp =
cm_adjust_date_between_two_tzs(stmt->v_systimestamp, g_timer()->tz, sql_get_session_timezone(stmt));
return cm_adjust_timestamp(&res->v_tstamp, prec);
}
static status_t sql_func_months_between_core(date_t date1, date_t date2, variant_t *res)
{
date_detail_t date_desc1;
date_detail_t date_desc2;
int32 year;
int32 mon;
int32 day;
int32 diff_mons;
int64 diff_secs;
bool32 last_mon_day;
dec8_t dec1;
dec8_t dec2;
cm_decode_date(date1, &date_desc1);
cm_decode_date(date2, &date_desc2);
year = (int32)((int32)date_desc1.year - (int32)date_desc2.year);
mon = (int32)((int32)date_desc1.mon - (int32)date_desc2.mon);
day = (int32)((int32)date_desc1.day - (int32)date_desc2.day);
diff_mons = OG_MONTH_PER_YEAR * year + mon;
last_mon_day = (sql_is_last_month_day(&date_desc1) && sql_is_last_month_day(&date_desc2));
if ((day == 0) || last_mon_day) {
cm_int32_to_dec(diff_mons, &res->v_dec);
} else {
Oracle calculates the fractional portion of the result based on a 31-day month
*/
diff_secs = (int64)day * (int64)OG_SEC_PER_DAY;
diff_secs += ((int32)date_desc1.hour - (int32)date_desc2.hour) * OG_SEC_PER_HOUR;
diff_secs += ((int32)date_desc1.min - (int32)date_desc2.min) * OG_SEC_PER_MIN;
diff_secs += ((int32)date_desc1.sec - (int32)date_desc2.sec);
cm_int64_to_dec(diff_secs, &dec1);
OG_RETURN_IFERR(cm_dec_div_int64(&dec1, (int64)OG_SEC_PER_DAY * OG_DAY_PER_MONTH, &dec2));
OG_RETURN_IFERR(cm_dec_add_int64(&dec2, (int64)diff_mons, &res->v_dec));
}
res->is_null = OG_FALSE;
res->type = OG_TYPE_NUMBER;
return OG_SUCCESS;
}
status_t sql_func_months_between(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t date_var1;
variant_t date_var2;
CM_POINTER2(func, res);
expr_tree_t *arg_date1 = func->argument;
CM_POINTER(arg_date1);
SQL_EXEC_FUNC_ARG_EX(arg_date1, &date_var1, res);
if (var_as_timestamp_flex(&date_var1) != OG_SUCCESS) {
cm_set_error_loc(arg_date1->loc);
return OG_ERROR;
}
expr_tree_t *arg_date2 = arg_date1->next;
CM_POINTER(arg_date2);
SQL_EXEC_FUNC_ARG_EX(arg_date2, &date_var2, res);
if (var_as_timestamp_flex(&date_var2) != OG_SUCCESS) {
cm_set_error_loc(arg_date2->loc);
return OG_ERROR;
}
return sql_func_months_between_core(date_var1.v_date, date_var2.v_date, res);
}
status_t sql_verify_months_between(sql_verifier_t *verifier, expr_node_t *func)
{
* MONTHS_BETWEEN(date1, date2)
* \brief Returns number of months between date1 and date2 (date1-date2).
* \param date1: date or datetime expression
* \param date2: date or datetime expression
*/
CM_POINTER2(verifier, func);
OG_RETURN_IFERR(sql_verify_func_node(verifier, func, 2, 2, OG_INVALID_ID32));
expr_tree_t *date_arg1 = func->argument;
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg1))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg1->loc, TREE_DATATYPE(date_arg1));
return OG_ERROR;
}
expr_tree_t *date_arg2 = date_arg1->next;
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg2))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg2->loc, TREE_DATATYPE(date_arg2));
return OG_ERROR;
}
func->datatype = OG_TYPE_NUMBER;
func->size = (uint16)MAX_DEC_BYTE_SZ;
return OG_SUCCESS;
}
status_t sql_func_next_day(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t date_var;
variant_t week_var;
date_detail_t time_desc;
date_detail_ex_t detail_ex;
expr_tree_t *arg1 = func->argument;
CM_POINTER2(func, res);
CM_POINTER(arg1);
SQL_EXEC_FUNC_ARG_EX(arg1, &date_var, res);
if (var_as_date(SESSION_NLS(stmt), &date_var) != OG_SUCCESS) {
cm_set_error_loc(arg1->loc);
return OG_ERROR;
}
cm_decode_date(date_var.v_date, &time_desc);
cm_get_detail_ex(&time_desc, &detail_ex);
uint8 start_week_day = detail_ex.day_of_week;
expr_tree_t *arg2 = arg1->next;
CM_POINTER(arg2);
SQL_EXEC_FUNC_ARG_EX(arg2, &week_var, res);
uint8 end_week_day;
if (sql_match_string_type((og_type_t)week_var.type)) {
cm_trim_text(&week_var.v_text);
if (!cm_str2week(&week_var.v_text, &end_week_day)) {
OG_THROW_ERROR(ERR_INVALID_PARAMETER, "of second column");
return OG_ERROR;
}
} else {
var_as_floor_integer(&week_var);
if (week_var.v_int < 1 || week_var.v_int > (int32)DAYS_PER_WEEK) {
OG_THROW_ERROR(ERR_INVALID_PARAMETER, "of second column");
return OG_ERROR;
} else {
end_week_day = (uint8)(week_var.v_int - 1);
}
}
if (end_week_day > start_week_day) {
OG_RETURN_IFERR(cm_date_add_days(date_var.v_date, (double)(end_week_day - start_week_day), &res->v_date));
} else {
OG_RETURN_IFERR(cm_date_add_days(date_var.v_date, (double)(DAYS_PER_WEEK + end_week_day - start_week_day),
&res->v_date));
}
res->is_null = OG_FALSE;
res->type = date_var.type;
return OG_SUCCESS;
}
status_t sql_verify_next_day(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
if (sql_verify_func_node(verf, func, 2, 2, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *date_arg = func->argument;
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
expr_tree_t *week_arg = date_arg->next;
if (week_arg != NULL) {
if (!sql_match_num_and_str_type(TREE_DATATYPE(week_arg))) {
OG_SRC_ERROR_REQUIRE_NUM_OR_STR(week_arg->loc, TREE_DATATYPE(week_arg));
return OG_ERROR;
}
}
func->datatype = OG_TYPE_DATE;
func->size = OG_DATE_SIZE;
return OG_SUCCESS;
}
status_t sql_func_sys_timestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *result)
{
int32 prec = OG_MAX_DATETIME_PRECISION;
CM_POINTER3(stmt, func, result);
SQL_GET_STMT_SYSTIMESTAMP(stmt, result);
if (stmt->session->call_version >= CS_VERSION_8) {
result->type = OG_TYPE_TIMESTAMP_TZ;
result->v_tstamp_tz.tz_offset = g_timer()->tz;
} else {
result->type = OG_TYPE_TIMESTAMP_TZ_FAKE;
}
result->is_null = OG_FALSE;
if (func->argument != NULL) {
if (!sql_get_datetime_prec_from_var(&func->argument->root->value, &prec)) {
OG_SRC_THROW_ERROR(func->argument->loc, ERR_INVALID_FUNC_PARAMS, "integer argument required");
return OG_ERROR;
}
}
if (prec < OG_MIN_DATETIME_PRECISION || prec > OG_MAX_DATETIME_PRECISION) {
OG_SRC_THROW_ERROR_EX(func->argument->loc, ERR_SQL_SYNTAX_ERROR, "fraction must between %d and %d. ",
OG_MIN_DATETIME_PRECISION, OG_MAX_DATETIME_PRECISION);
return OG_ERROR;
}
return cm_adjust_timestamp_tz(&result->v_tstamp_tz, prec);
}
static status_t sql_func_timestampadd_core(interval_unit_t unit, int64 itvl, date_t *date)
{
date_t new_dt = *date;
date_detail_t time_desc;
uint8 day;
switch (unit) {
case IU_YEAR: {
cm_decode_date(*date, &time_desc);
day = time_desc.day;
OG_RETURN_IFERR(sql_func_add_months_core((int32)itvl * 12, &time_desc));
if (day < time_desc.day) {
time_desc.day = day;
}
(*date) = cm_encode_date(&time_desc);
return OG_SUCCESS;
}
case IU_QUARTER: {
cm_decode_date(*date, &time_desc);
day = time_desc.day;
OG_RETURN_IFERR(sql_func_add_months_core((int32)itvl * 3, &time_desc));
if (day < time_desc.day) {
time_desc.day = day;
}
(*date) = cm_encode_date(&time_desc);
return OG_SUCCESS;
}
case IU_MONTH: {
cm_decode_date(*date, &time_desc);
day = time_desc.day;
OG_RETURN_IFERR(sql_func_add_months_core((int32)itvl, &time_desc));
if (day < time_desc.day) {
time_desc.day = day;
}
(*date) = cm_encode_date(&time_desc);
return OG_SUCCESS;
}
case IU_WEEK:
new_dt += (date_t)(itvl * UNITS_PER_DAY * 7);
break;
case IU_DAY:
new_dt += (date_t)(itvl * UNITS_PER_DAY);
break;
case IU_HOUR:
new_dt += (date_t)(itvl * SECONDS_PER_HOUR * MICROSECS_PER_SECOND);
break;
case IU_MINUTE:
new_dt += (date_t)(itvl * SECONDS_PER_MIN * MICROSECS_PER_SECOND);
break;
case IU_SECOND:
new_dt += (date_t)(itvl * MICROSECS_PER_SECOND);
break;
case IU_MICROSECOND:
new_dt += itvl;
break;
default:
OG_THROW_ERROR(ERR_INVALID_FUNC_PARAMS, "invalid UNIT");
return OG_ERROR;
}
if (CM_IS_DATETIME_ADDTION_OVERFLOW(*date, itvl, new_dt)) {
OG_SET_ERROR_DATETIME_OVERFLOW();
return OG_ERROR;
}
(*date) = new_dt;
return OG_SUCCESS;
}
status_t sql_func_sys_extract_utc(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
CM_POINTER3(stmt, func, res);
variant_t time_var;
expr_tree_t *arg_time = func->argument;
timezone_info_t tz_offset = cm_get_session_time_zone(SESSION_NLS(stmt));
SQL_EXEC_FUNC_ARG_EX(arg_time, &time_var, res);
if (!sql_match_timestamp(time_var.type) || time_var.type == OG_TYPE_UNKNOWN) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAMS, "timestamp argument required");
return OG_ERROR;
}
if (time_var.type == OG_TYPE_TIMESTAMP_TZ) {
tz_offset = time_var.v_tstamp_tz.tz_offset;
}
if (var_as_timestamp(SESSION_NLS(stmt), &time_var) != OG_SUCCESS) {
cm_set_error_loc(arg_time->loc);
return OG_ERROR;
}
tz_offset = -tz_offset;
if (sql_func_timestampadd_core(IU_MINUTE, (int64)tz_offset, &time_var.v_tstamp) != OG_SUCCESS) {
cm_set_error_loc(func->loc);
return OG_ERROR;
}
res->v_tstamp = time_var.v_tstamp;
res->type = OG_TYPE_TIMESTAMP;
res->is_null = OG_FALSE;
return OG_SUCCESS;
}
status_t sql_verify_sys_extract_utc(sql_verifier_t *verf, expr_node_t *func)
{
if (sql_verify_func_node(verf, func, 1, 1, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *arg = func->argument;
if (!sql_match_timestamp(TREE_DATATYPE(arg))) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAMS, "timestamp argument required");
return OG_ERROR;
}
func->datatype = OG_TYPE_TIMESTAMP;
func->precision = OG_DEFAULT_DATETIME_PRECISION;
func->size = OG_TIMESTAMP_SIZE;
return OG_SUCCESS;
}
status_t sql_verify_to_date(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
if (sql_verify_func_node(verf, func, DATE_FUNC_MIN_ARGS, DATE_FUNC_MAX_ARGS, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
og_type_t arg1_type = sql_get_func_arg1_datatype(func);
if (!sql_match_num_and_str_type(arg1_type)) {
OG_SRC_THROW_ERROR(func->argument->loc, ERR_INVALID_FUNC_PARAMS, "string or number argument expected");
return OG_ERROR;
}
func->datatype = OG_TYPE_DATE;
func->size = OG_DATE_SIZE;
sql_infer_func_optmz_mode(verf, func);
if (func->value.v_func.arg_cnt == 1 && NODE_IS_OPTMZ_CONST(func)) {
sql_add_first_exec_node(verf, func);
}
return OG_SUCCESS;
}
static status_t sql_text2timestamp(sql_stmt_t *stmt, text_t *text, variant_t *result, bool32 is_to_date)
{
result->is_null = OG_FALSE;
result->type = OG_TYPE_DATE;
timezone_info_t tz_offset = 0;
text_t fmt;
sql_session_nlsparam_geter(stmt, NLS_TIMESTAMP_TZ_FORMAT, &fmt);
if (cm_text2date_fixed(text, &fmt, &result->v_date, &tz_offset, is_to_date) == OG_SUCCESS) {
result->v_tstamp_tz.tz_offset = tz_offset;
return OG_SUCCESS;
}
cm_reset_error();
sql_session_nlsparam_geter(stmt, NLS_TIMESTAMP_TZ_FORMAT2, &fmt);
if (cm_text2date_fixed(text, &fmt, &result->v_date, &tz_offset, is_to_date) == OG_SUCCESS) {
result->v_tstamp_tz.tz_offset = tz_offset;
return OG_SUCCESS;
}
return OG_ERROR;
}
typedef struct {
const text_t *text;
const text_t *fmt;
text_t *nls;
expr_tree_t *arg1;
expr_tree_t *arg3;
} sql_date_func_args_t;
static status_t sql_execute_date_parsing(sql_stmt_t *stmt, const sql_date_func_args_t *args,
bool32 is_to_date, variant_t *result)
{
result->is_null = OG_FALSE;
result->type = OG_TYPE_DATE;
timezone_info_t tz_offset = 0;
text_t *nls_text = args->nls;
if (args->arg3 != NULL) {
variant_t nls_var;
SQL_EXEC_FUNC_ARG_EX(args->arg3, &nls_var, result);
if (!OG_IS_STRING_TYPE(nls_var.type)) {
OG_SRC_THROW_ERROR(args->arg3->loc, ERR_INVALID_FUNC_PARAMS,
"string argument expected for NLS_PARAM");
return OG_ERROR;
}
nls_text = &nls_var.v_text;
}
date_parse_params_t params = {
.text = args->text,
.fmt = args->fmt,
.nls = nls_text,
.is_date_fmt = is_to_date
};
if (cm_text2date_fixed_nls(¶ms, &result->v_date, &tz_offset) != OG_SUCCESS) {
cm_set_error_loc(args->arg1->loc);
return OG_ERROR;
}
result->v_tstamp_tz.tz_offset = tz_offset;
return OG_SUCCESS;
}
static status_t sql_func_to_date_core(sql_stmt_t *stmt, expr_node_t *func, variant_t *result, bool32 is_to_date)
{
variant_t var1;
variant_t fmt_var;
CM_POINTER3(stmt, func, result);
expr_tree_t *arg1 = func->argument;
CM_POINTER(arg1);
SQL_EXEC_FUNC_ARG_EX(arg1, &var1, result);
if (is_to_date) {
if (!sql_match_num_and_str_type(var1.type)) {
OG_SRC_THROW_ERROR(arg1->loc, ERR_INVALID_FUNC_PARAMS, "string or number argument expected");
return OG_ERROR;
}
if (!OG_IS_STRING_TYPE(var1.type)) {
if (sql_var_as_string(stmt, &var1) != OG_SUCCESS) {
cm_set_error_loc(arg1->loc);
return OG_ERROR;
}
}
} else {
if (!OG_IS_STRING_TYPE(var1.type)) {
OG_SRC_THROW_ERROR(arg1->loc, ERR_INVALID_FUNC_PARAMS, "string argument expected");
return OG_ERROR;
}
}
expr_tree_t *arg2 = arg1->next;
if (arg2 != NULL) {
sql_keep_stack_variant(stmt, &var1);
SQL_EXEC_FUNC_ARG_EX(arg2, &fmt_var, result);
if (!OG_IS_STRING_TYPE(fmt_var.type)) {
OG_SRC_THROW_ERROR(arg2->loc, ERR_INVALID_FUNC_PARAMS, "string argument expected");
return OG_ERROR;
}
} else if (!is_to_date) {
OG_RETURN_IFSUC(sql_text2timestamp(stmt, &var1.v_text, result, is_to_date));
cm_set_error_loc(arg1->loc);
return OG_ERROR;
} else {
sql_session_nlsparam_geter(stmt, is_to_date ? NLS_DATE_FORMAT : NLS_TIMESTAMP_FORMAT, &fmt_var.v_text);
}
expr_tree_t *arg3 = (arg2 != NULL) ? arg2->next : NULL;
sql_date_func_args_t args = {.text = &var1.v_text, .fmt = &fmt_var.v_text, .nls = NULL, .arg1 = arg1, .arg3 = arg3};
return sql_execute_date_parsing(stmt, &args, is_to_date, result);
}
status_t sql_func_to_date(sql_stmt_t *stmt, expr_node_t *func, variant_t *result)
{
return sql_func_to_date_core(stmt, func, result, OG_TRUE);
}
status_t sql_func_to_timestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *result)
{
if (sql_func_to_date_core(stmt, func, result, OG_FALSE) != OG_SUCCESS) {
return OG_ERROR;
}
if (result->type != OG_TYPE_COLUMN) {
result->type = OG_TYPE_TIMESTAMP;
}
return OG_SUCCESS;
}
status_t sql_verify_to_timestamp(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
if (sql_verify_func_node(verf, func, 1, 2, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
og_type_t arg1_type = sql_get_func_arg1_datatype(func);
if (!sql_match_string_type(arg1_type)) {
OG_SRC_THROW_ERROR(func->argument->loc, ERR_INVALID_FUNC_PARAMS, "string argument expected");
return OG_ERROR;
}
func->datatype = OG_TYPE_TIMESTAMP;
func->size = OG_TIMESTAMP_SIZE;
func->precision = OG_DEFAULT_DATETIME_PRECISION;
sql_infer_func_optmz_mode(verf, func);
if (func->value.v_func.arg_cnt == 1 && NODE_IS_OPTMZ_CONST(func)) {
sql_add_first_exec_node(verf, func);
}
return OG_SUCCESS;
}
status_t sql_func_timestampadd(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t unit_var;
variant_t date_var;
variant_t itvl_var;
CM_POINTER2(func, res);
expr_tree_t *arg_unit = func->argument;
CM_POINTER(arg_unit);
SQL_EXEC_FUNC_ARG_EX(arg_unit, &unit_var, res);
expr_tree_t *arg_itvl = arg_unit->next;
CM_POINTER(arg_itvl);
SQL_EXEC_FUNC_ARG_EX(arg_itvl, &itvl_var, res);
Convert second to microsecond
*/
if (unit_var.v_itvl_unit_id == IU_SECOND) {
OG_RETURN_IFERR(var_as_real(&itvl_var));
itvl_var.v_bigint = (int64)(itvl_var.v_real * MICROSECS_PER_SECOND);
itvl_var.type = OG_TYPE_BIGINT;
unit_var.v_itvl_unit_id = IU_MICROSECOND;
} else if (var_as_bigint(&itvl_var) != OG_SUCCESS) {
cm_set_error_loc(arg_itvl->loc);
return OG_ERROR;
}
if ((OG_IS_YM_UNIT(unit_var.v_itvl_unit_id) || OG_IS_DAY_UNIT(unit_var.v_itvl_unit_id)) &&
(itvl_var.v_bigint > (int64)CM_MAX_DATE ||
(itvl_var.v_bigint < (int64)CM_MIN_DATE && itvl_var.v_bigint != CM_ALL_ZERO_DATE))) {
OG_SET_ERROR_TIMESTAMP_OVERFLOW();
return OG_ERROR;
}
expr_tree_t *arg_date = arg_itvl->next;
CM_POINTER(arg_date);
SQL_EXEC_FUNC_ARG_EX(arg_date, &date_var, res);
if (var_as_timestamp_flex(&date_var) != OG_SUCCESS) {
cm_set_error_loc(arg_date->loc);
return OG_ERROR;
}
if (sql_func_timestampadd_core(unit_var.v_itvl_unit_id, itvl_var.v_bigint, &date_var.v_date) != OG_SUCCESS) {
cm_set_error_loc(func->loc);
return OG_ERROR;
}
res->is_null = OG_FALSE;
res->type = OG_TYPE_TIMESTAMP;
res->v_date = date_var.v_date;
return OG_SUCCESS;
}
static status_t sql_verify_func_arg(sql_verifier_t *verf, expr_node_t *func, expr_tree_t *arg, bool32 is_required)
{
if (arg == NULL) {
if (is_required) {
return OG_ERROR;
}
return OG_SUCCESS;
}
if (arg->root->type == EXPR_NODE_PRIOR) {
OG_SRC_THROW_ERROR_EX(arg->loc, ERR_SQL_SYNTAX_ERROR, "prior must be in the condition of connect by");
return OG_ERROR;
}
return sql_verify_expr_node(verf, arg->root);
}
status_t sql_verify_timestampadd(sql_verifier_t *verif, expr_node_t *func)
{
* TIMESTAMPADD(unit,interval,datetime_expr)
* \brief Adds the integer expression interval to the date or datetime expression datetime_expr
* \param unit: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR
* \param interval: integer
* \param datetime_expr: date or datetime expression
*/
CM_POINTER2(verif, func);
expr_tree_t *unit_arg = func->argument;
if (unit_arg == NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
if (sql_verify_datetime_unit(verif, unit_arg->root) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *itvl_arg = unit_arg->next;
if (itvl_arg == NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
OG_RETURN_IFERR(sql_verify_func_arg(verif, func, itvl_arg, OG_TRUE));
if (!sql_match_numeric_type(TREE_DATATYPE(itvl_arg))) {
OG_SRC_ERROR_REQUIRE_NUMERIC(itvl_arg->loc, TREE_DATATYPE(itvl_arg));
return OG_ERROR;
}
expr_tree_t *date_arg = itvl_arg->next;
if (date_arg == NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
OG_RETURN_IFERR(sql_verify_func_arg(verif, func, date_arg, OG_TRUE));
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
if (date_arg->next != NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
func->datatype = OG_TYPE_TIMESTAMP;
func->precision = OG_DEFAULT_DATETIME_PRECISION;
func->size = 8;
return OG_SUCCESS;
}
static inline int64 sql_get_time_parts_micro(date_t date)
{
int64 micro_secs = date % UNITS_PER_DAY;
if (micro_secs < 0) {
micro_secs += UNITS_PER_DAY;
}
return micro_secs;
}
* \brief Calculate number of months between dates for TIMESTAMPDIFF
* \param date1 oGRAC datetime value
* \param date2 oGRAC datetime value
* \returns number of months between dates date1 and date2 (date1-date2)
*/
static int32 sql_date_diff_months(date_t date1, date_t date2)
{
date_detail_t date_desc1;
date_detail_t date_desc2;
int32 year;
int32 mon;
int32 day;
int32 diff_mons;
int64 micro_secs;
cm_decode_date(date1, &date_desc1);
cm_decode_date(date2, &date_desc2);
year = (int32)((int32)date_desc1.year - (int32)date_desc2.year);
mon = (int32)((int32)date_desc1.mon - (int32)date_desc2.mon);
day = (int32)((int32)date_desc1.day - (int32)date_desc2.day);
micro_secs = sql_get_time_parts_micro(date1) - sql_get_time_parts_micro(date2);
diff_mons = 12 * year + mon;
if (diff_mons > 0) {
diff_mons -= ((day < 0) || (day == 0 && micro_secs < 0)) ? 1 : 0;
} else if (diff_mons < 0) {
diff_mons += ((day > 0) || (day == 0 && micro_secs > 0)) ? 1 : 0;
}
return diff_mons;
}
status_t sql_func_timestampdiff(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
variant_t unit_var;
variant_t date_var1;
variant_t date_var2;
CM_POINTER2(func, res);
expr_tree_t *arg_unit = func->argument;
SQL_EXEC_FUNC_ARG_EX(arg_unit, &unit_var, res);
expr_tree_t *arg_date1 = arg_unit->next;
SQL_EXEC_FUNC_ARG_EX(arg_date1, &date_var1, res);
if (var_as_timestamp_flex(&date_var1) != OG_SUCCESS) {
cm_set_error_loc(arg_date1->loc);
return OG_ERROR;
}
expr_tree_t *arg_date2 = arg_date1->next;
SQL_EXEC_FUNC_ARG_EX(arg_date2, &date_var2, res);
if (var_as_timestamp_flex(&date_var2) != OG_SUCCESS) {
cm_set_error_loc(arg_date2->loc);
return OG_ERROR;
}
switch (unit_var.v_itvl_unit_id) {
case IU_YEAR:
res->v_bigint = (int64)sql_date_diff_months(date_var2.v_date, date_var1.v_date);
res->v_bigint /= 12;
break;
case IU_QUARTER:
res->v_bigint = (int64)sql_date_diff_months(date_var2.v_date, date_var1.v_date);
res->v_bigint /= 3;
break;
case IU_MONTH:
res->v_bigint = (int64)sql_date_diff_months(date_var2.v_date, date_var1.v_date);
break;
case IU_WEEK:
res->v_bigint = (int64)cm_date_diff_days(date_var2.v_date, date_var1.v_date);
res->v_bigint /= 7;
break;
case IU_DAY:
res->v_bigint = (int64)cm_date_diff_days(date_var2.v_date, date_var1.v_date);
break;
case IU_HOUR:
res->v_bigint = (date_var2.v_date - date_var1.v_date) / SECONDS_PER_HOUR / MICROSECS_PER_SECOND;
break;
case IU_MINUTE:
res->v_bigint = (date_var2.v_date - date_var1.v_date) / SECONDS_PER_MIN / MICROSECS_PER_SECOND;
break;
case IU_SECOND:
res->v_bigint = (date_var2.v_date - date_var1.v_date) / MICROSECS_PER_SECOND;
break;
case IU_MICROSECOND:
res->v_bigint = (date_var2.v_date - date_var1.v_date);
break;
default:
OG_THROW_ERROR(ERR_INVALID_FUNC_PARAMS, "invalid UNIT");
return OG_ERROR;
}
res->is_null = OG_FALSE;
res->type = OG_TYPE_BIGINT;
return OG_SUCCESS;
}
status_t sql_verify_timestampdiff(sql_verifier_t *verif, expr_node_t *func)
{
uint32 arg_count;
CM_POINTER2(verif, func);
expr_tree_t *unit_arg = func->argument;
if (unit_arg == NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
if (sql_verify_datetime_unit(verif, unit_arg->root) != OG_SUCCESS) {
return OG_ERROR;
}
arg_count = 0;
expr_tree_t *date_arg = unit_arg->next;
while (date_arg != NULL) {
arg_count++;
if (arg_count > 2) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
if (date_arg->root->type == EXPR_NODE_PRIOR) {
OG_SRC_THROW_ERROR_EX(date_arg->loc, ERR_SQL_SYNTAX_ERROR, "prior must be in the condition of connect by");
return OG_ERROR;
}
if (sql_verify_expr_node(verif, date_arg->root) != OG_SUCCESS) {
return OG_ERROR;
}
if (!sql_match_datetime_type(TREE_DATATYPE(date_arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
date_arg = date_arg->next;
}
if (arg_count < 2) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name), 3, 3);
return OG_ERROR;
}
func->datatype = OG_TYPE_BIGINT;
func->size = sizeof(int64);
return OG_SUCCESS;
}
status_t sql_verify_unix_timestamp(sql_verifier_t *verf, expr_node_t *func)
{
if (sql_verify_func_node(verf, func, 0, 1, OG_INVALID_ID32) != OG_SUCCESS) {
return OG_ERROR;
}
expr_tree_t *arg = func->argument;
if (arg != NULL && !sql_match_datetime_type(TREE_DATATYPE(arg))) {
OG_SRC_ERROR_REQUIRE_DATETIME(arg->loc, TREE_DATATYPE(arg));
return OG_ERROR;
}
func->datatype = OG_TYPE_BIGINT;
func->size = OG_BIGINT_SIZE;
return OG_SUCCESS;
}
status_t sql_func_unix_timestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
res->type = OG_TYPE_BIGINT;
res->is_null = OG_FALSE;
timestamp_t ts_val;
do {
variant_t date_var;
expr_tree_t *arg = func->argument;
text_t fmt_text;
if (arg == NULL) {
SQL_GET_STMT_SYSTIMESTAMP(stmt, res);
res->v_bigint = cm_get_unix_timestamp(res->v_tstamp, CM_HOST_TIMEZONE);
break;
}
SQL_EXEC_FUNC_ARG_EX(arg, &date_var, res);
if (OG_IS_DATETIME_TYPE(date_var.type)) {
ts_val = date_var.v_tstamp;
res->v_bigint = cm_get_unix_timestamp(ts_val, SESSION_TIME_ZONE(stmt->session));
break;
}
if (!OG_IS_STRING_TYPE(date_var.type)) {
OG_SRC_ERROR_REQUIRE_DATETIME(arg->loc, TREE_DATATYPE(arg));
return OG_ERROR;
}
sql_session_nlsparam_geter(stmt, NLS_TIMESTAMP_FORMAT, &fmt_text);
if (cm_text2date(&date_var.v_text, &fmt_text, (date_t *)&ts_val) != OG_SUCCESS) {
cm_set_error_loc(arg->loc);
return OG_ERROR;
}
res->v_bigint = cm_get_unix_timestamp(ts_val, SESSION_TIME_ZONE(stmt->session));
} while (0);
if (res->v_bigint < CM_MIN_UTC || res->v_bigint > CM_MAX_UTC) {
res->v_bigint = 0;
}
return OG_SUCCESS;
}
status_t sql_verify_utctimestamp(sql_verifier_t *verf, expr_node_t *func)
{
CM_POINTER2(verf, func);
OG_RETURN_IFERR(sql_verify_func_node(verf, func, 0, 0, OG_INVALID_ID32));
func->datatype = OG_TYPE_DATE;
func->size = OG_DATE_SIZE;
func->precision = OG_DEFAULT_DATETIME_PRECISION;
sql_add_first_exec_node(verf, func);
return OG_SUCCESS;
}
status_t sql_func_utctimestamp(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
res->v_date = cm_utc_now();
res->is_null = OG_FALSE;
res->type = OG_TYPE_DATE;
return OG_SUCCESS;
}
status_t sql_verify_ymd(sql_verifier_t *verif, expr_node_t *func)
{
expr_tree_t *date_arg = NULL;
CM_POINTER2(verif, func);
date_arg = func->argument;
if (date_arg->next != NULL) {
OG_SRC_THROW_ERROR(func->loc, ERR_INVALID_FUNC_PARAM_COUNT, T2S(&func->word.func.name),
VERIFY_YMD_PARAM_MIN_COUNT, VERIFY_YMD_PARAM_MAX_COUNT);
return OG_ERROR;
}
OG_RETURN_IFERR(sql_verify_expr_node(verif, date_arg->root));
if (!(sql_match_interval_type(TREE_DATATYPE(date_arg)) || sql_match_datetime_type(TREE_DATATYPE(date_arg)))) {
OG_SRC_ERROR_REQUIRE_DATETIME(date_arg->loc, TREE_DATATYPE(date_arg));
return OG_ERROR;
}
func->datatype = OG_TYPE_INTEGER;
func->size = (func->datatype == OG_TYPE_INTEGER) ? sizeof(int32) : sizeof(dec8_t);
return OG_SUCCESS;
}
static status_t sql_func_extract_ymd(sql_stmt_t *stmt, expr_node_t *func, variant_t *res, interval_unit_t unit)
{
expr_tree_t *arg_date = NULL;
variant_t date_var;
CM_POINTER2(func, res);
arg_date = func->argument;
CM_POINTER(arg_date);
SQL_EXEC_FUNC_ARG_EX(arg_date, &date_var, res);
if (var_as_timestamp_flex(&date_var) == OG_SUCCESS) {
return sql_func_extract_date(unit, date_var, res);
} else {
cm_set_error_loc(arg_date->loc);
return OG_ERROR;
}
}
status_t sql_func_year(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
return sql_func_extract_ymd(stmt, func, res, IU_YEAR);
}
status_t sql_func_month(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
return sql_func_extract_ymd(stmt, func, res, IU_MONTH);
}
status_t sql_func_day(sql_stmt_t *stmt, expr_node_t *func, variant_t *res)
{
return sql_func_extract_ymd(stmt, func, res, IU_DAY);
}
