ngbinh opened a new issue, #70174: URL: https://github.com/apache/doris/issues/70174
### 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.1.5-release-917b20be838`: reproduces with both policy forms below. - `doris-4.1.1-rc01-b10073ad9ca`: reproduces with the plain `EXISTS` policy only. Both runs used a single FE (`-Xmx2048m`) and one BE, in integrated storage mode with default session and global variables. We first hit this on a 4.1.5 cloud-mode cluster. ### What's Wrong? The two tables in a join each have a restrictive row policy whose `USING` is an `EXISTS` over three ACL tables. When the read filters both tables on a single key value, the Nereids cascades optimizer never finishes, and the FE heap fills: - A 2 GiB FE throws `OutOfMemoryError` about 12 s after the `EXPLAIN` starts. - The client gets `ERROR 2013 (HY000): Lost connection to MySQL server during query`. `nereids_timeout_second` does not stop it. With `enable_nereids_timeout = true` and `nereids_timeout_second = 5`, the planner thread was still in `Optimizer.execute` at 5 s, and the FE died about 12.5 s in. `SimpleJobScheduler.executeJobPool` checks the timeout only between jobs. The same read with two key values (`IN ('p1', 'p101')`) plans in about 0.2 s. ### What You Expected? The one-key read plans in well under a second, like the two-key read. If the planner can't finish, it should fail the statement by `nereids_timeout_second` rather than take down the FE. ### How to Reproduce? Run steps 1 to 4 as root and step 5 as `rp_reader`. In cloud mode, also grant `rp_reader` `USAGE_PRIV` on a compute group. **1. Schema and user** ```sql CREATE DATABASE IF NOT EXISTS rp; CREATE TABLE rp.parent ( tenant varchar(100) NOT NULL, pk varchar(100) NOT NULL, name varchar(500) NOT NULL, code varchar(255) NULL ) UNIQUE KEY(tenant, pk) DISTRIBUTED BY HASH(tenant) BUCKETS 10 PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true"); CREATE TABLE rp.child ( tenant varchar(100) NOT NULL, ck varchar(100) NOT NULL, pk varchar(100) NOT NULL, name varchar(500) NOT NULL, code varchar(255) NULL, kind varchar(100) NULL ) UNIQUE KEY(tenant, ck) DISTRIBUTED BY HASH(tenant) BUCKETS 10 PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true"); CREATE TABLE rp.acl_cover ( tenant_hash bigint NOT NULL, resource_hash bigint NOT NULL, resource_type tinyint NOT NULL, side tinyint NOT NULL DEFAULT "0", l1 bigint NOT NULL, l2 bigint NOT NULL DEFAULT "0", l3 bigint NOT NULL DEFAULT "0", l4 bigint NOT NULL DEFAULT "0", l5 bigint NOT NULL DEFAULT "0" ) UNIQUE KEY(tenant_hash, resource_hash, resource_type, side, l1, l2, l3, l4, l5) DISTRIBUTED BY HASH(tenant_hash, resource_hash) BUCKETS 10 PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true"); CREATE TABLE rp.acl_grant ( tenant_hash bigint NOT NULL, l1 bigint NOT NULL, principal bigint NOT NULL, l2 bigint NOT NULL DEFAULT "0", l3 bigint NOT NULL DEFAULT "0", l4 bigint NOT NULL DEFAULT "0", l5 bigint NOT NULL DEFAULT "0", resource_type tinyint NOT NULL, channel bigint NOT NULL DEFAULT "0", role_level tinyint NOT NULL, reach tinyint NOT NULL DEFAULT "0" ) UNIQUE KEY(tenant_hash, l1, principal, l2, l3, l4, l5, resource_type, channel) DISTRIBUTED BY HASH(tenant_hash, l1) BUCKETS 10 PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true"); CREATE TABLE rp.acl_member ( tenant_hash bigint NOT NULL, member bigint NOT NULL, principal bigint NOT NULL ) UNIQUE KEY(tenant_hash, member, principal) DISTRIBUTED BY HASH(tenant_hash, member) BUCKETS 10 PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true"); CREATE USER IF NOT EXISTS 'rp_reader' IDENTIFIED BY ''; GRANT SELECT_PRIV ON rp.* TO 'rp_reader'; ``` **2. Data** ```sql INSERT INTO rp.parent SELECT concat('t', number % 100), concat('p', number), concat('Parent ', number), concat('P-', number) FROM numbers("number" = "500"); INSERT INTO rp.child SELECT concat('t', number % 100), concat('c', number), concat('p', number), concat('Child ', number), NULL, IF(number % 3 = 0, 'k1', NULL) FROM numbers("number" = "500"); INSERT INTO rp.acl_cover (tenant_hash, resource_hash, resource_type, side, l1, l2, l3) SELECT murmur_hash3_64(concat('t', number % 100)), murmur_hash3_64(concat('t', number % 100), 'P', concat('p', number % 500)), IF(number % 4 = 0, 1, 7), number % 2, murmur_hash3_64('g', number % 50), murmur_hash3_64('x', number % 500), IF(number % 3 = 0, number, 0) FROM numbers("number" = "4000"); INSERT INTO rp.acl_grant (tenant_hash, l1, principal, resource_type, channel, role_level, reach) SELECT murmur_hash3_64(concat('t', number % 100)), murmur_hash3_64('g', number % 50), number, IF(number % 4 = 0, 1, 7), IF(number % 7 = 0, 5, 0), 10 * (1 + number % 3), number % 2 FROM numbers("number" = "800"); INSERT INTO rp.acl_member SELECT murmur_hash3_64(concat('t', number % 100)), number % 3000, number % 800 FROM numbers("number" = "11000"); ``` **3. Sampled column statistics.** Wait until `information_schema.tables` shows `table_rows > 0` for all five tables. That took 55 to 110 s here. Then run: ```sql ANALYZE TABLE rp.parent WITH SAMPLE PERCENT 100 WITH SYNC; ANALYZE TABLE rp.child WITH SAMPLE PERCENT 100 WITH SYNC; ANALYZE TABLE rp.acl_cover WITH SAMPLE PERCENT 100 WITH SYNC; ANALYZE TABLE rp.acl_grant WITH SAMPLE PERCENT 100 WITH SYNC; ANALYZE TABLE rp.acl_member WITH SAMPLE PERCENT 100 WITH SYNC; ``` Sampled statistics are required: - Running the same statements without `WITH SAMPLE PERCENT 100` (full statistics) makes the one-key read plan normally on 4.1.5. - Auto-analyze's sampled statistics trigger it too. That is how our cluster hit it. - An `ANALYZE` that runs before `table_rows` is known records `row_count = 0`, and then the bug doesn't show. **4. Row policies.** Use one of the two forms. 4a. Plain `EXISTS` (loops on 4.1.1 and 4.1.5): ```sql DROP ROW POLICY IF EXISTS rp_parent ON rp.parent; CREATE ROW POLICY rp_parent ON rp.parent AS RESTRICTIVE TO rp_reader USING ( EXISTS(SELECT 1 FROM rp.acl_cover c JOIN rp.acl_grant g ON g.tenant_hash = c.tenant_hash AND g.resource_type = c.resource_type AND g.l1 = c.l1 JOIN rp.acl_member m ON m.principal = g.principal AND m.tenant_hash = g.tenant_hash WHERE c.resource_type = 7 AND c.side = 1 AND m.member = @member AND m.tenant_hash = @tenant_hash AND g.role_level >= 1 AND c.resource_hash = murmur_hash3_64(tenant, 'P', pk) )); DROP ROW POLICY IF EXISTS rp_child ON rp.child; CREATE ROW POLICY rp_child ON rp.child AS RESTRICTIVE TO rp_reader USING ( EXISTS(SELECT 1 FROM rp.acl_cover c JOIN rp.acl_grant g ON g.tenant_hash = c.tenant_hash AND g.resource_type = c.resource_type AND g.l1 = c.l1 JOIN rp.acl_member m ON m.principal = g.principal AND m.tenant_hash = g.tenant_hash WHERE c.resource_type = 7 AND c.side = 1 AND m.member = @member AND m.tenant_hash = @tenant_hash AND g.role_level >= 1 AND g.channel != 5 AND c.resource_hash = murmur_hash3_64(tenant, 'C', ck) )); ``` 4b. `<flag> OR EXISTS(...)` (loops on 4.1.5 only): ```sql DROP ROW POLICY IF EXISTS rp_parent ON rp.parent; CREATE ROW POLICY rp_parent ON rp.parent AS RESTRICTIVE TO rp_reader USING ( @is_admin = 1 OR EXISTS(SELECT 1 FROM rp.acl_cover c JOIN rp.acl_grant g ON g.tenant_hash = c.tenant_hash AND g.resource_type = c.resource_type AND g.l1 = c.l1 JOIN rp.acl_member m ON m.principal = g.principal AND m.tenant_hash = g.tenant_hash WHERE c.resource_type = 7 AND c.side = 1 AND m.member = @member AND m.tenant_hash = @tenant_hash AND g.role_level >= 1 AND c.resource_hash = murmur_hash3_64(tenant, 'P', pk) LIMIT 1)); DROP ROW POLICY IF EXISTS rp_child ON rp.child; CREATE ROW POLICY rp_child ON rp.child AS RESTRICTIVE TO rp_reader USING ( @is_admin = 1 OR EXISTS(SELECT 1 FROM rp.acl_cover c JOIN rp.acl_grant g ON g.tenant_hash = c.tenant_hash AND g.resource_type = c.resource_type AND g.l1 = c.l1 JOIN rp.acl_member m ON m.principal = g.principal AND m.tenant_hash = g.tenant_hash WHERE c.resource_type = 7 AND c.side = 1 AND m.member = @member AND m.tenant_hash = @tenant_hash AND g.role_level >= 1 AND g.channel != 5 AND c.resource_hash = murmur_hash3_64(tenant, 'C', ck) LIMIT 1)); ``` **5. As `rp_reader`, the one-key read.** `2965303710240291456` is `murmur_hash3_64('t1')`. ```sql SET @is_admin = 0; SET @member = 1; SET @tenant_hash = 2965303710240291456; -- murmur_hash3_64('t1') EXPLAIN SELECT ch.ck, p.name FROM rp.child ch JOIN rp.parent p ON ch.pk = p.pk AND ch.tenant = p.tenant WHERE ch.tenant = 't1' AND p.tenant = 't1' AND ch.pk = 'p1' AND p.pk = 'p1' ; ``` The contrast is the same statement with `ch.pk IN ('p1', 'p101') AND p.pk IN ('p1', 'p101')`. **Results.** Wall time of the `EXPLAIN`, including the client start-up: | Policy | Statistics | Read | 4.1.1 | 4.1.5 | |---|---|---|---|---| | 4a plain `EXISTS` | sampled | one key | **FE OOM at 12.5 s** | **FE OOM at 12.3 s** | | 4b `OR EXISTS` | sampled | one key | 123 ms | **FE OOM at 12.7 s** | | 4b `OR EXISTS` | full | one key | not run | 370 ms | | any | any | two keys | 125 to 134 ms | 111 to 250 ms | | any | sampled | one key, with `disable_nereids_rules = 'LOGICAL_SEMI_JOIN_SEMI_JOIN_TRANSPOSE_PROJECT'` | 128 to 240 ms | 204 to 256 ms | With the rule disabled, the row results matched 4.1.1 on our real schema. We checked one viewer who is granted access and one who isn't. ### Anything Else? **Planner thread.** This is a thread dump of the planner 5 s into the statement, on 4.1.5: ``` "mysql-nio-pool-1" #242 daemon prio=5 os_prio=0 cpu=3543.04ms elapsed=5.00s ... runnable java.lang.Thread.State: RUNNABLE at org.apache.doris.nereids.jobs.cascades.DeriveStatsJob.execute(DeriveStatsJob.java:92) at org.apache.doris.nereids.jobs.scheduler.SimpleJobScheduler.executeJobPool(SimpleJobScheduler.java:51) at org.apache.doris.nereids.jobs.executor.Optimizer.lambda$execute$1(Optimizer.java:64) ... at org.apache.doris.nereids.jobs.executor.Optimizer.execute(Optimizer.java:49) at org.apache.doris.nereids.NereidsPlanner.optimize(NereidsPlanner.java:565) ... at org.apache.doris.nereids.trees.plans.commands.ExplainCommand.run(ExplainCommand.java:133) ``` **What we think happens.** We have not root-caused this. - With a single `pk`, `murmur_hash3_64(tenant, 'P', pk)` folds to a constant. The correlated predicate becomes a filter on `acl_cover`, so each policy's semi join is left with no join condition. - The two stacked semi joins are then swapped over and over during exploration. - Disabling `LOGICAL_SEMI_JOIN_SEMI_JOIN_TRANSPOSE_PROJECT` stops it. On 4.1.5 with form 4a, each of these also lets the one-key read plan in 0.1 to 0.4 s. An unprotected control run right after them still ran the FE out of heap. - `disable_nereids_rules = 'LOGICAL_JOIN_COMMUTE'` - `disable_nereids_rules = 'LOGICAL_INNER_JOIN_LASSCOM_PROJECT'` - `disable_nereids_rules = 'PUSH_DOWN_PROJECT_THROUGH_INNER_OUTER_JOIN_LEFT,PUSH_DOWN_PROJECT_THROUGH_INNER_OUTER_JOIN_RIGHT'` - `enable_dphyp_optimizer = true` - `disable_join_reorder = true` - On 4.1.1, form 4b is planned as mark joins (`markJoinSlotReference=Optional[...]` in `EXPLAIN REWRITTEN PLAN`), and it doesn't loop. On 4.1.5 the same policies become plain semi joins. - We suspect #66482 / #67280 (mark join slot inference, `d6fdff126d0` on branch-4.1). That would explain why 4.1.5 newly loops on 4b. The loop itself already exists in 4.1.1 (form 4a). - `SemiJoinSemiJoinTransposeProject` was last changed in #66574. That change only adds a mark-join guard. - #69748 / #69814 (project push-down through joins, on branch-4.1 but not in a release) may be related. We have not tested it. - We don't know why only sampled statistics trigger it. On 4.1.5 they fill `hot_values`, while 4.1.1's sampled statistics leave it null. **Planner timeout in cloud mode.** On our cloud-mode cluster, the audit log said `Nereids cost too much time (305s > 300s)` while the session had `nereids_timeout_second = 60`. - `CloudSystemInfoService.waitForAutoStart` wraps the wait in `withTemporaryNereidsTimeout`. It runs even when the compute group is already `NORMAL`, and it is reached from common planning paths such as `getBackendsByCurrentCluster`. - `withTemporaryNereidsTimeout` calls `profile.setWarmup(true)` and never resets it. Its javadoc says "Temporarily set nereids timeout and restore it after execution". - `SimpleJobScheduler` and `RewriteJob` then use `Config.auto_start_wait_to_resume_times` (300) as the timeout for the rest of the statement. I can file this as a separate issue if you prefer. **Workaround.** We use this: ```sql SET GLOBAL disable_nereids_rules = 'LOGICAL_SEMI_JOIN_SEMI_JOIN_TRANSPOSE_PROJECT'; ``` ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
