[SQL] INSERT INTO [...] SELECT, met een extra count-kolom?

[Booster]

In een testomgeving, wil ik graag een database vullen met testdata uit een andere tabel.

Brontabel is een tabel met een log van ingelogde gebruikers. Doel is om op basis van die tabel de 30 meest ingelogde gebruikers te selecteren, en die in te voegen in de tabel van de testdatabase.

Het maken van een top 30 is niet al te moeilijk:

SQL:

SELECT
    log.userid,
    log.a
    log.b
    COUNT(log.userid) AS id_occurance_count,
FROM
    db2.logdata AS log
GROUP BY
    log.userid
ORDER BY
    id_occurance_count
DESC
LIMIT
    30;

Dit levert een resultset op met 4 kolommen. De laatste kolom bevat een nummer dat aangeeft hoe vaak een bepaald User ID voorkwam in de log. Hierop kan ik vervolgens weer sorteren om een top 30 te maken.

Nu wil ik de output van deze query graag weer gebruiken, om een INSERT te doen op een andere tabel.

SQL:

INSERT INTO
    db1.users (id, a, b)
SELECT
    log.userid,
    log.a
    log.b
    COUNT(log.userid) AS id_occurance_count,
FROM
    db2.logdata AS log
GROUP BY
    log.userid
ORDER BY
    id_occurance_count
DESC
LIMIT
    30;

Probleem: MySQL vind dat de SELECT een kolom teveel heeft. Dat klopt opzich. Hoe kan ik toch een top 30 maken aan de hand van die telling, zonder dat MySQL denkt dat ik de waarde van die kolom ergens kwijt moet in de INSERT?

Limitaties: het is de bedoeling dat het een enkele query blijft, en niet twee queries met wat scripting ertussen.

[NMe]

Zoiets?

SQL:

INSERT INTO
    db1.users (id, a, b)
SELECT
    tmp.userid,
    tmp.a
    tmp.b
FROM
(
    SELECT
        log.userid,
        log.a
        log.b
        COUNT(log.userid) AS id_occurance_count,
    FROM
        db2.logdata AS log
    GROUP BY
        log.userid
    ORDER BY
        id_occurance_count
    DESC
    LIMIT
        30
) AS tmp

[Booster]

Aha, slim. Volgensmij lost dit mijn punt inderdaad op. Ga het zo even testen, thanks

edit:
Werkt als een trein.

[ Voor 17% gewijzigd door Booster op 10-02-2015 18:45 ]

[Verwijderd]

is de group by wel compleet? ik weet dat het niet afgedwongen wordt bij elk dbms, maar toch

[chime]

Waarom niet gewoon die count laten vallen?

Draagt toch niks bij aan die insert - het is niet dat die door die count gaat groeperen op userid, dat doet je group by statement al.

[Dido]

Ten eerste is een count(kolomnaam) niet nodig, gewoon een count(*).

Ten tweede, maak je group by volledig. Als je ooit met een echt DBMS komt te werken zul je me dankbaar zijn.

Ten derde, en dat is het echt antwoord op je vraag, je hoeft helemaal niet op een kolom te selecteren die in je SELECT zit!

code:

SELECT
    log.userid
   ,log.a
   ,log.b
FROM
    db2.logdata AS log
GROUP BY
    log.userid
   ,log.a
   ,log.b
ORDER BY
    count(*) DESC
LIMIT
    30;

[kenneth]

Hoe verwerkt MySQL zo'n brakke query? Wordt dat een impliciete group by op log.a en log.b

[Voutloos]

Nee, random waardes voor die specifieke kolommen.

En er was al een eeuwigheid een stricte optie voor, en sinds heel recent werkt die optie nog heel sexy ook. Uiteraard blijft het falen met unsafe defaults... maar he.

[ Voor 62% gewijzigd door Voutloos op 11-02-2015 10:10 ]

[NMe]

Voutloos schreef op woensdag 11 februari 2015 @ 10:00:
Nee, random waardes voor die specifieke kolommen.

Zo random is dat in dit geval niet want zowel userid, a en b komen uit hetzelfde record. Je krijgt dus gewoon de a en b die bij het record horen dat je door die order geselecteerd hebt. Ik weet zelf prima hoe een group by hoort maar als ik met native MySQL werk laat ik voor lange selects de group by clause vaak wat korter waar dat kan.

Chime: die count is nodig om te bepalen welke 30 records geselecteerd worden. De volgorde interesseert vast niet.

Dido: wat is volgens jou het voordeel van count(*) over count(kolomnaam)? If anything is count(*) trager.

[Voutloos]

NMe schreef op woensdag 11 februari 2015 @ 10:36:
[...]

Zo random is dat in dit geval niet want zowel userid, a en b komen uit hetzelfde record. Je krijgt dus gewoon de a en b die bij het record horen dat je door die order geselecteerd hebt. Ik weet zelf prima hoe een group by hoort maar als ik met native MySQL werk laat ik voor lange selects de group by clause vaak wat korter waar dat kan.

Ik denk dat iedereen er vanuit gaat dat een user meerdere log entries heeft. Anders kan je die count() ook wel als 1 hardcoden. Dus ja, 't is non deterministic.

[NMe]

Ik had de query niet goed gelezen, my bad. Je hebt gelijk, je hebt zo geen idee wat a en b zijn tenzij die voor elk user id steeds hetzelfde zijn... En in dat geval horen ze waarschijnlijk niet in die tabel thuis.

[Dido]

NMe schreef op woensdag 11 februari 2015 @ 10:36:
Dido: wat is volgens jou het voordeel van count(*) over count(kolomnaam)? If anything is count(*) trager.

Voordeel van * is dat je minder kans hebt op rare resultaten, wat je wel hebt met count(kolomnaam)

Count(1) is op zich een leuk alternatief overigens:
http://www.sqlservercentral.com/articles/T-SQL/102474/

Het punt is dat ik vaak merk dat mensen daadwerkelijk semantische beteknis toekennen aan de kolomnaam die ze opgeven, waardoor ze over het hoofd zien dat ze records tellen en geen kolommen. Dat is op zich niet zo'n groot probleem, maar het is een symptoom dat ik vaak zie in combinatie met onvolledige group_by's, verwrongen joins of een overdaad an subqueries.

[Voutloos]

En anders mbt null values etc. Gewoon lekker altijd COUNT(*) doen, _tenzij_ je weet waarom je het per se anders wil. Andere vuistregels voor mysql zijn achterhaald en ik zou ook eigenlijk niet weten op welk performance voordeel er anno 2015 gedoeld wordt.

[whoami]

kenneth schreef op woensdag 11 februari 2015 @ 09:56:
Hoe verwerkt MySQL zo'n brakke query? Wordt dat een impliciete group by op log.a en log.b

Dat, en daarnaast:
Het aantal kolommen in de select clause moet toch matchen met het aantal kolommen gespecifieerd in de insert clause ?

[NMe]

whoami schreef op woensdag 11 februari 2015 @ 15:42:
[...]

Dat, en daarnaast:
Het aantal kolommen in de select clause moet toch matchen met het aantal kolommen gespecifieerd in de insert clause ?

Ja, maar dat was juist het probleem van de TS.

[.oisyn]

whoami schreef op woensdag 11 februari 2015 @ 15:42:
[...]

Dat

Nee!

Bij de volgende data:

A1  B1  C1
A1  B1  C2
A2  B1  C1
A2  B1  C2
A2  B2  C1
A2  B2  C2

Zal een SELECT * FROM table GROUP BY a in MySQL twee rijen uitvoeren. Als je óók nog zou groeperen op b en c, dan krijg je 6 rijen. Als je alleen op a groepeert, worden de waardes van b en c gehaald uit willekeurige rijen waar de groep aan voldoet.

Ik weet alleen niet of het gedefinieerd is of de waardes van b en c wel uit dezelfde rij komen, of dat dat ook nog eens verschillend kan zijn.

[ Voor 4% gewijzigd door .oisyn op 11-02-2015 15:48 ]

[NMe]

Voor zover mij bekend: de waardes van b en c komen uit dezelfde rij, maar wélke rij is undefined.

[Gomez12]

NMe schreef op woensdag 11 februari 2015 @ 16:08:
Voor zover mij bekend: de waardes van b en c komen uit dezelfde rij, maar wélke rij is undefined.

Het probleem met die gedachtengang is alleen dat het leuk werkt op simpele dingen (/1 tabel).

Maar dat het extreem moeilijk uit te leggen wordt op het moment dat je meerdere tabellen erbij gaat betrekken want dan wordt het waardes uit dezelfde rij binnen dezelfde tabel en het is niet zozeer dat het undefined is welke rij, technisch is dat best defined alleen het is niet echt makkelijk eenduidig uit te leggen aan een gebruiker.

Waardoor je resultaat 1 kan krijgen 2 weken lang op dezelfde database en dan doe je bijv een analyze actie, of je voegt een index toe ergens en opeens krijg je een ander resultaat (en met meerdere gejoinde tabellen kan je nog steeds 90% hetzelfde resultaat krijgen en dat er maar 1 kolom anders is)

In de praktijk heb ik altijd zoiets van : Dit principe bevat zoveel cave-ats en dingen waar je rekening mee moet houden dan maar liever de standaard volgen en alles wat relevant is inde group by gooien.

[Gomez12]

Dido schreef op woensdag 11 februari 2015 @ 15:16:
[...]
Voordeel van * is dat je minder kans hebt op rare resultaten, wat je wel hebt met count(kolomnaam)

Tja, persoonlijk heb ik altijd zoiets van : * geeft me juist rare resultaten, kolomnaam is tenminste duidelijk (het telt alle relevante ingevulde gegevens in die kolom en tja daar hoort NULL niet bij, wil ik de totalen weten dan tel ik PK's wel)

Plus dat ik me heel erg sterk afvraag hoe een "normaal" rdbms omgaat met * qua optimalisaties. Want met een kolomnaam kan je een kolom kiezen die onderdeel is van een index (zodat de data nooit gelezen hoeft te worden maar enkel de index)

[.oisyn]

Gomez12 schreef op woensdag 11 februari 2015 @ 18:45:
en het is niet zozeer dat het undefined is welke rij, technisch is dat best defined

Nogal wiedes, software is nou eenmaal deterministisch . Waar het om gaat als iets "undefined" is, dat het niet in de specificatie gedefiniëerd is (of eigenlijk expliciet ongedefiniëerd wordt gelaten). Dat betekent niet dat het niet deterministisch zou zijn, maar wel dat het onverstandig is er gedrag van af te laten hangen omdat het bij een volgende versie misschien een totaal andere uitkomst heeft. Aangezien er niets is gedefinieerd, wordt er ook geen moeite gedaan om output consistent te houden.

Dat is zeg maar het tegenovergestelde van hoe het bij PHP werkt, waar de implementatie de specificatie dicteert

[ Voor 19% gewijzigd door .oisyn op 11-02-2015 19:13 ]

[whoami]

Gomez12 schreef op woensdag 11 februari 2015 @ 18:53:
[...]

Plus dat ik me heel erg sterk afvraag hoe een "normaal" rdbms omgaat met * qua optimalisaties. Want met een kolomnaam kan je een kolom kiezen die onderdeel is van een index (zodat de data nooit gelezen hoeft te worden maar enkel de index)

Dat zou met een count(*) ook moeten kunnen. De engine kan zelfs kiezen.

[whoami]

.oisyn schreef op woensdag 11 februari 2015 @ 15:47:
[...]

Nee!

Bij de volgende data:

A1  B1  C1
A1  B1  C2
A2  B1  C1
A2  B1  C2
A2  B2  C1
A2  B2  C2

Zal een SELECT * FROM table GROUP BY a in MySQL twee rijen uitvoeren. Als je óók nog zou groeperen op b en c, dan krijg je 6 rijen. Als je alleen op a groepeert, worden de waardes van b en c gehaald uit willekeurige rijen waar de groep aan voldoet.

Ik weet alleen niet of het gedefinieerd is of de waardes van b en c wel uit dezelfde rij komen, of dat dat ook nog eens verschillend kan zijn.

Ik blijf dat een verkeerd gebruik van group by vinden. Dat het in mysql kan wil nog niet zeggen dat het juist is.
Wat ben je met een dergelijke resultset en waarom zou je dat überhaupt willen gebruiken?

[pedorus]

Voor de discussie over group by zie http://rpbouman.blogspot....-respects-functional.html

Mij lijkt Dido in "[SQL] INSERT INTO \[...] SELECT, met een extra count-kolom?" het juiste antwoord. Mits er een functionele dependency is klopt de group by, en anders breekt het vanaf 5.7.5 met de default settings.

[NMe]

whoami schreef op woensdag 11 februari 2015 @ 23:31:
[...]

Ik blijf dat een verkeerd gebruik van group by vinden. Dat het in mysql kan wil nog niet zeggen dat het juist is.
Wat ben je met een dergelijke resultset en waarom zou je dat überhaupt willen gebruiken?

Het idee is dat je het gebruikt waar b en c altijd gelijk zijn voor elke a. Iets als dit schijft lekker makkelijk weg:

SQL:

SELECT u.*, COUNT(a.*) AS Amount
FROM User u
JOIN AndereTabel a ON u.Id = a.UserId
GROUP BY u.Id

Het boeit dan niet dat een user ook een voornaam, een achternaam en een e-mailadres heeft. Je hebt ook geen functie nodig om te selecteren wélke van de weggeaggregeerde records je nodig hebt omdat de voornaam, achternaam en het e-mailadres toch per ID steeds gelijk zijn. In plaats van de hele field list van de User-tabel nog eens te herhalen kun je dus volstaan met een group op alleen het ID.

Of je dat mooi vindt of niet is persoonlijk denk ik. Zelf wil ik het nog wel eens gebruiken wanneer ik echt puur met native MySQL bezig ben, maar sinds ik eigenlijk al mijn queries met Doctrine opbouw zou ik het niet meer kunnen zelfs als ik het zou willen. Linksom of rechtsom: als je niet weet wat je doet en waarom je het doet kun je maar beter van die shorthand afblijven.

[Gomez12]

@NMe : Wat jij bedoelt is exact wat er in pedorus zijn link staat (en is blijkbaar onderdeel van de SQL99 standaard) en wat MySQL nu echt ondersteunt.

Het constante gezeur met MySQL is juist over wat er voor 5.7.5 inzat, namelijk dat je random iets terugkrijgt en dat het helemaal niet aan jouw voorbeeld voldoet.

Grofweg gezegd : Voor 5.7.5 (en waar de discussie altijd over is) :

SQL:

SELECT u.*, COUNT(a.*) AS Amount
FROM User u
JOIN AndereTabel a ON u.Id = a.UserId
GROUP BY u.FirstName

waarbij FirstName dus niet uniek is en er niet valt te voorspellen welke rij er uit u gepakt wordt en dus welke Amount er gaat komen

Met 5.7.5 kan dit dus niet meer (by default) maar je kan nog wel jouw voorbeeld doen.

[.oisyn]

Gomez12 schreef op donderdag 12 februari 2015 @ 01:36:
@NMe : Wat jij bedoelt is exact wat er in pedorus zijn link staat (en is blijkbaar onderdeel van de SQL99 standaard) en wat MySQL nu echt ondersteunt.

Wat MySQL altijd al ondersteund heeft, met daarnaast nog queries die volgens SQL99 niet zijn toegestaan.

Het constante gezeur met MySQL is juist over wat er voor 5.7.5 inzat, namelijk dat je random iets terugkrijgt en dat het helemaal niet aan jouw voorbeeld voldoet.

Dat is niet helemaal waar. Het gezeur ging over het selecteren van non-aggregates die niet in de GROUP BY staan in het algemeen. Want andere RDBMS'en konden dat ook niet (want SQL92), en is stom dat MySQL dat wel kan. Dat MySQL stiekem daardoor wel compatible is met SQL99 terwijl andere RDBMS'en dat niet zijn werd voor het gemak maar even vergeten, aangezien met de feature van MySQL er een superset van ondersteund werd. Maar dat je jezelf als gebruiker in je voet kunt schieten omdat de support breder is dan louter functional dependencies is daar imho ondergeschikt aan.

[cariolive23]

.oisyn schreef op donderdag 12 februari 2015 @ 09:56:
Dat MySQL stiekem daardoor wel compatible is met SQL99 terwijl andere RDBMS'en dat niet zijn ...

MySQL was tot versie 5.7.5 (september 2014) helemaal niet compatible, er wordt in oudere versies nergens geëist dat de GROUP BY op de primary key betrekking moet hebben. Zo geeft Gomez12 een voorbeeld met GROUP BY u.FirstName die gewoon fout is, en NMe geeft een voorbeeld met GROUP BY u.Id die best wel eens correct zou kunnen zijn.

In PostgreSQL (sinds versie 9.1, september 2011) is de GROUP BY u.Idgewoon toegestaan, mits de overige velden functioneel afhankelijk zijn van het veld in de GROUP BY:
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

Het is dus geen gezeur, het is gewoon een feit dat MySQL pas sinds versie 5.7.5 SQL99-compliant is met de GROUP BY. En ook dat MySQL niet de eerste was, ze waren 3 jaar later dan PostgreSQL

[.oisyn]

cariolive23 schreef op donderdag 12 februari 2015 @ 11:27:
[...]

MySQL was tot versie 5.7.5 (september 2014) helemaal niet compatible, er wordt in oudere versies nergens geëist dat de GROUP BY op de primary key betrekking moet hebben. Zo geeft Gomez12 een voorbeeld met GROUP BY u.FirstName die gewoon fout is, en NMe geeft een voorbeeld met GROUP BY u.Id die best wel eens correct zou kunnen zijn.

Misschien nog eens mijn post lezen, en dan met name het gedeelte waarin ik zeg dat MySQL een superset van SQL99 implementeert. Dat is wat compatibiliteit impliceert: alle queries die voldoen aan SQL99 (wat dit onderwerp betreft), werken in MySQL voor 5.7.5. SQL92 zegt dat je alleen non-aggregates uit de GROUP BY in je SELECT mag hebben. SQL99 zegt dat je ook andere non-aggregates mag gebruiken, mits ze functionallly dependent zijn op data in de GROUP BY. MySQL (voor 5.7.5) zegt dat je álles mag selecteren (alles wat volgens SQL99 mag valt daar dus ook onder!). Het gedrag is echter alleen niet gedefinieerd als ze niet functionally dependent zijn.

Laten we vooropstellen dat iets wel mogen belangrijker is dan iets niet mogen. MySQL deed voor 5.7.5 simpelweg een tradeoff - het feit dat je dingen ook fout kunt doen wordt voor lief genomen, omdat je door de "feature" ook dingen kunt doen die wél goed zijn. Of je de feature goed of fout gebruikt ligt bij de eindgebruiker, niet bij de software. Ik vind daar op zich helemaal niets mis mee.

[ Voor 5% gewijzigd door .oisyn op 12-02-2015 11:52 ]

[NMe]

cariolive23 schreef op donderdag 12 februari 2015 @ 11:27:
[...]

MySQL was tot versie 5.7.5 (september 2014) helemaal niet compatible, er wordt in oudere versies nergens geëist dat de GROUP BY op de primary key betrekking moet hebben. Zo geeft Gomez12 een voorbeeld met GROUP BY u.FirstName die gewoon fout is, en NMe geeft een voorbeeld met GROUP BY u.Id die best wel eens correct zou kunnen zijn.

Dat is precies wat .oisyn zegt: het ondersteunde in dit geval SQL99 maar tegelijkertijd ook een rits constructies die niet onder die spec vallen en ronduit fout zijn. Toch roepen veel mensen dat ook mijn constructie hierboven ronduit fout is, terwijl die dus gewoon gespect is en gedefinieerd gedrag heeft, ook in MySQL. Dat deze nieuwe wijziging een goed idee is, daar zijn we het verder allemaal over eens.

.oisyn schreef op donderdag 12 februari 2015 @ 11:33:
Of je de feature goed of fout gebruikt ligt bij de eindgebruiker, niet bij de software. Ik vind daar op zich helemaal niets mis mee.

Echt mis is het niet, maar zeker voor een tool die door zo veel beginners gebruikt wordt vind ik het niet erg dat ze het nu gaan afdichten. Ik ben het wel met je eens dat het beter is om een feature te ondersteunen en daarbij ook wat foute dingen mogelijk te maken, dan om het domweg onmogelijk te maken/houden.

[ Voor 22% gewijzigd door NMe op 12-02-2015 11:35 ]

[cariolive23]

NMe schreef op donderdag 12 februari 2015 @ 11:33:
Ik ben het wel met je eens dat het beter is om een feature te ondersteunen en daarbij ook wat foute dingen mogelijk te maken, dan om het domweg onmogelijk te maken/houden.

Weet je het heel erg zeker dat dit een gewenste feature is? Ik kom 2 á 3 keer per maand bij een nieuwe klant over de vloer die gebruikt maakt van MySQL. Ik kan mij niet herinneren dat ik de afgelopen 5 jaar géén bugs heb gevonden rondom het gebruik van GROUP BY en dat betreft echt altijd het missen van noodzakelijke velden om tot een zinnig antwoord te komen. Het gaat dan vrijwel altijd om rapportages van flink wat data die dan ineens totaal andere resultaten opleveren maar die na wat uitzoekwerk ook ineens correcte en vooral betere resultaten opleveren.

Natuurlijk kun je naar de programmeur wijzen omdat hij/zij een fout heeft gemaakt, maar je kunt ook een query afkeuren waarvan de database vooraf al weet dat deze geen 100% correcte resultaten op kan leveren. Dit scheelt gewoon heel veel bugs, foute/minder goede beslissingen en ook tijd om bugs jaren later nog eens te gaan fixen.

Al levert het natuurlijk wel een goed belegde boterham op, wat ook niet verkeerd is

[NMe]

Ik ben van mening dat een taal/DBMS/whatever waar mogelijk inderdaad de programmeur moet helpen om geen fouten te maken, maar dat de programmeur alsnog zelf verantwoordelijk is voor het schrijven van goede code. Omdat ik het wél goed weet te gebruiken heb ik die feature liever wél erin zitten op een manier die enigszins flawed is dan dat 'ie er helemaal niet in zit. Dat minder competente developers er vervolgens door in de problemen komen is op professioneel vlak mijn probleem niet.

_{Hier op het forum wel natuurlijk.}

[Gomez12]

NMe schreef op donderdag 12 februari 2015 @ 12:28:
Omdat ik het wél goed weet te gebruiken heb ik die feature liever wél erin zitten op een manier die enigszins flawed is dan dat 'ie er helemaal niet in zit. Dat minder competente developers er vervolgens door in de problemen komen is op professioneel vlak mijn probleem niet.

Dan ben ik toch wel benieuwd naar wat jouw methode is om er niet door in de problemen te komen?

Vooropgesteld ik houd niet zo van undefined en onoverdraagbare code tussen RDBMS'en dus ik gebruikte hem sowieso al niet bewust.

Maar als ik het zo lees dan zie ik er geen werkwijze omheen te bouwen die me niet ooit in de problemen gaat brengen.
Ik kan mezelf strikt voorschrijven dat ik het enkel doe bij kolommen die momenteel gegarandeerd uniek zijn. Dat kan ik best bij aanvang doen, dat is geen probleem.
Dan documenteer ik ook nog redelijk wat. Alleen niet dat query x vertrouwd op het feit dat binnen tabel y de kolommen a/z gegarandeerd uniek moeten zijn.

Alleen als ik iets klaar heb dan berust "klein" onderhoud etc voornamelijk op het piepsysteem om de bugs te traceren, dat is het liefste een piep uit een unit-test en het ergste een gebruikers piep.

Binnen SQL99 werkt dat allemaal perfect, ik verwijder een unique constraint (omdat iets niet meer unique is) en er gaat ergens wel een unit-test piepen.
Alleen binnen jullie zogenaamde superset van SQL99 is het undefined of mijn unit-test gaat piepen, het is undefined of een gebruiker gaat piepen.
Alleen bij bijv jaarlijkse archiveeracties (of bij een disaster recovery actie) is de kans opeens erg groot dat er van alles gaat piepen wat 11 maanden heeft gezwegen over de fout, ga ik alle query's vergelijken in source control tot 24 maanden terug dan zie ik nog geen wijziging die enige piep veroorzaakt kan hebben.

Dan ben ik wel even bezig voordat ik aanbeland ben bij de change-scripts van de database van 11 maanden terug waar ik de unique constraint eraf heb gehaald.
En als ik die gelokaliseerd heb dan mag ik ook nog even alle query's sinds begin systeem nalopen om te zien of die wel of niet gebruikmaken van die superset functionaliteit van SQL99 en corrigeren, want de kans is vrij klein dat ik na 11 maanden die unique constraint weer terug kan plaatsen.

Wellicht ben ik een minder competente developer maar ik zie niet hoe ik hier omheen zou kunnen werken, dus ik ben erg benieuwd...

[gekkie]

cariolive23 schreef op donderdag 12 februari 2015 @ 12:25:
[...]
Natuurlijk kun je naar de programmeur wijzen omdat hij/zij een fout heeft gemaakt, maar je kunt ook een query afkeuren waarvan de database vooraf al weet dat deze geen 100% correcte resultaten op kan leveren. Dit scheelt gewoon heel veel bugs, foute/minder goede beslissingen en ook tijd om bugs jaren later nog eens te gaan fixen.

Mjah daarom liever postgres, dat sputtert tenminste hard tegen, voor random data gebruik je maar een random functie.
Helaas zijn er nog een hoop opensource projectjes die daar anders over denken.

[Gomez12]

gekkie schreef op donderdag 12 februari 2015 @ 23:10:
[...]
Mjah daarom liever postgres, dat sputtert tenminste hard tegen, voor random data gebruik je maar een random functie.
Helaas zijn er nog een hoop opensource projectjes die daar anders over denken.

Tja, liever gewoon een volwassen database met een volwassen toolset en een volwassen ondersteuning.

En 1 ding : Voor random data wil je geen random functie uit je db-laag pakken. DB's zijn zeer inefficient in random zaken

[gekkie]

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:19:
[...]
Tja, liever gewoon een volwassen database met een volwassen toolset en een volwassen ondersteuning.

En 1 ding : Voor random data wil je geen random functie uit je db-laag pakken. DB's zijn zeer inefficient in random zaken

Zolang ze de non-random case maar geoptimaliseerd hebben

[EfBe]

.oisyn schreef op donderdag 12 februari 2015 @ 11:33:
Laten we vooropstellen dat iets wel mogen belangrijker is dan iets niet mogen. MySQL deed voor 5.7.5 simpelweg een tradeoff - het feit dat je dingen ook fout kunt doen wordt voor lief genomen, omdat je door de "feature" ook dingen kunt doen die wél goed zijn. Of je de feature goed of fout gebruikt ligt bij de eindgebruiker, niet bij de software. Ik vind daar op zich helemaal niets mis mee.

Het is geen feature, het is gewoon onzin wat MySql deed: de velden die niet in de groupby/aggregates zitten maar wel in de projection, daar is niets mee te doen dus die worden genegeerd, welke dat zijn is undefined. Het is hetzelfde als distinct toestaan in een query met een orderby op een veld dat niet in de projection zit. Maar goed, waar hebben we het over... MySql, de database die transactions commit terwijl er fouten zijn opgetreden.

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:19:
[...]
En 1 ding : Voor random data wil je geen random functie uit je db-laag pakken. DB's zijn zeer inefficient in random zaken

order by newid() ?

[ Voor 12% gewijzigd door EfBe op 13-02-2015 08:49 ]

[Voutloos]

Gomez12 schreef op donderdag 12 februari 2015 @ 22:51:
Vooropgesteld ik houd niet zo van undefined en onoverdraagbare code tussen RDBMS'en dus ik gebruikte hem sowieso al niet bewust.

Prima toch? Dan schrijf je alles wel netjes in de group by en kan jij dus de al sinds 2006 beschikbare ONLY_FULL_GROUP_BY aanzetten.

En nu is die eindelijk slimmer/gebruiksvriendelijker.

Overigens is dat slimmere gedrag wel een enorm verbetering, want als je dat gebruikt kan jouw unittest vd aggregated code wel falen als je bv. unique constraint wijzigt.

Omgekeerd: Als je dus van oudsher alles in de group by zette, is dat ook niet per se handig, want dan is je query na constraint wijzigingen nog steeds sql technisch correct, maar wellicht niet meer wat je functioneel wil (!).

[NMe]

Gomez12 schreef op donderdag 12 februari 2015 @ 22:51:
[...]

Dan ben ik toch wel benieuwd naar wat jouw methode is om er niet door in de problemen te komen?

Om te beginnen: het alleen gebruiken in query's waar je niet-geaggregeerde resultaten uit een enkele tabel komen of uit twee tabellen die one to one met elkaar verbonden zijn. Verder bij voorkeur niet in een applicatie zelf gebruiken, hooguit in query's die je handmatig draait om je datastructuur te upgraden of iets dergelijks.

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:19:
[...]

Tja, liever gewoon een volwassen database met een volwassen toolset en een volwassen ondersteuning.

Helemaal eerlijk om MySQL tegenwoordig nog onvolwassen te noemen is het IMO ook weer niet.

EfBe schreef op vrijdag 13 februari 2015 @ 08:48:
[...]

Het is geen feature, het is gewoon onzin wat MySql deed: de velden die niet in de groupby/aggregates zitten maar wel in de projection, daar is niets mee te doen dus die worden genegeerd, welke dat zijn is undefined.

De feature is ook niet dat de foute constructies toegestaan waren maar dat de goeie constructies dat wel waren. Blijkbaar was er destijds een reden (arbitrair of niet, geen idee) om geen onderscheid te maken tussen goede en foute group by clauses. Ik heb dan liever dat ze de feature wel bouwen op een manier die ook te misbruiken valt dan dat de feature er niet in zit.

Maar goed, waar hebben we het over... MySql, de database die transactions commit terwijl er fouten zijn opgetreden.

He, wat?

[.oisyn]

Gomez12 schreef op donderdag 12 februari 2015 @ 22:51:
Wellicht ben ik een minder competente developer maar ik zie niet hoe ik hier omheen zou kunnen werken, dus ik ben erg benieuwd...

En de meest voordehandliggende oplossing, een unit test voor de uniciteit op die kolom, voldoet niet omdat...?

[Gomez12]

.oisyn schreef op vrijdag 13 februari 2015 @ 11:35:
[...]

En de meest voordehandliggende oplossing, een unit test voor de uniciteit op die kolom, voldoet niet omdat...?

Het kan, maar het is een niveau unit-testing wat ik niet gebruik omdat ik van mening ben dat het in de database specificatie hoort te zitten.

Ik kan ook wel unit-tests gaan schrijven of een PK wel echt uniek is, maar dat vind ik net zo zinloos (persoonlijke mening) vanwege het feit dat "normaliter" de database dit gewoon checkt en afvangt.

Ik heb zoiets van : Er is een soort van verdeling van verantwoordelijkheid en de integriteit van de data en de uitkomsten van de query's ligt bij de database en hoef ik niet te unit-testen (ok, krom omschreven maar ik hoop dat je begrijpt wat ik bedoel)

Wellicht dat ik het wel zou overwegen om er een unit-test voor te schrijven als ik het echt actief zou gebruiken, maar het zou 1 van de eerste unit-tests zijn (voor mij) die echt over data-integriteit zou gaan en dan ook nog eens specifiek voor 1 database.

Het kan, maar ik vind het niet echt lekker werken als werkwijze (op 1 rdbms kan ik me dan een beetje luiheid veroorloven als ik eerst een unit-test schrijf specifiek voor dat ene rdbms en als ik overstap naar een ander rdbms dan heb ik een uitdaging en dat allemaal voor een beetje luiheid, nee dank je)

[.oisyn]

Gomez12 schreef op vrijdag 13 februari 2015 @ 14:37:
[...]

Het kan, maar het is een niveau unit-testing wat ik niet gebruik omdat ik van mening ben dat het in de database specificatie hoort te zitten.

Dan is je hele verhaal irrelevant. Je stelling was dat je het moeilijk kón testen. Dat kan dus wel. Heel makkelijk

Ik kan ook wel unit-tests gaan schrijven of een PK wel echt uniek is, maar dat vind ik net zo zinloos (persoonlijke mening) vanwege het feit dat "normaliter" de database dit gewoon checkt en afvangt.

Het punt was niet of je wilde testen of een constraint wel werkt. Het punt was dat je wilde bewaken tegen een change waarbij je de constraint weghaalt, omdat de query dan nog wel werkt maar niet meer doet wat je verwacht. Op het moment dat je dergelijke queries in MySQL hebt en je ervoor wilt waken dat je niet per ongeluk de unique constraint van de kolom afhaalt (want dat was de case waarbij het fout ging), dan kun je daar een unit test voor maken die daarvoor waakt.

Het kan, maar ik vind het niet echt lekker werken als werkwijze

Oftewel een compleet andere insteek dan je eerdere post waarin oh boehoehoe alles stort in elkaar als je dat doet

Je post was niet "ik wil dit niet doen omdat..." maar "je kunt dit niet doen zonder in de problemen te geraken". Dat eerste behoeft wat mij betreft helemaal geen uitleg.

[ Voor 14% gewijzigd door .oisyn op 13-02-2015 15:46 ]

[Gomez12]

.oisyn schreef op vrijdag 13 februari 2015 @ 15:43:
[...]
Je post was niet "ik wil dit niet doen omdat..." maar "je kunt dit niet doen zonder in de problemen te geraken". Dat eerste behoeft wat mij betreft helemaal geen uitleg.

Dan zal ik mijn post wel rot in elkaar gezet hebben denk ik dan.

Mijn insteek was : Ik zie niet in hoe je dit kan doen zonder in de problemen te raken met een werkwijze en mijn werkwijze is als volgt...

Technisch gezien kan bijna alles zonder in de problemen te geraken. Alleen kost het ene je 1000 uur nazorg en het andere maar 10 uur bouwwerk. Het kan allebei, maar ik prefereer er maar eentje.

[.oisyn]

Maar dat is dus bullshit. Een test die ervoor zorgt dat je een unique constraint niet van een kolom af kunt halen kost niet 990 uur.

[Booster]

Je kijkt even niet, en plots zijn er tientallen reacties met feedback over je query
Ik zal de reacties straks nog even goed doorlezen om te zien wat jullie aanraden.

Ik houd mij nauwelijks bezig met SQL en queries (dat was al duidelijk ) en ik zet mijn queries dan vooral 'op gevoel' in elkaar. Dat is ook meteen de verklaring waarom mijn query/idee dingen mist die je wel zou verwachten in een goede of efficient opgezette SQL-query.

[cariolive23]

Dido schreef op woensdag 11 februari 2015 @ 15:16:
Voordeel van * is dat je minder kans hebt op rare resultaten, wat je wel hebt met count(kolomnaam)

Hoezo minder kans op rare resultaten? Het zijn 2 verschillende vragen, de bekende appels en peren. Niks raars aan dus, maar je moet wel het verschil tussen een appel en een peer kennen.

• COUNT(*): aantal records ongeacht de inhoud
• COUNT(1): aantal records ongeacht de inhoud, voor ieder record wordt de waarde 1 gebruikt
• COUNT(kolomnaam): aantal records die NOT NULL zijn

COUNT(kolomnaam) is dus echt een andere vraag, niet vergelijkbaar met COUNT(*) of COUNT(1), al kan het resultaat hetzelfde zijn. Het queryplan kan ook verschillen, afhankelijk van de bruikbare indexen en de gebruikte database.