Details
Description
--source include/have_innodb.inc
|
|
create table t (pk int primary key, a int, key(a)) engine=InnoDB; |
insert into t values (1,NULL),(2,8),(3,5),(4,NULL),(5,10); |
|
SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; |
|
# Cleanup
|
drop table t; |
10.5 acd23da4 |
--source include/have_innodb.inc
|
|
create table t (pk int primary key, a int, key(a)) engine=InnoDB; |
insert into t values (1,NULL),(2,8),(3,5),(4,NULL),(5,10); |
|
SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; |
|
# Cleanup
|
drop table t; |
10.5 acd23da4 |
SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; |
MIN(pk) a |
3 5
|
2 8
|
5 10
|
EXPLAIN EXTENDED SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY a 5 NULL 6 100.00 Using where; Using index for group-by |
Warnings:
|
Note 1003 select min(`test`.`t`.`pk`) AS `MIN(pk)`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`pk` <> 1 group by `test`.`t`.`a` |
A row for (4,NULL) is missing.
10.4 doesn't use index for group-by, and the result there is correct.
Attachments
Issue Links
- is caused by
-
MDEV-23634 Select query hanged the server and leads to OOM in MariaDB 10.4.14
-
- Closed
-
The issue is in how aggregate rows are read in and filtered.
Thread 14 "mariadbd" hit Breakpoint 4, rr_quick (info=0x7fff9801a8a8) at /home/rex/src/mariadb/server.30605/sql/records.cc:403
403 while ((tmp= info->select->quick->get_next()))
(gdb) n
408 return tmp;
(gdb) p info->table
$63 = (TABLE *) 0x7fff98066f88
(gdb) p dbp(info->table)
$64 = 0x5555577e2300 <dbug_print_row_buff> "t(pk,a)=(1,NULL)"
(gdb) cont
Continuing.
Thread 14 "mariadbd" hit Breakpoint 4, rr_quick (info=0x7fff9801a8a8) at /home/rex/src/mariadb/server.30605/sql/records.cc:403
403 while ((tmp= info->select->quick->get_next()))
(gdb) n
[New Thread 0x7fffba7fc700 (LWP 15823)]
408 return tmp;
(gdb) p dbp(info->table)
$65 = 0x5555577e2300 <dbug_print_row_buff> "t(pk,a)=(3,5)"
(gdb) cont
Continuing.
Thread 14 "mariadbd" hit Breakpoint 4, rr_quick (info=0x7fff9801a8a8) at /home/rex/src/mariadb/server.30605/sql/records.cc:403
403 while ((tmp= info->select->quick->get_next()))
(gdb) n
408 return tmp;
(gdb) p dbp(info->table)
$66 = 0x5555577e2300 <dbug_print_row_buff> "t(pk,a)=(2,8)"
(gdb) cont
Continuing.
Thread 14 "mariadbd" hit Breakpoint 4, rr_quick (info=0x7fff9801a8a8) at /home/rex/src/mariadb/server.30605/sql/records.cc:403
403 while ((tmp= info->select->quick->get_next()))
(gdb) n
408 return tmp;
(gdb) p dbp(info->table)
$67 = 0x5555577e2300 <dbug_print_row_buff> "t(pk,a)=(5,10)"
(gdb) cont
Continuing.
It appears to skip past the rest of the group (a==NULL) without further examination.