0

Using MariaDB 10.3.9 and given the following query:

SELECT photoId FROM photos WHERE photoId = 123;

using PDO and mysqlnd in php var_dump will give int(123), as expected.

But if I change the query to

SELECT photoId FROM photos WHERE photoId = 123 UNION SELECT 1 FROM photos WHERE photoId = 456;

Then the output becomes string(3) "123", string(1), "1".

If I change the query to

SELECT photoId FROM photos WHERE photoId = 123 UNION SELECT CAST(1 AS UNSIGNED INT) FROM photos WHERE photoId = 456;

I correctly get int(123), int(1).

This seems like very strange behaviour - why is MariaDB choosing to cast the explicit 1 to a string, rather than leave it as the integer it's written as?

Weirder still, I have photoId defined as unsigned. If I just use CAST(1 AS INT) it still comes through as a string, just because I missed the UNSIGNED part.

Codemonkey
  • 4,455
  • 5
  • 44
  • 76

1 Answers1

0

To MySQL... If the column photoId is INT (or any other numeric type), then either of these work equally well:

WHERE photoId = 123
WHERE photoId = '123'
WHERE photoId = "123"

If the column photoId is a VARCHAR, then you should do one of these:

WHERE photoId = '123'
WHERE photoId = "123"

Failure to use quote the string defeats the use of an index, thereby making the query run slow.

PDO will do the 'right thing'.

Don't bother with any CASTing.

My experience says that the first SELECT in a UNION controls the datatype. So I, too, am surprised by your results.

Rick James
  • 135,179
  • 13
  • 127
  • 222