MySQL Bugs: #110341: FOUND_ROWS() gives wrong result on prepared query against partitioned table

Bug #110341	FOUND_ROWS() gives wrong result on prepared query against partitioned table
Submitted:	10 Mar 2023 17:18	Modified:	13 Mar 2023 7:50
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
FOUND_ROWS() returns 0 in error for a prepared query with parameters against a partitioned table.

This results in connector functions like `row_count()` returning 0 unexpectedly for some queries.

This seems to be a bug that appeared after 8.0.30. I tested on MySQL 8.0.30 and 8.0.32.

How to repeat:
SELECT VERSION(); 

SET @p1 = '2023-02-03', @p2='2023-02-25';

CREATE TABLE t1 (
    created_at DATE
) ;

INSERT INTO t1 VALUES (@p1), (@p2);

SET @s = "select * from t1 where created_at in (?, ?)";

PREPARE stmt FROM @s;

EXECUTE stmt USING @p1, @p2; -- returns 2 rows as expected

SELECT FOUND_ROWS(); -- returns count of 2 as expected

DEALLOCATE PREPARE stmt;

CREATE TABLE t2 (
    created_at DATE
) 
PARTITION BY RANGE (DAYOFMONTH(created_at)) (
    PARTITION p1 VALUES LESS THAN (16), 
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

INSERT INTO t2 VALUES (@p1), (@p2);

SET @s = "select * from t2 where created_at in (?, ?)";

PREPARE stmt FROM @s;

EXECUTE stmt USING @p1, @p2; -- returns 2 rows as expected

SELECT FOUND_ROWS(); -- returns count of 0, this is incorrect

DEALLOCATE PREPARE stmt;

Further test: It seems to be a problem related only to using parameters. If I use a prepared query with fixed values (no parameters), then FOUND_ROWS() correctly reports 2.

SET @s = CONCAT('select * from t2 where created_at in (', QUOTE(@p1), ',', QUOTE(@p2), ')');

prepare stmt from @s;

execute stmt; -- returns 2 rows as expected

select found_rows(); -- returns count of 2 as expected

Hello Bill,

Thank you for the report and feedback.

Thanks,
Umesh

Also, SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future release. Consider using COUNT(*) instead. Please see https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

- 5.7 not affected

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.41 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.00 sec)

mysql> use test
Database changed
mysql> SELECT VERSION();

SET @p1 = '2023-02-03', @p2='2023-02-25';

CREATE TABLE t1 (
    created_at DATE
) ;

INSERT INTO t1 VALUES (@p1), (@p2);

SET @s = "select * from t1 where created_at in (?, ?)";

PREPARE stmt FROM @s;

EXECUTE stmt USING @p1, @p2; -- returns 2 rows as expected

SELECT FOUND_ROWS(); -- returns count of 2 as expected
+-----------+
| VERSION() |
+-----------+
| 5.7.41    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> SET @p1 = '2023-02-03', @p2='2023-02-25';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE t1 (
    ->     created_at DATE
    -> ) ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t1 VALUES (@p1), (@p2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SET @s = "select * from t1 where created_at in (?, ?)";
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> EXECUTE stmt USING @p1, @p2; -- returns 2 rows as expected
+------------+
| created_at |
+------------+
| 2023-02-03 |
| 2023-02-25 |
+------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT FOUND_ROWS(); -- returns count of 2 as expected
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
mysql>
mysql> CREATE TABLE t2 (
    ->     created_at DATE
    -> )
    -> PARTITION BY RANGE (DAYOFMONTH(created_at)) (
    ->     PARTITION p1 VALUES LESS THAN (16),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO t2 VALUES (@p1), (@p2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SET @s = "select * from t2 where created_at in (?, ?)";
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> EXECUTE stmt USING @p1, @p2; -- returns 2 rows as expected
+------------+
| created_at |
+------------+
| 2023-02-03 |
| 2023-02-25 |
+------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT FOUND_ROWS(); -- returns count of 0, this is incorrect
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

Thanks, I know that FOUND_ROWS() is deprecated. Deprecated does not mean buggy or that it fails in arbitrary ways. Deprecated means it should continue to work as it had worked in the past, to give users a chance to adopt another solution.

Also, the same bug occurs in connectors that call the C API function mysql_affected_rows(). The C API is not deprecated.

PHP script that demonstrates the bug using the C API.

Attachment: test.php (text/php), 649 bytes.

Hi all：

It is very terrible that my mysqld is crashed, when I excute the prepared query with parameters against a partitioned table. 

The error information:

Assertion failed: param_state() != NO_VALUE, file D:\SourceDebug\MySQL\mysql-8.0.32\sql\item.cc, line 4224
abort() has been called2023-03-16T12:32:56Z UTC - mysqld got exception 0x80000003 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x1f3e4a46e50
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff7672573a4    mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:372]
7ff92e9bc3e1    ucrtbased.dll!raise()
7ff92e9be039    ucrtbased.dll!abort()
7ff92e9c386f    ucrtbased.dll!_get_wide_winmain_command_line()
7ff92e9c1868    ucrtbased.dll!_get_wide_winmain_command_line()
7ff92e9c41cf    ucrtbased.dll!_wassert()
7ff7650e6126    mysqld.exe!Item_param::val_str()[item.cc:4224]
7ff7650d1b5c    mysqld.exe!Item::get_date_from_string()[item.cc:1434]
7ff7650e6933    mysqld.exe!Item_param::get_date()[item.cc:4118]
7ff7650cfa7f    mysqld.exe!Item::val_date_temporal()[item.cc:392]
7ff764f257dc    mysqld.exe!get_datetime_value()[item_cmpfunc.cc:1517]
7ff764f14d92    mysqld.exe!in_datetime::set()[item_cmpfunc.cc:4511]
7ff764f140cc    mysqld.exe!in_vector::fill()[item_cmpfunc.cc:4251]
7ff764f1b868    mysqld.exe!Item_func_in::populate_bisection()[item_cmpfunc.cc:5396]
7ff7658d13b7    mysqld.exe!get_func_mm_tree_from_in_predicate()[range_analysis.cc:179]
7ff7658d327d    mysqld.exe!get_func_mm_tree()[range_analysis.cc:609]
7ff7658d0b8c    mysqld.exe!get_full_func_mm_tree()[range_analysis.cc:775]
7ff7658d437a    mysqld.exe!get_mm_tree()[range_analysis.cc:964]
7ff765a76ca0    mysqld.exe!prune_partitions()[partition_pruning.cc:323]
7ff7657cb570    mysqld.exe!Query_block::apply_local_transforms()[sql_resolver.cc:809]
7ff7657cf070    mysqld.exe!Query_block::prepare()[sql_resolver.cc:592]
7ff76577f38f    mysqld.exe!Sql_cmd_select::prepare_inner()[sql_select.cc:470]
7ff76577cfea    mysqld.exe!Sql_cmd_dml::prepare()[sql_select.cc:386]
7ff7656b0159    mysqld.exe!Prepared_statement::prepare_query()[sql_prepare.cc:1419]
7ff7656b169e    mysqld.exe!Prepared_statement::prepare()[sql_prepare.cc:2595]
7ff7656ae8d7    mysqld.exe!mysql_sql_stmt_prepare()[sql_prepare.cc:1826]
7ff7653668fa    mysqld.exe!mysql_execute_command()[sql_parse.cc:3343]
7ff7653649f6    mysqld.exe!dispatch_sql_command()[sql_parse.cc:5322]
7ff7653701b6    mysqld.exe!dispatch_command()[sql_parse.cc:2040]
7ff76536d75e    mysqld.exe!do_command()[sql_parse.cc:1439]
7ff764f440ae    mysqld.exe!handle_connection()[connection_handler_per_thread.cc:302]
7ff7681e9a6f    mysqld.exe!pfs_spawn_thread()[pfs.cc:2988]
7ff767255cfc    mysqld.exe!win_thread_start()[my_thread.cc:73]
7ff92e9c542c    ucrtbased.dll!_register_onexit_function()
7ff990647034    KERNEL32.DLL!BaseThreadInitThunk()
7ff990b62651    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1f3e1eef6d8): select * from t2 where created_at in (?, ?)
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.