[MDEV-29634] Wrong Results from query Created: 2022-09-26  Updated: 2022-09-26  Resolved: 2022-09-26

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.5.7, 10.5.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Venkat Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: sql, wrong_result
Environment:

Centos7, MariaDB standalone

Attachments: Text File SQL_Bug.txt    

 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    |
 
+------+-------+----------+



 Comments   
Comment by Marko Mäkelä [ 2022-09-26 ]

Did you check the output of SHOW WARNINGS? Does it warn something about converting a string to integer? Did you try after the following?

SET SQL_MODE=STRICT_ALL_TABLES;

Comment by Venkat [ 2022-09-26 ]

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

--------------------------------------------------------------------------------------------------------+
}}

Comment by Venkat [ 2022-09-26 ]

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'

---------------------------------------------------------

Comment by Venkat [ 2022-09-26 ]

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

Comment by Sergei Golubchik [ 2022-09-26 ]

https://mariadb.com/kb/en/type-conversion/#rules-for-conversion-on-comparison

Generated at Thu Feb 08 10:10:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.