MySQL Bugs: #106318: FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`

Bug #106318	FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`
Submitted:	28 Jan 2022 2:01	Modified:	10 Feb 2022 6:21
Reporter:	chen yi (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	8.0.25	OS:	CentOS (CentOS Linux release 8.2.2004 (Core) )
Assigned to:		CPU Architecture:	x86 (Intel Xeon E3-12xx v2 (Ivy Bridge, IBRS))

Description:
FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true`.
It 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.

How to repeat:
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

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)

Hi,

What version of JDBC are you using?

Thanks

Hi 
JDBC version same as MySql server version: 8.0.25

Hi,

This seems to be data specific. With data we can create it does not reproduce. 
I will verify this non the less for the connector team to take a look.

thanks

Hi,

This is not connector issue but change on server side as we are deprecating force index ...

https://dev.mysql.com/doc/refman/8.0/en/index-hints.html says:
 
The FORCE INDEX hint acts like USE INDEX (index_list), with the addition
that a table scan is assumed to be very expensive...

Note
As of MySQL 8.0.20, the server supports the index-level optimizer hints
JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, and INDEX, which are equivalent to and
intended to supersede FORCE INDEX index hints, as well as the NO_JOIN_INDEX,
NO_GROUP_INDEX, NO_ORDER_INDEX, and NO_INDEX optimizer hints, which are
equivalent to and intended to supersede IGNORE INDEX index hints. Thus, you
should expect USE INDEX, FORCE INDEX, and IGNORE INDEX to be deprecated in a
future release of MySQL, and at some time thereafter to be removed
altogether. For more information, see Index-Level Optimizer Hints.

Hi 
We already try index-level optimizer hints but same as bug described.
We perpared to install the newest Mysql version 8.0.28 and try reproduce again. I'll let you known if something found.

Hi,

Feel free to test but I do not believe there will be any changes since nothing wrt this is changed in the new version.

The issue here is I cannot reproduce this as I do not have your data. If you can make a reproducible test case with data it will be much easier. This is unrelated to the connector from what I see and is changed behavior on the server side.

Thanks