MySQL UNION mit UNSIGNED und SIGNED Spalten

Bei der Verwendung von MySQL UNION Queries ist Vorsicht geboten, wenn es um die Verwendung von UNSIGNED und SIGNED Spalten geht.
Im Beispiel wurde MySQL 5.0.51 verwendet, auch in neueren Versionen dürfte das Verhalten jedoch identisch sein.

Als Beispiel nehmen wir folgende Datenbankstruktur:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

CREATE TABLE IF NOT EXISTS `tabelle1` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `betrag` double(13,2) UNSIGNED NOT NULL,
  `type` varchar(4) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `tabelle2` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `betrag` double(13,2) NOT NULL,
  `type` varchar(4) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

[/pastacode]

Die Tabellen werden natürlich in der Realität noch weitere Spalten haben und in den Abfragen wird man meist auch nur bestimmte Werte aus den Tabellen suchen, dies ist aber für das Beispiel nicht interessant.

Füllen wir die Tabellen nun mit ein wenig Beispieldaten:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

INSERT INTO `tabelle1` (`id`, `betrag`, `type`) VALUES
(1, 102.45, 'typ1'),
(2, 14.98, 'typ1'),
(3, 0.99, 'typ1'),
(4, 231.12, 'typ2');
 
INSERT INTO `tabelle2` (`id`, `betrag`, `type`) VALUES
(1, -98.32, 'typ2'),
(2, 100.78, 'typ1'),
(3, -9.10, 'typ2'),
(4, -25.10, 'typ1');

[/pastacode]

Wir möchten nun in einem UNION Query alle Einträge beider Tabellen auslesen und nach dem Betrag sortieren:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

SELECT `id`, `betrag`, `type` FROM `tabelle2` WHERE 1
UNION ALL SELECT `id`, `betrag`, `type` FROM `tabelle1` WHERE 1 ORDER BY `betrag`

[/pastacode]

Die Ausgabe ist genau das, was wir erwartet haben:

id 	betrag	type
1 	-98.32 	typ2
4 	-25.10 	typ1
3 	-9.10 	typ2
3 	0.99 	typ1
2 	14.98 	typ1
2 	100.78 	typ1
1 	102.45 	typ1
4 	231.12 	typ2

Nun machen wir (eigentlich) die gleiche Abfrage, nur tauschen wir die Reihenfolge der Tabellen in der UNION.

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

SELECT `id`, `betrag`, `type` FROM `tabelle1` WHERE 1
UNION ALL SELECT `id`, `betrag`, `type` FROM `tabelle2` WHERE 1 ORDER BY `betrag`

[/pastacode]

Das Ergebnis ist:

id 	betrag	type
1 	0.00 	typ2
3 	0.00 	typ2
4 	0.00 	typ1
3 	0.99 	typ1
2 	14.98 	typ1
2 	100.78 	typ1
1 	102.45 	typ1
4 	231.12 	typ2

Warum sind nun die negativen Spalteninhalte aus der Tabelle plötzlich im Ergebnis 0?
Die Erklärung ist simpel. Bei UNION Abfragen ist es notwendig, dass die Spalten in allen verbundenen Abfragen die gleiche Anzahl und den gleichen Typ haben. Im Beispiel ist das bei den Spalten „id“ und „typ“ der Fall, bei „betrag“ jedoch nicht.
MySQL nimmt nun den Spaltentyp der ersten Abfrage als Grundlage und dieser ist vom Typ UNSIGNED, lässt also keine negativen Werte zu. Die Spaltenwerte in der zweiten Abfrage werden daraufhin in UNSIGNED umgewandelt.

Um sicherzustellen, dass wir die richtigen Ergebnisse kriegen, sollte der Typ der Spalte in der ersten Abfrage in SIGNED umgewandelt werden. Dies funktioniert mit der MySQL Funktion CAST, sofern es sich um Ganzzahlen handelt.

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

SELECT `id`, CAST(`betrag` AS SIGNED) as `betrag`, `type` FROM `tabelle1` WHERE 1
UNION ALL SELECT `id`, `betrag`, `type` FROM `tabelle2` WHERE 1 ORDER BY `betrag`

[/pastacode]

Hierdurch wird die „betrag“ Spalte der ersten Abfrage in den SIGNED Typen umgewandelt, der negative Werte zulässt. Nun haben die Betrag-Spalten in beiden Abfragen den gleichen Typ.
Aber Vorsicht! Dies entfernt bei den gecasteten Werten die Kommastellen und wandelt in einen Int Typ um.
id betrag type
1 -98 typ2
4 -25 typ1
3 -9 typ2
3 0.99 typ1
2 14.98 typ1
2 100 typ1
1 102.45 typ1
4 231.12 typ2

Es ist anzuraten die Spalte in der MySQL Tabelle direkt auf den gleichen Typ zu setzen und in Kauf zu nehmen einen SIGNED DOUBLE Typ zu verwenden, auch wenn man keine negativen Werte benötigt. So ist sichergestellt, dass bei einem UNION mit anderen SIGNED Spalten auch das korrekte Ergebnis kommt.
Handelt es sich um Spalten mit den Typen INT, TINYINT etc. so ist die Verwendung von CAST natürlich kein Problem.