Strange Behavior

[NancyJ]

I've just discovered a strange bug with a new client's site, it wasn't noticed because everything seemed to be working fine.

The client has 'friendly' urls and it was greedily capturing too much for the id number. I've fixed that but I'm confused as to why it was working.

eg. this query:
SELECT *
FROM attika_properties
WHERE id = '329/Villefranche--Cap-Ferrat-Property'

Now the id = 329 but that record comes back correctly. The only thing I can think of is its because the id is being quoted as a string (good thing too, since it is incorrectly a string) and mysql is somehow, rather cleverly understanding what is being meant.

[awatson]

Perhaps the ID is setup as a int, so mysql is throwing out the non-numeric characters, or even just ignoring everything once it hits the first non-numeric character....

[Fumigator]

I agree, I wouldn't ever imagine that would work.

[ralph l mayo]

Try it in the official client and use show warnings; afterwards

mysql> select * from a where id = '1/some junk here';
+----+
| id |
+----+
| 1 |
+----+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1/some junk here' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1/some junk here' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

So it's not just dropping non-digits, it's truncating the value (twice?)
the id is not actually a double, so I don't know where it gets that.

compare:

mysql> select * from a where id = 'some junk in front this time/1';
Empty set (0.00 sec)


NB: No warnings?!?!