summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/join_cardinality.inc131
-rw-r--r--mysql-test/main/join_cardinality.result960
-rw-r--r--mysql-test/main/join_cardinality.test310
-rw-r--r--mysql-test/main/opt_trace.result18
-rw-r--r--mysql-test/main/opt_trace_index_merge.result1
-rw-r--r--mysql-test/main/opt_trace_index_merge_innodb.result1
6 files changed, 1421 insertions, 0 deletions
diff --git a/mysql-test/include/join_cardinality.inc b/mysql-test/include/join_cardinality.inc
new file mode 100644
index 00000000000..1ab6edb75cb
--- /dev/null
+++ b/mysql-test/include/join_cardinality.inc
@@ -0,0 +1,131 @@
+--echo #
+--echo # BASIC CASES
+--echo #
+
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # Equi-join condition
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # CASE where selectivity estimates are not accurate
+
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # The cardinality here would be not accurate because we don't have
+--echo # selectivity for the predicate t1.b=10
+--echo #
+
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # BASIC CASES FOR AND CONJUNCT
+--echo #
+
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # BASIC CASES FOR OR CONJUNCT
+--echo #
+
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # The cardinality here would be not accurate because for the
+--echo # OR conjunct all predicates can't be resolved to one column
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # COMPLEX CASES WITH AND-OR CONJUNCTS
+--echo #
+
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1
+WHERE ((t1.a > 20 and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
+((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
+((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN EXTENDED SELECT * from t1 WHERE ((t1.a IS NULL) or (t1.a = 5));
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # Test with subquery
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE t1.a = (select t2.a from t2 limit 1);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
diff --git a/mysql-test/main/join_cardinality.result b/mysql-test/main/join_cardinality.result
new file mode 100644
index 00000000000..f1379c53e1e
--- /dev/null
+++ b/mysql-test/main/join_cardinality.result
@@ -0,0 +1,960 @@
+CREATE TABLE t1(a INT, b INT, c INT, KEY(b), KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
+CREATE TABLE t2(a INT, b INT, key(b));
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_trace=1;
+SET optimizer_switch='rowid_filter=off';
+#
+# RUNNING the join cardinality tests when statistics are
+# available only from indexes
+#
+#
+# BASIC CASES
+#
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL b NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL b NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b b 5 NULL 5 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref b b 5 const 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# Equi-join condition
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 100 Using where
+1 SIMPLE t1 ref a a 5 test.t2.b 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# CASE where selectivity estimates are not accurate
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# The cardinality here would be not accurate because we don't have
+# selectivity for the predicate t1.b=10
+#
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# BASIC CASES FOR AND CONJUNCT
+#
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b,a b 5 NULL 3 Using index condition; Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b,a b 5 NULL 3 Using index condition; Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range b b 5 NULL 5 Using index condition
+1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# BASIC CASES FOR OR CONJUNCT
+#
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 4 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# The cardinality here would be not accurate because for the
+# OR conjunct all predicates can't be resolved to one column
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 100
+1 SIMPLE t2 ALL b NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL b,a NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# COMPLEX CASES WITH AND-OR CONJUNCTS
+#
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 100 Using where
+1 SIMPLE t1 ref a a 5 test.t2.b 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 100 Using where
+1 SIMPLE t1 ref a a 5 test.t2.b 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 100 Using where
+1 SIMPLE t1 ref a a 5 test.t2.b 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1
+WHERE ((t1.a > 20 and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
+((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
+((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 6 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN EXTENDED SELECT * from t1 WHERE ((t1.a IS NULL) or (t1.a = 5));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ref_or_null a a 5 const 2 100.00 Using index condition
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# Test with subquery
+#
+EXPLAIN SELECT * from t1 WHERE t1.a = (select t2.a from t2 limit 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ref a a 5 const 1 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 100
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t2 ref b b 5 test.t1.b 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false,
+ true
+]
+DROP TABLE t1,t2;
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+#
+# RUNNING the join cardinality tests when only statistics
+# are available from stat tables
+#
+#
+# BASIC CASES
+#
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# Equi-join condition
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# CASE where selectivity estimates are not accurate
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# The cardinality here would be not accurate because we don't have
+# selectivity for the predicate t1.b=10
+#
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# BASIC CASES FOR AND CONJUNCT
+#
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# BASIC CASES FOR OR CONJUNCT
+#
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+# The cardinality here would be not accurate because for the
+# OR conjunct all predicates can't be resolved to one column
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# COMPLEX CASES WITH AND-OR CONJUNCTS
+#
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1
+WHERE ((t1.a > 20 and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
+((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
+((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN EXTENDED SELECT * from t1 WHERE ((t1.a IS NULL) or (t1.a = 5));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 1.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# Test with subquery
+#
+EXPLAIN SELECT * from t1 WHERE t1.a = (select t2.a from t2 limit 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 100
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false,
+ true
+]
+DROP TABLE t1,t2;
+#
+# Combination with statistics from stat tables and
+# statistics from indexes
+#
+CREATE TABLE t1(a INT, b INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=t1.b;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 100 100.00 98.00 97.00 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES ();
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status Table is already up to date
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=10;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE t1 ref a a 5 const 1 1.00 97.66 100.00 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=t1.b;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 100 100.00 95.70 97.00 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+ANALYZE SELECT * from t1 WHERE t1.b > 3 OR t1.a=t1.b;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 100.00 100.00 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+DROP TABLE t1;
+#
+# Tests with simple predicates
+#
+CREATE TABLE t1(a INT, b INT, c VARCHAR(10), KEY(a), KEY(b));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
+#
+# Tests with LIKE PREDICATES
+#
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# Tests with NULL predicates
+#
+EXPLAIN SELECT * from t1 WHERE t1.a IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 5 const 1 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+#
+# Tests with </<=/>/>=/BETWEEN
+#
+EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE 5 < t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b a 5 NULL 1 Using index condition; Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE t1.a BETWEEN 1 AND 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE 5 BETWEEN 1 AND t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE 1 BETWEEN t1.a AND t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b a 5 NULL 1 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# Tests with IN predicate
+#
+EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge a,b a,a,a 5,5,5 NULL 3 Using union(a,a,a); Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * from t1 WHERE
+t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge a,b a,a,a 5,5,5 NULL 3 Using union(a,a,a); Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * from t1
+WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b b 5 NULL 3 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+#
+# Tests using different functional predicates
+#
+EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * from t1 WHERE abs(a) > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * from t1 WHERE a > 10+1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 1 Using index condition
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+DROP TABLE t1;
+#
+# Tests when updating stats tables manually
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+INSERT INTO
+mysql.column_stats(db_name, table_name, column_name, nulls_ratio) VALUES ('test', 't1', 'a', 0);
+#
+# estimates from stat tables not available, all the fields should be NOT NULL
+#
+EXPLAIN SELECT * from t1 WHERE a < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+DROP TABLE t1;
+#
+# Using multiple equalities at the top level
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+#
+# Tests with views (using Item_direct_view_ref)
+#
+CREATE VIEW v1 AS SELECT a, b FROM t1;
+EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+DROP TABLE t1,t2;
+DROP VIEW v1;
+#
+# TESTS to get ndv from rec_per_key
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+ALTER TABLE t1 ADD KEY k1(a);
+set @save_use_stat_tables= @@use_stat_tables;
+set use_stat_tables='never';
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+CREATE TABLE t2 like t1;
+INSERT INTO t2 SELECT * FROM t1;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status Table is already up to date
+EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range k1 k1 5 NULL 2 Using index condition
+1 SIMPLE t2 ref k1 k1 5 test.t1.a 1
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+set use_stat_tables=@save_use_stat_tables;
+DROP TABLE t1,t2;
+#
+# Tests with multi-component keys
+#
+CREATE TABLE t1 (a INT, b INT, KEY(a,b));
+INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+ALTER TABLE t1 ADD KEY k1(a);
+EXPLAIN SELECT * FROM t1 where t1.a<3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,k1 a 5 NULL 2 Using where; Using index
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+DROP TABLE t1;
+#
+# Tests when statistics from stat tables cannot be used because of the system variables
+# use_stat_tables and optimizer_use_condition_selectivity
+#
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+SELECT * FROM t1;
+a b c
+1 1 1
+2 2 2
+3 3 3
+4 4 4
+5 5 5
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+10 10 10
+SELECT * FROM t2;
+a b
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+SET @save_use_stat_tables= @@use_stat_tables;
+SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+SET use_stat_tables='never';
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+SET use_stat_tables= @save_use_stat_tables;
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ true
+]
+set optimizer_use_condition_selectivity=1;
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+[
+ false
+]
+SET optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/main/join_cardinality.test b/mysql-test/main/join_cardinality.test
new file mode 100644
index 00000000000..ae67350edca
--- /dev/null
+++ b/mysql-test/main/join_cardinality.test
@@ -0,0 +1,310 @@
+--source include/have_sequence.inc
+
+CREATE TABLE t1(a INT, b INT, c INT, KEY(b), KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
+
+CREATE TABLE t2(a INT, b INT, key(b));
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_trace=1;
+SET optimizer_switch='rowid_filter=off';
+
+--echo #
+--echo # RUNNING the join cardinality tests when statistics are
+--echo # available only from indexes
+--echo #
+
+--source include/join_cardinality.inc
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
+
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+
+--disable_result_log
+--disable_query_log
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(a,b) INDEXES();
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+--enable_query_log
+--enable_result_log
+
+--echo #
+--echo # RUNNING the join cardinality tests when only statistics
+--echo # are available from stat tables
+--echo #
+
+--source include/join_cardinality.inc
+
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # Combination with statistics from stat tables and
+--echo # statistics from indexes
+--echo #
+
+CREATE TABLE t1(a INT, b INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=t1.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES ();
+
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ANALYZE SELECT * from t1 WHERE t1.b > 3 AND t1.a=t1.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ANALYZE SELECT * from t1 WHERE t1.b > 3 OR t1.a=t1.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Tests with simple predicates
+--echo #
+
+CREATE TABLE t1(a INT, b INT, c VARCHAR(10), KEY(a), KEY(b));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
+
+--echo #
+--echo # Tests with LIKE PREDICATES
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%';
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%';
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # Tests with NULL predicates
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE t1.a IS NULL;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # Tests with </<=/>/>=/BETWEEN
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE 5 < t1.a;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE t1.a BETWEEN 1 AND 5;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE 5 BETWEEN 1 AND t1.a;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE 1 BETWEEN t1.a AND t1.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # Tests with IN predicate
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3);
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE
+t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1
+WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3));
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # Tests using different functional predicates
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE abs(a) > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * from t1 WHERE a > 10+1;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Tests when updating stats tables manually
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+
+INSERT INTO
+mysql.column_stats(db_name, table_name, column_name, nulls_ratio) VALUES ('test', 't1', 'a', 0);
+
+--echo #
+--echo # estimates from stat tables not available, all the fields should be NOT NULL
+--echo #
+
+EXPLAIN SELECT * from t1 WHERE a < 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Using multiple equalities at the top level
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+
+--echo #
+--echo # Tests with views (using Item_direct_view_ref)
+--echo #
+
+CREATE VIEW v1 AS SELECT a, b FROM t1;
+
+EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+
+--echo #
+--echo # TESTS to get ndv from rec_per_key
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+ALTER TABLE t1 ADD KEY k1(a);
+
+set @save_use_stat_tables= @@use_stat_tables;
+set use_stat_tables='never';
+analyze table t1;
+
+CREATE TABLE t2 like t1;
+INSERT INTO t2 SELECT * FROM t1;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+set use_stat_tables=@save_use_stat_tables;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Tests with multi-component keys
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY(a,b));
+INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+
+EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 ADD KEY k1(a);
+EXPLAIN SELECT * FROM t1 where t1.a<3;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Tests when statistics from stat tables cannot be used because of the system variables
+--echo # use_stat_tables and optimizer_use_condition_selectivity
+--echo #
+
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT);
+INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+SET @save_use_stat_tables= @@use_stat_tables;
+SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+SET use_stat_tables='never';
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+SET use_stat_tables= @save_use_stat_tables;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+set optimizer_use_condition_selectivity=1;
+
+EXPLAIN SELECT * FROM t1 WHERE t1.a > 10;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
+FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+SET optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 8f4f6ec97ce..c10018adab6 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -124,6 +124,7 @@ select * from v1 {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -270,6 +271,7 @@ select * from (select * from t1 where t1.a=1)q {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -421,6 +423,7 @@ select * from v2 {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -985,6 +988,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -1434,6 +1438,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -1628,6 +1633,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -1811,6 +1817,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -2076,6 +2083,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -2848,6 +2856,7 @@ explain extended select * from t1 where a in (select pk from t10) {
"execution_plan_for_potential_materialization": {
"steps": [
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -2878,6 +2887,7 @@ explain extended select * from t1 where a in (select pk from t10) {
}
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -3296,6 +3306,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -3924,6 +3935,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -4422,6 +4434,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"execution_plan_for_potential_materialization": {
"steps": [
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -4499,6 +4512,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
}
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -4948,6 +4962,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
}
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -6521,6 +6536,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"execution_plan_for_potential_materialization": {
"steps": [
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -6595,6 +6611,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
@@ -6672,6 +6689,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
}
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 3b79a7a44e8..da2d0591002 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -200,6 +200,7 @@ explain select * from t1 where a=1 or b=1 {
]
},
{
+ "cardinality_accurate": false,
"considered_execution_plans": [
{
"plan_prefix": [],
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 509569021a5..49c59c96058 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -198,6 +198,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
]
},
{
+ "cardinality_accurate": true,
"considered_execution_plans": [
{
"plan_prefix": [],
View Lorry Controller Status