MySQL Bugs: #117211: Error triggered by least operator

Bug #117211	Error triggered by least operator
Submitted:	15 Jan 13:44	Modified:	15 Jan 14:53
Reporter:	Chenglin Tian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	9.0.1	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
When I executed a SQL statement, I encountered an error that was not in line with my expectations.
This is my table creation statement:

 CREATE DATABASE database0;
 USE database0;
 CREATE TABLE t0(c0 INT ZEROFILL  COMMENT 'asdf'   COLUMN_FORMAT FIXED  STORAGE MEMORY) ;
 INSERT LOW_PRIORITY IGNORE INTO t0(c0) VALUES("-7");

After creating the table and inserting data, the following data exists in the t0 table:

 SELECT t0.c0 AS ref0 FROM t0; 
 --[0]

However, when performing conditional retrieval, the following query does not meet my expected output:

 SELECT t0.c0 AS ref0 FROM t0 WHERE (LEAST(t0.c0, 0.20694762514557385, '7zk7'));
 --expected:[]  actual:[0]

At this time, the c0 column of the t0 table has only one data, which is 0, so the result of the condition (LEAST(t0.c0, 0.20694762514557385)) should be 0, then query 2 should not return any results, but in fact it returns one data. To verify my idea, I replaced t0.c0 in the query with data 0 and got a result containing one data:

 SELECT t0.c0 AS ref0 FROM t0 WHERE (LEAST(0, 0.20694762514557385, '7zk7'));
 --expected:[0]  actual:[0]

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE t0(c0 INT ZEROFILL  COMMENT 'asdf'   COLUMN_FORMAT FIXED  STORAGE MEMORY) ;
INSERT LOW_PRIORITY IGNORE INTO t0(c0) VALUES("-7");
--SELECT t0.c0 AS ref0 FROM t0 WHERE (LEAST(t0.c0, 0.20694762514557385, '7zk7'));

Suggested fix:
I think this may have something to do with the least function not getting the correct data when processing the column references, could you please tell me if this is a bug, thank you

HI Mr. Tian,

Thank you for your bug report.

However, this is not a bug, but expected behaviour.

We have got the following results:

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE (LEAST(t0.c0, 0.20694762514557385, '7zk7'));
+------------+
| ref0       |
+------------+
| 0000000000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE (LEAST(0, 0.20694762514557385, '7zk7'));
Empty set (0.00 sec)

These are expected results since you are mixing totally incompatible data types, like integer, floating point and strings.

That goes 100 % contrary to the valid SQL standards, which demand a return of the hard error.

MySQL tries to find a common denominator, which  avoids hard errors, but you can not expect results that you imagine you should get.

Your mixing of the incompatible data types is so extreme, that you use a string that can not be converted to any numeric type.

This is definitely not a bug.

Thanks for your reply.

We made further attempts. The following query can return results, indicating that the condition is true.

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE LEAST(t0.c0, 0.20694762514557385, '7zk7');
+------------+
| ref0       |
+------------+
| 0000000000 |
+------------+
1 row in set (0.00 sec)

The following query cannot return results, indicating that the condition is false.

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0;
Empty set (0.00 sec)

Then the following query condition: (NOT ((LEAST(t0.c0, 0.20694762514557385, '7zk7'))OR (t0.c0))) is equivalent to not((true)or(false)). The result should be false, but why is one piece of data retrieved? Is this contradictory?

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE (NOT ((LEAST(t0.c0, 0.20694762514557385, '7zk7'))OR (t0.c0)));
+------------+
| ref0       |
+------------+
| 0000000000 |
+------------+
1 row in set (0.00 sec)

No, it is not contradictory since your filtering does not have a condition. 

You can do without a condition if your expression always returns a boolean. LEAST() can never return a boolean.

I understand what you mean, but may I ask what the condition of this query (NOT (LEAST (t0. c0, 0.20694762514557385,'7zk7 ') OR (t0. c0)) should return? Because the data was retrieved, the condition should be true, which means that the condition in' not 'should return false, which means that' LEAST (t0. c0, 0.20694762514557385,'7zk7 ')' OR (t0. c0) should return false. Only when both sides of 'or' are 0, will the expression return false. Therefore, 'LEAST (t0. c0, 0.20694762514557385,'7zk7') should be 0, but according to your previous statement, the following query result is correct, so (LEAST (t0. c0, 0.20694762514557385, '7zk7') should not be 0.

mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE LEAST(t0.c0, 0.20694762514557385, '7zk7');
+------------+
| ref0       |
+------------+
| 0000000000 |
+------------+
1 row in set (0.00 sec)

Sorry, I really don't understand why this is happening. Could you please help me? Thank you

Hi,

There is truly nothing to explain.

Function LEAST() returns smallest of it's arguments ..... 

It does not return BOOLEAN ....... 

Hence, if you introduce OR, you get into a second stage of the search for the common denominator and results are unpredictable.

We recommend that you read some good book on how to design SQL queries .......

This is a forum for the bugs with fully repeatable test cases.

And this is definitely not a bug .....

This is NOT a forum for asking questions ....

We also have forums for free and paid support. If you wish us to send you links to those two forums, please let us know .....