[SQL] Lijst genereren met flags

[posttoast]

Ik was net bezig met het in elkaar draaien van een SQL-query. Sinds mijn afstuderen een paar jaar geleden is dat voor mij geen dagelijkse kost meer, dus ik merkte dat mijn kennis een beetje stoffig is. Het probleem dat ik heb is prima met een workaround op te lossen, alleen ik kan het niet hebben dat het me niet in één query lukt. Goed, dat was de disclaimer, nu het echte verhaal

Ik heb een database met daarin kalenderitems en artiesten. Een kalenderitems kan gekoppeld zijn aan 0 of meerdere artiesten. Dit heb ik opgelost volgens het boekje, met een koppeltabel dus. Nu heb ik een pagina met daarop de info van een kalenderitem en daarin wil ik een lijst hebben van alle artiesten met checkboxes erachter. Als de artiest gekoppeld is aan het huidige kalenderitem, dan moet de checkbox aangevinkt zijn.

Wat ik nu dus nodig heb is een query die mij de id's en namen van alle artiesten teruggeeft mét een vlaggetje waaraan ik kan zien of de artiest gekoppeld is aan het huidige kalenderitem. En dat krijg ik dus niet voor elkaar. Ik dacht er te zijn met onderstaande query, maar de oplettende lezer zal zien dat dit niet gaat werken:

SQL:

SELECT
  id,
  title,
  calendaritem_id
FROM
  artist a
LEFT JOIN
  calendaritem__artist c
ON
  a.id = c.artist_id
WHERE
  calendaritem_id = 2
GROUP BY
  id
ORDER BY
  title

De output die ik wil hebben is zoiets:

artist_id	artist_title	is_linked
1	The Beatles	1
2	Lily Allen	0
3	Beppie Kraft	1
4	Robbie Williams	0

Kan iemand mij een stapje richting de oplossing helpen?

Nogmaals: het gaat me niet om de oplossing van mijn probleem, maar ik wil gewoon mijn SQL-skills even een kleine boost geven!

[pedorus]

"calendaritem_id" veranderen in "(COUNT(calendaritem_id)>0) as is_linked" ?

Hieronder gaat nog een group-by-discussie komen over dat (artist_)title in de group by clause zou moeten komen, en dat MySQL het fout doet. Dit is niet waar.

[posttoast]

Nope, dat werkt niet. Ik krijg dan alleen de artiesten terug die gekoppeld zijn aan het kalenderitem. Of doe ik dan iets verkeerd?

[pedorus]

posttoast schreef op zondag 06 september 2009 @ 16:41:
Nope, dat werkt niet. Ik krijg dan alleen de artiesten terug die gekoppeld zijn aan het kalenderitem. Of doe ik dan iets verkeerd?

Dat komt vanwege die WHERE-clause. Dat stukje moet naar de ON-clause ( a.id = c.artist_id AND calendaritem_id = 2 ), of je moet " OR ... IS NULL" gebruiken, zodat ook items worden geretourneerd waarbij de join niet lukt.

[RobertMe]

Als ik me niet vergis kun je ook gewone voorwaarden aan een ON hangen, als je de WHERE dus naar de LEFT JOIN verplaatst heb je al een grotere kans dat het werkt

SQL:

LEFT JOIN 
  calendaritem__artist c 
ON 
  a.id = c.artist_id 
AND
  calendaritem_id = 2

Werkt dat niet, moet je van de LEFT JOIN een subquery maken

SQL:

LEFT JOIN
  (SELECT
    *
  FROM
    calenderitem_artiest
  WHERE
    calenderitem_id = 2)
ON
...

Hieronder gaat nog een group-by-discussie komen over dat (artist_)title in de group by clause zou moeten komen, en dat MySQL het fout doet. Dit is niet waar.

Tja, als jij kans wil lopen op onzinnige resultaten, vooral door gaan met het niet "correct" gebruiken van GROUP BY, er zal immers toch wel een reden zijn dat elke andere db deze query meteen zou afschieten op een incorrecte GROUP BY? En tuurlijk zullen ze bij MySQL zeggen dat het een feature is...

Edit:
Met een OR calenderitem_id IS NULL kan inderdaad ook als die "where" in de on niet werkt/mogelijk is

[ Voor 5% gewijzigd door RobertMe op 06-09-2009 16:51 ]

[Gomez12]

pedorus schreef op zondag 06 september 2009 @ 16:35:
Hieronder gaat nog een group-by-discussie komen over dat (artist_)title in de group by clause zou moeten komen, en dat MySQL het fout doet. Dit is niet waar.

Tja, als ik de volgende quote van die pagina pak :

Having realized that, we can now ask ourselves: assuming there are multiple values in the birth column for a particular value of species, which one should be returned? What did we mean when we specified the birth column in the SELECT list?

There is no good answer to this question. It is certainly possible to select just one of the possible values for the birth column: in fact, this is exactly what MySQL does.

Dan krijg ik toch zoiets van : Er is geen goed antwoord, dus we pakken er maar 1...
Ik verwacht gewoon goede antwoorden, geen goed antwoord is gewoon geen antwoord ( wat alle andere dbms'en dan ook retourneren in de vorm van een foutmelding ).

En de ONLY_FULL_GROUP_BY modus heeft ook weer de nodige bugs

[posttoast]

OK dan, het is gelukt! De truc was dus inderdaad om de WHERE-clause bij de ON te voegen! De uiteindelijke query:

SQL:

SELECT
  id,
  title,
  calendaritem_id
FROM
  artist a
LEFT JOIN
  calendaritem__artist c
ON
  a.id = c.artist_id
AND
  calendaritem_id = 1
GROUP BY
  id
ORDER BY
  title

[Verwijderd]

Misschien een hele domme vraag - ik werk zelf niet met mySQL - maar waarom zit er een 'GROUP BY' in de query, terwijl er nergens gebruik wordt gemaakt van een aggregate functie?

[cariolive23]

Verwijderd schreef op dinsdag 08 september 2009 @ 10:23:
Misschien een hele domme vraag - ik werk zelf niet met mySQL - maar waarom zit er een 'GROUP BY' in de query, terwijl er nergens gebruik wordt gemaakt van een aggregate functie?

Uitstekende vraag, vooral omdat de huidige query compleet fout is en geen correcte resultaten kán opleveren.

Gooi die GROUP BY weg (beste oplossing) of zorg voor een correcte GROUP BY. Mochtje een DISTINCT nodig hebben, zet dan een DISTINCT in je query. GROUP BY is hier onbruikbaar en hoort een foutmelding op te leveren.

[posttoast]

cariolive23 schreef op dinsdag 08 september 2009 @ 11:36:
[...]

Uitstekende vraag, vooral omdat de huidige query compleet fout is en geen correcte resultaten kán opleveren.

Gooi die GROUP BY weg (beste oplossing) of zorg voor een correcte GROUP BY. Mochtje een DISTINCT nodig hebben, zet dan een DISTINCT in je query. GROUP BY is hier onbruikbaar en hoort een foutmelding op te leveren.

Ehm, ik krijg gewoon een goed resultaat hoor en geen foutmelding. Maar misschien kun je even uitleggen wat er dan verkeerd is en waarom?

[pedorus]

cariolive23 schreef op dinsdag 08 september 2009 @ 11:36:
[...]

Uitstekende vraag, vooral omdat de huidige query compleet fout is en geen correcte resultaten kán opleveren.

Zie reactie 1.

cariolive23 schreef op dinsdag 08 september 2009 @ 11:36:
Gooi die GROUP BY weg (beste oplossing) of [...]

Vanwaar die alternatieven? Aangezien TS zeer waarschijnlijk een normale koppeltabel (calendaritem__artist) heeft waarin de combinatie (artist_id, calendaritem_id) vanwege een key maximaal 1x kan voorkomen, kan die group by gewoon weg... (Doordat je calendar_id vast zet op maximaal 1 waarde; als je meer dan 1 calendaritem_id opvraagt gaat dit natuurlijk niet op.)

[Verwijderd]

posttoast schreef op dinsdag 08 september 2009 @ 11:50:
[...]

Ehm, ik krijg gewoon een goed resultaat hoor en geen foutmelding. Maar misschien kun je even uitleggen wat er dan verkeerd is en waarom?

Een 'GROUP BY' gebruik je in combinatie met een aggregate functie (MIN, MAX, AVG, COUNT, SUM, etc.) om aan te geven over welke groep van gegevens de functie moet aggregeren. Aangezien je geen gebruik maakt van een aggregate functie, is het ook volkomen zinloos om hier een 'GROUP BY' te gebruiken. MySQL klaagt dan wel niet, maar dat maakt het niet minder fout.

Waarom heb je die 'GROUP BY' erbij gezet?

[curry684]

pedorus schreef op zondag 06 september 2009 @ 16:35:
Dit is niet waar.

Ja want een Certification Developer van het MySQL Professional Services Team staat op dev.mysql.com natuurlijk helemaal niet voor eigen kerk te preken, in een absoluut meelijwekkend artikel waarin ie idioot krom gedrag recht probeert te praten

[Creepy]

Uit het artikel dat perorus linkt:
In MySQL, one can write GROUP BY queries that reference non-aggregated columns in the SELECT list that are not included in the GROUP BY clause, even if these columns are not functionally dependent upon the GROUP BY clause. This behaviour conforms to none of the SQL standard's versions. It is possible to avoid this behaviour by including ONLY_FULL_GROUP_BY in the sql_mode server setting, but it might make more sense to take advantage of the ability to write only partial GROUP BY clauses.

Aka: ze geven zelf al toe dat geen enkele SQL standaard dit toelaat. Daarnaast meld hij ook dat als een column die niet in de group by is opgenomen niet afhankelijk is van een kolom die wel in de group by is opgenomen je random resultaten terugkrijgt. Ik zie hier in PRG echter wel met enige regelmaat er vragen of problemen over voorbij komen omdat mensen niet de juiste data terugkrijgen i.c.m. met een aggragated function. Ik zie echt niet in hoe je dat goed kan praten

[ Voor 9% gewijzigd door Creepy op 08-09-2009 13:35 ]

[pedorus]

Verwijderd schreef op dinsdag 08 september 2009 @ 12:36:
Waarom heb je die 'GROUP BY' erbij gezet?

Ik denk vanwege het starten met een generalisatie:
"Geef alle artiesten (id, title) en geef aan (1/0) of ze gekoppeld zijn aan een kalendaritem."

Als ik er opnieuw over denk, zou ik dat in SQL trouwens zoiets opschrijven:

SQL:

select artist_id, artist_title, 
    (artist_id in (select artist_id from calendaritem__artist)) as is_linked 
    from artists

Maar met MySQL krijg je dan al snel een trage dependant subquery. Dus uitgegaan van het voorbeeld in de TS krijg je dan zoiets:

SQL:

select id, title, (count(calendaritem_id)>0) as is_linked 
    from artist a left join calendaritem__artist c on a.id = c.artist_id 
    group by id order by title 

Ondanks dat title niet in de group by staat, staat deze vast vanwege de functionele afhankelijkheid id->title. MySQL voldoet in dat opzicht aan de standaard, en staat toe om geen aggregate-functie te gebruiken Ik voeg title hier dan ook niet toe aan de group by-clause, omdat dat het alleen maar trager maakt.

Overigens kan met gebruik van niet standaard-functionaliteit dit ook als "not calendaritem_id is null" geschreven worden, maar ongedefinieerde functionaliteit gebruiken is lastig te verkopen als de winst nihil is.

Nu wil TS het specifieker:
"Geef alle artiesten (id, title) en geef aan (1/0) of ze gekoppeld zijn aan een kalendaritem."
wordt
"Geef alle artiesten (id, title) en geef aan (1/0) of ze gekoppeld zijn aan kalendaritem x"
Dan is het al snel vrij logisch om die group by onnodig te laten staan en een where-clause toe te voegen...

[.oisyn]

curry684 schreef op dinsdag 08 september 2009 @ 13:01:
[...]

Ja want een Certification Developer van het MySQL Professional Services Team staat op dev.mysql.com natuurlijk helemaal niet voor eigen kerk te preken, in een absoluut meelijwekkend artikel waarin ie idioot krom gedrag recht probeert te praten

Sowieso, de daadwerkelijke kern van het artikel is dat de opmerking "je mag alleen selecten wat in de group by clause staat, of waarop geaggregeerd is" technisch gezien niet helemaal juist is, omdat je ook mag selecteren op kolommen die functioneel afhankelijk zijn. En daarbij gaat hij voor het gemak maar even compleet voorbij gaat aan het feit dat menig mysql gebruiker ook selecteert op kolommen die niet functioneel afhankelijk zijn en daardoor *terecht* de (technisch ietwat verkeerd geformuleerde) opmerking naar hun hoofd krijgen, wat komt doordat MySQL het al jaren fout doet maar in de gevallen waarin ze wel functioneel afhankelijk zijn *toevallig* goed doet.

[NMe]

Creepy schreef op dinsdag 08 september 2009 @ 13:33:
[...]
Ik zie echt niet in hoe je dat goed kan praten

Ik zie nog wat veel mooiers in dat artikel:

Of course, it would be nice if MySQL was smart enough to deduce that the result cannot possibly be different due to including the title column in the GROUP BY list. It could attempt to detect that a key of the film table is included in the GROUP BY list and that the title column can be completely ignored when evaluating the GROUP BY clause, because there will be exactly one value in the title column for each film_id column. But, then again, MySQL does not require us to write a full group by list. So if performance is paramount, be smart and do not write a full GROUP BY clause.

"Ja, het zou mooi zijn als MySQL wat slimmer was en het allemaal goed af zou handelen....maar in plaats daarvan accepteren we SQL die unpredictable results oplevert wel als workaround voor performantere code!"

[pedorus]

Mm, bij "But, then again..." gaat er iets fout. Daar had natuurlijk moeten staan: "Therefore, MySQL is working hard to implement this." En dan niet zozeer om een volledige group by sneller te maken, maar om in debug- en beginners-modus te checken of queries zonder volledige group by goed zijn. Dat ze er niet mee bezig zijn kan eigenlijk alleen maar zijn om de MySQL-bashers het makkelijk te maken...

[Alain]

NMe: Als je weet waar de gaatjes zitten, dan kun je er prima mee werken.
.oisyn: Tja, dat geldt ook voor vrouwen, maar dat wil nog niet zeggen dat ze niet af en toe het bloed onder je nagels vandaan halen.

er wordt zoveeeel gezeurd over deze issue in MySQL ...

Het is fout gedrag en je moet er rekening mee houden. Ik ben bezig met een SCADA pakket en de fouten die ik 5 jaar geleden gemeld heb, zitten er nog steeds in.

Live with it, of neem een rdms die wel doet wat je verwacht.

[NMe]

Ik zeur niet over de issue (althans, niet hier ) maar ik zeur over het feit dat ze ontkennen dat het überhaupt een issue is door te doen alsof het een handige feature is. Die vervolgens in 99 van de 100 gevallen random data gaat opleveren.

[curry684]

NMe schreef op dinsdag 08 september 2009 @ 22:16:
Ik zeur niet over de issue (althans, niet hier ) maar ik zeur over het feit dat ze ontkennen dat het überhaupt een issue is door te doen alsof het een handige feature is. Die vervolgens in 99 van de 100 gevallen random data gaat opleveren.

Niet random he, unpredictable

[Alain]

NMe schreef op dinsdag 08 september 2009 @ 22:16:
Die vervolgens in 99 van de 100 gevallen random data gaat opleveren.

Volgens mij levert dit in 99 van de 100 gevallen op wat er verwacht wordt, maar het is niet betrouwbaar. Vaak als deze fout gemaakt wordt is het veld functioneel afhankelijk. Ondanks dat het fout is, levert het wel het gewenste resultaat op.

[NMe]

curry684 schreef op dinsdag 08 september 2009 @ 22:51:
[...]

Niet random he, unpredictable

True, uiteraard. Al is een random number generator die niet eerst geseed wordt daarmee ook een unpredictable number generator volgens dezelfde logica.

AlainS schreef op dinsdag 08 september 2009 @ 22:58:
[...]

Volgens mij levert dit in 99 van de 100 gevallen op wat er verwacht wordt, maar het is niet betrouwbaar. Vaak als deze fout gemaakt wordt is het veld functioneel afhankelijk. Ondanks dat het fout is, levert het wel het gewenste resultaat op.

Kun je mij een praktijkvoorbeeld noemen waar twee velden inderdaad functioneel afhankelijk zijn van elkaar maar je geen aggregate nodig hebt om te bepalen welke waarde je in de gegroepeerde row wil zien?

[curry684]

NMe schreef op dinsdag 08 september 2009 @ 23:14:
[...]

True, uiteraard. Al is een random number generator die niet eerst geseed wordt daarmee ook een unpredictable number generator volgens dezelfde logica.

Nou nee ervan uitgaande dat een unseeded generator een constant seed gebruikt is het een predictable number generator

Kun je mij een praktijkvoorbeeld noemen waar twee velden inderdaad functioneel afhankelijk zijn van elkaar maar je geen aggregate nodig hebt om te bepalen welke waarde je in de gegroepeerde row wil zien?

Die's niet zo moeilijk hoor, ontstaat voornamelijk bij joins.

SQL:

SELECT count(c.CustomerId), cs.CustomerStatusId, cs.CustomerStatusName
FROM Customer c
JOIN CustomerStatus cs ON cs.CustomerStatusId = c.CustomerStatusId
GROUP BY cs.CustomerStatusId

CustomerStatusName is in deze overbodig in de GROUP BY omdat er geen enkele situatie denkbaar is waar er geen 1-op-1 relatie is tussen CustomerStatusId en CustomerStatusName.

[NMe]

curry684 schreef op dinsdag 08 september 2009 @ 23:22:
[...]

Nou nee ervan uitgaande dat een unseeded generator een constant seed gebruikt is het een predictable number generator

Hangt ervanaf of die constante seed system defined is of niet.

Die's niet zo moeilijk hoor, ontstaat voornamelijk bij joins.

SQL:

SELECT count(c.CustomerId), cs.CustomerStatusId, cs.CustomerStatusName
FROM Customer c
JOIN CustomerStatus cs ON cs.CustomerStatusId = c.CustomerStatusId
GROUP BY CustomerStatusId

CustomerStatusName is in deze overbodig in de GROUP BY omdat er geen enkele situatie denkbaar is waar er geen 1-op-1 relatie is tussen CustomerStatusId en CustomerStatusName.

Hmm, ok, duidelijk. Maar die zou dus niet voorkomen als je bijvoorbeeld de CustomerName ook zou willen opvragen; c.CustomerName zou dan unpredictable zijn. Maar goed, dat klinkt allemaal als verdedigbaar gedrag, alleen jammer dat ze het om de verkeerde redenen op die manier supporten.

[curry684]

NMe schreef op dinsdag 08 september 2009 @ 23:36:
[...]

Hangt ervanaf of die constante seed system defined is of niet.

Volgens mij is een undefined constante een contradictio in terminis?