MySQL: Join auf sich selbst schlägt fehlt

[tme]

Hallo,

ich habe hier in einem Projekt folgende Tabelle:

DROP TABLE IF EXISTS `simplepilot`.`users`;
CREATE TABLE  `users` (
  `username` varchar(100) NOT NULL DEFAULT '',
  `shops_domain` varchar(100) NOT NULL DEFAULT '',
  `kunden_name` varchar(100) NOT NULL DEFAULT '',
  `passwort` varchar(40) NOT NULL DEFAULT '',
  `passwort_aendern` varchar(5) NOT NULL DEFAULT 'false',
  `rolle` varchar(20) NOT NULL DEFAULT '',
  `kontakte_id_rechnung` int(11) NOT NULL DEFAULT '0',
  `kontakte_id_lieferung` int(11) NOT NULL DEFAULT '0',
  `ueberprueft` varchar(5) NOT NULL DEFAULT 'false',
  `agb_akzeptiert` varchar(5) NOT NULL DEFAULT 'false',
  `gesamtsortiment` varchar(5) NOT NULL DEFAULT 'true',
  `auftraege_durchleiten` varchar(5) NOT NULL DEFAULT 'false',
  `auftraege_durchleiten_bis` double NOT NULL,
  `kundennummer` varchar(20) NOT NULL,
  `gesperrt` varchar(5) NOT NULL DEFAULT 'false',
  `vorgesetztenfunktion_aktiv` varchar(5) NOT NULL DEFAULT 'false',
  `vorgesetzter_username` varchar(100) NOT NULL,
  `vertreterregelung_aktiv` varchar(5) NOT NULL DEFAULT 'false',
  `vertreter_username` varchar(100) NOT NULL,
  `geaendert_von` varchar(100) NOT NULL DEFAULT '',
  `geaendert_am` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`username`,`shops_domain`),
  KEY `kunden_name` (`kunden_name`),
  KEY `ueberprueft` (`ueberprueft`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hierzu sende ich über ein beliebiges Abfragetool (z.B. MySQL Query Browser unter Windows) folgende Abfrage:

select u.shops_domain,u.username,u.kontakte_id_lieferung
from users u
left outer join users u2 on u2.shops_domain = u.shops_domain and u2.username = u.username
where not u.kontakte_id_lieferung is null and not u.kontakte_id_lieferung = 0
and u2.username is null;

Diese Abfrage gibt Datensätze aus. Die Implikation ist, dass sich die Datensätze nicht selbst finden. Dazu ein paar Ideen und Fragen:

1. Abfragen werden im Text an den Server gesendet und dort ausgeführt, bis die Ergebnisse zurückgemeldet werden können. Wie kann es dann zu diesem Problem kommen?
2. Haben die Einstellungen für Collation der Verbindung oder des Clients ggf. doch Einfluß auf die Ausführung des Servers? Diese sind folgendermaßen vergeben:

'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'

3. Eigentlich wollten wir alles auf UTF8 umstellen, um solchen Problemen zu entkommen, aber UTF8 bietet als Collation keine irgendwie deutschgeartete Collation an. Mit welchen Einstellungen fahrt ihr produktive MySQL-Datenbanken, die UTF8 codiert sind?

Danke,

Thomas

 

[SlaterB]

> and u2.username is null;
zusammen mit
> `username` varchar(100) NOT NULL DEFAULT '',
führt dazu, dass für u2 nichts in Frage kommt,

bei einem left outer join wird damit für jedes Element von u ein Ergebnis zurückgeliefert, u verknüpft mit null,

stelle das select auf "select u.username,u2.username" um, dann wirst du Ergebnisse
> John, null
> Paul, null
usw. erhalten

 

[tme]

Leider hast du die Sachlage falsch verstanden, ich versuche, dies nochmal genauer darzulegen.

and u2.username is null

sorgt bei einem outer join dafür, dass ich das Vorhandensein der zweiten Seite abfragen kann. Ich könnte dort jedes beliebige Feld der mittels OUTER gejointen Tabelle abfragen.

Die Tabelle beinhaltet ca. 800.000 Datensätze, davon kommen genau 22 mittels der oben angegebenen Abfrage zurück. Auffällig ist dabei, dass nahezu alle ein "ß" oder doppel "s" tragen, was für mich auf Collationprobleme hinweist.

 

[SlaterB]

kannst du das noch näher erläutern, wie username is null bei obiger Tabellendefinition möglich ist?

an welcher Stelle gehts überhaupt um die Texte mit ss/ ß, bei username?
wie kann dort ss/ ß drin sein, wenn der username gerade null sein soll?

oder bei shops_domain? dann hat der username mit der ganzen Sache nix zu tun?
ich würde dann die Query etwas vereinfachen, ein Beispiel heraussuchen, dass fälschlicherweise joint bzw. gerade nicht joint obwohl es soll,
für u und u2 genaue Ids vorgeben und dann die fraglichen Werte im Detail anschauen,
auch in der DB wird es doch sicher Methoden wie LIKE-Vergleiche geben, vielleicht einzelne Zeichen extrahieren und vergleichen usw.

-----

zu deinem generellen Thema Collation/ Charsets kann ich aber nix beitragen