VIEW doesn't work with read in order optimization

[UnamedRus]

Describe the situation

It's not possible to utilize read_in_order optimization if you are using view.
Because conditions being pushed only to HAVING clause and not WHERE.

How to reproduce
ClickHouse version 22.2

 CREATE TABLE default.test_table_order
(
    `key` UInt32,
    `value` UInt32,
    `date` Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (key, date)
SETTINGS index_granularity = 8192

INSERT INTO test_table_order SELECT number % 1000000 as key, intDiv(key, 10000) + key % 10 ,  today() + intDiv(number,1000000) as date FROM numbers(209440000);

CREATE VIEW default.test_table_view
(
    `key` UInt32,
    `value` UInt32,
    `date` Date
) AS
SELECT *
FROM default.test_table_order


SELECT *
FROM test_table_view
WHERE value > 0
ORDER BY key DESC
LIMIT 1

Query id: 15a3a708-5437-4da3-9b01-b4b8a8943477

┌────key─┬─value─┬───────date─┐
│ 999999 │   108 │ 2022-02-19 │
└────────┴───────┴────────────┘

1 rows in set. Elapsed: 0.055 sec. Processed 209.44 million rows, 2.09 GB (3.82 billion rows/s., 38.22 GB/s.)

SELECT *
FROM test_table_order
WHERE value > 0
ORDER BY key DESC
LIMIT 1

Query id: f3e3a4fc-ddda-41c3-8078-5a51e2a770d6

┌────key─┬─value─┬───────date─┐
│ 999999 │   108 │ 2022-05-27 │
└────────┴───────┴────────────┘

1 rows in set. Elapsed: 0.006 sec. Processed 270.57 thousand rows, 2.71 MB (48.28 million rows/s., 482.82 MB/s

EXPLAIN SYNTAX
SELECT *
FROM test_table_view
WHERE value > 0
ORDER BY key DESC
LIMIT 1

Query id: 794701c9-1013-4e1c-b4c5-b10f5964731b

┌─explain───────────────────────────┐
│ SELECT                            │
│     key,                          │
│     value,                        │
│     date                          │
│ FROM                              │
│ (                                 │
│     SELECT *                      │
│     FROM default.test_table_order │
│     HAVING value > 0              │
│ ) AS test_table_view              │
│ WHERE value > 0                   │
│ ORDER BY key DESC                 │
│ LIMIT 1                           │
└───────────────────────────────────┘


EXPLAIN SYNTAX
SELECT
    key,
    value,
    date
FROM
(
    SELECT *
    FROM default.test_table_order
) AS test_table_view
WHERE value > 0
ORDER BY key DESC
LIMIT 1

┌─explain───────────────────────────┐
│ SELECT                            │
│     key,                          │
│     value,                        │
│     date                          │
│ FROM                              │
│ (                                 │
│     SELECT                        │
│         key,                      │
│         value,                    │
│         date                      │
│     FROM default.test_table_order │
│     PREWHERE value > 0            │
│ ) AS test_table_view              │
│ WHERE value > 0                   │
│ ORDER BY key DESC                 │
│ LIMIT 1                           │
└───────────────────────────────────┘

Expected performance

Similar to case, when you will query source table.

Related #21756 #17936

[vitlibar]

Fixed in #54628