point_get is not chosen as expected

[XuHuaiyu]

Enhancement

schema

CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  UNIQUE KEY `idx` (`a`)
);

sql1:

explain analyze select t.*, _tidb_rowid from t where a = 1;

sql2:

explain analyze select t.*  from t where a = 1;

Expected:
Both SQL1 and SQL2 use point_get plan.

Actual:
SQL1 does not use the point_get plan.

[XuHuaiyu]

tidb/planner/core/point_get_plan.go

Lines 1487 to 1494 in 50dfe6b

	func findCol(tbl *model.TableInfo, colName *ast.ColumnName) *model.ColumnInfo {
	for _, col := range tbl.Columns {
	if col.Name.L == colName.Name.L {
	return col
	}
	}
	return nil
	}

We should check !PKIsHandle here:

	for _, col := range tbl.Columns {
		logutil.BgLogger().Error(fmt.Sprintf("%v, %v",col.Name.L, colName.Name.L))
		if colName.Name.L == model.ExtraHandleName.L && !tbl.PKIsHandle {
			colInfo := model.NewExtraHandleColInfo()
			colInfo.Offset = len(tbl.Columns) - 1
			return colInfo
		}
		if col.Name.L == colName.Name.L {
			return col
		}
	}

[XuHuaiyu]

Before:

tidb> explain  select t.*, _tidb_rowid from t where a = 1;
+-------------------------------+---------+-----------+-----------------------+---------------------------------------------+
| id                            | estRows | task      | access object         | operator info                               |
+-------------------------------+---------+-----------+-----------------------+---------------------------------------------+
| IndexLookUp_7                 | 1.00    | root      |                       |                                             |
| ├─IndexRangeScan_5(Build)     | 1.00    | cop[tikv] | table:t, index:idx(a) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe)     | 1.00    | cop[tikv] | table:t               | keep order:false, stats:pseudo              |
+-------------------------------+---------+-----------+-----------------------+---------------------------------------------+
3 rows in set (0.00 sec)

After:

tidb> explain analyze select t.*, _tidb_rowid from t where a = 1;
+-------------+---------+---------+------+-----------------------+-------------------------------------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object         | execution info                                              | operator info | memory | disk |
+-------------+---------+---------+------+-----------------------+-------------------------------------------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 1       | root | table:t, index:idx(a) | time:112.8µs, loops:2, Get:{num_rpc:2, total_time:33.8µs}   |               | N/A    | N/A  |
+-------------+---------+---------+------+-----------------------+-------------------------------------------------------------+---------------+--------+------+

[XuHuaiyu]

explain analyze select t.*, _tidb_rowid, date_format(a, "") from t where a = 1; should also use point_get

point_get -> projection

[qw4990]

isPointGetConvertableSchema should be updated as well.

[XuHuaiyu]

select *, date_format(b, "") from t where a =1 for update; should also use point_get
point_get-->select_lock-->projection