[MDEV-30709] the query result is incorrect when use 'group by' Created: 2023-02-22  Updated: 2023-04-04  Resolved: 2023-03-30

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.5.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: guozhentang Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

centos 7.6

Attachments: PNG File image-2023-02-22-20-31-53-471.png     File sql_file.rar    

 Description   

Step 1: Create a table and insert data
1.Create the database sqltester and enter the database
create database sqltester
use sqltester
2.Execute the attachment file to create a table and insert data
source sqltester_for_parallel_with_index.2k_dp_210col.index_desc.sql
3.Execute the attachment file to create a table and insert data
source sqltester_for_parallel_with_index_view_part_tab.sql
Step 2: Execute the query

      select `subq_1`.`c1` AS `c0`,`subq_1`.`c0` AS `c1` from (select pi() AS `c0`,`subq_0`.`c1` AS `c1` from (`t10` `ref_0` left join (select `ref_1`.`c_ytd_payment` AS `c0`,pi() AS `c1` from `t4` `ref_1` where (`ref_1`.`c_first` is null)) `subq_0` on((`subq_0`.`c0` is not null))) where true) `subq_1` where true;

Step 3: Execute the query

      select `subq_1`.`c1` AS `c0`,`subq_1`.`c0` AS `c1` from (select pi() AS `c0`,`subq_0`.`c1` AS `c1` from (`t10` `ref_0` left join (select `ref_1`.`c_ytd_payment` AS `c0`,pi() AS `c1` from `t4` `ref_1` where (`ref_1`.`c_first` is null)) `subq_0` on((`subq_0`.`c0` is not null))) where true) `subq_1` where true group by 1,2;

The expected result is that the query result will not change after the query is executed, but it will actually change the query result

 Comments   
Comment by Alice Sherepa [ 2023-03-30 ]

Thank you for the report!
I repeated as described on 10.5.16, but 10.5.19 returns the correct result, so apparently the bug was fixed. 

MariaDB [sqltester]> select `subq_1`.`c1` AS `c0`,`subq_1`.`c0` AS `c1` from (select pi() AS `c0`,`subq_0`.`c1` AS `c1` from (`t10` `ref_0` left join (select `ref_1`.`c_ytd_payment` AS `c0`,pi() AS `c1` from `t4` `ref_1` where (`ref_1`.`c_first` is null)) `subq_0` on((`subq_0`.`c0` is not null))) where true) `subq_1` where true;
 
+----+----------+
 
| c0 | c1       |
 
+----+----------+
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
| NULL | 3.141593 |
 
+----+----------+
 
27 rows in set (0,001 sec)
 
 
 
MariaDB [sqltester]> select `subq_1`.`c1` AS `c0`,`subq_1`.`c0` AS `c1` from (select pi() AS `c0`,`subq_0`.`c1` AS `c1` from (`t10` `ref_0` left join (select `ref_1`.`c_ytd_payment` AS `c0`,pi() AS `c1` from `t4` `ref_1` where (`ref_1`.`c_first` is null)) `subq_0` on((`subq_0`.`c0` is not null))) where true) `subq_1` where true group by 1,2;
 
+------+----------+
 
| c0   | c1       |
 
+------+----------+
 
| NULL | 3.141593 |
 
+------+----------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [sqltester]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.5.19-MariaDB |
 
+-----------------+
 
1 row in set (0,000 sec)

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