bug: the EXTRACT() function error

davidshiz commented 2 years ago

Have you read the Contributing Guidelines on issues?

[X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

[X] 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 commented 2 years ago

ACK

DandreChen commented 2 years ago

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 commented 2 years ago

not run MINUTE_SECOND case

DandreChen commented 2 years ago

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 commented 2 years ago

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 commented 1 year ago

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 commented 1 year ago

*(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);
}

DandreChen commented 1 year ago

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_);

stoneatom / stonedb