SQL - Truncated incorrect DOUBLE value

0
votes

I keep having an issue with my SQL query, and I'm not able to resolve it myself after various attempts and research.

The error:

Warning: #1292 Truncated incorrect DOUBLE value: 'TITLE1_00'

Warning: #1292 Truncated incorrect DOUBLE value: 'TITLE2'

Warning: #1292 Truncated incorrect DOUBLE value: '_00'

My query:

SELECT t1.titleid
FROM tmdb t1
LEFT JOIN patch t2
ON t1.titleid = t2.name + '_00'
WHERE t2.name IS NULL

Structure:

tmdb: titleid(varchar)(15)

patch: name(varchar15)(15)

I've tried:

name(varchar)(15) was name(varchar)(9), and I've changed this to 15.

I've ran the query in phpmyadmin in both databases.

Does anyone know why this is happening, and what the solution is?

The question was marked as a duplicate. The answer in the linked post does not answer my question as this query is different. As mentioned in this post I've done research and already came across this post!

2
mysqlsql
@UnhandledExcepSean I've seen this post, and this does certainly not answer my question. The query isn't even close with what's posted on there. – Appel Flap
Please double check the post before marking it as a duplicate. – Appel Flap
this t2.name + '_00' not work in MySQL use CONCAT(t2.name , '_00') – Bernd Buffen
@BerndBuffen Thanks for helping out. That indeed worked! – Appel Flap

2 Answers

1
votes

+ is numeric addition, not string concatenation... so MySQL tries to cast your values to numbers. Having no better plan, it goes for the fallback type of DOUBLE (64 bit floating point value). But since your strings don't make very convincing numbers, you get warnings.

The correct construct for string concatenation:

ON t1.titleid = CONCAT(t2.name, '_00')
0
votes

What's the point of that SQL? Could you give an example of data and result what you need?

You try to add '_00' to null. Isn't it mistake in where clause?

Did you try with CONCAT(ifnull(t2.name,''),'_00') ?