MySQL a sql_mode vs gridy

ondrej256
Člen | 175
+
+1
-

Ahoj,

už se mě vícekrát stalo, že jsem na nějakém projektu musel měnit sql_mode aby mně fungovalo zobrazení nějakého datagridu. Uvedu příklad sql dotazu, který používám v gridu:

SELECT customer_id, firstname, sum(amount) AS soucet_pohybu
FROM credit_move
GROUP BY customer_id

Tenhle dotaz ve striktnějším sql módu spadne, protože v selectu smím uvést pouze sloupec který je v GROUP BY nebo tam můžu dát agregační funkci (např. SUM), já tam mám uvedeny taky sloupec firstname, což není ani v group by ani to neni agregační funkce a tím pádem databáze takový dotaz neprovede. Takže nejjednoušší a zároveň asi nejméně správné řešení je nastavit prázdný sql_mode

set global sql_mode='';
set session sql_mode='';

Tím pádem mně všechny gridy fungují. No ale teď nevím jak bych to měl řešit správně.

Předpokládám, že z pohledu databázisty je správné řešení na sql_mode nesahat, vytáhnout si v tom dotazu jen idcko a soucet, následně dotaz obalit dalším dotazem kde provedu vytažení sloupce firstname a přidám k výsledku. Jenže to je při programování docela nepohodlný způsob, když jde jen změnit sql_mode a všechno funguje jak má.

Tak jsem se chtěl zeptat jak podobné problémy řešíte vy a a zda je velký hřích si upravit sql_mode na nějakou benevolentnější variantu

Editoval ondrej256 (17. 9. 2021 9:35)

Michal Kumžák
Člen | 106
+
0
-

Když se podíváš jaké módy máš nastavené, je mezi nimi sqlmode_only_full_group_by? Tipuji, že to dělá právě tento mód.

https://dev.mysql.com/…ql-mode.html#…

ondrej256
Člen | 175
+
+1
-

No možná jsem se špatně zeptal, ale neřeším který sql mód to dělá, ale spíš je to taková filozofická otázka, zda se mám cítit provinile, že si upravuju sql_mode na něco méně striktního, nebo se to prostě dělá běžně a nic špatného na tom není?

Michal Kumžák
Člen | 106
+
0
-

Natolik do toho nevidím, ale s bez tohoto módu se mi mysql standardně instaluje. Osobně bych si ten mód vypnul, protože je to dost restriktivní a hlavně omezující.

SankaCoffee
Člen | 7
+
0
-

Nevím jestli je to úplně OK řešení, ale v případě potřeby tento problém obcházím pomocí MAX(firstname). Databáze si na nic nestěžuje, získám co potřebuji a s tabulkami do milionu záznamů mi vše běhalo celkem svižně.

Editoval SankaCoffee (20. 9. 2021 10:41)

Michal Kumžák
Člen | 106
+
+1
-

Jen se možná blbě zeptám, ten firstname máš v té samé tabulce co agreguješ? Pokud ne, tak pak bych možná pak upravil SQL dotaz asi nějak takto:

SELECT c.firstname, t.customer_id, t.soucet_pohybu
FROM customer AS c
LEFT JOIN (
	SELECT customer_id, sum(amount) AS soucet_pohybu
	FROM credit_move
	GROUP BY customer_id) AS t ON t.customer_id=c.id

Tohle už ti ten mód pustí.

Kamil Valenta
Člen | 562
+
0
-

SankaCoffee napsal(a):

Nevím jestli je to úplně OK řešení, ale v případě potřeby tento problém obcházím pomocí MAX(firstname). Databáze si na nic nestěžuje, získám co potřebuji a s tabulkami do milionu záznamů mi vše běhalo celkem svižně.

Nebo ANY_VALUE(firstname), protože MAX() mi u nečíselných hodnot nikdy nedávalo smysl. Ale vlastně jsem nedělal test, co je rychlejší…

Editoval Kamil Valenta (20. 9. 2021 11:06)

jiri.pudil
Nette Blogger | 1008
+
+4
-

ONLY_FULL_GROUP_BY chceš mít zapnutý, jako je to ostatně by default od MySQL 5.7.5. Třeba v takovém PostgreSQL se tohle chování ani vypnout nedá.

Pokud ti kvůli tomu padají dotazy v nějakém gridu, je to buďto bug toho gridu, anebo špatně navržená databáze. Přiznám se, že taky moc nechápu, proč je v tabulce credit_move nějaké křestní jméno.

Pokud tam totiž skutečně je, představ si, že máš v té tabulce takováhle data:

customer_id firstname amount
1 Jan 42
1 Petr 69

Pokud groupuješ jenom podle customer_id, pro hodnotu 1 máš najednou dvě různé hodnoty firstname a databáze neví, kterou z nich si má vybrat. Proto vyžaduje, abys groupoval i podle firstname.


Pokud máš firstname v tabulce se zákazníky, jak naznačil Michal výše, měl by ses obejít bez dalšího groupování i bez subquery. Pokud totiž groupuješ podle primárního klíče (nebo třeba non-null unique indexu), databáze si dokáže spočítat funkční závislosti a pozná, že pro každé c.id může existovat jenom jedna unikátní hodnota firstname:

SELECT c.id, c.firstname, SUM(t.amount)
FROM customer AS c
LEFT JOIN credit_move AS t ON c.id = t.customer_id
GROUP BY c.id