Yuchen Pei added a comment - 2024-04-29 00:28 - edited With this new approach, the case ( type_temporal_innodb ) that resulted in the opening of MDEV-33648 now passes, presumably because we still construct multiple equalities (just with a different item), so it is no longer blocking this ticket. However, main.timezone2 still failed at my commit, this time with SEGV, which was caused by Item_timestamp_copy not marked as constant. So I updated methods to mark it as constant when copy() is called (note the docstring of copy() . New patch which the CI seems to be happy with - ptal psergei thanks 828d379e9c8 bb-10.5-mdev-29363-cheap MDEV-29363 replace subqueries with their values before adding to item_equal

Galina Shalygina (Inactive) added a comment - 2024-06-24 06:13 - edited The crash can be fixed in the condition pushdown from HAVING into WHERE optimization moving disabling of IMMUTABLE_FL to the last step. Every condition that is going to be pushed into WHERE clause is first cleaned up, then refixed. Constant subqueries are not cleaned or refixed because they will remain the same after refixing, so this complicated procedure can be omitted for them (introduced in MDEV-21184 ). Constant subqueries are marked with flag IMMUTABLE_FL, that helps to miss the cleanup stage for them. Also they are marked as fixed, so refixing is also not done for them. Because of the multiple equality propagation several references to the same constant subquery can exist in the condition that is going to be pushed into WHERE. After the first reference to the constant subquery is processed, the flag IMMUTABLE_FL for the constant subquery is disabled. So, when the second reference to this constant subquery is processed, the flag is already disabled and the subquery goes through the procedure of cleaning and refixing. That causes a crash. The branch with fix can be found at (waiting for the review): https://github.com/MariaDB/server/tree/bb-10.5-mdev-29363-late-flag-cleanup This fix will also fix MDEV-32293 , MDEV-32304 and MDEV-32424 crashes.

Igor Babaev (Inactive) added a comment - 2024-07-04 04:23 Ok to push into 10.5.

Galina Shalygina (Inactive) added a comment - 2024-07-04 15:34 - edited Pushed in 10.5 ( commit ) The initial query doesn't crash anymore. MariaDB [t]> CREATE TABLE v1200 ( v1201 TEXT NOT NULL ) ; MariaDB [t]> ( ( SELECT v1201 FROM v1200 WHERE v1201 IN ( 'x' = v1201 ) ORDER BY v1201 + v1201 , v1201 + v1201 ) ) ; MariaDB [t]> UPDATE v1200 SET v1201 = 82 WHERE v1201 = 39 ; MariaDB [t]> INSERT INTO v1200 ( v1201 ) VALUES ( 8 ) , ( 71 ) ;   MariaDB [t]> SELECT DISTINCT ( ( 52851147.000000 OR NOT TRUE ) - 46 ) , 21 FROM v1200 WINDOW v1208 AS ( PARTITION BY FALSE ORDER BY ( SELECT DISTINCT 90 FROM ( SELECT DISTINCT 'x' , ( WITH RECURSIVE v1202 AS ( SELECT DISTINCT v1201 FROM v1200 ) SELECT v1201 FROM ( SELECT DISTINCT ( ( NOT ( 14419645.000000 AND v1201 = 0 ) ) = 2147483647 AND v1201 = 58 ) % 83 , ( v1201 = 85 OR v1201 > 'x' ) FROM v1200 WHERE v1201 = 79 AND ( EXISTS ( SELECT ( v1201 NOT IN ( v1201 ) AND v1201 NOT IN ( 55 ^ v1201 ) ) , v1201 + v1201 FROM v1200 GROUP BY v1201 HAVING ( v1201 != 1 AND v1201 = v1201 AND ( NOT ( 'x' = 'x' AND FALSE = 84 ) ) AND v1201 LIKE 'x' ) WINDOW v1209 AS ( ORDER BY v1201 - v1201 , ( 0 < v1201 AND v1201 = 64 ) ) ) OR v1201 = -1 OR v1201 = 8 ) ) AS v1204 NATURAL JOIN v1200 WHERE ( v1201 = 1 OR v1201 = 25453422.000000 ) NOT LIKE 'x' AND CASE v1201 * 2147483647 = -1 WHEN 64 THEN 'x' WHEN -1 THEN 'x' ELSE 255 END != 9 GROUP BY v1201 , v1201 ORDER BY v1201 DESC LIMIT 1 OFFSET 1 ) , 127 , 73570201.000000 FROM v1200 ) AS v1205 JOIN v1200 AS v1206 NATURAL JOIN v1200 AS v1207 JOIN v1200 ) DESC RANGE BETWEEN 36297935.000000 FOLLOWING AND 89149539.000000 FOLLOWING ) ; + ------------------------------------------+----+ | ( ( 52851147.000000 OR NOT TRUE ) - 46 ) | 21 | + ------------------------------------------+----+ | -45 | 21 | + ------------------------------------------+----+ 1 row in set (0,005 sec)

Galina Shalygina (Inactive) added a comment - 2024-08-05 07:15 A short description that could describe the problem in this MDEV and problems with other MDEVs that are covered in the provided fix for this task. The server could crash for a query with a HAVING clause such that: 1. It has several references to the same non-trivial constant (e.g. a subquery), 2. Condition pushdown optimization would try to move at least one of the references from HAVING clause into WHERE.