MySQL Bugs: #110720: Contribute by tencent: prepared statement can't execute match+rollup twice.

Bug #110720	Contribute by tencent: prepared statement can't execute match+rollup twice.
Submitted:	19 Apr 2023 1:21	Modified:	19 Apr 2023 6:09
Reporter:	tianfeng li (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S6 (Debug Builds)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	fulltext search, prepared statement, rollup

Description:
In prepared statement, a fulltext search with rollup could not execute twice, then arise crash like the following:

build/runtime_output_directory/mysqld(Query_block::check_column_privileges(THD*)+0x258) [0x347b09a]
build/runtime_output_directory/mysqld(Sql_cmd_select::check_privileges(THD*)+0x17c) [0x3478f2a]
build/runtime_output_directory/mysqld(Sql_cmd_dml::execute(THD*)+0x345) [0x3477c37]
build/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x5349) [0x33f8066]
build/runtime_output_directory/mysqld(Prepared_statement::execute(THD*, String*, bool)+0x883) [0x343c8e9]
build/runtime_output_directory/mysqld(Prepared_statement::execute_loop(THD*, String*, bool)+0x2eb) [0x343aeef]
build/runtime_output_directory/mysqld(mysqld_stmt_execute(THD*, Prepared_statement*, bool, unsigned long, PS_PARAM*)+0x1f8) [0x34380ab]

How to repeat:

```
SET optimizer_switch = 'hypergraph_optimizer=on';
CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x));
INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz');
ANALYZE TABLE t;
prepare stmt1 from "SELECT MATCH (x) AGAINST ('abc') AS score FROM t  GROUP BY score WITH ROLLUP HAVING score > 0";
execute stmt1;
execute stmt1;
```

In stmt1, having clause is unnecessary to repeat the crash.

Hello tianfeng li,

Thank you for the report and test case.
Observed that 8.0.33 debug build is affected(hypergraph optimizer does not yet support 'use in non-debug builds').

regards,
Umesh

- 8.0.33 release build - NA

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> SET optimizer_switch = 'hypergraph_optimizer=on';
ERROR 3999 (42000): The hypergraph optimizer does not yet support 'use in non-debug builds'
mysql> CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x));
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> prepare stmt1 from "SELECT MATCH (x) AGAINST ('abc') AS score FROM t  GROUP BY score WITH ROLLUP HAVING score > 0";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt1;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'reading non-aggregated results of the MATCH full-text search function after GROUP BY WITH ROLLUP'
mysql> execute stmt1;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'reading non-aggregated results of the MATCH full-text search function after GROUP BY WITH ROLLUP'
mysql>
mysql> shutdown;

- 8.0.33 debug build - affected

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.33-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> SET optimizer_switch = 'hypergraph_optimizer=on';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x));
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> prepare stmt1 from "SELECT MATCH (x) AGAINST ('abc') AS score FROM t  GROUP BY score WITH ROLLUP HAVING score > 0";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt1;
+--------------------+
| score              |
+--------------------+
| 0.0492168664932251 |
| 0.0984337329864502 |
+--------------------+
2 rows in set (0.00 sec)

mysql> execute stmt1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

(gdb) bt
#0  0x00007ff082a47aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003f9296e in my_write_core(int) ()
#2  0x00000000032365f5 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x000000000311f175 in Query_block::check_column_privileges(THD*) ()
#5  0x000000000311f43b in Sql_cmd_select::check_privileges(THD*) ()
#6  0x000000000312370f in Sql_cmd_dml::execute(THD*) ()
#7  0x00000000030c4964 in mysql_execute_command(THD*, bool) ()
#8  0x00000000030f16b6 in Prepared_statement::execute(THD*, String*, bool) ()
#9  0x00000000030f47ca in Prepared_statement::execute_loop(THD*, String*, bool) ()
#10 0x00000000030f4ef1 in mysql_sql_stmt_execute(THD*) ()
#11 0x00000000030c1947 in mysql_execute_command(THD*, bool) ()
#12 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#13 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#14 0x00000000030c926e in do_command(THD*) ()
#15 0x0000000003227bb7 in handle_connection ()
#16 0x0000000004743af9 in pfs_spawn_thread ()
#17 0x00007ff082a42ea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007ff080d49b2d in clone () from /lib64/libc.so.6
(gdb)