SQLITE ifnull komisches verhalten

[Devil1925]

Moin, ich habe hier ein äußerst komisches verhalten bei einer SQL Abfrage auf SQLITE:

Die Tabelle auf die die Abfrage läuft:
http://www.delphipraxis.net/attachme...1&d=1489573610

jetzt setze ich folgendes SQL auf die Tablle ab:

Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname  = I.Abhaengig
where T.Value = 1

Ergebnis:
http://www.delphipraxis.net/attachme...1&d=1489573870

Soweit so gut. jetzt möchte ich aber alle Zeilen als ergebnis haben, in welchen die spalte

Delphi-Quellcode:

T.Value

entweder '1' oder NULL ist. Logische schlussfolgerung: ein

Delphi-Quellcode:

ifnull

verwenden!

Also ein

Delphi-Quellcode:

ifnull

drum gesetzt:

Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname  = I.Abhaengig
where ifnull(T.Value, 1) = 1

Ergebnis:
http://www.delphipraxis.net/attachme...1&d=1489574293

Jetzt meine Frage: wo ist die Zeile "MailMonate" hin? habe ich ifnull irgendwie falsch verstanden? In meinem Wissensstand macht ifnull bei einer Spalte aus allen Feldern, in welchen NULL steht den angegebenen Wert und ansonsten gibt es den vorhandenen Wert zurück. dementsprechend müsste hier doch auch die "MailMonate" Zeile mit kommen oder?

[jobo]

kann sein dass das ein Typproblem ist. mach mal auf die andere Seite auch ifnull(1,1) zum Test

[nahpets]

Naja, da der Wert von Value = 6 ist und Du auf Value = 1 abfragst, dürfte das Ergebnis schon stimmen.

Aufgrund Deiner Frage und der Darstellungen in den Bildern hätte ich dieses SQL erwartet:

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname = I.Abhaengig
where ifnull(T.Value1, 1) = 1

[Ghostwalker]

Nein, eigentlich nicht. Auch die Doku dazu https://www.techonthenet.com/sqlite/...ons/ifnull.php sagt das gleiche.

Eigentlich sollten beide Abfragen das gleiche Ergebnis bringen, da die Spalte Value nie NULL ist,
von daher wär das mit ifnull eh überflüssig.

[mkinzler]

Versuche es mal mit

SQL-Code:

...where coalesce( TValue1,1) = 1;

[Ghostwalker]

Ich würds mal so probieren:

Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname = I.Abhaengig
where (T.Value = 1) OR ISNULL(T.Value);

[nahpets]

Zitat:

Zitat von mkinzler (Beitrag 1364262)

Versuche es mal mit

SQL-Code:

...where coalesce( TValue1,1) = 1;

Vor allem, die Abfrage erstmal auf T.Value1 ändern. Solange ich eine Spalte abfrage, die nicht den abzufragenden Wert enthält, bekomme ich auch nicht das gewünschte Ergebnis. Hier den Fehler dann irgendwo in den Datenbankfunktionen zu suchen, oder eine Funktion durch eine andere zu ersetzen, bringt nichts.

Der gesuchte Tabelleneintrag enthält in der Spalte Value den Wert 6. Wenn ich da auf 1 Abfrage, werde ich ihn nicht in der Ergebnismenge erhalten, auch dann nicht, wenn ich ihn vorher auf Null prüfe und für den Fall, dass er Null sein sollte durch 1 ersetze.

6 ist nicht Null und wird daher durch ein IfNull(6,1) auch nicht zu 1.

[Devil1925]

Ich habe jetzt noch nicht verstanden wie ihr auf

Delphi-Quellcode:

T.Value1

kommt? ich möchte auf den Value Wert aus der Unterabfrage aus dem

Delphi-Quellcode:

LEFT JOIN

abfragen, und die Spalte heisst doch

Delphi-Quellcode:

Value

Zitat:

Zitat von nahpets (Beitrag 1364257)

Naja, da der Wert von Value = 6 ist und Du auf Value = 1 abfragst, dürfte das Ergebnis schon stimmen.

Wäre das der Value Wert den ich Abfrage würde das ja auch korrekt laufen, allerdings ist

Delphi-Quellcode:

 I.Value

6 und ich frage

Delphi-Quellcode:

T.Value

ab, welcher in diesem Fall 1 ist und dementsprechend mit selektiert werden müsste.

Zitat:

Zitat von mkinzler (Beitrag 1364262)

Versuche es mal mit

SQL-Code:

...where coalesce( TValue1,1) = 1;

auch diese Abfrage gibt mir nicht die Gewünschten Werte zurück. Wobei es eben die Spalte

SQL-Code:

TValue1

nicht gibt sondern nur die Spalte

SQL-Code:

T.Value

.

Zitat:

Zitat von nahpets (Beitrag 1364264)

Zitat:

Zitat von mkinzler (Beitrag 1364262)

Versuche es mal mit

SQL-Code:

...where coalesce( TValue1,1) = 1;

Vor allem, die Abfrage erstmal auf T.Value1 ändern. Solange ich eine Spalte abfrage, die nicht den abzufragenden Wert enthält, bekomme ich auch nicht das gewünschte Ergebnis. Hier den Fehler dann irgendwo in den Datenbankfunktionen zu suchen, oder eine Funktion durch eine andere zu ersetzen, bringt nichts.

Wie schon gesagt, wie kommst du darauf dass ich das auf "T.Value1" ändern soll? diese Spalte gibt es doch garnicht? Ausserdem dürfte doch wenn das so läuft wie du es gerade meinst doch auch nur "KontaktSub" und "Mail" anzeigen, allerdings wird mehr angezeigt.

[Devil1925]

Ich habe es jetzt gelöst indem ich als Abfrage nicht

SQL-Code:

... where ifnull(T.Value, 1) = 1

verwende sondern

SQL-Code:

 ...where (T.Value = 1 or T.Value is NULL)

. Dies liefert das gewünschte Ergebnis. Aber sollten nicht beide Abfragen das korrekte Ergebnis Liefern?

[mkinzler]

In der Ergebnismege (JOIN) ist das Feld Value 2 Mal enthalten. Über diese Tatsache stolpert Deine Abfrage.

[Devil1925]

Habe die Abfrage jetzt gerade mal angepasst:

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value as Abhaengig_Value from INITAB) T on T.Keyname = I.Abhaengig
where ifnull(T.Abhaengig_Value, 1) = 1

Trotzdem bekomme ich noch immer nicht die gewünschten ergebnisse...

[nahpets]

Ok, die Screenshots sind nicht die Tabellen, sondern die Ergebnisse mit nicht zwingend verständlichen Namen.

Die Spaltenüberschrift Value1 hatte ich als Namen der Spalte Value1 verstanden.

Es ist aber wohl die von SQLite vorgenommene Nummerierung von nicht eindeutigen Spaltennamen in der Ergebnismenge.

Danke für die Verwirrung.

Wo finde ich denn jetzt eigentlich die Tabellendefinition von INITAB, um da etwas eindeutigere Informationen zu bekommen, statt der Spalten der nicht zufriedenstellenden Abfrageversuche?

Dashier

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname = I.Abhaengig
where ifnull(T.Value, 1) = 1

würde ich auch anders formulieren:

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value As Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB where ifnull(Value, 1) = 1) T on T.Keyname = I.Abhaengig

Warum die Einschränkung für die Teilmenge im Left-Join ausserhalb des Unterselects? Das führt beim Lesen (und eventuell auch bei der Interpretation durch die Datenbank?) sehr schnell zu Verwirrung.

Bau Dir bitte mal für dasda

SQL-Code:

(Select Sectionname, Keyname, Value from INITAB where ifnull(Value, 1) = 1)

'ne View und binde die, nachdem ihre Ergebnisse als richtig verifiziert wurden, ins Left Join ein.
Eventuell wird dann die Abfragelogik leichter les- und verstehbar.

[Devil1925]

Zitat:

Zitat von nahpets (Beitrag 1364279)

Ok, die Screenshots sind nicht die Tabellen, sondern die Ergebnisse mit nicht zwingend verständlichen Namen.

Die Spaltenüberschrift Value1 hatte ich als Namen der Spalte Value1 verstanden.

Es ist aber wohl die von SQLite vorgenommene Nummerierung von nicht eindeutigen Spaltennamen in der Ergebnismenge.

Danke für die Verwirrung.

Entschuldigung für die Verwirrung durch das erste Bild.

Zitat:

Zitat von nahpets (Beitrag 1364279)

Wo finde ich denn jetzt eigentlich die Tabellendefinition von INITAB, um da etwas eindeutigere Informationen zu bekommen, statt der Spalten der nicht zufriedenstellenden Abfrageversuche?

Hier einmal die genaue Tabellendefinition:
Tabelle: INITAB
Spalten:
Sectionname VARCAHR
Keyname VARCHAR
Value VARCHAR
Abhaengig VARCHAR

Zitat:

Zitat von nahpets (Beitrag 1364279)

Dashier

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB) T on T.Keyname = I.Abhaengig
where ifnull(T.Value, 1) = 1

würde ich auch anders formulieren:

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value As Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB where ifnull(Value, 1) = 1) T on T.Keyname = I.Abhaengig

Warum die Einschränkung für die Teilmenge im Left-Join ausserhalb des Unterselects? Das führt beim Lesen (und eventuell auch bei der Interpretation durch die Datenbank?) sehr schnell zu Verwirrung.

Es geht ja darum, dass ich in der endabfrage sowohl die Datensätze haben möchte, welche im Value der unterabfrage eine 1 stehen haben, als auch die, welche keinen Datensatz in der Unterabfrage zugeordnet haben. Somit brauche ich aus der Gesamtabfrage alle Datensätze die in der Spalte (so in der Abfrage benannt) "Abhaengig_Value" entweder NULL oder 1 stehen haben.

Zitat:

Zitat von nahpets (Beitrag 1364279)

Bau Dir bitte mal für dasda

SQL-Code:

(Select Sectionname, Keyname, Value from INITAB where ifnull(Value, 1) = 1)

'ne View und binde die, nachdem ihre Ergebnisse als richtig verifiziert wurden, ins Left Join ein.
Eventuell wird dann die Abfragelogik leichter les- und verstehbar.

man kann in SQLITE Views einbauen? das ist mir neu, kann aber auch sein das ich das einfach noch nicht wusste.

[nahpets]

Keine Ahnung, ob SQLite Views kann, würd' es aber zumindest mal probieren.

Siehe http://www.tutorialspoint.com/sqlite/sqlite_views.htm

Value ist VarChar, Du fragst aber auf nummerisch ab.

Ist ein leeres Value nun Null oder ein Leerstring?

Bitte mal noch ein Select * from INITAB als Screenshot, damit man das auch mal sehen kann.

Bei Value = VarChar wäre die Mindeständerung:

SQL-Code:

Select I.Keyname, I.Value, I.Abhaengig, T.Value As Abhaengig_Value from INITAB I
LEFT JOIN (Select Sectionname, Keyname, Value from INITAB where ifnull(Value, '1') = '1') T on T.Keyname = I.Abhaengig

[Aviator]

Zitat:

Zitat von nahpets (Beitrag 1364292)

Keine Ahnung, ob SQLite Views kann, würd' es aber zumindest mal probieren.

Natürlich kann SQLite Views. Siehe hier: https://www.sqlite.org/lang_createview.html

[Devil1925]

Danke, der Denkanstoß fehlte mir! Durch das Ändern von [CODE=SQL]... where ifnull(T.Value, 1) = 1[CODE] auf

SQL-Code:

... where ifnull(T.Value, '1') = '1'

funktioniert das auch!

(mich wundert es nur, dass da kein Fehler ausgeworfen wurde und der die Abfrage einfach ausgeführt hat, aber vielleicht bin ich da auch einfach vom MSSQL Managementstudio verwöhnt)

[Devil1925]

Zitat:

Zitat von Aviator (Beitrag 1364294)

Zitat:

Zitat von nahpets (Beitrag 1364292)

Keine Ahnung, ob SQLite Views kann, würd' es aber zumindest mal probieren.

Natürlich kann SQLite Views. Siehe hier: https://www.sqlite.org/lang_createview.html

OK, danke für die Info!

[jobo]

Zitat:

Zitat von Devil1925 (Beitrag 1364295)

Danke, der Denkanstoß fehlte mir! Durch das Ändern von

Code:

.. where ..'1' = '1'..

funktioniert das auch!

(mich wundert es nur, dass da kein Fehler ausgeworfen wurde und der die Abfrage einfach ausgeführt hat, aber vielleicht bin ich da auch einfach vom MSSQL Managementstudio verwöhnt)

Siehe mein Post #2.
SQLite ist nicht pingelig mit Typen.
Der Typ des Ausdrucks "ifnull(<feld>,1)" ist (mir) erstmal unbekannt.
Genaugenommen wohl auch sqlite, da die Funktion verschiedenste Spaltentypen als Eingangsparameter schluckt.
Um sicherzugehen, dass ein Vergleich mit festem Typ funktioniert, muss man die gewünschten Typen auch gesichert herbeiführen. Also Cast oder sowas. Hast Du gemacht mit Anführungszeichen. (Ich wäre mir übrigens nicht sicher, dass Deine Methode hinreichend sicher ist)
Mein Vorschlag, am anderen Ende des "=" die gleiche Funktion einzusetzen, nur mit fixen Parametern, war ein erster Wurf, um Dich auf das Problem aufmerksam zu machen.

P.S.: Auch in anderen RDBMS gibt es solche Funktionen, deren Ausgabetyp notgedrungen (oder eleganter Weise) variieren kann. Das wird dynamisch zum Zeitpunkt des Aufrufs bestimmt und ist idR auch dokumentiert, wie die Funktionen das machen.
Also an der Ecke schadet es nie, 2x hinzusehen.

[nahpets]

Also da muss ich jetzt mal was loswerden!

Wieso scheren sich Entwickler, die bei Delphi die Typsicherheit loben (aber durchaus auch andere), bei der Nutzung von SQL 'nen Teufel um die Datentypen?

Da wird verglichen auf teufelkommraus, aber mal links und rechts des Gleichheitszeichens den gleichen Datentyp verwenden?

Warum, solange es nicht knallt, wird es wohl schon gehen.

Und wenn nicht: Erst ist es mal ein Probelm der Datenbank und deren fehlerhaften Funktionen.

Übrigens ein Problem, dass ich schon meine ganze Zeit als Entwickler immer und immerwieder beobachten durfte, ist also nix persönliches oder explizites hier in diesem Thread oder Forum.

Nur ganz allgemein und extrem nervend.

Man sucht Fehler in 'ner Datenbank und deren Implementierung.

Kaum achtet man mal auf den Datentyp, schon hat sich das Problem in Luft aufgelöst.

Vermutlich ist das größte Problem bei der Nutzung von SQL, dass es bei unterschiedlichen Datentypen extrem fehlertolerant ist, aber diese Fehlertoleranz nicht immer im Sinne der Wünsche der Nutzer erfolgt, sondern im Sinne der technischen Implementierung der Datenbank.

Oder kurz:

Wenn ich Zeichenfolgen zu vergleichen habe, dann vergleiche ich Zeichenfolgen.

Wenn ich nummerische Werte zu vergleichen habe, dann vergleiche ich nummerische Werte.

Muss ich das Eine mit dem Anderen vergleichen, dann nutze ich die entsprechend Typkonvertierung und verlasse mich nicht darauf, dass die Datenbank selbständige die Typkonvertierung nutzt, von der ich meine, dass sie im konkreten Fall die Richtige sei.
Das kann funktionieren, muss aber nicht. Und der Fehler liegt dann nicht in der Datenbank, sondern sitzt davor.

So, genug gemeckert ;-)

[Devil1925]

Zur Kenntnis genommen!

Zitat:

Zitat von nahpets (Beitrag 1364300)

Und der Fehler liegt dann nicht in der Datenbank, sondern sitzt davor.

Das ist sowieso in 90% der Fehlerfall.

Und damit noch einen schönen Start in den Feierabend :cheers:

[jobo]

Ja, sehe ich auch so. Und spaßig finde ich es, weil wir hier kein VBA Forum oder sowas sind, sondern naja, ist ja bekannt. Der Begriff "Typ" sollte also ein Begriff sein, und das Thema wird ja immer wieder mal auch hier genüßlich filetiert. Also die Typenhandhabung in Delphi. Warum in der DB und den gebauten Tabellen dieses Thema schlampiger behandelt werden sollte erschließt sich mir nicht.
Aber was solls, es schafft Arbeitsplätze, solange sich keiner drum kümmert.

[mkinzler]

Zitat:

Warum in der DB und den gebauten Tabellen dieses Thema schlampiger behandelt werden sollte erschließt sich mir nicht.

Ist aber leider so. In einer Hochsprache würde niemand auf die Idee kommen alles in Strings zu Packen, um "flexibel" zu bleiben. In Datenbanken sieht man dass aber leider seht oft.

[nahpets]

Naja, Strings würd' ich da nicht nehmen, lieber direkt Variant oder OleVariant. Da kann dann wirklich alles rein und wird sicherlich auch irgendwie interpretiert werden ;-)