MySQL Bugs: #3777: inner join between columns of a different type containing zeros fouls up

Bug #3777	inner join between columns of a different type containing zeros fouls up
Submitted:	15 May 2004 18:08	Modified:	17 May 2004 14:27
Reporter:	Are you mortal Then prepare to die.	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.15a-standard	OS:	dec-osf5.1 on alphaev67
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
I am performing an INNER JOIN between two columns in two tables.

Rows containg the value zero (int) are returned by the join, as if the two columns match.

The value zero (int) is matching promiscuously with values in the varchar column.

How to repeat:
drop table if exists one;
create temporary table one (
one varchar(20) not null
);

drop table if exists two;
create temporary table two (
two int not null
);

insert into one values('A');
insert into one values('B');
insert into one values('C');
insert into one values('D');

insert into two values(0);
insert into two values(0);
insert into two values(0);
insert into two values(0);

SELECT * FROM one INNER JOIN two ON one.one = two.two;

+-----+-----+
| one | two |
+-----+-----+
| A   |   0 |
| B   |   0 |
| C   |   0 |
| D   |   0 |
| A   |   0 |
| B   |   0 |
| C   |   0 |
| D   |   0 |
| A   |   0 |
| B   |   0 |
| C   |   0 |
| D   |   0 |
| A   |   0 |
| B   |   0 |
| C   |   0 |
| D   |   0 |
+-----+-----+
16 rows in set (0.00 sec)
 

Suggested fix:

Perhaps this relates to the handling of unsigned ints, where the subtraction of one unsigned int from anoter smaller unsigned int gives an incorrect answer.

I think the latter is a bug, as the behaviour below does just what you would expect...

drop table if exists one;
create temporary table one (
one tinyint unsigned not null
);

drop table if exists two;
create temporary table two (
two tinyint unsigned not null
);

insert into one values(50);
insert into two values(100);

SELECT one.one * two.two from one, two;
+-------------------+
| one.one * two.two |
+-------------------+
|              5000 |
+-------------------+

But this does not...

SELECT one.one - two.two from one, two;

Perhaps related to this problem.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected behavior.
The arguments (string and number) are compared as floating-point numbers:
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html

Try the following:
select 'A'=0;

This sucks!

In my opinion this is definatly a bug - Why cant mysql do propper type casting like other programming languages can?

Is this an ISO issue?

I wonder how many errors these two issues account for in the real world.

Thanks for the link to the 
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html

This is good stuff...

In general how am I to 'predict' the behaviour of an equality between numeric and string values...

for example 

select mid(1001001001,2,2) > 10;

This is really bad...

I use the above syntax to encode a hierarchy...

1.1.1.1 = 1001001001001

Level 1.1 = left(7,1001001001001);

I can then do all kinds of neat tricks which now I find out may be failing or in fact promiscuously matching.

How can I force string or numeric comparison?

Why cant mysql do this for me?

You can use CAST() (or CONVERT()) function.
Take a look at:
http://dev.mysql.com/doc/mysql/en/Cast_Functions.html
http://dev.mysql.com/doc/mysql/en/Charset-CAST.html

Thanks for these tips.

Can I ask if the behaviour of the comparison operator is specified by the ISO SQL standard?

Do all databases share the same rules, or are some of the rules given at http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html peculiar to mysql?

Cheers

That behavior is non-standart. 
ISO standard doesn't allow implicit type cast.

Please, ask your questions about mysql behaviour vs others at
mysql@lists.mysql.com.

I don't understand your reply, what behaviour is non-standard?

Is the behaviour of mysql comparison operator standard?

I think it is good to get this infor hear associated with this bug report, that way when people search they get all the associated information they need.

I will try 

mysql@lists.mysql.com, but in my opinion this should be a news group rather than a mailing list.

behaviour of comparison operator is nonstandard.
According to the standard ... WHERE "A"=0 should generate an error - you cannot compare an integer to a string.

It is planned to add a swich to enable this strict type checking.