bug: the EXTRACT() function error #845

davidshiz · 2022-10-29T11:55:55Z

Have you read the Contributing Guidelines on issues?

I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

I confirm there is no existing issue for this

Describe the problem

The result returned by the function EXTRACT() is incorrect
The wrong result is as follows:

mysql> select * from ctimestamp;
+---------------------+
| a                   |
+---------------------+
| 2019-01-02 00:02:03 |
| 2019-01-02 01:02:03 |
| 2019-01-02 10:11:12 |
+---------------------+
3 rows in set (0.00 sec)

mysql> select EXTRACT(DAY_HOUR FROM a), EXTRACT(MINUTE_SECOND FROM a) from ctimestamp;
+--------------------------+-------------------------------+
| EXTRACT(DAY_HOUR FROM a) | EXTRACT(MINUTE_SECOND FROM a) |
+--------------------------+-------------------------------+
|                      200 |                           200 |
|                      201 |                           201 |
|                      210 |                           210 |
+--------------------------+-------------------------------+
3 rows in set (0.01 sec)

Expected behavior

mysql> select EXTRACT(DAY_HOUR FROM a), EXTRACT(MINUTE_SECOND FROM a) from ctimestamp;
+--------------------------+-------------------------------+
| EXTRACT(DAY_HOUR FROM a) | EXTRACT(MINUTE_SECOND FROM a) |
+--------------------------+-------------------------------+
|                      200 |                           203 |
|                      201 |                           203 |
|                      210 |                          1112 |
+--------------------------+-------------------------------+
3 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE ctimestamp (a timestamp);
INSERT INTO ctimestamp VALUES ('2019-01-02 00:02:03'),
('2019-01-02 01:02:03'), ('2019-01-02 10:11:12');
select EXTRACT(DAY_HOUR FROM a), EXTRACT(MINUTE_SECOND FROM a) from ctimestamp;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 86a7685dc
        Last commit time: Date:   Sat Oct 22 03:38:32 2022 +0800
        Build time: Date: Sat 22 Oct 2022 02:14:05 PM UTC
root@ub01:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.5 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.5 LTS"
VERSION_ID="20.04"

Are you interested in submitting a PR to solve the problem?

Yes, I will!

DandreChen · 2022-11-03T03:42:10Z

ACK

DandreChen · 2022-11-08T12:24:59Z

single use it ,can use successfully

 select EXTRACT(MINUTE_SECOND FROM a) from ctimestamp;
+-------------------------------+
| EXTRACT(MINUTE_SECOND FROM a) |
+-------------------------------+
|                           203 |
|                           203 |
|                          1112 |
+-------------------------------+
3 rows in set (3.07 sec)

DandreChen · 2022-11-09T03:00:11Z

not run MINUTE_SECOND case

DandreChen · 2022-11-09T03:01:48Z

Judgment basis：
INTERVAL_MINUTE_SECOND
INTERVAL_DAY_HOUR

longlong Item_extract::val_int()
{
  assert(fixed == 1);
  MYSQL_TIME ltime;
  uint year;
  ulong week_format;
  long neg;
  if (date_value)
  {
    if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
      return 0;
    neg=1;
  }
  else
  {
    if (get_arg0_time(&ltime))
      return 0;
    neg= ltime.neg ? -1 : 1;
  }
  switch (int_type) {
  case INTERVAL_YEAR:		return ltime.year;
  case INTERVAL_YEAR_MONTH:	return ltime.year*100L+ltime.month;
  case INTERVAL_QUARTER:	return (ltime.month+2)/3;
  case INTERVAL_MONTH:		return ltime.month;
  case INTERVAL_WEEK:
  {
    week_format= current_thd->variables.default_week_format;
    return calc_week(&ltime, week_mode(week_format), &year);
  }
  case INTERVAL_DAY:		return ltime.day;
  case INTERVAL_DAY_HOUR:	return (long) (ltime.day*100L+ltime.hour)*neg;
  case INTERVAL_DAY_MINUTE:	return (long) (ltime.day*10000L+
					       ltime.hour*100L+
					       ltime.minute)*neg;
  case INTERVAL_DAY_SECOND:	 return ((longlong) ltime.day*1000000L+
					 (longlong) (ltime.hour*10000L+
						     ltime.minute*100+
						     ltime.second))*neg;
  case INTERVAL_HOUR:		return (long) ltime.hour*neg;
  case INTERVAL_HOUR_MINUTE:	return (long) (ltime.hour*100+ltime.minute)*neg;
  case INTERVAL_HOUR_SECOND:	return (long) (ltime.hour*10000+ltime.minute*100+
					       ltime.second)*neg;
  case INTERVAL_MINUTE:		return (long) ltime.minute*neg;
  case INTERVAL_MINUTE_SECOND:	return (long) (ltime.minute*100+ltime.second)*neg;
  case INTERVAL_SECOND:		return (long) ltime.second*neg;
  case INTERVAL_MICROSECOND:	return (long) ltime.second_part*neg;
  case INTERVAL_DAY_MICROSECOND: return (((longlong)ltime.day*1000000L +
					  (longlong)ltime.hour*10000L +
					  ltime.minute*100 +
					  ltime.second)*1000000L +
					 ltime.second_part)*neg;
  case INTERVAL_HOUR_MICROSECOND: return (((longlong)ltime.hour*10000L +
					   ltime.minute*100 +
					   ltime.second)*1000000L +
					  ltime.second_part)*neg;
  case INTERVAL_MINUTE_MICROSECOND: return (((longlong)(ltime.minute*100+
							ltime.second))*1000000L+
					    ltime.second_part)*neg;
  case INTERVAL_SECOND_MICROSECOND: return ((longlong)ltime.second*1000000L+
					    ltime.second_part)*neg;
  case INTERVAL_LAST: assert(0); break;  /* purecov: deadcode */
  }
  return 0;					// Impossible
}

DandreChen · 2022-11-09T07:56:00Z

CREATE TABLE ctimestamp (a timestamp, b timestamp);
INSERT INTO ctimestamp VALUES ('2019-01-02 00:02:03', '2019-01-02 00:02:03'),
('2019-01-02 01:02:03', '2019-01-02 00:02:03'), ('2019-01-02 10:11:12', '2019-01-02 10:11:12');
select EXTRACT(DAY_HOUR FROM a), EXTRACT(MINUTE_SECOND FROM b) from ctimestamp;

result is right:

mysql> select EXTRACT(DAY_HOUR FROM a), EXTRACT(MINUTE_SECOND FROM b) from ctimestamp;
+--------------------------+-------------------------------+
| EXTRACT(DAY_HOUR FROM a) | EXTRACT(MINUTE_SECOND FROM b) |
+--------------------------+-------------------------------+
|                      200 |                           203 |
|                      201 |                           203 |
|                      210 |                          1112 |
+--------------------------+-------------------------------+

DandreChen · 2022-11-21T08:28:18Z

VirtualColumnAlreadyExists() function return wrong result:

int Query::VirtualColumnAlreadyExists(const TabID &tmp_table, MysqlExpression *expression) {
  int exists = common::NULL_VALUE_32;
  for (auto it = tab_id2expression.lower_bound(tmp_table), end = tab_id2expression.upper_bound(tmp_table); it != end;
       ++it) {
    if (*(it->second.second) == *expression) {
      exists = it->second.first;
      break;
    }
  }
  return exists;
}

*(it->second.second) == *expression is wrong

DandreChen · 2022-11-21T09:00:29Z

*(it->second.second) == *expression imply :

bool MysqlExpression::operator==(MysqlExpression const &other) const {
  return ((mysql_type == other.mysql_type) && (decimal_precision == other.decimal_precision) &&
          (decimal_scale == other.decimal_scale) && (deterministic == other.deterministic) &&
          (*item == *(other.item)) && (tianmu_fields_cache.size() == other.tianmu_fields_cache.size()) &&
          vars == other.vars &&
          equal(tianmu_fields_cache.begin(), tianmu_fields_cache.end(), other.tianmu_fields_cache.begin(),
                SameTIANMUFieldSet));
}

bool operator==(Item const &l_, Item const &r_) {
  Item::Type t = l_.type();
  bool same = t == r_.type();
  if (same) {
    switch (static_cast<int>(t)) {
      case (Item::FIELD_ITEM): {
        same = false;  // not implemented
                       //              Item_field const* l = static_cast<Item_field
                       //              const*>(&l_); Item_field const* r =
                       //              static_cast<Item_field const*>(&r_);
                       //                  same = l->field->
      } break;
      case (Item::COND_ITEM):
      case (Item::FUNC_ITEM): {
        Item_func const *l = static_cast<Item_func const *>(&l_);
        Item_func const *r = static_cast<Item_func const *>(&r_);
        same = !std::strcmp(l->func_name(), r->func_name());
        same = same && (l->arg_count == r->arg_count);
        same = same && l->functype() == r->functype();
        if (l->functype() == Item_func::GUSERVAR_FUNC) {
          if (same) {
            Item_func_get_user_var const *ll = static_cast<Item_func_get_user_var const *>(&l_);
            Item_func_get_user_var const *rr = static_cast<Item_func_get_user_var const *>(&r_);
            same = !std::strcmp(ll->name.ptr(), rr->name.ptr());
          }
        } else {
          same = same && l->arg_count == r->arg_count;
          for (uint i = 0; same && (i < l->arg_count); ++i) same = same && (*l->arguments()[i] == *r->arguments()[i]);

          // Item_func* lll = (Item_func*)&l;
          // Item_func* mmm = (Item_func*)&r;

          // bool x = l->const_item();
          // bool y = r->const_item();
          // longlong zzz = lll->val_int_result();
          // longlong vvv = mmm->val_int_result();
          same = same && l_.item_name.eq(r_.item_name);
          same = same && (l->const_item() == r->const_item());
          if (same && l->const_item())
            same = ((Item_func *)&l_)->val_int() == ((Item_func *)&r_)->val_int();
          if (dynamic_cast<const Item_date_add_interval *>(&l_)) {
            const Item_date_add_interval *l = static_cast<const Item_date_add_interval *>(&l_);
            const Item_date_add_interval *r = static_cast<const Item_date_add_interval *>(&r_);
            same = same && dynamic_cast<const Item_date_add_interval *>(&r_);
            same = same && ((l->int_type == r->int_type) && (l->date_sub_interval == r->date_sub_interval));
          }
          if (l->functype() == Item_func::IN_FUNC) {
            const Item_func_in *l = static_cast<const Item_func_in *>(&l_);
            const Item_func_in *r = static_cast<const Item_func_in *>(&r_);
            same = same && l->negated == r->negated;
          }
          if (same && (l->functype() == Item_func::COND_AND_FUNC || l->functype() == Item_func::COND_OR_FUNC)) {
            Item_cond *l = const_cast<Item_cond *>(static_cast<Item_cond const *>(&l_));
            Item_cond *r = const_cast<Item_cond *>(static_cast<Item_cond const *>(&r_));
            List_iterator<Item> li(*l->argument_list());
            List_iterator<Item> ri(*r->argument_list());
            Item *il, *ir;
            while ((il = li++) && (ir = ri++)) {
              same = same && *il == *ir;
            }
            same = same && (!ir && !il);
          }
          if (same && l->functype() == Item_func::XOR_FUNC) {
            same = false;  // not implemented.
          }
        }
      } break;
      case static_cast<int>(Item_tianmufield::enumTIANMUFiledItem::TIANMUFIELD_ITEM): {
        Item_tianmufield const *l = static_cast<Item_tianmufield const *>(&l_);
        Item_tianmufield const *r = static_cast<Item_tianmufield const *>(&r_);
        same = (*l == *r);
      } break;
      case (Item::REF_ITEM): {
        Item_ref const *l = static_cast<Item_ref const *>(&l_);
        Item_ref const *r = static_cast<Item_ref const *>(&r_);
        same = (!(l->ref || r->ref)) ||
               (l->ref && r->ref &&
                ((!(*(l->ref) || *(r->ref))) || (*(l->ref) && *(r->ref) && (*(*(l->ref)) == *(*(r->ref))))));
      } break;
      case (Item::NULL_ITEM):
      case (Item::STRING_ITEM):
      case (Item::DECIMAL_ITEM):
      case (Item::REAL_ITEM):
      case (Item::VARBIN_ITEM):
      case (Item::INT_ITEM): {
        same = l_.eq(&r_, true);
      } break;
      default: {
        same = generic_item_same(l_, r_);
      } break;
    }
  }
  return (same);
}

add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t).

Add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t). Convert static_cast to down_cast

DandreChen · 2022-11-23T02:49:55Z

warning:

[ 40%] Building CXX object storage/tianmu/CMakeFiles/tianmu.dir/core/mysql_expression.cpp.o
/opt/github/stonedb57/storage/tianmu/core/mysql_expression.cpp: In function ‘bool Tianmu::core::operator==(const Item&, const Item&)’:
/opt/github/stonedb57/storage/tianmu/core/mysql_expression.cpp:627:63: warning: the compiler can assume that the address of ‘l_’ will never be NULL [-Waddress]
  627 |           if (static_cast<const Item_date_add_interval *>(&l_)) {
      |                                                               ^
/opt/github/stonedb57/storage/tianmu/core/mysql_expression.cpp:630:75: warning: the compiler can assume that the address of ‘r_’ will never be NULL [-Waddress]
  630 |             same = same && static_cast<const Item_date_add_interval *>(&r_);
      |                                                                           ^
/opt/github/stonedb57/storage/tianmu/core/mysql_expression.cpp:627:11: warning: nonnull argument ‘l_’ compared to NULL [-Wnonnull-compare]
  627 |           if (static_cast<const Item_date_add_interval *>(&l_)) {
      |           ^~
/opt/github/stonedb57/storage/tianmu/core/mysql_expression.cpp:630:25: warning: nonnull argument ‘r_’ compared to NULL [-Wnonnull-compare]
  630 |             same = same && static_cast<const Item_date_add_interval *>(&r_);

Add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t). Convert static_cast to down_cast

davidshiz added A-bug Something isn't working B-testing Testing tools and infrastructure labels Oct 29, 2022

davidshiz added this to the stonedb_5.7_v1.0.2 milestone Oct 29, 2022

davidshiz removed the B-testing Testing tools and infrastructure label Oct 29, 2022

RingsC assigned DandreChen Nov 1, 2022

DandreChen added a commit to DandreChen/stonedb that referenced this issue Nov 22, 2022

fix(tianmu): the EXTRACT() function error.(stoneatom#845)

70d18a5

add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t).

DandreChen added a commit to DandreChen/stonedb that referenced this issue Nov 22, 2022

fix(tianmu): the EXTRACT() function error.(stoneatom#845)

c4c5d17

add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t).

DandreChen mentioned this issue Nov 22, 2022

fix(tianmu): the EXTRACT() function error.(#845) #982

Merged

14 tasks

mergify bot closed this as completed in #982 Nov 29, 2022

mergify bot pushed a commit that referenced this issue Nov 29, 2022

fix(tianmu): the EXTRACT() function error.(#845)

881f713

Add item_name comparison for the diff operation of the same function,as EXTRACT(DAY_HOUR FROM t) and EXTRACT(MINUTE_SECOND FROM t). Convert static_cast to down_cast

hustjieke added this to StoneDB for MySQL 5.7 Nov 29, 2022

hustjieke moved this to Done in StoneDB for MySQL 5.7 Nov 29, 2022

hustjieke mentioned this issue May 6, 2023

backport: the EXTRACT() function error #1691

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

bug: the EXTRACT() function error #845

bug: the EXTRACT() function error #845

davidshiz commented Oct 29, 2022 •

edited

Loading

DandreChen commented Nov 3, 2022

DandreChen commented Nov 8, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 21, 2022

DandreChen commented Nov 21, 2022 •

edited

Loading

DandreChen commented Nov 23, 2022

bug: the EXTRACT() function error #845

bug: the EXTRACT() function error #845

Comments

davidshiz commented Oct 29, 2022 • edited Loading

Have you read the Contributing Guidelines on issues?

Please confirm if bug report does NOT exists already ?

Describe the problem

Expected behavior

How To Reproduce

Environment

Are you interested in submitting a PR to solve the problem?

DandreChen commented Nov 3, 2022

DandreChen commented Nov 8, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 9, 2022

DandreChen commented Nov 21, 2022

DandreChen commented Nov 21, 2022 • edited Loading

DandreChen commented Nov 23, 2022

davidshiz commented Oct 29, 2022 •

edited

Loading

DandreChen commented Nov 21, 2022 •

edited

Loading