You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "turbo-one-off (via GitHub)" <gi...@apache.org> on 2026/04/18 02:34:27 UTC
[I] [Bug] SELECT * FROM view ORDER BY ... LIMIT returns NULL for non-key/non-orderby columns (TopN lazy materialization) [doris]
turbo-one-off opened a new issue, #62598:
URL: https://github.com/apache/doris/issues/62598
### Search before asking
- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
doris-4.0.5-rc01-59de8c4c524 (also reproducible on 4.0.4-rc01-d74dc1fb93)
### What's Wrong?
`SELECT * FROM <regular_view> ORDER BY ... LIMIT N` returns NULL for
columns that are NOT referenced in WHERE or ORDER BY, while the same
query on the underlying physical table works correctly.
Reproduces with the smallest possible case: a single UNIQUE KEY table
with 3 rows behind a trivial view. No joins, no complex WHERE, no
large data volume.
This appears to be a regression in the TopN Lazy Materialization code
path (introduced in 4.0): the first phase reads ORDER BY columns and
row identifiers correctly, but the second-phase fetch of the remaining
columns fails when the query goes through a view, silently returning
NULL.
This is a silent wrong-result bug (no error raised), which is
especially dangerous for data warehousing use cases where views are
used as abstraction layers — callers may not realize the NULLs are
incorrect.
### What You Expected?
Query through a view should return the same data as the query on the
underlying physical table.
SELECT * FROM v ORDER BY id DESC LIMIT 3
= SELECT * FROM t ORDER BY id DESC LIMIT 3 (modulo any extra view columns)
### How to Reproduce?
### Setup
```sql
CREATE DATABASE bug_repro;
USE bug_repro;
CREATE TABLE t (
id BIGINT NOT NULL,
name VARCHAR(50),
email VARCHAR(100),
age INT
) UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_num" = "1",
"enable_unique_key_merge_on_write" = "true"
);
INSERT INTO t VALUES
(1, 'Alice', 'alice@x.com', 30),
(2, 'Bob', 'bob@x.com', 25),
(3, 'Carol', 'carol@x.com', 35);
CREATE VIEW v AS
SELECT id, name, email, age, CAST('a' AS VARCHAR(32)) AS tag FROM t;
```
### Baseline — querying the physical table (correct)
```sql
SELECT * FROM t ORDER BY id DESC LIMIT 3;
```
Output:
```
+----+-------+-------------+------+
| id | name | email | age |
+----+-------+-------------+------+
| 3 | Carol | carol@x.com | 35 |
| 2 | Bob | bob@x.com | 25 |
| 1 | Alice | alice@x.com | 30 |
+----+-------+-------------+------+
```
### Bug — same query through the view
```sql
SELECT * FROM v ORDER BY id DESC LIMIT 3;
```
Actual output:
```
+----+------+-------+------+------+
| id | name | email | age | tag |
+----+------+-------+------+------+
| 3 | NULL | NULL | NULL | a |
| 2 | NULL | NULL | NULL | a |
| 1 | NULL | NULL | NULL | a |
+----+------+-------+------+------+
```
- `id` (ORDER BY column) is populated correctly.
- `tag` (a CAST constant) is populated correctly.
- `name`, `email`, `age` (columns not in WHERE/ORDER BY) are all NULL.
### What works (no bug)
```sql
SELECT COUNT(*) FROM v; -- returns 3
SELECT * FROM v WHERE id = 1; -- complete data
SELECT * FROM t ORDER BY id DESC LIMIT 3; -- complete data (physical table)
```
### Session variables attempted — none of them fix the bug
```sql
SET enable_two_phase_read_opt = false;
SET experimental_topn_lazy_materialization_threshold = 0;
SET enable_partition_topn = false;
SET push_topn_to_agg = false;
SET topn_opt_limit_threshold = 0;
-- Still returns NULL for name/email/age:
SELECT * FROM v ORDER BY id DESC LIMIT 3;
```
### Subquery and CTE wrapping — also do not fix the bug
```sql
-- Still returns NULL:
SELECT * FROM (SELECT * FROM v) x ORDER BY id DESC LIMIT 3;
-- Still returns NULL:
WITH x AS (SELECT * FROM v)
SELECT * FROM x ORDER BY id DESC LIMIT 3;
```
### Workaround — replacing the view with an async materialized view returns correct data
```sql
DROP VIEW v;
CREATE MATERIALIZED VIEW v
BUILD IMMEDIATE
REFRESH AUTO ON SCHEDULE EVERY 1 MINUTE
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES ("replication_num" = "1")
AS SELECT id, name, email, age, CAST('a' AS VARCHAR(32)) AS tag FROM t;
-- Returns correct data:
SELECT * FROM v ORDER BY id DESC LIMIT 3;
```
### Anything Else?
Possibly related prior work:
- PR #45693 "support one phase DeferMaterializeTopN"
- PR #59074 "Fix TopN filter probe expressions to wrap nullable slots"
- PR #56344 "invoke validator after topn lazy materialization processor"
Workaround: replacing the regular view with an async materialized view
returns correct data, but this has performance implications
(full-refresh cost) for large base tables and is not a viable
general-purpose solution.
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] SELECT * FROM view ORDER BY ... LIMIT returns NULL for non-key/non-orderby columns (TopN lazy materialization) [doris]
Posted by "englefly (via GitHub)" <gi...@apache.org>.
englefly commented on issue #62598:
URL: https://github.com/apache/doris/issues/62598#issuecomment-4281532363
fixed by this pr: https://github.com/apache/doris/pull/62533
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] SELECT * FROM view ORDER BY ... LIMIT returns NULL for non-key/non-orderby columns (TopN lazy materialization) [doris]
Posted by "HappenLee (via GitHub)" <gi...@apache.org>.
HappenLee commented on issue #62598:
URL: https://github.com/apache/doris/issues/62598#issuecomment-4282066765
We have identified this issue and will fix it in a subsequent release. Thank you for reporting it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] SELECT * FROM view ORDER BY ... LIMIT returns NULL for non-key/non-orderby columns (TopN lazy materialization) [doris]
Posted by "yuvarajl (via GitHub)" <gi...@apache.org>.
yuvarajl commented on issue #62598:
URL: https://github.com/apache/doris/issues/62598#issuecomment-4446875712
@HappenLee Can we close this issue. We verified it worked on 4.1.0 release
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org