[MySQL 5.7] HAVING clause is not in GROUP BY clause

[gnoe93]

Ik heb volgende query en snap niet goed waarom ik hier volgende error bij krijg:

Error in query (1055): Expression #1 of HAVING clause is not in GROUP BY clause and contains nonaggregated column 'db.inner_user.display_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

code:

SELECT
    outer_user.id,
    outer_user.display_name
FROM
    `user` outer_user
WHERE
    LOWER(outer_user.display_name) IN (
        SELECT
            LOWER(inner_user.display_name) AS lower_display_name
        FROM
            `user` inner_user
        GROUP BY
            lower_display_name
        HAVING
            COUNT(lower_display_name) > 1
    )

Er wordt toch op display name geaggregeerd? Dus waarom deze error? Als ik de subquery apart uitvoer lukt dit zonder problemen:

code:

        SELECT
            LOWER(inner_user.display_name) AS lower_display_name
        FROM
            `user` inner_user
        GROUP BY
            lower_display_name
        HAVING
            COUNT(lower_display_name) > 1

[Montaner]

GROUP BY COUNT(lower_display_name)

[gnoe93]

Montaner schreef op zondag 5 juli 2020 @ 22:15:
GROUP BY COUNT(lower_display_name)

Je kan toch niet groupen op een aggregatiefunctie?

[Voutloos]

Nee, dat slaat nergens op.

Wat je nu in de select hebt staan, hoort ook in de group by (minus de alias). En de count in de having kan gewoon count(*)>1.

Hoe je er ook over na kan denken: In executievolgorde zou eigenlijk de select clause als 1 vd laatste opgeschreven worden.

[ Voor 28% gewijzigd door Voutloos op 05-07-2020 22:27 ]

[gnoe93]

Ik weet niet zeker of ik helemaal begrijp wat je bedoelt, maar wil je zeggen dat `inner_user.display_name` en `lower_display_name` als 2 aparte kolommen gerekend worden?

Als dat zo is, waarom lukt het uitvoeren van de subquery apart wel? Het is geen correlated subquery, dus die zou toch compleet geisoleerd uitgevoerd moeten worden?

[The Eagle]

Kijk eens naar je explain plan?
Een group by en een having doe je over de volledige resultset. Je hebt hem nu in je subquery staan, daar hoort ie niet thuis. Haal dat deel buiten haakjes. Voor de volledigheid kun je lower_display_name ook even in het select deel van de hoofdquery zetten, dan zie je wat je feitelijk probeert te doen. Wil je hem daadwerkelijk niet ophalen, haal je hem weg,

[pedorus]

Ik snap niet waarom je lower nodig zou hebben als de collation case insensitive kan. Ik denk dat het ook waarschijnlijk efficiënter is om per naam te kijken of er niet tenminste 1 dezelfde achternaam is met een ander id met self-join+group by, en met de juiste collation (normaal utf8mb4_unicode_ci) kun je lower dus weglaten.

Mysql probeert IN-queries ook te optimaliseren, dat gaat hier waarschijnlijk mis omdat de query wat brak is. Zonder lower en met de juiste collation doet die query het ook gewoon. Met lower in de group by + een having clause gaat het eigenlijk altijd mis, ook met count(*), dat zou best een bugje kunnen zijn.

Zie https://www.db-fiddle.com/f/u6g4fQpD56PhvcZUjNaAzw/1 (maar het kan dus wellicht beter anders met een self-join)

[Voutloos]

gnoe93 schreef op zondag 5 juli 2020 @ 22:43:
Ik weet niet zeker of ik helemaal begrijp wat je bedoelt, maar wil je zeggen dat `inner_user.display_name` en `lower_display_name` als 2 aparte kolommen gerekend worden?

Als dat zo is, waarom lukt het uitvoeren van de subquery apart wel? Het is geen correlated subquery, dus die zou toch compleet geisoleerd uitgevoerd moeten worden?

De losse query werkt omdat mysql zo aardig/brak (woordkeuze afhankelijk van mening lezer) is om de term die je aliast voor jouw uit te pluizen en naar de group by te kopieren.

Maar jouw query idee is volgens mij letterlijk:

MySQL:

SELECT
            LOWER(inner_user.display_name) AS lower_display_name
        FROM
            `user` inner_user
        GROUP BY
            LOWER(inner_user.display_name)
        HAVING
            COUNT(*) > 1

Dus schrijf dat dan zo expliciet op. Jij wil groeperen op gelijke lowercase strings, met meer dan 1 row en o ja, uiteindelijk ook de lowercase string selecten.

Dit is letterlijk wat ik zei. En ik verwacht dat dit ook als subquery gaat werken.

Qua optimalisatie loopt het wel los gok ik, en dat was je vraag ook niet, want misschien is dit uberhaupt een eenmalige actie? Alternatief kan je ook een EXISTS(zoek lowercase string maar niet hetzelfde ID) doen.

[pedorus]

Voutloos schreef op maandag 6 juli 2020 @ 11:09:
En ik verwacht dat dit ook als subquery gaat werken.

Dat dacht ik ook, maar is dat ook zo? Klik eens op run:
https://www.db-fiddle.com/f/u6g4fQpD56PhvcZUjNaAzw/2

[The Eagle]

Het gaat gewoon fout omdat die sql_mode=only_full_group_by aan staat, wat tegenwoordig de default is.
Zie ook https://www.percona.com/b...y_full_group_by-sql-mode/
MySQL was vroeger wat vergefelijker qua voldoen aan ANSI SQL, maar tegenwoordig staat dit by default aan. En idd, waarom zou je group by en having in je subquery doen als de optimizer zoiets voor je op kan lossen
Dat ie vroeger niet zo slim was maakt nu niks meer uit