Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.2.38
-
None
-
Linux jitsi.kgs-born.de 4.15.0-144-generic #148-Ubuntu SMP Sat May 8 02:33:43 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
Description
If i compare a bigint with a varchar i get a wrong result
here a sample:
This query should only find 1 row, but it returns 2 rows
MariaDB [bernd]> desc big;
|
+-------+------------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+------------------+------+-----+---------+----------------+
|
| id | int(11) unsigned | NO | PRI | NULL | auto_increment | |
| bi | bigint(11) | YES | | NULL | | |
| vc | varchar(20) | YES | | NULL | | |
+-------+------------------+------+-----+---------+----------------+
|
3 rows in set (0.01 sec) |
|
|
MariaDB [bernd]> select * from big;
|
+----+--------------------+--------------------+
|
| id | bi | vc |
|
+----+--------------------+--------------------+
|
| 1 | 137226000008073943 | 137226000008073943 | |
| 2 | 137226000008073929 | 137226000008073929 | |
+----+--------------------+--------------------+
|
2 rows in set (0.05 sec) |
|
|
MariaDB [bernd]> select * from big where vc = 137226000008073943; |
+----+--------------------+--------------------+
|
| id | bi | vc |
|
+----+--------------------+--------------------+
|
| 1 | 137226000008073943 | 137226000008073943 | |
| 2 | 137226000008073929 | 137226000008073929 | |
+----+--------------------+--------------------+
|
2 rows in set (0.00 sec) |
|
|
MariaDB [bernd]>
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-
MariaDB [test]> select '137226000008073943' = 137226000008073929;+--------------------------------------------+| '137226000008073943' = 137226000008073929 |+--------------------------------------------+| 1 |+--------------------------------------------+1 row in set (0.00 sec)I guess the problem is described here https://mariadb.com/kb/en/type-conversion/#rules-for-conversion-on-comparison and https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
please use CAST to avoid the problem (select * from t1 where vc = CAST(137226000008073943 AS CHAR); )