[MySQL] Vreemd resultaat bij alleen kolomnaam opgeven

[Spiral]

Ik maakte vanmorgen een typefout waarbij ik de volgende query uitvoerde in MySQL 5.1.42-community

SQL:

SELECT * FROM person WHERE lastname

ipv

SQL:

SELECT * FROM person WHERE lastname = 'blaat'

Waarbij lastname een kolom is van varchar(255) en de tabel bevat ~300 rijen

Tot mijn verbazing kreeg ik 2 resultaten terug. Ik heb dit ook geprobeerd op een andere tabel en kreeg 1 resultaat.Op een andere machine met dezelfde database structuur, maar met minder data heb ik het niet kunnen reproduceren. Welke SQL logica schuilt hierachter?

Google op 'mysql where only column given' oid geeft geen resultaat

[TheRookie]

Wat was de inhoud van het lastname veld voor de 2 resultaten die je kreeg ?
Als daar overeenkomsten in zitten zou dat een hint kunnen zijn richting de logica

[cariolive23]

En welke resultaten krijg je retour met alleen "lastname" ? Blijkbaar vertaalt MySQL de waarde van "lastname" naar true en dus is er een match.

[Voutloos]

Het is idd gewoon een test op de waarde van lastname.

[Spiral]

Wegens privacy redenen kan ik het resultaat niet plaatsen, maar bij het wijzigen van de data in de desbetreffende kolom kwam deze niet meer voor in het resultaat.

Waarbij het om de andere machine om testdata gaat en veelal herhalende informatie is. Word er bij de machine waar het voorkomt een test gedaan met importeren van daadwerkelijke gegevens. Deze gegevens zijn diverser en daar is de kans op een match dus op kolomnaam schijnbaar groter. Het is een tekst met respectievelijk 9 en 29 karakters lang.

Voutloos schreef op dinsdag 22 juni 2010 @ 09:13:
Het is idd gewoon een test op de waarde van lastname.

Ik zou graag willen weten wat voor test er wordt uitgevoerd.

[ Voor 18% gewijzigd door Spiral op 22-06-2010 09:29 . Reden: Reageren op Voutloos ]

[CodeCaster]

Ik kan zo snel niks vinden hoe MySQL omgaat met casten naar boolean, maar ik kan me voorstellen als zo'n veld begint met bijvoorbeeld een T, 1 of Y dat 'ie als true wordt geëvalueerd

Edit: het lijkt bij een snelle test alleen te werken met velden die met een 1 beginnen.

MySQL:

SELECT "1foo" = true; -- result: 1
SELECT "bar" = true; -- result: 0

[ Voor 33% gewijzigd door CodeCaster op 22-06-2010 09:30 ]

[Voutloos]

SQL:

CREATE TABLE `Test` (
  `Prut` varchar(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `Test`
-- 

INSERT INTO `Test` (`Prut`) VALUES 
('abc'),
('123abc'),
('Theo'),
('Yes'),
('42'),
('-999');

SELECT * FROM `Test` WHERE Prut;

Geeft '123abc', '42', '-999'.

[PolarBear]

MS SQL lijkt dit soort dingen niet te doen en geeft aan een expressie te missen. Ik snap even de logica hiervan niet.

[JoSchaap]

heb je niet ergens in de code niet een functie gemaakt met de naam lastname?

*edit* nvm had niet refreshed voor ik reageerde

[ Voor 25% gewijzigd door JoSchaap op 22-06-2010 09:35 ]

[CodeCaster]

Voutloos schreef op dinsdag 22 juni 2010 @ 09:28:

SQL:

CREATE TABLE `Test` (
  `Prut` varchar(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `Test`
-- 

INSERT INTO `Test` (`Prut`) VALUES 
('abc'),
('123abc'),
('Theo'),
('Yes'),
('42'),
('-999');

SELECT * FROM `Test` WHERE Prut;

Geeft '123abc', '42', '-999'.

Héél apart.

SQL:

SELECT "-999" = true 

Geeft 0 terug...

[Spiral]

Voutloos heeft een test voorbeeld waar het inderdaad naar voren komt. Ik maak gebruik van utf8 ipv latin1, maar dit maakt niks uit voor het resultaat! Tevens heb ik meteen de kolomnaam gewijzigd in `prutser` om te zien als dit iets uitmaakt. Maar het resultaat blijft hetzelfde.
Kan je daaruit concluderen dat de kolomnaam geen invloed heeft op de functie die de where clause evalueert?

[Voutloos]

De kolomnaam zelf maakt inderdaad helemaal niet uit.

Zo. En begin nu maar 100 posts over hoe stupide deze mysql feature is, terwijl je het helemaal nooit last van hoeft te hebben.

[Spiral]

Voutloos schreef op dinsdag 22 juni 2010 @ 09:42:
De kolomnaam zelf maakt inderdaad helemaal niet uit.

Zo. En begin nu maar 100 posts over hoe stupide deze mysql feature is, terwijl je het helemaal nooit last van hoeft te hebben.

Haha nee zal ik niet gaan doen. Ben al blij dat ik niet de enige ben die de logica erachter niet snapt. Het is gewoon een, tot dusver bekend, niet gedocumenteerde feature van MySQL. Laten we het daar maar bij laten

[cariolive23]

Spiral schreef op dinsdag 22 juni 2010 @ 09:56:
[...]


Haha nee zal ik niet gaan doen. Ben al blij dat ik niet de enige ben die de logica erachter niet snapt. Het is gewoon een, tot dusver bekend, niet gedocumenteerde feature van MySQL. Laten we het daar maar bij laten

Dit is gewoon gedocumenteerd hoor, zoals vrijwel alle bugs/features van MySQL:
http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

[Spiral]

cariolive23 schreef op dinsdag 22 juni 2010 @ 10:39:
[...]

Dit is gewoon gedocumenteerd hoor, zoals vrijwel alle bugs/features van MySQL:
http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

Aan welke criteria voldoet dan de WHERE clause in de documentatie die jij geeft?

•If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true.
•If both arguments in a comparison operation are strings, they are compared as strings.
•If both arguments are integers, they are compared as integers.
•Hexadecimal values are treated as binary strings if not compared to a number.
•If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
•In all other cases, the arguments are compared as floating-point (real) numbers.

Aan de allerlaatste regel zal het dan moeten voldoen, maar waar tegen vergelijkt MySQL het dan? Dat geef je nou net niet op in de WHERE clausule.

[PolarBear]

Voutloos schreef op dinsdag 22 juni 2010 @ 09:42:
De kolomnaam zelf maakt inderdaad helemaal niet uit.

Zo. En begin nu maar 100 posts over hoe stupide deze mysql feature is, terwijl je het helemaal nooit last van hoeft te hebben.

Een database heeft een doel namelijk betrouwbaar data opslaan. Logisch gevolg is dat je dit soort zaken niet toestaat. Het is een soort automagische feature. MS SQL en Oracle weigeren gewoon de query.

[cariolive23]

Spiral schreef op dinsdag 22 juni 2010 @ 11:07:
[...]


Aan welke criteria voldoet dan de WHERE clause in de documentatie die jij geeft?


[...]


Aan de allerlaatste regel zal het dan moeten voldoen, maar waar tegen vergelijkt MySQL het dan? Dat geef je nou net niet op in de WHERE clausule.

In de WHERE is er sprake van booleans, true of false, 1 of 0. MySQL probeert iedere waarde in jouw kolom te kasten naar 1 of 0 en daar komen dus een aantal 1-en uit voort, zie de resultaten. Je kunt dit ook in je SELECT laten zien:

SQL:

SELECT 
  kolom, 
  kolom = true 
FROM 
  tabel;

Zo krijg je per waarde te zien of deze wordt gecast naar true of false, 1 of 0.

Ps. PostgreSQL weigert dit soort queries uiteraard ook, een varchar is tenslotte geen boolean. Vrijwel iedere database zal dit weigeren, het is gewoon fout.

[ Voor 9% gewijzigd door cariolive23 op 22-06-2010 12:12 ]

[Spiral]

Hoe evalueert de WHERE clausule '-999' dan naar true?

Jij haalt een url aan waar jij aangeeft dat het gedocumenteerd is, maar dat zie ik niet terug in de die url. Voor mij is het duidelijk dat er bij de WHERE clausule een evaluatie plaatsvind.

Ik ben het niet met je eens dat dit gedrag gedocumenteerd is.

[ Voor 70% gewijzigd door Spiral op 22-06-2010 13:06 . Reden: toelichting ]

[cariolive23]

Spiral schreef op dinsdag 22 juni 2010 @ 12:59:
Hoe evalueert de WHERE clausule '-999' dan naar true?

Jij haalt een url aan waar jij aangeeft dat het gedocumenteerd is, maar dat zie ik niet terug in de die url. Voor mij is het duidelijk dat er bij de WHERE clausule een evaluatie plaatsvind.

Ik ben het niet met je eens dat dit gedrag gedocumenteerd is.

Uit de handleiding:

A value of zero is considered false. Nonzero values are considered true

-999 is geen 0, dus nonzero, en wordt als boolean dus gecast naar een true.

Het staat er echt, ik ben dus van mening dat dit is gedocumenteerd. Dat het extreem vaag gedrag is, dat ben ik ook met je eens, het is niet voor niets dat ik een hekel heb aan MySQL.

[Spiral]

Ok! De query voldoet aan het laatste punt van de lijst nl.

•In all other cases, the arguments are compared as floating-point (real) numbers.

SQL:

SELECT CAST('23test' AS SIGNED)

Geeft 23

SQL:

SELECT CAST('test23' AS SIGNED)

Terwijl deze 0 teruggeeft.

Aangezien 0 gelijk is aan false en alle andere reële waarden gelijkstaan aan true zijn. Zou MySQL alleen de resultaten weergeven waar het begint met een getal ongelijk aan 0 zelf.

Bedankt voor de verdere discussie cariolive23.

[cariolive23]

Precies! En nu zullen jouw resultaten ook duidelijk zijn, je had blijkbaar varchars in jouw database staan die begonnen met een getal en dankzij het vage cast-gedrag van MySQL werden omgezet naar een true en dus een match opleverden.

Dit gedrag maakt MySQL een lastige en kostbare database, het is afhankelijk van jouw data en queries of de zaken toevallig goed of fout gaan. Het resultaat is onvoorspelbaar, met Oracle, SQL Server of PostgreSQL weet je heel zeker dat een string nooit zomaar wordt gecast naar een boolean en krijg je foutmeldingen wanneer je een fout in je SQL maakt. Dat scheelt dus tijd, geld en frustratie.

SQL, NoSQL of NoMySQL? That's the question!