MDEV-25630: Crash with window function in left expr of IN subquery

* Make Item_in_optimizer::fix_fields inherit the with_window_func
  attribute of the subquery's left expression (the subquery itself
  cannot have window functions that are aggregated in this select)

* Make Item_cache_wrapper::Item_cache_wrapper() inherit
  with_window_func attribute of the item it is caching.

5 files changed, 56 insertions, 0 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index dd74c5c77fd..8a31dcc0634 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3892,5 +3892,24 @@ id	rn
 1	1
 drop table t1;
 #
+# MDEV-25630: Crash with window function in left expr of IN subquery
+#
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1;
+lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a)
+NULL
+1
+0
+DROP TABLE t1;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1;
+sum(i) over () IN ( SELECT 1 FROM t1 a)
+0
+0
+0
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
index 27eedc45028..3c81d7b6046 100644
--- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result
+++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
@@ -3898,6 +3898,25 @@ id	rn
 1	1
 drop table t1;
 #
+# MDEV-25630: Crash with window function in left expr of IN subquery
+#
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1;
+lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a)
+NULL
+1
+0
+DROP TABLE t1;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1;
+sum(i) over () IN ( SELECT 1 FROM t1 a)
+0
+0
+0
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 57214ab0165..c07a81f17da 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2543,5 +2543,19 @@ order by rn desc;
 drop table t1;
 
 --echo #
+--echo # MDEV-25630: Crash with window function in left expr of IN subquery
+--echo #
+
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/item.cc b/sql/item.cc
index be64edca9a1..d7a3659a2ce 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8203,6 +8203,7 @@ Item_cache_wrapper::Item_cache_wrapper(THD *thd, Item *item_arg):
   name= item_arg->name;
   name_length= item_arg->name_length;
   with_subselect=  orig_item->with_subselect;
+  with_window_func= orig_item->with_window_func;
 
   if ((expr_value= Item_cache::get_cache(thd, orig_item)))
     expr_value->setup(thd, orig_item);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 7b7604053e3..8a2c532f621 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1416,6 +1416,9 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref)
     maybe_null=1;
   with_subselect= 1;
   with_sum_func= with_sum_func || args[1]->with_sum_func;
+  with_window_func= args[0]->with_window_func;
+  // The subquery cannot have window functions aggregated in this select
+  DBUG_ASSERT(!args[1]->with_window_func);
   with_field= with_field || args[1]->with_field;
   with_param= args[0]->with_param || args[1]->with_param; 
   used_tables_and_const_cache_join(args[1]);