Explicit warning about ineffective MPP plan generation when forcing MPP mode

[zanmato1984]

After #1766 is done, we can reason that when forcing MPP, we should warn about ineffective MPP plan generation.

This way, a user journey would be:

• User expects an MPP plan, if it is effective, end of story.
• If MPP is ineffective, user could specify force MPP variable, if that works, end of story.
• If force MPP not working, a warning about why MPP is ineffective will be given to user.

[LittleFall]

just summary notes

use test;
select tidb_version();
show tables;
set tidb_isolation_read_engines = 'tidb, tiflash, tikv';
set tidb_allow_mpp=2;
select * from INFORMATION_SCHEMA.tiflash_replica;


select * from t;
desc t;

-- 0. can push down
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;

-- 1. generated column
drop table if exists t;
CREATE TABLE t (a int, b int as (a+1));
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY b;

-- 2. operator sort can't be pushed down
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM (select * from t order by a) t1 GROUP BY a;

-- 3. partition table
drop table if exists t;
CREATE TABLE t (a int, b int) PARTITION BY HASH(a);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;

-- 4. can't read from tiflash isolation engine (no tiflash replica)
drop table if exists t;
CREATE TABLE t (a int, b int);
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;

-- 5. can't read from tiflash isolation engine (tiflash replica not ready)
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;

-- 6. can't read from tiflash isolation engine (config [isolation-read] limit)
-- [isolation-read]
-- engines = ["tikv", "tidb"]
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;

-- 7. can't read from tiflash isolation engine (variable tidb_isolation_read_engines limit)
set @@session.tidb_isolation_read_engines = 'tikv';
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY a;
set @@session.tidb_isolation_read_engines = 'tidb, tiflash, tikv';

-- 8. new_collations_enabled_on_first_bootstrap open and join/agg key is string
SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
drop table if exists t;
CREATE TABLE t (a int, b char);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY b;

-- 9. can't read from tiflash isolation engine (hint limit)
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT /*+ read_from_storage(tikv[t]) */ COUNT(*) FROM t GROUP BY a;

-- 10. operator union/union all can't be pushed down
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM (SELECT * from t UNION ALL select * from t) s GROUP BY a;

-- 11. operator window can't be pushed down
drop table if exists t;
create table t(a int, b int);
ALTER TABLE t set tiflash replica 1;
explain SELECT a, ROW_NUMBER() OVER (ORDER BY a) FROM t;

-- 12. join without equal condition
drop table if exists t, s;
create table t(a int); create table s(a int);
ALTER TABLE t set tiflash replica 1;
ALTER TABLE s set tiflash replica 1;
explain select * from t join s on t.a<s.a;

-- 13. expression can't pushed down (with another warning)
drop table if exists t;
CREATE TABLE t (a int, b time);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY b;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY md5(a);

-- 13. expression can't pushed down (with another warning)
drop table if exists t;
CREATE TABLE t (a int, b time);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY b;
EXPLAIN SELECT COUNT(*) FROM t GROUP BY md5(a);

-- 14. operator limit can't be pushed down
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
EXPLAIN SELECT COUNT(*) FROM (select * from t limit 3) t1 GROUP BY a;

-- 15. use conflict another join hint.
drop table if exists t, s;
create table t(a int); create table s(a int);
ALTER TABLE t set tiflash replica 1;
ALTER TABLE s set tiflash replica 1;
explain select * from t join s on t.a=s.a;
explain select /*+ MERGE_JOIN(t, s) */ * from t join s on t.a=s.a;
explain select /*+ INL_JOIN(t) */ * from t join s on t.a=s.a;
explain select /*+ INL_HASH_JOIN(t, s) */ * from t join s on t.a=s.a;
explain select /*+ HASH_JOIN(t, s) */ * from t join s on t.a=s.a;

HASH_AGG()
STREAM_AGG()
USE_INDEX(t1_name, idx1_name [, idx2_name ...])
USE_INDEX_MERGE

-- 16. index
drop table if exists t;
CREATE TABLE t (a int, b int);
ALTER TABLE t set tiflash replica 1;
create index idx on t(a);
create index idx2 on t(b);
EXPLAIN SELECT /*+ USE_INDEX(t, idx) */ COUNT(*) FROM t GROUP BY a;
EXPLAIN SELECT /*+ HASH_AGG() */ COUNT(*) FROM t GROUP BY a;
EXPLAIN SELECT /*+ STREAM_AGG() */ COUNT(*) FROM t GROUP BY a;
EXPLAIN SELECT /*+ USE_INDEX_MERGE(t, idx, idx2) */ COUNT(*) FROM t where a>1 or b>1 GROUP BY a ;
EXPLAIN SELECT /*+ READ_CONSISTENT_REPLICA() */ COUNT(*) FROM t where a>1 or b>1 GROUP BY a;

[LittleFall]

need push down projection

[LittleFall]

closes by pingcap/tidb#25302