Bug #121695 Incorrect 100% filtering estimate for row-valued IN subquery with GROUP BY and aggregate

Description:
For a row-valued IN subquery whose subquery has GROUP BY and MAX(), 
the outer table's filtering estimate stays at 100%, even though only ~1% of rows actually pass.

Because of GROUP BY/aggregate, the subquery is not a semijoin candidate
and remains as an Item_in_optimizer predicate in WHERE. Item_in_optimizer
does not override get_filtering_effect(), so calculate_condition_filter()
falls back to Item::get_filtering_effect(), which returns
COND_FILTER_ALLPASS (1.0). I confirmed this call path with a debugger
(best_access_path -> calculate_condition_filter -> get_filtering_effect,
dynamic type Item_in_optimizer).

Since the fallback does not read statistics, ANALYZE TABLE does not
change the result. The same behavior was first seen on 8.0.42 with real
data (about 2,771 of 369,666 rows returned, filtered=100.00).

In the test case below:
  - EXPLAIN:          outer rows=9910, filtered=100.00
  - EXPLAIN ANALYZE:  Filter estimated rows=9910, actual rows=100
  - optimizer_trace:  final_filtering_effect=1

How to repeat:
CREATE TABLE prices (
    id BIGINT PRIMARY KEY,
    stock_id INT NOT NULL,
    price_date DATE NOT NULL,
    UNIQUE KEY uk_stock_date (stock_id, price_date)
);

CREATE TABLE digits (n INT PRIMARY KEY);

INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- 100 stocks x 100 days = 10,000 rows
INSERT INTO prices (id, stock_id, price_date)
SELECT (10*s1.n+s0.n)*100 + (10*d1.n+d0.n) + 1,
       10*s1.n+s0.n+1,
       DATE_ADD('2025-01-01', INTERVAL (10*d1.n+d0.n) DAY)
FROM digits s1, digits s0, digits d1, digits d0;
DROP TABLE digits;
ANALYZE TABLE prices;

EXPLAIN
SELECT p.* FROM prices p
WHERE (p.stock_id, p.price_date) IN (
  SELECT stock_id, MAX(price_date) FROM prices GROUP BY stock_id);

-- Observed (trunk):
-- 1 PRIMARY  p      index uk_stock_date ... 9910 100.00 Using where; Using index
-- 2 SUBQUERY prices range uk_stock_date ...  101 100.00 Using index for group-by

EXPLAIN ANALYZE
SELECT p.* FROM prices p
WHERE (p.stock_id, p.price_date) IN (
  SELECT stock_id, MAX(price_date) FROM prices GROUP BY stock_id);

-- Observed:
-- -> Filter: <in_optimizer>(...)  (cost=997 rows=9910) (actual ... rows=100 loops=1)
--     -> Covering index scan on p using uk_stock_date (cost=997 rows=9910)
--        (actual ... rows=10000 loops=1)

-- Expected: The optimizer should estimate the filtering effect
-- rather than unconditionally assuming 100% selectivity.
-- Actual selectivity: 1% (100 of 10,000 rows match).

Hi JUNHYEON IM,

Thank you for the test case. Verified as described.