• Galina Shalygina's avatar
    MDEV-11588 Support for ONLY_FULL_GROUP_BY functional dependency · 1fb2c7d4
    Galina Shalygina authored
    Support functional dependencies usage when ONLY_FULL_GROUP_BY
    SQL_MODE is enabled.
    
    It is allowed to use fields that are either GROUP BY fields or
    fields that are functionally dependent on GROUP BY fields in
    SELECT list, HAVING clause and ORDER BY clause.
    Functionally dependent fields can be extracted from the WHERE clause
    (for the most outer JOIN tables) and ON expressions (for JOIN weak part
    tables) equalities.
    
    For these equalities such rules should hold:
    1. Equalities should be of the form (1):
    
       F2 = g(H11,..,H1n), where
    
       (H11, ..., H1n)  are some functions of GROUP BY fields and/or
                        GROUP BY fields and/or constants.
        g               is some function. It can be identity function.
        F2              is some non GROUP BY field.
    
        F2 here can be extracted as a new functionally dependent field.
    2. If F2 is from the left part of LEFT JOIN it can’t be extracted
    3. If F2 is from the right part of the LEFT JOIN either:
       2.1. H11,...,H1n should be fields from the right part
       2.2  n=1 and H1 is from the left part of the considered JOIN
    4. If (1) is used in ON expression this ON expression can't depend
       on non GROUP BY fields of the left part of the LEFT JOIN.
    5. If (1) is used in ON expression this ON expression can't depend
       on the right JOIN part fields only.
    6. 4 and 5 doesn't work if F2 table has at least one field used in
       GROUP BY.
    1fb2c7d4
item_cmpfunc.cc 202 KB