bug: mysqld crash with having clause

[davidshiz]

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

refer to stonedb-5.7-dev/mysql-test/suite/tianmu/t/issue415.test

mysql> select
    ->     'aa' as ag_column1,
    ->     !(tinyint_1bit_test) as ag_column2,
    ->     1 as ag_column3,
    ->     min((smallint_test + smallint_test) = 11) as ag_column_fun
    -> from
    ->     select_base_with_primary_key
    -> GROUP BY
    ->     ag_column1,
    ->     ag_column2,
    ->     ag_column3
    -> having
    ->     ag_column_fun <= 37;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/stonedb/build/install8/tmp/mysql.sock' (111)

Expected behavior

No response

How To Reproduce

No response

Environment

[root@HAST04 bin]# ./mysqld --version
/stonedb/build/install8/bin/mysqld  Ver 8.0.30-debug for Linux on x86_64 (Source distribution)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-8.0-dev
        Branch name: stonedb-8.0-dev
        Last commit ID: 514e5ad
        Last commit time: Date:   Thu Apr 20 19:35:24 2023 +0800
        Build time: Date: Mon Apr 24 19:29:19 CST 2023

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

• [ ] Yes, I will!

[RingsC]

Maybe assertion failed in debug mode.

[hustjieke]

1. having 1 < 2 not supported

mysql> CREATE TABLE test_having (a int, b int, c char(8), d char);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_having VALUES (0, 1, 'XXXX', 'A');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT 1 AS one FROM test_having HAVING 1 > 2;
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

2. having a < 1 is ok

mysql> select a from test_having group by a having a < 1;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

[hustjieke]

Crashed for first time, if reconnect to mysqld, the query will not crash.

[RingsC]

the statement of having 1 < 2 should be removed, first stage of optimization. const fold and remove the redundant statement.

[hustjieke]

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

refer to stonedb-5.7-dev/mysql-test/suite/tianmu/t/issue415.test

mysql> select
    ->     'aa' as ag_column1,
    ->     !(tinyint_1bit_test) as ag_column2,
    ->     1 as ag_column3,
    ->     min((smallint_test + smallint_test) = 11) as ag_column_fun
    -> from
    ->     select_base_with_primary_key
    -> GROUP BY
    ->     ag_column1,
    ->     ag_column2,
    ->     ag_column3
    -> having
    ->     ag_column_fun <= 37;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/stonedb/build/install8/tmp/mysql.sock' (111)

Expected behavior

No response

How To Reproduce

No response

Environment

[root@HAST04 bin]# ./mysqld --version
/stonedb/build/install8/bin/mysqld  Ver 8.0.30-debug for Linux on x86_64 (Source distribution)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-8.0-dev
        Branch name: stonedb-8.0-dev
        Last commit ID: 514e5ad
        Last commit time: Date:   Thu Apr 20 19:35:24 2023 +0800
        Build time: Date: Mon Apr 24 19:29:19 CST 2023

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

• [ ] Yes, I will!

ping @davidshiz Please check it again in latest code，if crashed again?

[Double0101]

Item_field::cleanup in mysql 8.0

void Item_field::cleanup() {
  DBUG_TRACE;
  if (!fixed) return;

  Item_ident::cleanup();
  /*
    When TABLE is detached from TABLE_LIST, field pointers are invalid,
    unless field objects are created as part of statement (placeholder tables).
    Also invalidate the original field name, since it is usually determined
    from the field name in the Field object.
  */
  if (table_ref != nullptr && !table_ref->is_view_or_derived() &&
      !table_ref->is_recursive_reference()) {
    field = nullptr;
    m_orig_field_name = nullptr;
  }

  // Restore result field back to the initial value
  result_field = field;

  /*
    When table_ref is NULL, table_name must be reassigned together with
    table pointer.
  */
  if (table_ref == nullptr) table_name = nullptr;

  // Reset field before next optimization (multiple equality analysis)
  item_equal = nullptr;
  item_equal_all_join_nests = nullptr;
  null_value = false;
}

cleanup() in mysql 5.7

void Item_field::cleanup()
{
  DBUG_ENTER("Item_field::cleanup");
  Item_ident::cleanup();
  /*
    Even if this object was created by direct link to field in setup_wild()
    it will be linked correctly next time by name of field and table alias.
    I.e. we can drop 'field'.
   */
  table_ref= NULL;
  field= result_field= 0;
  item_equal= NULL;
  null_value= FALSE;
  DBUG_VOID_RETURN;
}

the cleanup function are diffierent in 8.0 and 5.7, and the crash happen in cleanup_after_query()

[Double0101]

crash happen in sql_parse.cc in the end of the function dispatch_sql_command(THD *thd, Parser_state *parser_state)

[RingsC]

mysql>  select
    ->         'aa' as ag_column1,
    ->         !(tinyint_1bit_test) as ag_column2,
    ->       1 as ag_column3,
    ->         min((smallint_test + smallint_test) = 11) as ag_column_fun
    ->     from
    ->          select_base_with_primary_key
    ->      GROUP BY
    ->           ag_column1,
    ->          ag_column2,
    ->           ag_column3
    ->       having
    ->           ag_column_fun <= 37;
+------------+------------+------------+---------------+
| ag_column1 | ag_column2 | ag_column3 | ag_column_fun |
+------------+------------+------------+---------------+
| aa         |          0 |          1 |             0 |
+------------+------------+------------+---------------+
1 row in set (0.00 sec)

mysql> 

In the latest version, it works.