SQL Query Weirdness When Selecting an Entry ID From Comments Table
Development and Programming
I’m running this SQL on the DB directly in Navicat:
SELECT COUNT(*) AS count
FROM exp_comments
WHERE entry_id = '448'
AND author_id = '1'That returns ‘2’ as it should, because there are two rows that match this criteria. I’m trying to develop a plugin and testing for various ways to make it fail, and I tried this:
SELECT COUNT(*) AS count
FROM exp_comments
WHERE entry_id = '448aldbgkadufbg'
AND author_id = '1'That also returns ‘2’ - and there is obviously no comment with an entry_id like that. Any idea why that is happening?
Is that second SQL statement you are running being run from inside Navicat also or from within your plugin?
I just tried that same sort of thing in Sequel Pro on a site that I have and came back with no comments as you would expect. I suspect something a little iffy up there but not sure if that was run in Navicat or your plugin?
Best wishes,
Mark
The response you are seeing is because you are trying to compare an integer column to a string value. In that case, MySQL will type-cast the string literal value to an integer, and when it does that it starts from the left of the string and as soon as it reaches a character that cannot be considered part of a number, it strips out everything from that point on. So trying to compare “448aldbgkadufbg” to an integer column will result in actually comparing the number 448.
Hi Silenz,
Very interesting read that one. Didn’t totally understand it 😉 but interesting. Just wondering why it didn’t do that to me though when I tested using Sequel Pro I do have an entry_id of 48 and it does have comments in it so I would have expected it to fail under these conditions? Would this problem only be due to making a plugin or in certain SQL applications and not others perhaps?
Best wishes,
Mark
It’s like type conversion in PHP.
> $foo = 5 + "10 Small Pigs";
> echo $foo;
> 15MySQL sees that the column is of the type integer and you are trying to compare it against a string, so it tries to convert the string into a number which succeeds if the string actually starts with a number. Any string that does not evaluates as 0 if compared to a number.
This is working as intended so I didn’t know whether that Sequel Pro handles things differently than MySQL planned.
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
Thanks, silenz. I don’t see how my example would ever actually happen, but it’s good to know why that’s happening.
It’s like type conversion in PHP.> $foo = 5 + "10 Small Pigs"; > echo $foo; > 15
Thanks for the mini tutorial there. Had never seen that before. Explained it to me right away, thanks.
Best wishes,
Mark