| Bug #105825 | query get wrong result with in expression | ||
|---|---|---|---|
| Submitted: | 8 Dec 2021 7:35 | Modified: | 15 Dec 2021 3:23 |
| Reporter: | x j | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Dec 2021 13:44]
MySQL Verification Team
Hi Mr. j,
Thank you for your bug report.
However, it is not a bug.
Beside character set and collation, there are also rules of precedence, which are explained in our Reference Manual, in the chapters on the character sets.
Simply, the predicate of the searched tuple takes precedence of the set of tuples where the search is made.
It is easily proven:
mysql> set names utf8mb4 collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> select ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci));
+----------------------------------------------------------------------------------+
| ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci)) |
+----------------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci), ('b', 'b'));
+----------------------------------------------------------------------------------------------+
| ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci), ('b', 'b')) |
+----------------------------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Not a bug.
[13 Dec 2021 3:32]
x j
> the predicate of the searched tuple takes precedence of the set of tuples where the search is made.
I do not quite understand this, do you mean, in my case, we should use ('a','a')'s collation?
you may notice that the result of the two queries in my case is different, is that still expected? It does not make sense.
[13 Dec 2021 12:53]
MySQL Verification Team
Hi, Yes, as we already explained, what you report is the expected behaviour, due to the coercibility rules.
[15 Dec 2021 3:23]
x j
Thank you, I get it.
BTW,
mysql> select ('a' collate utf8mb4_bin, 'a' collate utf8mb4_bin) in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci));
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_general_ci,EXPLICIT) for operation '='
I found that, if the searched tuple has only one element, it will be rewritten to `=`, which will lead to this error. I think it will surprised the users
Description: in expression get a different result How to repeat: set names utf8mb4 collate utf8mb4_bin mysql> select ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci)); +----------------------------------------------------------------------------------+ | ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci)) | +----------------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci), ('b', 'b')); +----------------------------------------------------------------------------------------------+ | ('a', 'a') in (('A' collate utf8mb4_general_ci, 'A' collate utf8mb4_general_ci), ('b', 'b')) | +----------------------------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) as above, I do not understand why it returns 0.