FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`

[chen yi]

Details:

mysql version: Server version: 8.0.25 MySQL Community Server - GPL
jdbc connector version: mysql-connector-java-8.0.25

reproduce:
step 1: create table:


CREATE DATABASE PRICING;
USE PRICING:
CREATE TABLE `QUOTE` (
`ID` BIGINT NOT NULL,
`GROUP_ID` SMALLINT NOT NULL,
`SYMBOL_ID` SMALLINT NOT NULL,
`SOURCE_ID` BIGINT NULL DEFAULT NULL,
`PRODUCT_ID` INT NOT NULL,
`COMPANY_ID` TINYINT NOT NULL,
`LOW` DECIMAL(15,6) NOT NULL,
`HIGH` DECIMAL(15,6) NOT NULL,
`OPEN` DECIMAL(15,6) NOT NULL,
`SCALE` TINYINT NOT NULL,
`STATUS` TINYINT NOT NULL,
`LADDERED` TINYINT NOT NULL,
`DEVIATION` DECIMAL(15,6) NOT NULL,
`BID_PRICE` DECIMAL(15,6) NOT NULL,
`ASK_PRICE` DECIMAL(15,6) NOT NULL,
`QUOTE_DATE` DATE NOT NULL,
`QUOTE_TIME` BIGINT NOT NULL,
`BID_TRADABLE` TINYINT NOT NULL,
`ASK_TRADABLE` TINYINT NOT NULL,
`BID_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`ASK_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`VERSION` INT NOT NULL DEFAULT '1',
`INSERT_DATETIME` TIMESTAMP NULL DEFAULT NULL,
`UPDATE_DATETIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
INDEX `INDEX_01` (`SOURCE_ID`),
INDEX `INDEX_02` (`PRODUCT_ID`),
INDEX `INDEX_03` (`PRODUCT_ID`, `QUOTE_DATE`),
INDEX `INDEX_04` (`PRODUCT_ID`, `QUOTE_TIME`)
)
ENGINE=InnoDB;


step 2: insert 10,000,000 records to above table. `PRODUCT_ID` 's cardinality is 200.

step 3: use following java code to access above table

public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/PRICING?useServerPrepStmts=true", "user", "pass")) {
try (PreparedStatement ps = conn.prepareStatement("select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = ? order by ID desc limit 512")) {
ps.setInt(1, 10);
try (ResultSet rs = ps.executeQuery()) {
int count = 0;
while (rs.next()) {
count++;
}
System.out.println("count:" + count);
}
}
}
}

we expect above sql to use INDEX_02, key = INDEX_02, Extra = Backward index scan

above code takes very long time to fetch result. if we changed `useServerPrepStmts=true` to `false` or use `Statement` instead of `PreparedStatement` or replace `FORCE INDEX` to `FORCE INDEX FOR JOIN`. it's fast as normal.

mysql execution Plan:

mysql> EXPLAIN select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = 1 order by ID desc limit 512;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | QUOTE | NULL | ref | INDEX_02 | INDEX_02 | 4 | const | 1 | 100.00 | Backward index scan |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)