Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

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

Pagina: 1
Acties:

Vraag


Acties:
  • 0Henk 'm!

  • gnoe93
  • Registratie: september 2016
  • Laatst online: 24-04 09:10
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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:
1
2
3
4
5
6
7
8
        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

Alle reacties


Acties:
  • 0Henk 'm!

  • Montaner
  • Registratie: januari 2005
  • Laatst online: 23:34
GROUP BY COUNT(lower_display_name)

Acties:
  • 0Henk 'm!

  • gnoe93
  • Registratie: september 2016
  • Laatst online: 24-04 09:10
Montaner schreef op zondag 5 juli 2020 @ 22:15:
GROUP BY COUNT(lower_display_name)
Je kan toch niet groupen op een aggregatiefunctie?

Acties:
  • 0Henk 'm!

  • Voutloos
  • Registratie: januari 2002
  • Niet online
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]

Talkin.nl daily photoblog


Acties:
  • 0Henk 'm!

  • gnoe93
  • Registratie: september 2016
  • Laatst online: 24-04 09:10
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?

Acties:
  • 0Henk 'm!

  • The Eagle
  • Registratie: januari 2002
  • Laatst online: 21:23

The Eagle

I wear my sunglasses at night

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,

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)


Acties:
  • 0Henk 'm!

  • pedorus
  • Registratie: januari 2008
  • Niet online
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)

Vitamine D tekorten in Nederland | Middelen tegen corona


Acties:
  • 0Henk 'm!

  • Voutloos
  • Registratie: januari 2002
  • Niet online
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:
1
2
3
4
5
6
7
8
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.

Talkin.nl daily photoblog


Acties:
  • +1Henk 'm!

  • pedorus
  • Registratie: januari 2008
  • Niet online
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

Vitamine D tekorten in Nederland | Middelen tegen corona


Acties:
  • 0Henk 'm!

  • The Eagle
  • Registratie: januari 2002
  • Laatst online: 21:23

The Eagle

I wear my sunglasses at night

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 :)

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)

Pagina: 1


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True