Added the test case for bug mdev-10836.

The bug was fixed by the patch for mdev-10882.

1 files changed, 78 insertions, 0 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 836d284e3b6..973a14096c0 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -7688,3 +7688,81 @@ EXPLAIN
 }
 DROP VIEW v1;
 DROP TABLE t1,t2;
+#
+# MDEV-10836: pushdown of the predicate with cached value
+#
+CREATE TABLE t (pk INT PRIMARY KEY, f INT) ENGINE=MyISAM;
+CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t;
+INSERT INTO t VALUES (1,1),(3,2);
+SELECT * FROM v AS v1, v AS v2
+WHERE v2.pk > v1.f AND v1.f IN ( SELECT COUNT(pk) FROM t );
+pk	f	pk	f
+3	2	3	2
+EXPLAIN FORMAT=JSON
+SELECT * FROM v AS v1, v AS v2
+WHERE v2.pk > v1.f AND v1.f IN ( SELECT COUNT(pk) FROM t );
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "system",
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "message": "Select tables optimized away"
+          }
+        }
+      }
+    },
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "(v1.f = 2)",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "table": {
+            "table_name": "t",
+            "access_type": "ALL",
+            "rows": 2,
+            "filtered": 100,
+            "attached_condition": "(t.f = 2)"
+          }
+        }
+      }
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "<derived4>",
+        "access_type": "ALL",
+        "rows": 2,
+        "filtered": 100,
+        "attached_condition": "(v2.pk > 2)"
+      },
+      "buffer_type": "flat",
+      "buffer_size": "256Kb",
+      "join_type": "BNL",
+      "materialized": {
+        "query_block": {
+          "select_id": 4,
+          "table": {
+            "table_name": "t",
+            "access_type": "ALL",
+            "rows": 2,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+DROP VIEW v;
+DROP TABLE t;