• Sergei Petrunia's avatar
    MDEV-28621: group by optimization incorrectly removing subquery where subject buried in a function · 40b3525f
    Sergei Petrunia authored
    Workaround patch: Do not remove GROUP BY clause when it has
    subquer(ies) in it.
    
    remove_redundant_subquery_clauses() removes redundant GROUP BY clause
    from queries in form:
      expr IN (SELECT no_aggregates GROUP BY ...)
      expr {CMP} {ALL|ANY|SOME} (SELECT no_aggregates GROUP BY ...)
    This hits problems when the GROUP BY clause itself has subquer(y/ies).
    
    This patch is just a workaround: it disables removal of GROUP BY clause
    if the clause has one or more subqueries in it.
    
    Tests:
    - subselect_elimination.test has all known crashing cases.
    - subselect4.result, insert_select.result are updated.
    Note that in some cases results of SELECT are changed too (not just
    EXPLAINs). These are caused by non-deterministic SQL: when running a
    query like:
    
      x > ANY( SELECT col1 FROM t1 GROUP BY constant_expression)
    
    without removing the GROUP BY, the executor is free to pick the value
    of t1.col1 from any row in the GROUP BY group (denote it $COL1_VAL).
    Then, it computes x > ANY(SELECT $COL1_VAL).
    
    When running the same query and removing the GROUP BY:
    
       x > ANY( SELECT col1 FROM t1)
    
    the executor will actually check all rows of t1.
    40b3525f
subselect_elimination.test 5.64 KB