return wrong results when using plan-cache and dynamic-mode

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

prepared-plan-cache.enabled = true

use test;
drop table if exists UK_RP17396;
CREATE TABLE `UK_RP17396` (
  `COL1` int(16) DEFAULT '29' COMMENT 'NUMERIC UNIQUE INDEX',
  `COL2` varchar(20) DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint(20) DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  UNIQUE KEY `UK_COL1` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`COL1`)
(PARTITION `P0` VALUES LESS THAN (-1964648596),
 PARTITION `P1` VALUES LESS THAN (-1798171365),
 PARTITION `P2` VALUES LESS THAN (-224353317),
 PARTITION `P3` VALUES LESS THAN (74739562),
 PARTITION `P4` VALUES LESS THAN (330180811),
 PARTITION `P5` VALUES LESS THAN (1267470870));

insert into UK_RP17396 values(-556906299, "竂揿蜓锌璸籾珵岄浑祿恺鮘茷拦没盬饎醱僛橉", "9269-08-13 18:36:02", 363208857940760420, -1.28355e38);


set tidb_partition_prune_mode=static;
prepare stmt from 'select * from UK_RP17396 where col3 < ? and col1 in (?, ?, ?) and col2 != ?;';
set @a=4020185825752602279, @b=-4331, @c=-29749, @d=-13306, @e="AgHVfFsnfEO";
execute stmt using @a,@b,@c,@d,@e;
set @a=2972768264503339938, @b=5150, @c=-556906299, @d=1232847360, @e="=姵犵駘鏤ⱴȴ褋韨窷晢镸辈夁沶蚩皭兩隧撬";
execute stmt using @a,@b,@c,@d,@e;


set tidb_partition_prune_mode=dynamic;
prepare stmt from 'select * from UK_RP17396 where col3 < ? and col1 in (?, ?, ?) and col2 != ?;';
set @a=4020185825752602279, @b=-4331, @c=-29749, @d=-13306, @e="AgHVfFsnfEO";
execute stmt using @a,@b,@c,@d,@e;
set @a=2972768264503339938, @b=5150, @c=-556906299, @d=1232847360, @e="=姵犵駘鏤ⱴȴ褋韨窷晢镸辈夁沶蚩皭兩隧撬";
execute stmt using @a,@b,@c,@d,@e;

2. What did you expect to see? (Required)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| COL1       | COL2                                                         | COL4                | COL3               | COL5        |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| -556906299 | 竂揿蜓锌璸籾珵岄浑祿恺鮘茷拦没盬饎醱僛橉 | 9269-08-13 18:36:02 | 363208857940760420 | -1.28355e38 |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
1 row in set (0.00 sec)

MySQL [test]>
MySQL [test]>
MySQL [test]> set tidb_partition_prune_mode=dynamic;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> prepare stmt from 'select * from UK_RP17396 where col3 < ? and col1 in (?, ?, ?) and col2 != ?;';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set @a=4020185825752602279, @b=-4331, @c=-29749, @d=-13306, @e="AgHVfFsnfEO";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
Empty set (0.01 sec)

;ySQL [test]> set @a=2972768264503339938, @b=5150, @c=-556906299, @d=1232847360, @e="=姵犵駘鏤ⱴȴ褋韨窷晢镸辈夁沶蚩皭兩隧撬"
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| COL1       | COL2                                                         | COL4                | COL3               | COL5        |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| -556906299 | 竂揿蜓锌璸籾珵岄浑祿恺鮘茷拦没盬饎醱僛橉 | 9269-08-13 18:36:02 | 363208857940760420 | -1.28355e38 |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+

1 row in set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| COL1       | COL2                                                         | COL4                | COL3               | COL5        |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| -556906299 | 竂揿蜓锌璸籾珵岄浑祿恺鮘茷拦没盬饎醱僛橉 | 9269-08-13 18:36:02 | 363208857940760420 | -1.28355e38 |
+------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
1 row in set (0.00 sec)

MySQL [test]>
MySQL [test]>
MySQL [test]> set tidb_partition_prune_mode=dynamic;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> prepare stmt from 'select * from UK_RP17396 where col3 < ? and col1 in (?, ?, ?) and col2 != ?;';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set @a=4020185825752602279, @b=-4331, @c=-29749, @d=-13306, @e="AgHVfFsnfEO";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
Empty set (0.01 sec)

;ySQL [test]> set @a=2972768264503339938, @b=5150, @c=-556906299, @d=1232847360, @e="=姵犵駘鏤ⱴȴ褋韨窷晢镸辈夁沶蚩皭兩隧撬"
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a,@b,@c,@d,@e;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.0.0-alpha-56-g9a4ca3c
Edition: Community
Git Commit Hash: 9a4ca3ca6919699fb4f0da72edd7151c56f84edd
Git Branch: master
UTC Build Time: 2022-03-11 01:02:55
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[mjonss]

Slightly smaller test case:

use test;
drop table if exists TestTable;
CREATE TABLE `TestTable` (
  `COL1` int(16) DEFAULT '29' COMMENT 'NUMERIC UNIQUE INDEX',
  `COL2` bigint(20) DEFAULT NULL,
  UNIQUE KEY `UK_COL1` (`COL1`)
) PARTITION BY RANGE (`COL1`)
(PARTITION `P0` VALUES LESS THAN (0));

insert into TestTable values(-5, 7); 

set tidb_partition_prune_mode=dynamic;
prepare stmt from 'select *,? from TestTable where col2 < ? and col1 in (?, ?)';
set @a=111, @b=1, @c=2, @d=22;
execute stmt using @d,@a,@b,@c;
set @a=112, @b=-2, @c=-5, @d=33;
execute stmt using @d,@a,@b,@c;

My investigations so far indicates that the user variables is using the values from previous execute and not the ones just set, effectively running the same query twice, without taking into account the new values of @a-@d.

[bb7133]

Change it to 'major' because dynamic pruning is experimental and disabled by default.

[bb7133]

I am able to reproduce this issue with binary protocol:

import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.math.BigDecimal;

import java.lang.*;

class Prepare {
	public static void test(String url) throws Exception {
	    Connection conn = DriverManager.getConnection(url, "", "");
		DatabaseMetaData meta = conn.getMetaData();
		System.out.println("URL:" + url);
		System.out.println("Driver version: " + meta.getDriverVersion());
		System.out.println("Server version: " + meta.getDatabaseProductVersion());

		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("SELECT @@tidb_partition_prune_mode");
		while (rs.next()) {
			System.out.println("tidb_partition_prune_mode: " + rs.getString(1));
		}
		int a, b, c, d;
		try (PreparedStatement ps = conn.prepareStatement("select *,? from TestTable where col2 < ? and col1 in (?, ?)")) {
			a = 111;
			b = 1;
			c = 2;
			d = 22;
			ps.setInt(1, d);
			ps.setInt(2, a);
			ps.setInt(3, b);
			ps.setInt(4, c);
			ResultSet result = ps.executeQuery();
			if (result.next()) {
				System.out.println("Result: " + result.getString(1) + ", " + result.getString(2) + ", " + result.getString(3));
			} else {
				System.out.println("Result: empty.");
			}

			a = 112;
			b = -2;
			c = -5;
			d = 33;
			ps.setInt(1, d);
			ps.setInt(2, a);
			ps.setInt(3, b);
			ps.setInt(4, c);
			result = ps.executeQuery();
			if (result.next()) {
				System.out.println("Result: " + result.getString(1) + ", " + result.getString(2) + ", " + result.getString(3));
			} else {
				System.out.println("Result: empty.");
			}
		}
	}

   public static void main(String[] args) throws Exception {
	    System.setProperty( "oracle.jdbc.Trace", Boolean.TRUE.toString() );
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        test("jdbc:mysql://address=(protocol=tcp)(host=127.0.0.1)(port=4000)(user=root)/test?&useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=true&useSSL=false&rewriteBatchedStatements=true");
    }
}

For the schema given by #33031 (comment), it gives:

tidb_partition_prune_mode: static
Result: empty.
Result: -5, 7, 33

tidb_partition_prune_mode: dynamic
Result: empty.
Result: empty.

[mjonss]

Lowering the severity to moderate since the plan cache is disabled for dynamic prune mode by #33259.

[mjonss]

Help needed, I think the issue is that in func (b *executorBuilder) buildIndexLookUpReader(v *plannercore.PhysicalIndexLookUpReader) Executor the v.PartitionInfo.PruningConds is not cleared/updated after the first execution of the prepared statement, so the pruning will always be done according to the bound variables from when the plan was cached/first executed. So we need to re-bind these for each execute statement so it is always using right parameters.