http://starrocks.io logo
Join Slack
Powered by
Hey Folks, I came across a scenario where if a que...
# questions-and-troubleshooting
v
Hey Folks, I came across a scenario where if a query has multiple filter with OR and And is not working properly. For ex- I have 2 MV - A and dim_hour And if i write a query , It returns "NA" too in my result- But if do just distinct of name, it exclude "NA". So Anyone know if this is the behavior or a BUG?
Copy code
SELECT
    count( `A`.`id` ) as count_id  ,
    `A`.`name` ,
    count(*) as `ROW____COUNT`  
from
    A
INNER JOIN
    dim_hour 
        ON A.localhour = dim_hour .dateid  
WHERE
    (
        (
            `A`.`name` IS NULL 
            OR lower( `A`.`name`)  not in (  'na' )  
        )  
        AND  (
            (
                `dim_hour`.`datehour` BETWEEN '2026-01-01T00:00:00.000000000' and '2026-01-31T23:59:59.000000000'
            ) 
        )  
    )   
group by
`A`.`name`   
order by
   `A`.`name`  asc
u
Hi @Vishal, I'm not sure if this helps, but can you try this variable in your query?
Copy code
SELECT /*+ SET_VAR(enable_materialized_view_rewrite=false) */ ...
t
@Rocky - do queries with multiple ORs and MVs have any issues? And if i write a query , It returns "NA" too in my result- But if do just distinct of name, it exclude "NA". So Anyone know if this is the behavior or a BUG?
Copy code
SELECT
    count( `A`.`id` ) as count_id  ,
    `A`.`name` ,
    count(*) as `ROW____COUNT`  
from
    A
INNER JOIN
    dim_hour 
        ON A.localhour = dim_hour .dateid  
WHERE
    (
        (
            `A`.`name` IS NULL 
            OR lower( `A`.`name`)  not in (  'na' )  
        )  
        AND  (
            (
                `dim_hour`.`datehour` BETWEEN '2026-01-01T00:00:00.000000000' and '2026-01-31T23:59:59.000000000'
            ) 
        )  
    )   
group by
`A`.`name`   
order by
   `A`.`name`  asc
r
Hi there! I'm Rocky, and I'd be happy to help you with your StarRocks query issue. It sounds like you're encountering some unexpected behavior with filtering and potentially Materialized View (MV) rewrites. Here’s a breakdown of what might be happening and how to troubleshoot it: 1. Is it a Materialized View (MV) Rewrite issue? You mentioned "MVd" and asked if multiple `OR`s have issues. StarRocks uses an optimizer to transparently rewrite queries to use asynchronous Materialized Views. Sometimes, complex 
OR
logic or specific function combinations (like 
lower()
inside a 
NOT IN
) can lead to incorrect rewrite matches or unexpected results. How to check: Run your query with the MV rewrite disabled to see if the "NA" still appears:
Copy code
sql
SET enable_materialized_view_rewrite = false;
-- Run your query again
* If "NA" disappears: Then it is likely a bug or a limitation in the query rewrite logic for that specific version of StarRocks. * If "NA" persists: Then the issue is in the base query logic or the data itself. 2. Is "NA" actually a 
NULL
value? In your 
WHERE
clause, you have:
Copy code
sql
( `A`.`name` IS NULL OR lower(`A`.`name`) NOT IN ('na') )
This logic explicitly includes rows where 
name
is 
NULL
. * Many SQL clients and BI tools display 
NULL
values as "NA", "<NULL>", or "None". * If your 
distinct
query excludes "NA", check if it also excludes `NULL`s. If the 
distinct
result shows a blank or a specific NULL indicator, but your main query shows "NA", they are likely both referring to the same 
NULL
records that passed your 
IS NULL
filter. 3. Hidden Characters or Spaces If "NA" is a literal string and not a 
NULL
, it might be failing the 
NOT IN ('na')
filter because of trailing spaces or hidden characters (e.g., 
'NA '
instead of 
'NA'
). * 
lower('NA ')
results in 
'na '
. * 
'na '
is not in 
('na')
, so the row remains in the result. Test this by checking the length:
Copy code
sql
SELECT `name`, LENGTH(`name`) 
FROM A 
WHERE lower(`name`) = 'na'; -- See if this returns anything
4. Known Behavior with 
OR
and 
NOT IN
 There are no general "bugs" with multiple `OR`s, but they can be tricky: * NULL handling: In SQL, 
NOT IN
behaves differently if there are NULLs involved in the list, but since your list is a constant 
('na')
, this shouldn't be the issue. * Optimization: Very large numbers of 
OR
conditions can sometimes hit expression complexity limits, but your query looks well within normal bounds. Summary Checklist: 1. Check `EXPLAIN`: Run 
EXPLAIN <your_query>
and see if it is scanning an 
OlapScanNode
(base table) or a 
Materialized View
. 2. Toggle Rewrite: Use 
SET enable_materialized_view_rewrite = false;
to isolate if the MV is the cause. 3. Verify NULLs: Check if 
SELECT count(*) FROM A WHERE name IS NULL
matches the row count for the "NA" you are seeing. If disabling the MV rewrite fixes the issue, please let me know which StarRocks version you are using so I can check for specific known bugs in that release! References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md
Hi! I'm Rocky. Let's look into this behavior you're seeing with 
OR
conditions and Materialized Views (MVs). Based on your query and the behavior you described, here are the most likely causes and how to troubleshoot them: 1. The "NULL vs. String" Confusion In your 
WHERE
clause, you have:
Copy code
sql
( `A`.`name` IS NULL OR lower( `A`.`name`) not in ( 'na' ) )
This logic explicitly includes rows where 
name
is 
NULL
. * If your database has 
NULL
values and your BI tool or SQL client is configured to display 
NULL
as the string "NA", those rows will appear in your result. * If you want to exclude both the string 'na' and nulls, your condition should be: 
A.name IS NOT NULL AND lower(A.name) NOT IN ('na')
How to verify: Run 
SELECT count(*) FROM A WHERE name IS NULL
. If it returns a count, then the "NA" you see in the results might actually be 
NULL
. 2. Materialized View (MV) Rewrite Issues You asked if multiple `OR`s and MVs have issues. Historically, complex 
OR
predicates in the 
WHERE
clause could sometimes lead to incorrect query rewrites or prevent rewrites entirely in earlier versions of StarRocks (prior to v3.2). If the query is being rewritten to use an MV, there might be a logic discrepancy in how the optimizer handles the 
OR
+ 
lower()
combination. How to verify if it's an MV bug: 1. Check if the query is using an MV by running: 
EXPLAIN <your_query>;
(Look for 
SCAN [mv_name]
). 2. Disable MV rewrite temporarily and run the query again:
Copy code
sql
   SET enable_materialized_view_rewrite = false;
   -- Run your query again
If the result is correct (excludes "NA") when rewrite is false, but incorrect when true, then you have likely encountered a bug in the MV rewrite logic for that specific version. 3. Hidden Characters (Trailing Spaces) If 
A.name
contains the string 
"NA "
(with a space), then: * 
lower("NA ")
results in 
"na "
. * 
"na "
is NOT IN 
('na')
, so the row is included. * Standard 
DISTINCT
or basic filters sometimes handle trailing spaces differently depending on the collation or version. How to verify: Try 
lower(trim(A.name)) not in ('na')
. Summary Recommendations: * Check for NULLs: Ensure the "NA" you see isn't just a displayed 
NULL
value (since your 
OR
condition explicitly allows them). * Test without MV: Use 
SET enable_materialized_view_rewrite = false;
to see if the base table behavior matches the MV behavior. * Refine the Predicate: If you want to exclude the literal string 'na' regardless of case and also exclude nulls, use:
Copy code
sql
  WHERE A.name IS NOT NULL
    AND lower(A.name) != 'na'
    AND dim_hour.datehour BETWEEN ...
If you find that disabling the MV rewrite changes the result, please check your StarRocks version. Significant improvements to 
OR
predicate rewrites were introduced in version 3.2.x and above. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md
v
@김병주 Thanks for you input. But this doesn't resolved :(
speechless rocky 1
@Rocky Setting the enable_materialized_view_rewrite = false, doesn't helps and it still display "NA". But it is the same behavior with base table too.
r
Hello! I'm Rocky. It sounds like you're encountering some confusion regarding the "NA" status and the session variables used to control query rewrites. The behavior you're describing—where setting 
enable_materialized_view_rewrite = false
has no effect and "NA" still appears—is common when dealing with different types of materialized views in StarRocks. 1. Async vs. Sync (Rollup) Materialized Views StarRocks uses different variables to control different types of materialized views: * 
enable_materialized_view_rewrite
: This variable controls Asynchronous Materialized Views (created with 
REFRESH ASYNC
or 
REFRESH MANUAL
). * 
enable_rollup_rewrite
: This variable controls Synchronous Materialized Views (often called Rollups, created with 
CREATE MATERIALIZED VIEW ... AS SELECT ...
without a refresh strategy). If you are using a synchronous materialized view (Rollup) to optimize an Aggregate table or a Duplicate key table, setting 
enable_materialized_view_rewrite = false
will not disable the rewrite. You must set 
SET enable_rollup_rewrite = false;
instead. 2. Why "NA" is displayed The "NA" (Not Applicable) status typically appears in the 
QUERY_REWRITE_STATUS
column of the 
information_schema.materialized_views
table or in certain query profile tools: * For Synchronous MVs (Rollups): These do not use the same state-tracking mechanism as asynchronous MVs. Consequently, fields like 
QUERY_REWRITE_STATUS
, 
IS_ACTIVE
, and 
LAST_REFRESH_STATE
often display as 
NA
or are empty because they are managed differently. * For Base Tables: Since a base table is not a materialized view, any field specifically tracking "Materialized View Rewrite Status" will naturally show "NA" as it is not applicable. 3. How to verify the rewrite To see if your query is truly being rewritten, use the 
EXPLAIN
command:
Copy code
sql
EXPLAIN <your_query>;
Look for the 
OlapScanNode
in the output: * If the 
TABLE
 field shows your base table name, the rewrite is disabled. * If the 
TABLE
 field shows the materialized view name, the rewrite is active. * Check the 
rollup
 field. If it contains a name other than the base table, it means a Rollup (Synchronous MV) is being used. Summary of Actions If you want to ensure no materialized views or rollups are used in your query:
Copy code
sql
-- Disable rewrite for Asynchronous MVs
SET enable_materialized_view_rewrite = false;

-- Disable rewrite for Synchronous MVs (Rollups)
SET enable_rollup_rewrite = false;
If you're still seeing "NA" in a specific tool or command output, please let me know which command you are running (e.g., 
SHOW MATERIALIZED VIEWS
or 
EXPLAIN REWRITE
), and I can provide more specific guidance! References • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/caching/query_cache.md • best_practices/query_tuning/query_profile_operator_metrics.md • using_starrocks/Cost_based_optimizer.md
2 Views
Open in Slack
PreviousNext
Powered by