Alice Sherepa added a comment - 2023-11-03 13:48 - edited Thank you! I repeated on 10.9-11.2, not repeatable on 10.4-10.6 Version: '10.9.8-MariaDB-debug-log' mariadbd: /10.9/sql/item.cc:10799: virtual table_map Item_direct_view_ref::used_tables() const: Assertion `fixed()' failed. 231103 14:45:48 [ERROR] mysqld got signal 6 ;   Server version: 10.9.8-MariaDB-debug-log source revision: 3e0009dc3a771e4dbf2fa4a4cf87e750453fb2eb   sql/signal_handler.cc:241(handle_fatal_signal)[0x5649245e9c1c] /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f2f896eafd6] sql/item.cc:10801(Item_direct_view_ref::used_tables() const)[0x56492469e7e5] sql/sql_select.cc:26203(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x564923eb3fb8] sql/sql_select.cc:5714(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x564923e20abe] sql/sql_select.cc:2554(JOIN::optimize_inner())[0x564923e005db] sql/sql_select.cc:1893(JOIN::optimize())[0x564923df9639] sql/sql_select.cc:5093(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x564923e1b0d0] sql/sql_select.cc:584(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564923deaeeb] sql/sql_parse.cc:6288(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564923d0cdb7] sql/sql_parse.cc:3958(mysql_execute_command(THD*, bool))[0x564923cfb5e4] sql/sql_parse.cc:8060(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x564923d180d2] sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x564923cedcaf] sql/sql_parse.cc:1407(do_command(THD*, bool))[0x564923ceaa08] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5649241ae597] sql/sql_connect.cc:1320(handle_one_connection)[0x5649241adef4] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x564924ddcc78] nptl/pthread_create.c:478(start_thread)[0x7f2f89c05609]   Query (0x6290001092a8): SELECT t0 . c41 AS c38 FROM ( SELECT c40 AS c51 , ( SELECT c41 AS c58 FROM t0 LIMIT 1 ) - LTRIM ( 103 ) AS c2 , -69 AS c58 FROM t0 ) AS t1 JOIN t0 ON t0 . c12 = t0 . c40 WHERE OCT ( + NULLIF ( REVERSE ( c12 ) IS NOT NULL , -49 ) ) NOT LIKE LOG ( 55 NOT LIKE SUBSTRING( -34 , ABS ( 21 ) - FORMAT ( 4 IS NULL , TRUNCATE ( 74 , -1279808445404668877 ) IS NULL IS NULL ) ) , -31 ) GROUP BY c58 , c51 , c41 HAVING c51 = 53 REGEXP EXP ( t1 . c58 ) CREATE TABLE t0 ( b int ) ; INSERT INTO t0 (1),(2),(3);   SELECT 5 FROM ( SELECT b, 7 AS c FROM t0 ) AS t1 JOIN t0 ON 1 GROUP BY c , t1.b HAVING t1.b = 53 REGEXP EXP ( t1.c );

Oleg Smirnov added a comment - 2024-07-10 14:48 May be related to MDEV-28506 as disabling either of optimizations `derived_merge` or `condition_pushdown_from_having` makes the query finish successfully.

Oleg Smirnov added a comment - 2024-10-09 05:24 Simplified test case: CREATE TABLE t1 (a int ) ; INSERT INTO t1 values (1),(2),(3);   SELECT a FROM ( SELECT a, 1 AS b FROM t1 ) AS dt GROUP BY a, b HAVING a = b + 1; This issue is caused by MDEV-28958 . When building pushable conditions for HAVING there is a call to `multiple_equality_transformer` which executes the following code: bool Item_equal::create_pushable_equalities() { ... if (!clone_const) { /* Also set IMMUTABLE_FL for any sub-items of the right_item. This is needed to prevent Item::cleanup_excluding_immutables_processor from peforming cleanup of the sub-items and so creating an item tree where a fixed item has non-fixed items inside it. */ int16 new_flag= MARKER_IMMUTABLE; right_item->walk(&Item::set_extraction_flag_processor, false , ( void *)&new_flag); } When processing `HAVING a = b + 1` the `right_item` is of type `Item_func_plus`, and `set_extraction_flag_processor` sets MARKER_IMMUTABLE for this item and its children: void set_extraction_flag(int16 flags) { if (!basic_const_item()) { marker= marker & ~MARKER_EXTRACTION_MASK; marker|= flags; } } But the problem is that function argument `b` is constant, so MARKER_IMMUTABLE is not set for it. Later there is a call to `cleanup_excluding_immutables_processor`: Item *st_select_lex::pushdown_from_having_into_where() { ... if (item->walk(&Item::cleanup_excluding_immutables_processor, 0, STOP_PTR) ... } which cleans up the `Item_direct_view_ref` corresponding to the field `b` while the embedding `Item_func_plus` and `Item_func_eq` are marked as immutable and are not cleaned up. Therefore `fix_fields_if_needed()` skips the function items and their arguments, so `Item_direct_view_ref`(b) remains unfixed.

Oleg Smirnov added a comment - 2024-10-09 06:53 igor , what do you think such a fix ? bool Item::cleanup_excluding_immutables_processor (void *arg) { - if (!(get_extraction_flag() == MARKER_IMMUTABLE)) + if (!(get_extraction_flag() == MARKER_IMMUTABLE) && !basic_const_item()) return cleanup_processor(arg); else { I believe we can consider basic const items as immutable.

Igor Babaev (Inactive) added a comment - 2024-12-06 15:22 This bug is assigned to me

Oleg Smirnov added a comment - 2025-01-13 07:32 - edited igor , what do you mean by "assigned to me"? I asked for your review of the solution proposed above. If you approve it, I'll prepare a test case and an appropriate patch for pushing.