mysql: Error 1111 bei Verwendung von UPDATE und CONCAT

[Flo]

Hallo,

mein SELECT funktioniert:

select 
CONCAT('Anfangstext ', 
SUM(TRIM(LENGTH( Tore)) - TRIM(LENGTH(REPLACE( Tore, ',', '' )))) +1, 
' Endtext', Spalte) 
from table 
where SpieleID = 1943 

// Gibt mir Anfangstext, Anzahl der Tore und Endtext aus

update table set     
    Spalte = 
    CONCAT('Anfangstext ',
    SUM(TRIM(LENGTH( Tore)) - TRIM(LENGTH(REPLACE( Tore, ',', '' )))) +1,
    ' Endtext', Spalte)
    where SpieleID = 1943 

// ergibt #1111 - Falsche Verwendung einer Gruppierungsfunktion

Wir brauchen über den Konstruktionsfehler der Spalte "Tore" nicht sprechen, der ist bekannt und wird gerade parallel behoben.

Dennoch, und weil das länger dauert, würde ich gerne den Update umsetzen.

Welche Gruppierungsfunktionen sind hier gemeint, die ich falsch verwende? Wo liegt hier der fehler, bzw. wie behebt man den?

Flo

[Rolf B]

Hallo Flo,

SUM ist eine Gruppierungsfunktion, genau, und kann als solche nur in einem SELECT verwendet werden. Ich frag mich nur was Du da eigentlich vorhast?

Ich versuche mal, den Select zu verstehen.

Da steht:

CONCAT('Anfangstext ', ...monster..., ' Endtext', Spalte)

Du baust also aus 4 Teilen einen String zusammen und gibst ihn als Ergebnis aus. Bzw. im Update-Statement möchtest Du daraus einen neuen Spaltenwert zusammenbauen.

Na gut, kann man machen - sollte man aber definitiv nur nach reiflicher Überlegung tun. Datenbanken haben die Aufgabe, Daten aufzubewahren und so zu speichern, dass man leicht damit hantieren kann. Die Aufbereitung für ein bestimmtes UI ist Sache der Anwendung, die die Datenbank nutzt. Genau das tust Du an dieser Stelle nicht. Hinzu kommt, dass Du auf diese Weise Redundanz erzeugst. Redundanz ist das Schmieröl der Datenbank, aber wehe, du vergisst dann diesen Schmiernippel irgendwann. Redundanzen baut man nur ein, wenn die DB andernfalls zu langsam wird. In deinem Fall wäre der redundante Wert das Ergebnis des Monsters, und die Einbettung in Text ist definitiv eine Aufgabe für die Anwendung, nicht für die Datenbank.

Und was tut das "Monster" im Concat? Es sieht so aus:

SUM(TRIM(LENGTH(Tore))-TRIM(LENGTH(REPLACE(Tore,',',''))))+1
               '----'                     '-----------'
        '------------'            '--------------------'
                           '----------------------------'
   '-----------------------------------------------------'

Dabei wird jedem respektablen SQL Server schlecht. Und dieser Ausdruck zeigt, dass Du Dich mit den Themen "Datentypen" dringend beschäftigen musst.

Tore

das ist mutmaßlich ein String. Und der kann Kommas enthalten. Nun gut. Du erwähntest den Konstruktionsfehler ja schon.

LENGTH(...)

das ist die Länge eines Strings, also eine Zahl. Du bestimmst einmal die Stringlänge und einmal die Stringlänge ohne die Kommas. Aha. Soso. Der Konstruktionsfehler beginnt, sein Wesen zu enthüllen 😉.

TRIM(...)

entfernt Leerstellen vom Anfang und Ende eines Strings. Aber Du wendest das auf eine Zahl an?! MySQL muss also die ermittelte Länge nun erstmal in einen String konvertieren, um davon dann die führenden und abschließenden Leerstellen zu amputieren. Die gibt's aber gar nicht. MYSQL setzt keine Spaces vor oder hinter eine Zahl, die es on the fly in einen String konvertiert.

TRIM(x) - TRIM(y)

TRIM liefert einen String. Aber für Strings ist der "-" Operator in SQL nicht definiert^[1]. Der versteht nur Zahlen. MySQL hat also mühsam Zahlen in Strings verwandelt und nun werden wieder Zahlen draus, die Du dann subtrahierst.

Und das Ergebnis ist: Die Anzahl der Kommata in "Tore".

Erster Schritt zum Verständnis wäre also: Die TRIMs sind weder Handwerks- noch schöne Kunst und können ersatzlos weg.

SUM(LENGTH(Tore) - LENGTH(REPLACE( Tore, ',', '' ))) + 1,

Nun steht da noch SUM. Das ist eine Aggregatfunktion, und sie ergibt nur dann einen Sinn, wenn die Query (ohne das SUM) mehr als einen Satz liefern kann. Ist das bei Dir so? Du fragst nämlich SpieleID=... ab. Ist die SpieleId in deiner Table ein Primär- oder ein Fremdschlüssel?

Wenn sie Primärschlüssel ist (d.h. es gibt zu jeder SpieleId genau eine Zeile in table), dann kann auch der SUM weg.

Wenn sie ein Fremdschlüssel ist (d.h. table enthält pro SpieleId keinen, einen oder auch mehrere Sätze mit Tore-Angaben), dann ergibt SUM schon einen Sinn, aber dann würde ich fragen, ob das "+1" an der richtigen Stelle steht. Was willst Du da bestimmen? Die Anzahl der kommagetrennten Einträge in "Tore", über alle Sätze dieser SpieleId hinweg? Dann müsste das +1 innerhalb des SUM() stehen.

Allerdings ergibt der UPDATE dann keinen Sinn mehr. Wenn SpieleId nämlich ein Fremdschlüssel ist, dann ist es in einer einzelnen Zeile von table nicht unbedingt sinnvoll, die Anzahl von Tore-Angaben über alle Zeilen mit dieser SpieleId hinweg zu vermerken.

Dieser Vermerk ist - wie eingangs erwähnt - in der DB ohnehin nicht sinnvoll. Es ist eine redundante Information, und Du müsstest den Inhalt von "Spalte" ständig mühsam aktuell halten, sobald sich irgendwo in "Tore" die Anzahl der Werte ändert.

Und wenn der Konstruktionsfehler "Tore" behoben wird - der da lautet: Eklatante Verletzung der ersten Normalform, die ein "Array-Feld" verbietet - dann ist das Problem eh keins mehr, weil Du dann nämlich einfach die Sätze mit Tore-Angaben zählst.

Wenn dann auch noch der Konstruktionsfehler "Datenaufbereitung in der DB" behoben wird, entfällt auch der Update.

Es sei denn, ich habe das alles grob mistverstanden.

Rolf

---

1. Die einzige mir bekannte Sprache, die ein "-" für Strings definiert hat, war dBase. Eine Stringverkettung mit "-" hat die Spaces am Ende des ersten Operanden hinter den zweiten Operanden geschoben. "A " - "B " ergab "AB ". Hilfreich, wenn man mit einem Monospace-Font Tabellen erzeugt hat und darin Spalten aus Strings zusammengesetzt werden mussten. Ah, die 80er... ↩︎

[Flo]

Hi Rolf,

es ist etwas zu kompliziert zu erklären, daher habe ich versuht, ein neues beispiel zu kreieren, das den Sachverhalt einigermaßen wiedergibt.

Der Konstruktionsfehler ist gefühlte 50 Jahre alt und ich ziehe ihn erfolgreich durch die jahre hindurch mit, weil es an zu vielen Stellen benötigt wird. Das lustig-tragische ist natürlich, dass die Hürde, ihn zu beheben umso größer wird, an je mehr Stellen ich ihn durch die Hintertür pampere. 😂 Tröstlich ist aber, dass es der einzige "Normalformfehler" der Anwendung ist, der nicht absichtlich herbei geführt wurde. 😉

In der Query ist ein Fahler, den Du aufgedeckt hast. Mysql-Trim und php-trim sind unterschiedlich, das wußte ich nicht. Da aber eh keine Leerzeichen vorhanden sind, kann trim ohnehin weg. Und nicht nur deshalb, wie Du schon sagtest.

Stell dir die Spalte "Tore" so vor, als würde bei jedem Tor ähnlich einer Strichliste ein Werz in die Spalte "Tore" gefolgt von einem Komma eingetragen.

Was willst Du da bestimmen? Die Anzahl der kommagetrennten Einträge in "Tore", über alle Sätze dieser SpieleId hinweg? Dann müsste das +1 innerhalb des SUM() stehen.

Genau. Warum müsste das +1 innerhalb der SUM-Funktion stehen? Jedenfalls funktioniertb es tadellos so, wie ich es notiert habe.

Ich habe den Update inzwischen ganz anders gelöst. Mich hätte trotzdem interessiert, ob es eine Update-Lösung ohne den Umweg über einen zusätzlichen SELECT gegeben hätte.

Danke für Dein "Reindenken" in die zugegeben verworrene Frage.

Flo

[Rolf B]

Hallo Flo,

Warum müsste das +1 innerhalb der SUM-Funktion stehen?

Das musst eigentlich Du beantworten. "+1" innerhalb der SUM Funktion bedeutet: +1 je Row in der Table, die summiert wird. Und +1 außerhalb der Funktion addiert genau einmal die 1, egal wieviele Rows summiert wurden.

Aber ich habe den Sinn der +1 wohl falsch gedeutet. Du schriebst jetzt in deiner Antwort:

Stell dir die Spalte "Tore" so vor, als würde bei jedem Tor ähnlich einer Strichliste ein Werz in die Spalte "Tore" gefolgt von einem Komma eingetragen.

Das lese ich nun so, dass in "Tore" immer genau so viele Kommas wie Werte stehen. Also "1,2,3," oder "4,7,1,1,". Meine Annahme war, dass die Kommas nur zwischen den Werten stehen. "1,2,3" oder "4,7,1,1".

Wenn auch am Ende immer ein Komma ist, dann kannst Du die Anzahl der Kommas summieren und bekommst die Anzahl der Werte heraus.

Das "+1" dient also nicht dazu, aus der Kommazahl die Wertezahl zu berechnen, sondern hat eine ganz andere Aufgabe, die ich nicht kenne.

Die unklare Frage ist also nach wie vor: Brauchst Du den SUM überhaupt? Der TRIM war ja auch unnötig. Hast Du in der Table mehr als einen Satz zur SpielID?

Rolf

[Flo]

Hallo Rolf,

Das lese ich nun so, dass in "Tore" immer genau so viele Kommas wie Werte stehen. Also "1,2,3," oder "4,7,1,1,". Meine Annahme war, dass die Kommas nur zwischen den Werten stehen. "1,2,3" oder "4,7,1,1".

String1,Strinzwei,String_3

So in der Art, also auch keine verlässlich gleich langen Strings. Und ohne Komma am Ende des letzten strings.

Das "+1" dient also nicht dazu, aus der Kommazahl die Wertezahl zu berechnen, sondern hat eine ganz andere Aufgabe, die ich nicht kenne.

Doch, genau dazu dient das +1, weil immer ein Wert mehr als kommata.

Die unklare Frage ist also nach wie vor: Brauchst Du den SUM überhaupt? Der TRIM war ja auch unnötig. Hast Du in der Table mehr als einen Satz zur SpielID?

Eigentlich nicht, fällt mir gerade auf. Hm, das muss ich morgen gleich mal als erstes testen. Wie kam ich denn auf das SUM? Verteufelt, war ein langer Tag, ich weiß nicht mehr, was mir das durch den kopf ging 😉

Flo

[Rolf B]

Hallo Flo,

LOL - gut, dass wir drüber gesprochen haben.

Rolf