Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.7, 10.5.9
-
Centos7, MariaDB standalone
Description
Hi
I came across this behavior in 10.5.7 (and also verified with 10.5.9) and am wondering if it's a BUG or if it has to do with any parameter configuration.
MariaDB [test]> select version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.5.6-MariaDB |
|
+----------------+
|
(This issue happens with and without primary key)
MariaDB [test]> create table test(id bigint, name varchar(10), location varchar(10));
|
Query OK, 0 rows affected (0.038 sec)
|
|
|
MariaDB [test]> insert into test values (1,'John','Dublin'), (2, 'Ann', 'London'), (3, 'Pat', 'Paris'), (3, 'Maria', 'Tokyo');
|
Query OK, 4 rows affected (0.006 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select * from test;
|
+------+-------+----------+
|
| id | name | location |
|
+------+-------+----------+
|
| 1 | John | Dublin |
|
| 2 | Ann | London |
|
| 3 | Pat | Paris |
|
| 3 | Maria | Tokyo |
|
+------+-------+----------+
|
Now whatever values I provide in the WHERE condition along with the ID, it returns the row that matches the first column (ID) in the query
MariaDB [test]> select * from test where id='1 Thomas Edison';
|
+------+------+----------+
|
| id | name | location |
|
+------+------+----------+
|
| 1 | John | Dublin |
|
+------+------+----------+
|
1 row in set, 1 warning (0.001 sec)
|
|
|
MariaDB [test]> select * from test where id='1@Thomas£Edison';
|
+------+------+----------+
|
| id | name | location |
|
+------+------+----------+
|
| 1 | John | Dublin |
|
+------+------+----------+
|
1 row in set, 1 warning (0.001 sec)
|
|
|
MariaDB [test]> select * from test where id='2 is it a bug';
|
+------+------+----------+
|
| id | name | location |
|
+------+------+----------+
|
| 2 | Ann | London |
|
+------+------+----------+
|
|
|
MariaDB [test]> select * from test where id='whoever';
|
Empty set, 1 warning (0.001 sec)
|
|
|
MariaDB [test]> select * from test where id='whoever3';
|
Empty set, 1 warning (0.001 sec)
|
|
|
MariaDB [test]> select * from test where id='3whoever';
|
+------+-------+----------+
|
| id | name | location |
|
+------+-------+----------+
|
| 3 | Pat | Paris |
|
| 3 | Maria | Tokyo |
|
+------+-------+----------+
|
Attachments
Activity
Hi Marko
That warning you see in the result is from this error (from a SQL with a typo)
{{MariaDB [test]> select * from test where id='1 Thomas Edison');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
MariaDB [test]> show global variables like 'SQL_MODE';
--------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
--------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
--------------------------------------------------------------------------------------------------------+
}}
I am getting the same wrong results even with strict SQL_MODE
MariaDB [test]> SET SQL_MODE=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from test where id='3whoever';
-------------------
| id | name | location |
-------------------
| 3 | Pat | Paris |
| 3 | Maria | Tokyo |
-------------------
2 rows in set, 1 warning (0.001 sec)
MariaDB [test]> show warnings;
---------------------------------------------------------
| Level | Code | Message |
---------------------------------------------------------
| Warning | 1292 | Truncated incorrect DOUBLE value: '3whoever' |
---------------------------------------------------------
By the way, as far as I can see, this issue (or the bug) seems to happen only when the first column of the table is in the WHERE clause
Did you check the output of SHOW WARNINGS? Does it warn something about converting a string to integer? Did you try after the following?