From dc1d6213f9a5ad4a53d35a886f52adf4ee0a2b5c Mon Sep 17 00:00:00 2001
From: Sergei Petrunia <sergey@mariadb.com>
Date: Tue, 7 Mar 2023 19:49:57 +0300
Subject: MDEV-30806: ANALYZE FORMAT=JSON: better support for BNL and BNL-H
 joins

In block-nl-join, add:

- r_loops - this shows how many incoming record combinations this
  query plan node had.

- r_effective_rows - this shows the average number of matching rows
  that this table had for each incoming record combination. This is
  comparable with r_rows in non-blocked access methods.
  For BNL-joins, it is always equal to
   $.table.r_rows * $.table.r_filtered
  For BNL-H joins the value cannot be computed from other values

Reviewed by: Monty <monty@mariadb.org>
---
 mysql-test/main/analyze_format_json.test | 42 ++++++++++++++++++++++++++++++++
 1 file changed, 42 insertions(+)

(limited to 'mysql-test/main/analyze_format_json.test')

diff --git a/mysql-test/main/analyze_format_json.test b/mysql-test/main/analyze_format_json.test
index 3f3324e9eec..84f44869afe 100644
--- a/mysql-test/main/analyze_format_json.test
+++ b/mysql-test/main/analyze_format_json.test
@@ -226,3 +226,45 @@ create table t2 as select * from t1;
 --source include/analyze-format.inc
 analyze format=json select a, (select t2.b from t2 where t2.a<t1.a order by t2.c limit 1) from t1 where t1.a<0;
 drop table t0,t1,t2;
+
+
+--echo #
+--echo # MDEV-30806: ANALYZE FORMAT=JSON: better support for BNL and BNL-H joins
+--echo #
+--source include/have_sequence.inc
+create table t10 (
+  a int,
+  b int
+);
+insert into t10 select seq, seq/3 from seq_0_to_999;
+
+create table t11 (
+  a int,
+  b int
+);
+insert into t11 select seq, seq/5 from seq_0_to_999;
+
+analyze table t10,t11 persistent for all;
+
+--source include/analyze-format.inc
+analyze format=json
+select * from t10, t11
+where
+ t10.a < 700 and
+ t11.a < 100
+ and t10.b=t11.b;
+
+set @tmp=@@join_cache_level, join_cache_level=6;
+
+--source include/analyze-format.inc
+analyze format=json
+select * from t10, t11
+where
+ t10.a < 700 and
+ t11.a < 100
+ and t10.b=t11.b;
+
+set join_cache_level=@tmp;
+drop table t10, t11;
+
+
-- 
cgit v1.2.1