Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 105 insertions, 0 deletions

diff --git a/mysql-test/main/join_cache_cardinality.result b/mysql-test/main/join_cache_cardinality.result
new file mode 100644
index 00000000..df7f29d7
--- /dev/null
+++ b/mysql-test/main/join_cache_cardinality.result
@@ -0,0 +1,105 @@
+create table t1 (a int, b int, c int);
+insert into t1 select seq,seq/2, seq/4 from seq_1_to_100;
+create table t2 (a int, b int, c int);
+insert into t2 select seq, seq/2, seq/4 from seq_1_to_200;
+analyze table t1,t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set optimizer_trace=1;
+set join_cache_level=6;
+set optimizer_switch='hash_join_cardinality=on';
+explain select *
+from t1, t2
+where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c;
+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	hash_ALL	NULL	#hash#$hj	15	test.t1.a,test.t1.a,test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "a",
+                "avg_frequency": 1
+            },
+            {
+                "field": "b",
+                "avg_frequency": 2
+            },
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 1
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[100]
+explain select *
+from t1, t2 where t1.c=t2.c;
+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	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 3.9216
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[392.16]
+explain select *
+from t1 straight_join t2 where t1.c=t2.c and t2.a<30;
+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	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+# Note that rows is the same:
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 3.9216
+    }
+]
+# Despite available selectivity:
+select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["NULL < a < 30"],
+            "selectivity_from_histogram": 0.145
+        }
+    ]
+]
+drop table t1,t2;