< Rocky> hello StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV> hello

# questions-and-troubleshooting

Lý Thành

08/15/2026, 7:23 AM

@Rocky hello

Rocky

08/15/2026, 7:23 AM

Hello! I'm Rocky, your StarRocks assistant. How can I help you today?

Lý Thành

08/15/2026, 7:23 AM

Rocky

08/15/2026, 7:23 AM

This crash log indicates a Segmentation Fault (SIGSEGV) in the StarRocks Backend (BE) during the execution of a query. Specifically, the crash occurs in

starrocks::BinaryColumnBase<unsigned int>::_build_slices()

, which is triggered by the

replace

string function. Analysis of the Stack Trace The trace reveals a specific path in the vectorized execution engine: 1. Operator:

ProjectOperator

(performing an expression evaluation). 2. Expression Tree:

VectorizedArithmeticExpr

→

VectorizedCoalesceExpr

→

VectorizedCastExpr

→

VectorizedNullIfExpr

→

replace

. 3. Point of Failure: The

replace

function uses a

ColumnViewer

to access string data. The

ColumnViewer

calls

_build_slices()

to prepare the string offsets and data for processing. The crash happens here, likely because it is attempting to access a memory address that is null or has already been freed. Potential Causes * Nullability Inconsistency: The expression involves

COALESCE

CAST

, and

NULLIF

. There are known issues in some StarRocks versions where the nullability of a column is incorrectly handled after a

CAST

COALESCE

operation, causing downstream functions (like

replace

) to receive a column that doesn't match its expected internal structure. * Constant vs. Vectorized Column Mismatch: If one of the arguments to

replace

(the string, the pattern, or the replacement) is a constant that was converted into a specific column type incorrectly, the

ColumnViewer

might fail when trying to build slices for it. * Memory Corruption/Use-after-free: The crash in

_build_slices

can occur if the underlying data buffer or offset buffer of a

BinaryColumn

has been released prematurely. Recommended Troubleshooting Steps 1. Identify the Query: The

query_id

01a0012d-75f7-7c9e-a51f-010a458607d5

. Check your FE (Frontend) logs (

fe.audit.log

fe.log

) for this ID to find the exact SQL being executed. 2. Simplify the Expression: Once you have the SQL, try to isolate the part of the

SELECT

WHERE

clause causing the crash. It likely looks something like:

Copy code

sql
    REPLACE(COALESCE(CAST(NULLIF(col1, col2) AS VARCHAR), 'default'), 'search', 'replace')

3. Check for Known Bug: Version 4.0.12 (referenced in your log as a 2026 release) suggests you may be running a very recent or potentially unreleased/development build if this is a simulation. However, in StarRocks 3.x and 4.x, similar crashes have been linked to Issue #65454 or #65429, where incorrect nullability settings after expression merging caused BE crashes. 4. Workaround: Try rewriting the expression to use

CASE WHEN

instead of a nested

NULLIF/COALESCE/CAST

chain to see if the crash persists. _ _Example*: Replace

NULLIF(a, b)

with

CASE WHEN a = b THEN NULL ELSE a END

. Next Steps If you can provide the SQL query and the schema (DDL) of the tables involved, I can help determine if there is a specific data pattern or expression nesting that is triggering this bug. You should also consider reporting this to the StarRocks community via GitHub or Slack, attaching the full

be.out

log. References • sql-reference/sql-functions/condition-functions/coalesce.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/condition-functions/nullif.md • sql-reference/sql-functions/condition-functions/ifnull.md • knowledge/usage/best-practice.md

Rocky

08/15/2026, 7:23 AM

• sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • faq/Sql_faq.md • faq/loading/Stream_load_faq.md

Lý Thành

08/17/2026, 4:17 AM

@Rocky CREATE TABLE LOSREP.APPLICATION ( ID NUMBER(20,0), APP_UID VARCHAR2(50), APP_NUMBER NUMBER(11,0), APP_CODE VARCHAR2(20), BUSINESS_LINE VARCHAR2(50), BUSINESS_PROCESS VARCHAR2(50), LOAN_PURPOSE VARCHAR2(50), LOAN_PURPOSE_LBL VARCHAR2(250), LOAN_PURPOSE_OTHER VARCHAR2(250), PRODUCT_CATEGORY VARCHAR2(50), PRODUCT_CATEGORY_LBL VARCHAR2(250), SCHEME_PRODUCT VARCHAR2(50), SCHEME_PRODUCT_LBL VARCHAR2(250), LOAN_TERM VARCHAR2(50), YEAR_INTEREST NUMBER(5,2), MONTH_INTEREST NUMBER(5,2), HAS_INSURANCE VARCHAR2(50), INSURANCE_COMPANY VARCHAR2(50), INSURANCE_COMPANY_LBL VARCHAR2(250), INSURANCE_RATE NUMBER(5,2), INSURANCE_FEE NUMBER(20,2), LOAN_CONSUMER NUMBER(20,2), LOAN_AMOUNT NUMBER(20,2), CUSTOMER_NAME VARCHAR2(250), CUSTOMER_SHORT_NAME VARCHAR2(250), CUSTOMER_NICK_NAME VARCHAR2(250), CUSTOMER_CODE VARCHAR2(50), DATE_OF_BIRTH DATE, GENDER VARCHAR2(50), MARITAL_STATUS VARCHAR2(50), MARITAL_STATUS_LBL VARCHAR2(100), EDUCATION VARCHAR2(50), EDUCATION_LBL VARCHAR2(100), PASSPORT_NUMBER VARCHAR2(50), ID_CARD_TYPE VARCHAR2(50), ID_CARD_NUMBER VARCHAR2(50), ID_CARD_ISSUE_DATE DATE, ID_CARD_ISSUE_PLACE VARCHAR2(50), ID_CARD_ISSUE_PLACE_LBL VARCHAR2(100), ID_CARD_NUMBER_OTHER VARCHAR2(50), MOBILE_PHONE VARCHAR2(50), MOBILE_PHONE_OTHER VARCHAR2(50), EMAIL VARCHAR2(100), JOB VARCHAR2(50), JOB_LBL VARCHAR2(100), JOB_OTHER VARCHAR2(250), POA_TYPE VARCHAR2(50), POA_TYPE_LBL VARCHAR2(150), POA_NUMBER VARCHAR2(50), POA_ISSUE_DATE DATE, POA_ISSUE_PLACE VARCHAR2(50), POA_ISSUE_PLACE_LBL VARCHAR2(100), HOME_PHONE VARCHAR2(50), COMPANY_ADDRESS VARCHAR2(500), WORKING_ADDRESS VARCHAR2(500), TEMPORARY_RESIDENCE_STATUS VARCHAR2(50), PERMANENT_ADDRESS VARCHAR2(500), TEMPORARY_ADDRESS VARCHAR2(500), DISBURSEMENT_CHANNEL VARCHAR2(50), AGENCY_PAYMENT VARCHAR2(50), ACCOUNT_NUMBER VARCHAR2(50), ACCOUNT_NAME VARCHAR2(100), BANK_NAME VARCHAR2(50), BRANCH_NAME VARCHAR2(250), SALE_DEPARTMENT_PATH VARCHAR2(250), SALE_CODE VARCHAR2(50), SALE_NAME VARCHAR2(250), SALE_MOBILE VARCHAR2(50), APPLICATION_DATE DATE, APPROVAL_DATE DATE, CONTRACT_NUMBER VARCHAR2(50), SIGN_CONTRACT_DATE DATE, LOAN_DEPOSIT_NUMBER VARCHAR2(50), MONTHLY_PAYMENT NUMBER(20,2), FIRST_PAYMENT_DATE DATE, DISBURSEMENT_DATE DATE, DISBURSEMENT_STATUS VARCHAR2(50), CREATED_DATE DATE, CREATED_BY VARCHAR2(50), LAST_UPDATED_DATE DATE, LAST_UPDATED_BY VARCHAR2(50), LMS_CIF VARCHAR2(50), BANK_NAME_LBL VARCHAR2(250), DISBURSEMENT_CHANNEL_LBL VARCHAR2(250), APPLICATION_SERI_NUMBER VARCHAR2(50), DISBURSEMENT_FEE NUMBER(20,2), PARTNER_ID VARCHAR2(50), DATA_SOURCE VARCHAR2(50), SALE_HR_CODE VARCHAR2(50), SALE_LEAD_CODE VARCHAR2(50), SALE_LEAD_NAME VARCHAR2(250), SALE_LEAD_MOBILE VARCHAR2(50), SALE_LEAD_HR_CODE VARCHAR2(50), SUB_APP_UID VARCHAR2(50), SUB_APP_NUMBER NUMBER(11,0), ID_CARD_EXPIRY_DATE DATE, SALE_SUBMIT_DATE DATE, ACCEPTED_APPLICATION_DATE DATE, TOTAL_GOODS_PRICE NUMBER(20,2), PREPAID_AMOUNT NUMBER(20,2), AGREE_PROVIDE_INFO VARCHAR2(50), AGREE_RECEIVE_ADVERTISING VARCHAR2(50), THIRD_PARTY_NAME VARCHAR2(250), THIRD_PARTY_TAX_CODE VARCHAR2(250), THIRD_PARTY_CODE VARCHAR2(50), PRODUCT_DATA CLOB, PRODUCT_TYPE VARCHAR2(250), CONTRACT_TYPE VARCHAR2(50), OVERDUE_INTEREST_RATES NUMBER(5,2), LOAN_FEE NUMBER(20,2), APPLICATION_TYPE VARCHAR2(50), SALE_COMMENT VARCHAR2(4000), MOBILE_ZALO_TYPE VARCHAR2(50), MOBILE_PHONE_ZALO VARCHAR2(50), MOBILE_OWNER_STATUS VARCHAR2(250), TYPE_STATEMENT VARCHAR2(50), TYPE_STATEMENT_LABEL VARCHAR2(250), HAS_SMART_PHONE VARCHAR2(50), YEAR_INSURANCE_FEE NUMBER(20,2), MONTH_INSURANCE_FEE NUMBER(20,2), CREDIT_INSTITUTIONS_IN_PCB VARCHAR2(50), LIVING_TIME_IN_MONTH NUMBER(3,0), PRODUCT_METHOD VARCHAR2(50), PRODUCT_GROUP VARCHAR2(50), PRODUCT_CODE VARCHAR2(50), IS_CHANGE_DATA_KYC VARCHAR2(20), SALE_REGION_CODE VARCHAR2(100), SALE_REGION_NAME VARCHAR2(500), SALE_DEPARTMENT_CODE VARCHAR2(100), SALE_DEPARTMENT_NAME VARCHAR2(250), PRODUCT_CLASS VARCHAR2(50), OP_CALL_STATUS VARCHAR2(20), DISBURSEMENT_TYPE VARCHAR2(100), DISBURSEMENT_TYPE_LBL VARCHAR2(250), CUSTOMER_GROUP VARCHAR2(100), CUSTOMER_GROUP_LBL VARCHAR2(250), SALE_LEAD_RETURN_FLAG VARCHAR2(20), NATIONALITY VARCHAR2(50), FAST_CHECK VARCHAR2(10), LOAN_PURPOSE_CLASSIFICATION VARCHAR2(100), LOAN_PURPOSE_CLASSIFICATION_LBL VARCHAR2(300), ID_CARD_NUMBER_QR VARCHAR2(50), ID_CARD_NUMBER_OTHER_QR VARCHAR2(50), MOBILE_ZALO_TYPE_LBL VARCHAR2(50), FLAG_MERGE_CUSTOMER VARCHAR2(50), REFERRAL_CODE VARCHAR2(250), ASSET VARCHAR2(25), LOAN_CONSUMER_REQUESTED NUMBER(20,2), LOAN_AMOUNT_REQUESTED NUMBER(20,2), LOAN_TERM_REQUESTED VARCHAR2(50), COMPANY_EMAIL VARCHAR2(100), RANUNI VARCHAR2(50), PARTNER_TRANSACTION_ID VARCHAR2(100), NEW_LOCATION VARCHAR2(250), STRATEGY_GROUP VARCHAR2(150), CONSTRAINT APPLICATION_APP_CODE_UNIQUE UNIQUE (APP_CODE), CONSTRAINT APPLICATION_APP_UID_UNI UNIQUE (APP_UID), CONSTRAINT PK_APPLICATION PRIMARY KEY (ID), CONSTRAINT SYS_C009655 CHECK ("ID" IS NOT NULL) ); to starrocks table

Rocky

08/17/2026, 4:18 AM

To convert your Oracle

LOSREP.APPLICATION

table to StarRocks, you should use the Primary Key model since the original table defines a primary key (

ID

). Key Mapping Considerations: 1. Data Types: *

NUMBER(p, 0)

maps to

BIGINT

(up to 19 digits) or

DECIMAL(p, 0)

. *

NUMBER(p, s)

maps to

DECIMAL(p, s)

. *

VARCHAR2(n)

maps to

VARCHAR(n)

. Note: StarRocks

VARCHAR

limit is 65,533 bytes. *

DATE

in Oracle includes time, so it maps to

DATETIME

in StarRocks. *

CLOB

is mapped to

VARCHAR(65533)

. If the data is specifically JSON, you could use the

JSON

type. 2. Constraints: * StarRocks Primary Key tables enforce uniqueness on the primary key. * StarRocks does not support additional

UNIQUE

constraints or

CHECK

constraints. 3. Storage: * The

DISTRIBUTED BY HASH(ID)

clause is used to spread data across the cluster. StarRocks DDL ```sql CREATE TABLE APPLICATION ( ID DECIMAL(20,0) NOT NULL COMMENT "Original: NUMBER(20,0)", APP_UID VARCHAR(50), APP_NUMBER BIGINT COMMENT "Original: NUMBER(11,0)", APP_CODE VARCHAR(20), BUSINESS_LINE VARCHAR(50), BUSINESS_PROCESS VARCHAR(50), LOAN_PURPOSE VARCHAR(50), LOAN_PURPOSE_LBL VARCHAR(250), LOAN_PURPOSE_OTHER VARCHAR(250), PRODUCT_CATEGORY VARCHAR(50), PRODUCT_CATEGORY_LBL VARCHAR(250), SCHEME_PRODUCT VARCHAR(50), SCHEME_PRODUCT_LBL VARCHAR(250), LOAN_TERM VARCHAR(50), YEAR_INTEREST DECIMAL(5,2), MONTH_INTEREST DECIMAL(5,2), HAS_INSURANCE VARCHAR(50), INSURANCE_COMPANY VARCHAR(50), INSURANCE_COMPANY_LBL VARCHAR(250), INSURANCE_RATE DECIMAL(5,2), INSURANCE_FEE DECIMAL(20,2), LOAN_CONSUMER DECIMAL(20,2), LOAN_AMOUNT DECIMAL(20,2), CUSTOMER_NAME VARCHAR(250), CUSTOMER_SHORT_NAME VARCHAR(250), CUSTOMER_NICK_NAME VARCHAR(250), CUSTOMER_CODE VARCHAR(50), DATE_OF_BIRTH DATETIME, GENDER VARCHAR(50), MARITAL_STATUS VARCHAR(50), MARITAL_STATUS_LBL VARCHAR(100), EDUCATION VARCHAR(50), EDUCATION_LBL VARCHAR(100), PASSPORT_NUMBER VARCHAR(50), ID_CARD_TYPE VARCHAR(50), ID_CARD_NUMBER VARCHAR(50), ID_CARD_ISSUE_DATE DATETIME, ID_CARD_ISSUE_PLACE VARCHAR(50), ID_CARD_ISSUE_PLACE_LBL VARCHAR(100), ID_CARD_NUMBER_OTHER VARCHAR(50), MOBILE_PHONE VARCHAR(50), MOBILE_PHONE_OTHER VARCHAR(50), EMAIL VARCHAR(100), JOB VARCHAR(50), JOB_LBL VARCHAR(100), JOB_OTHER VARCHAR(250), POA_TYPE VARCHAR(50), POA_TYPE_LBL VARCHAR(150), POA_NUMBER VARCHAR(50), POA_ISSUE_DATE DATETIME, POA_ISSUE_PLACE VARCHAR(50), POA_ISSUE_PLACE_LBL VARCHAR(100), HOME_PHONE VARCHAR(50), COMPANY_ADDRESS VARCHAR(500), WORKING_ADDRESS VARCHAR(500), TEMPORARY_RESIDENCE_STATUS VARCHAR(50), PERMANENT_ADDRESS VARCHAR(500), TEMPORARY_ADDRESS VARCHAR(500), DISBURSEMENT_CHANNEL VARCHAR(50), AGENCY_PAYMENT VARCHAR(50), ACCOUNT_NUMBER VARCHAR(50), ACCOUNT_NAME VARCHAR(100), BANK_NAME VARCHAR(50), BRANCH_NAME VARCHAR(250), SALE_DEPARTMENT_PATH VARCHAR(250), SALE_CODE VARCHAR(50), SALE_NAME VARCHAR(250), SALE_MOBILE VARCHAR(50), APPLICATION_DATE DATETIME, APPROVAL_DATE DATETIME, CONTRACT_NUMBER VARCHAR(50), SIGN_CONTRACT_DATE DATETIME, LOAN_DEPOSIT_NUMBER VARCHAR(50), MONTHLY_PAYMENT DECIMAL(20,2), FIRST_PAYMENT_DATE DATETIME, DISBURSEMENT_DATE DATETIME, DISBURSEMENT_STATUS VARCHAR(50), CREATED_DATE DATETIME, CREATED_BY VARCHAR(50), LAST_UPDATED_DATE DATETIME, LAST_UPDATED_BY VARCHAR(50), LMS_CIF VARCHAR(50), BANK_NAME_LBL VARCHAR(250), DISBURSEMENT_CHANNEL_LBL VARCHAR(250), APPLICATION_SERI_NUMBER VARCHAR(50), DISBURSEMENT_FEE DECIMAL(20,2), PARTNER_ID VARCHAR(50), DATA_SOURCE VARCHAR(50), SALE_HR_CODE VARCHAR(50), SALE_LEAD_CODE VARCHAR(50), SALE_LEAD_NAME VARCHAR(250), SALE_LEAD_MOBILE VARCHAR(50),

Rocky

08/17/2026, 4:18 AM

SALE_LEAD_HR_CODE VARCHAR(50), SUB_APP_UID VARCHAR(50), SUB_APP_NUMBER BIGINT, ID_CARD_EXPIRY_DATE DATETIME, SALE_SUBMIT_DATE DATETIME, ACCEPTED_APPLICATION_DATE DATETIME, TOTAL_GOODS_PRICE DECIMAL(20,2), PREPAID_AMOUNT DECIMAL(20,2), AGREE_PROVIDE_INFO VARCHAR(50), AGREE_RECEIVE_ADVERTISING VARCHAR(50), THIRD_PARTY_NAME VARCHAR(250), THIRD_PARTY_TAX_CODE VARCHAR(250), THIRD_PARTY_CODE VARCHAR(50), PRODUCT_DATA VARCHAR(65533) COMMENT "Original: CLOB", PRODUCT_TYPE VARCHAR(250), CONTRACT_TYPE VARCHAR(50), OVERDUE_INTEREST_RATES DECIMAL(5,2), LOAN_FEE DECIMAL(20,2), APPLICATION_TYPE VARCHAR(50), SALE_COMMENT VARCHAR(4000), MOBILE_ZALO_TYPE VARCHAR(50), MOBILE_PHONE_ZALO VARCHAR(50), MOBILE_OWNER_STATUS VARCHAR(250), TYPE_STATEMENT VARCHAR(50), TYPE_STATEMENT_LABEL VARCHAR(250), HAS_SMART_PHONE VARCHAR(50), YEAR_INSURANCE_FEE DECIMAL(20,2), MONTH_INSURANCE_FEE DECIMAL(20,2), CREDIT_INSTITUTIONS_IN_PCB VARCHAR(50), LIVING_TIME_IN_MONTH INT, PRODUCT_METHOD VARCHAR(50), PRODUCT_GROUP VARCHAR(50), PRODUCT_CODE VARCHAR(50), IS_CHANGE_DATA_KYC VARCHAR(20), SALE_REGION_CODE VARCHAR(100), SALE_REGION_NAME VARCHAR(500), SALE_DEPARTMENT_CODE VARCHAR(100), SALE_DEPARTMENT_NAME VARCHAR(250), PRODUCT_CLASS VARCHAR(50), OP_CALL_STATUS VARCHAR(20), DISBURSEMENT_TYPE VARCHAR(100), DISBURSEMENT_TYPE_LBL VARCHAR(250), CUSTOMER_GROUP VARCHAR(100), CUSTOMER_GROUP_LBL VARCHAR(250), SALE_LEAD_RETURN_FLAG VARCHAR(20), NATIONALITY VARCHAR(50), FAST_CHECK VARCHAR(10), LOAN_PURPOSE_CLASSIFICATION VARCHAR(100), LOAN_PURPOSE_CLASSIFICATION_LBL VARCHAR(300), ID_CARD_NUMBER_QR VARCHAR(50), ID_CARD_NUMBER_OTHER_QR VARCHAR(50), MOBILE_ZALO_TYPE_LBL VARCHAR(50), FLAG_MERGE_CUSTOMER VARCHAR(50), REFERRAL_CODE VARCHAR(250), ASSET VARCHAR(25), LOAN_CONSUMER_REQUESTED DECIMAL(20,2), LOAN_AMOUNT_REQUESTED DECIMAL(20,2), LOAN_TERM_REQUESTED VARCHAR(50), COMPANY_EMAIL VARCHAR(100), RANUNI VARCHAR(50), PARTNER_TRANSACTION_ID VARCHAR(100), NEW_LOCATION VARCHAR(250), STRATEGY_GROUP VARCHAR(150) ) ENGINE=OLAP PRIMARY KEY(ID) DISTRIBUTED BY HASH(ID) BUCKETS 16; ``` Important Notes for your Version (4.0.12): The crash log you shared earlier indicated a segmentation fault related to string processing functions (

replace

) involving complex expressions. When using this table: * CLOB Data: If

PRODUCT_DATA

contains JSON, consider using the

JSON

type for better performance and easier querying. * Expression Safety: If you use this table in queries with nested

REPLACE(COALESCE(...))

functions, ensure the input types are correctly cast to avoid the BE crash seen in your previous log. References • table_design/table_types/Primary_key_table.md • sql-reference/sql-statements/data-types/DECIMAL.md • sql-reference/sql-statements/data-types/DATETIME.md • sql-reference/sql-statements/data-types/VARCHAR.md • sql-reference/sql-statements/data-definition-language/CREATE_TABLE.md • table_design/Data_types.md • sql-reference/sql-statements/data-types/JSON.md • table_design/Sort_key.md

Lý Thành

08/17/2026, 8:38 AM

@Rocky ví dụ dùng masterialized view syn

Rocky

08/17/2026, 8:38 AM

Trong StarRocks, Synchronous Materialized View (Sync MV) là loại MV được cập nhật đồng bộ ngay khi dữ liệu được nạp vào bảng gốc. Nó chủ yếu được dùng để tăng tốc các câu lệnh bản chất là tính toán gộp (aggregation) trên một bảng duy nhất. Dưới đây là ví dụ chi tiết: 1. Tạo bảng gốc (Base Table) Giả sử bạn có bảng lưu trữ thông tin giao dịch bán hàng:

Copy code

sql
CREATE TABLE sales_records (
    record_id INT,
    seller_id INT,
    store_id INT,
    sale_date DATE,
    sale_amt DECIMAL(10, 2)
) 
DUPLICATE KEY(record_id, seller_id)
DISTRIBUTED BY HASH(record_id);

2. Tạo Materialized View đồng bộ (Sync MV) Chúng ta muốn tính tổng doanh số theo từng cửa hàng (

store_id

). Sync MV sẽ tự động tính toán và lưu trữ kết quả này.

Copy code

sql
CREATE MATERIALIZED VIEW mv_store_sales AS
SELECT 
    store_id, 
    SUM(sale_amt) AS total_sales
FROM sales_records
GROUP BY store_id;

3. Cách hoạt động và đặc điểm * Tự động cập nhật: Khi bạn dùng

INSERT INTO

hoặc

Stream Load

để nạp dữ liệu vào bảng

sales_records

, StarRocks sẽ tự động tính toán phần chênh lệch và cập nhật vào

mv_store_sales

trong cùng một transaction. * Tự động Query Rewrite: Bạn không cần phải gọi tên MV trong câu lệnh

SELECT

. Optimizer của StarRocks sẽ tự nhận diện và lấy dữ liệu từ MV nếu nó nhanh hơn. Ví dụ truy vấn: Dù bạn viết lệnh truy vấn vào bảng gốc:

Copy code

sql
SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;

Nhưng thực tế StarRocks sẽ đọc từ

mv_store_sales

để trả kết quả ngay lập tức mà không cần quét toàn bộ bảng gốc. 4. Một số lưu ý quan trọng (Hạn chế của Sync MV) 1. Chỉ hỗ trợ một bảng: Không thể JOIN nhiều bảng trong Sync MV (nếu muốn JOIN bạn phải dùng Async MV). 2. Hàm gộp hạn chế: Chỉ hỗ trợ các hàm cơ bản như

SUM

MIN

MAX

COUNT

BITMAP_UNION

HLL_UNION

. 3. Bảng gốc: Chỉ hỗ trợ các bảng loại

Duplicate Key

Primary Key

hoặc

Aggregate Key

. 4. Hiệu năng ghi: Vì là cập nhật đồng bộ, nếu bạn tạo quá nhiều Sync MV trên một bảng, tốc độ nạp dữ liệu (Load) vào bảng đó có thể bị chậm lại một chút. Khi nào nên dùng Sync MV thay vì Async MV? * Dùng Sync MV khi bạn cần dữ liệu real-time tuyệt đối (vừa nạp vào là thấy ngay) và chỉ cần tính toán đơn giản trên 1 bảng. * Dùng Async MV khi bạn cần JOIN nhiều bảng, dùng các hàm phức tạp (Window functions) hoặc khi bảng gốc có lượng dữ liệu khổng lồ mà bạn chấp nhận độ trễ (latency) vài phút để làm mới dữ liệu. Tài liệu tham khảo • table_design/materialized_view/Materialized_view.md • sql-reference/sql-statements/data-definition-language/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • table_design/materialized_view/explore_materialized_view.md

Lý Thành

08/19/2026, 7:27 AM

@Rocky how to grant select all db in starrocks

Rocky

08/19/2026, 7:27 AM

To grant

SELECT

permissions on all databases and tables to a specific user or role in StarRocks, you use the

GRANT

statement with the

ALL DATABASES

scope. 1. Grant to a User Replace

'username'@'host'

with your actual user identity (e.g.,

'john'@'%'

Copy code

sql
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO USER 'username'@'host';

2. Grant to a Role It is often better practice to grant permissions to a role and then assign the user to that role.

Copy code

sql
-- Create the role (if it doesn't exist)
CREATE ROLE read_only_all;

-- Grant select on all tables/views in all databases
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE read_only_all;

-- Assign the role to a user
GRANT 'read_only_all' TO USER 'username'@'host';

Important Considerations: * Views: The

SELECT

privilege on

ALL TABLES

typically covers both physical tables and views. * Catalogs: If you are using StarRocks 3.0+ with External Catalogs (like Hive or Iceberg), you might need to specify the catalog or use the global syntax if the user needs access beyond the

default_catalog

. * To grant on all tables in a specific external catalog:

GRANT SELECT ON ALL TABLES IN ALL DATABASES ON CATALOG <catalog_name> TO USER 'username'@'host';

* Permissions Required: You must have the

user_admin

role or the

GRANT

privilege to execute these commands. * Verify Permissions: You can check the granted permissions using:

Copy code

sql
    SHOW GRANTS FOR 'username'@'host';

References • sql-reference/sql-statements/account-management/GRANT.md • administration/privilege_item.md • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • administration/Manage_privileges.md • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/account-management/REVOKE.md • administration/native_auth.md • sql-reference/sql-statements/account-management/CREATE_ROLE.md

Lý Thành

08/19/2026, 8:52 AM

@Rocky GRANT CREATE ON ALL MATERIALIZED VIEWS IN DATABASE dmapp TO hungnm40; correct

Rocky

08/19/2026, 8:52 AM

Your statement is incorrect. In StarRocks, the privilege to create new materialized views is granted at the Database level, not on the collection of existing materialized views. Why your syntax is incorrect: 1. Object Level: The syntax

ON ALL MATERIALIZED VIEWS IN DATABASE

is used to grant privileges (like

SELECT

REFRESH

, or

ALTER

) on MVs that already exist. You cannot "create" an object on an object that doesn't exist yet. 2. Privilege Name: The specific privilege name is

CREATE MATERIALIZED VIEW

, not just

CREATE

. The correct command: To allow the user

hungnm40

to create materialized views in the database

dmapp

, use:

Copy code

sql
GRANT CREATE MATERIALIZED VIEW ON DATABASE dmapp TO USER 'hungnm40'@'%';

(Note: Replace
'%'
with the actual host if necessary, or omit
USER
if
hungnm40
is a role). Summary of related privileges: * To create MVs:

GRANT CREATE MATERIALIZED VIEW ON DATABASE <db> TO ...

* To manage existing MVs:

GRANT REFRESH, ALTER, DROP ON ALL MATERIALIZED VIEWS IN DATABASE <db> TO ...

* To query existing MVs:

GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE <db> TO ...

References • sql-reference/sql-statements/account-management/GRANT.md • administration/privilege_item.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/account-management/GRANT.md

Lý Thành

08/19/2026, 9:17 AM

@Rocky my CREATED_DATE colume have data like this: 2025-03-17 134444 2025-03-17 134445; how to group by CREATED_DATE by daily

Rocky

08/19/2026, 9:17 AM

To group your

CREATED_DATE

column by day in StarRocks, you have two primary options depending on whether you want the result to be a

DATE

type or a

DATETIME

type. 1. Using
date()
or
to_date()
(Recommended) This is the simplest way. It converts the datetime into a

DATE

type (YYYY-MM-DD), removing the time component completely.

Copy code

sql
SELECT 
    date(CREATED_DATE) AS daily,
    count(*) 
FROM your_table
GROUP BY daily;

_Note:

to_date(CREATED_DATE)

works identically._ 2. Using
date_trunc()
This function truncates the time to the start of the day (

00:00:00

). It is useful if you want to keep the result as a

DATETIME

type.

Copy code

sql
SELECT 
    date_trunc('day', CREATED_DATE) AS daily,
    count(*) 
FROM your_table
GROUP BY daily;

3. Using
CAST
You can also explicitly cast the column to a

DATE

type.

Copy code

sql
SELECT 
    CAST(CREATED_DATE AS DATE) AS daily,
    count(*) 
FROM your_table
GROUP BY daily;

Summary of results: Input | date()
/
CAST
| date_trunc('day', ...)

2025-03-17 13:44:44

2025-03-17

2025-03-17 00:00:00

Performance Tip: If you frequently group or filter by date on a very large table, consider using Expression Partitioning with

date_trunc('day', CREATED_DATE)

Lý Thành

08/20/2026, 3:41 AM

@Rocky can starrocks create masterialied view change datatype from source table.

Rocky

08/20/2026, 3:41 AM

Yes, StarRocks allows you to change the data type of columns in a materialized view (MV) relative to the source table. The method depends on whether you are using an Asynchronous or Synchronous materialized view. 1. Asynchronous Materialized Views (Recommended) Asynchronous MVs are very flexible and support nearly any expression or function in the

SELECT

statement to transform data types. * Using
CAST
: You can explicitly cast a column to a different type. * Using Date Functions: You can convert strings or datetimes to dates. Example:

Copy code

sql
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH ASYNCHRONOUS
AS
SELECT 
    CAST(user_id AS VARCHAR) AS user_id_str, -- Change INT to VARCHAR
    date(created_at) AS report_date,         -- Change DATETIME to DATE
    SUM(price) AS total_revenue
FROM sales_table
GROUP BY user_id_str, report_date;

2. Synchronous Materialized Views In StarRocks v3.1 and later, synchronous MVs also support complex expressions and type casting in the

SELECT

list. * Requirement: You must provide an alias for any column that uses an expression or a type change. * Example:

Copy code

sql
    CREATE MATERIALIZED VIEW sync_mv AS
    SELECT
        k1,
        CAST(k2 AS BIGINT) AS k2_big, -- Type change with alias
        SUM(v1)
    FROM base_table
    GROUP BY k1, k2_big;

Key Considerations * Query Rewrite: If you change the data type in an MV, StarRocks can still use it to rewrite queries automatically, provided the query's logic matches the MV's transformed columns. * Partition Keys: You can even change the data type of a partition key from the base table (e.g., from

STRING

DATE

) to enable more efficient partitioning in the materialized view. * Precision: Be careful when casting to types with lower precision (e.g.,

DOUBLE

INT

), as this will truncate data in the MV. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md

Lý Thành

08/20/2026, 3:44 AM

@Rocky how to crreate asyn masterial view, and how to refresh this mv

Rocky

08/20/2026, 3:44 AM

To create and manage an Asynchronous Materialized View in StarRocks, follow the steps below for creation, scheduling, and manual triggering. 1. Create an Asynchronous Materialized View When creating an async MV, you define how it should be distributed and when it should refresh. A. Scheduled Refresh (Every 1 Day)

Copy code

sql
CREATE MATERIALIZED VIEW order_mv
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC START('2025-03-18 00:00:00') EVERY (INTERVAL 1 DAY)
AS 
SELECT order_id, sum(price) as total
FROM order_list
GROUP BY order_id;

B. Manual Refresh Only If you don't want automatic schedules, set the refresh strategy to

MANUAL

Copy code

sql
CREATE MATERIALIZED VIEW manual_mv
DISTRIBUTED BY HASH(`user_id`)
REFRESH MANUAL
AS 
SELECT user_id, count(*) FROM activity_log GROUP BY user_id;

C. Immediate vs. Deferred * Default (
IMMEDIATE
): The MV starts building (refreshing) as soon as the

CREATE

statement finishes. * Deferred: Use

REFRESH DEFERRED

if you want to create the definition now but wait to populate the data later.

Copy code

sql
    CREATE MATERIALIZED VIEW deferred_mv
    REFRESH DEFERRED MANUAL
    AS SELECT ...;

────────── 2. How to Refresh the MV You can trigger a refresh manually regardless of the defined schedule. Manual Trigger (Async Call) This sends the task to the background. The command returns immediately, but the refresh continues in the background.

Copy code

sql
REFRESH MATERIALIZED VIEW order_mv;

Manual Trigger (Sync Call) The SQL client will wait until the refresh is actually finished before returning.

Copy code

sql
REFRESH MATERIALIZED VIEW order_mv WITH SYNC MODE;

Refresh Specific Partitions If your MV is partitioned, you can save resources by refreshing only specific ranges.

Copy code

sql
REFRESH MATERIALIZED VIEW order_mv PARTITION ('2025-03-17', '2025-03-18');

────────── 3. Monitoring Refresh Status Since these tasks run in the background, you can check their progress using the

information_schema

. * Check the Task Name:

Copy code

sql
    SELECT * FROM information_schema.tasks WHERE TASK_NAME LIKE '%order_mv%';

* Check Success/Failure History:

Copy code

sql
    SELECT TASK_NAME, CREATE_TIME, FINISH_TIME, STATE, ERROR_MESSAGE
    FROM information_schema.task_runs
    ORDER BY CREATE_TIME DESC
    LIMIT 10;

Key Rules * Base Tables: Async MVs can be built on StarRocks internal tables, external tables (like Hive, Iceberg), or even other MVs. * Partitioning: It is highly recommended to partition your MV if the base table is large. This allows StarRocks to perform incremental refreshes (only updating changed partitions) instead of a full rebuild. References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md

Lý Thành

08/20/2026, 3:51 AM

@Rocky DISTRIBUTED BY HASH(

card_account_no

los_contract_no

) PROPERTIES ( "compression" = "LZ4", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" ) add properties to masterial view

Rocky

08/20/2026, 3:51 AM

To create a Materialized View (MV) with specific distribution and storage settings, you include the

DISTRIBUTED BY

clause and a

PROPERTIES

block. However, not all standard table properties are valid or necessary for Materialized Views. Below is how you should structure your SQL and which properties to keep or remove. Recommended SQL Structure

Copy code

sql
CREATE MATERIALIZED VIEW your_mv_name
REFRESH ASYNC -- Or MANUAL
DISTRIBUTED BY HASH(`card_account_no`, `los_contract_no`)
PROPERTIES (
    "replication_num" = "2",
    "compression" = "LZ4",
    "replicated_storage" = "true" -- Use only if you are on a Shared-data (Cloud Native) cluster
)
AS 
SELECT 
    card_account_no, 
    los_contract_no, 
    count(*) as cnt
FROM your_source_table
GROUP BY card_account_no, los_contract_no;

Analysis of your requested properties: 1. DISTRIBUTED BY HASH(...)
: (Supported) This is standard and recommended to align the distribution with your query patterns. Note that it is a standalone clause, not inside the

PROPERTIES

block. 2. "replication_num" = "2"
: (Supported) Valid for StarRocks shared-nothing clusters. Ensure you have at least 2 Backend (BE) nodes. 3. "compression" = "LZ4"
: (Supported/Default) StarRocks uses LZ4 by default for all tables and MVs. You can explicitly set it, but it is usually unnecessary. 4. "replicated_storage" = "true"
: (Shared-data Only) Only include this if you are using StarRocks shared-data (cloud-native) architecture. For standard shared-nothing clusters, this property is not used. 5. "enable_persistent_index" = "true"
: (NOT SUPPORTED) Persistent Indexes are only for Primary Key tables. Asynchronous MVs in StarRocks are built on a Duplicate Key model (internally), so they do not support this property. Including it will likely cause an error. 6. "fast_schema_evolution" = "true"
: (NOT APPLICABLE) MVs derive their schema directly from the

SELECT

query. If the base table schema changes, the MV is typically updated via

REFRESH

ALTER MATERIALIZED VIEW ... ACTIVE

. You do not set this property on the MV itself. How to add/change properties on an EXISTING MV If the MV already exists, use `ALTER MATERIALIZED VIEW`:

Copy code

sql
ALTER MATERIALIZED VIEW your_mv_name SET ("replication_num" = "3");

References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

Lý Thành

08/20/2026, 4:01 AM

@Rocky how view refresh processs of masterial

Rocky

08/20/2026, 4:01 AM

To monitor the refresh process of an asynchronous Materialized View (MV) in StarRocks, you can use several methods depending on whether you want to see history, real-time progress, or performance metrics. 1. View Refresh History & Status (Recommended) The

information_schema

database contains logs of every refresh attempt. You can check if a refresh is

RUNNING

SUCCESS

, or

FAILED

Copy code

sql
-- View all recent refresh attempts
SELECT 
    TASK_NAME, 
    CREATE_TIME, 
    FINISH_TIME, 
    STATE,          -- PENDING, RUNNING, FAILED, SUCCESS
    PROGRESS,       -- e.g., "50%"
    ERROR_MESSAGE 
FROM information_schema.task_runs 
ORDER BY CREATE_TIME DESC 
LIMIT 10;

_Note: To filter for a specific MV, find its

TASK_NAME

by querying

SELECT _ FROM information_schema.tasks

.* 2. View Real-Time Progress If a refresh is currently running and you want to see its resource usage (CPU, Memory, Rows Scanned), use the

SHOW PROC

command:

Copy code

sql
-- Shows all currently running queries/tasks
SHOW PROC '/current_queries';

Look for an

INSERT OVERWRITE

statement in the output; this is how StarRocks performs the MV refresh internally. 3. Check MV Metadata To see the last time an MV was refreshed and whether it is currently active, use:

Copy code

sql
SHOW MATERIALIZED VIEWS WHERE NAME = 'your_mv_name' \G

Key fields to look for: * last_refresh_time
: When the last successful refresh finished. * is_active
: If

false

, the MV might be out of sync or failed a schema update. * last_refresh_error_message
: Brief summary of the most recent error. 4. Analyze Refresh Performance (Profiles) For deep troubleshooting (e.g., "Why is my refresh so slow?"), you can view the Query Profile: 1. Go to the StarRocks FE Web UI (usually

http://<FE_IP>:8030

). 2. Navigate to the Queries or Finished Queries tab. 3. Look for the

INSERT OVERWRITE

statement that corresponds to your MV refresh. 4. Click the Profile link to see a detailed breakdown of execution time across nodes. 5. Prometheus Monitoring If you have Prometheus and Grafana set up, StarRocks exports specific metrics for MVs (v3.1+): * `mv_refresh_jobs`: Total number of refresh jobs. * `mv_refresh_total_success_jobs`: Success count. * `mv_refresh_total_failed_jobs`: Failure count. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • administration/management/monitoring/metrics-materialized_view.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • administration/management/monitoring/metrics-materialized_view.md _view) References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • administration/management/monitoring/metrics-materialized_view.md

Lý Thành

08/20/2026, 7:18 AM

@Rocky CREATE TABLE DWH.ETL_DWH_LOG ( SYS_RUN_DATE DATE, SEQ_NO NUMBER, FUNCTION_CODE VARCHAR2(100), PACKAGE_NAME VARCHAR2(100), PROCEDURE_NAME VARCHAR2(100), TABLE_NAME VARCHAR2(100), DEAL_NO VARCHAR2(50), SUB_DEAL_NO VARCHAR2(50), TOTAL_RECORD NUMBER, STATUS VARCHAR2(3), RUN_TIME DATE, ERROR_DESC VARCHAR2(4000), CONSTRAINT SYS_C0079897 CHECK ("SYS_RUN_DATE" IS NOT NULL), CONSTRAINT SYS_C0079898 CHECK ("SEQ_NO" IS NOT NULL), CONSTRAINT SYS_C0079899 CHECK ("FUNCTION_CODE" IS NOT NULL), CONSTRAINT SYS_C0079900 CHECK ("RUN_TIME" IS NOT NULL) ); CREATE INDEX ETL_DWH_LOG_IDX1 ON DWH.ETL_DWH_LOG (SYS_RUN_DATE); to starrocks dup table

Rocky

08/20/2026, 7:19 AM

To convert your Oracle-style DDL to a StarRocks Duplicate Key table, you need to map the data types and use StarRocks-specific clauses for sorting, partitioning, and distribution. StarRocks Duplicate Table DDL

Copy code

sql
CREATE TABLE DWH.etl_dwh_log (
    sys_run_date    DATE NOT NULL,
    seq_no          BIGINT NOT NULL,
    function_code   VARCHAR(100) NOT NULL,
    package_name    VARCHAR(100),
    procedure_name  VARCHAR(100),
    table_name      VARCHAR(100),
    deal_no         VARCHAR(50),
    sub_deal_no     VARCHAR(50),
    total_record    BIGINT,
    status          VARCHAR(3),
    run_time        DATETIME NOT NULL,
    error_desc      VARCHAR(4000)
)
DUPLICATE KEY(sys_run_date, seq_no, function_code)
PARTITION BY RANGE(sys_run_date) (
    START ("2024-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(seq_no) BUCKETS 8
PROPERTIES (
    "replication_num" = "3",
    "compression" = "LZ4"
);

Key Mapping & Design Notes 1. Data Types: * NUMBER
: Converted to

BIGINT

. If your numbers have decimals, use

DECIMAL(precision, scale)

. * VARCHAR2
: Converted to

VARCHAR

. StarRocks

VARCHAR

supports up to 1,048,576 bytes. * DATE
(Oracle): In Oracle,

DATE

includes time. I mapped

RUN_TIME

DATETIME

to preserve the hours/minutes/seconds, and

SYS_RUN_DATE

DATE

(YYYY-MM-DD). 2. DUPLICATE KEY: * This replaces the traditional index for performance. Put the columns you filter by most often (like

sys_run_date

) first in this list. This defines the sort order on disk. 3. Constraints: * StarRocks does not support

CHECK

constraints. Use

NOT NULL

in the column definition instead. 4. Partitioning: * For log tables, it is highly recommended to use

PARTITION BY RANGE

on the date column. This makes it much faster to delete old data (dropping a partition) and improves query speed. 5. Distribution: * `DISTRIBUTED BY HASH(seq_no)`: This spreads data across the cluster nodes. Use a high-cardinality column like a sequence number or ID. 6. Index: * The

ETL_DWH_LOG_IDX1

SYS_RUN_DATE

from your original DDL is handled automatically by putting

sys_run_date

at the beginning of the

DUPLICATE KEY

clause. References • table_design/table_types/duplicate_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_types.md

Lý Thành

08/20/2026, 7:43 AM

@Rocky how to convert in starrocks like this: select CURRENT_DATE() -- 2026-08-20 --> '19Aug26'

Rocky

08/20/2026, 7:43 AM

To achieve the format

'19Aug26'

from today's date, you need to subtract one day and then use the

date_format

function with specific identifiers. The SQL Query

Copy code

sql
SELECT date_format(date_sub(current_date(), INTERVAL 1 DAY), '%d%b%y');

Breakdown of functions: 1. date_sub(current_date(), INTERVAL 1 DAY)
: Subtracts 1 day from the current date (to get the "19th" when today is the "20th"). 2. date_format(..., '%d%b%y')
: * %d
: Day of the month as a numeric value (01 to 31). * %b
: Abbreviated month name (e.g., Jan, Feb, Aug). * %y
: Year as a two-digit numeric value (e.g., 26). Alternative: If you just want to format the current date If your example "19Aug26" was just a format preference and you want to apply it to today's date directly:

Copy code

sql
SELECT date_format(current_date(), '%d%b%y'); 
-- Output: '20Aug26'

References • sql-reference/sql-functions/date-time-functions/date_format.md • sql-reference/sql-functions/date-time-functions/date_sub.md

Lý Thành

08/20/2026, 7:52 AM

@Rocky i have a table like below: CREATE TABLE dwh_link.etl_dwh_log_link ( sys_run_date datetime , table_name VARCHAR(100), total_record double, status VARCHAR(3) ) ENGINE=JDBC PROPERTIES ( "resource" = "DWH_JDBC", "table" = "DWH.etl_dwh_log" ); when i run select with WHERE d.sys_run_date = '19Aug26' is a exact string , then where send to oracle to find, like this sql: select *, CURRENT_DATE() from dwh_link.etl_dwh_log_link d WHERE d.sys_run_date = '19Aug26' But when i run sql like below: select *, CURRENT_DATE(), a.DWH_RUN_DATE from dwh_link.etl_dwh_log_link d, (SELECT date_format(date_sub(current_date(), INTERVAL 1 DAY), '%d%b%y') as DWH_RUN_DATE) a WHERE d.sys_run_date = a.DWH_RUN_DATE starrock load all data from oracle, not send where

Rocky

08/20/2026, 7:52 AM

The reason your second query loads all data from Oracle is that StarRocks treats the join with the subquery

(SELECT date_format(...))

as a Join operation rather than a simple filter. In StarRocks, JDBC external tables only push down predicates that are literal constants (like

'19Aug26'

) or expressions that can be folded into constants at the planning stage. When you use a join or a subquery to provide the value, the optimizer usually plans to fetch the data from both sides and perform the join locally in StarRocks. How to Fix It To trigger Predicate Pushdown (sending the

WHERE

clause to Oracle), you should move the date calculation directly into the

WHERE

clause of your main query. Recommended SQL:

Copy code

sql
SELECT *, CURRENT_DATE() 
FROM dwh_link.etl_dwh_log_link 
WHERE sys_run_date = date_format(date_sub(current_date(), INTERVAL 1 DAY), '%d%b%y');

Why this works: 1. Constant Folding: StarRocks evaluates

date_format(date_sub(current_date(), INTERVAL 1 DAY), '%d%b%y')

at the beginning of query execution because it is a "deterministic" expression. 2. Predicate Pushdown: Once the expression is folded into a string (e.g.,

'19Aug26'

), the optimizer sees a simple comparison:

sys_run_date = '19Aug26'

. 3. Remote Execution: StarRocks then generates the SQL

SELECT ... FROM DWH.etl_dwh_log WHERE sys_run_date = '19Aug26'

and sends it to Oracle. Troubleshooting Tips * Use EXPLAIN: Run

EXPLAIN <your_sql>

and look for the

JDBC SCAN

node. Check the

PREDICATES

section. If you see the filter there, it is being pushed to Oracle. If you see it in a

JOIN

FILTER

node above the scan, it is being processed locally in StarRocks. * JDBC Catalog (Recommended): The

ENGINE=JDBC

external table is deprecated. If you are on StarRocks v3.2.9+ or v3.3.1+, it is highly recommended to use a JDBC Catalog for Oracle. Catalogs have a more advanced optimizer that handles predicate pushdown much more efficiently than the old external tables. * Data Type Warning: Your StarRocks table defines

sys_run_date

datetime

, but you are comparing it to a string

'19Aug26'

. While Oracle might handle this via implicit conversion, it is safer to compare

datetime

to a standard ISO format (e.g.,

'2026-08-19'

) to avoid NLS setting issues in Oracle. References • data_source/External_table.md • data_source/catalog/jdbc_catalog.md • sql-reference/sql-functions/date-time-functions/date_format.md References • data_source/External_table.md • using_starrocks/accelerate-cardinality-preserving-joins.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

Lý Thành

08/20/2026, 8:20 AM

@Rocky start time: Thu Jul 2 060243 PM +07 2026, server uptime: 180243 up 202 days, 6:15, 6 users, load average: 0.47, 0.50, 0.52 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' 4.0.12 RELEASE (build 1fe715d distro centos arch x86_64) query_id:01a0012d-75f7-7c9e-a51f-010a458607d5, fragment_instance:01a0012d-75f7-7c9e-a51f-010a458607d6, plan_node_id:10 * Aborted at 1786726151 (unix time) try "date -d @1786726151" if you are using GNU date * PC: @ 0x8ff4460 starrocks::BinaryColumnBase<unsigned int>::_build_slices() const * SIGSEGV (@0xf) received by PID 1894009 (TID 0x14f7a9539640) LWP(1894365) from PID 15; stack trace: * @ 0x14f7fd48f218 __pthread_once_slow @ 0xdfdcd94 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) @ 0x14f7feac76ce PosixSignals::chained_handler(int, siginfo_t*, void*) [clone .part.0] @ 0x14f7feac81c6 JVM_handle_linux_signal @ 0x14f7fd43ebf0 (/usr/lib64/libc.so.6+0x3ebef) @ 0x8ff4460 starrocks::BinaryColumnBase<unsigned int>::_build_slices() const @ 0x8f86568 starrocks::ColumnViewer<(starrocks::LogicalType)17>::ColumnViewer(starrocks:Cow<starrocksColumn>ImmutPtr<starrocks:Column> const&) @ 0x747487f starrocks:StringFunctions:replace(starrocks::FunctionContext*, std:vector<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column>, std:allocator<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column> > > const&) @ 0x602d29a std: Function handler<starrocksStatusOr<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column> > (starrocks::FunctionContext*, std:vector<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column>, std:allocator<starrocksCow<starrocks:C@ @ 0x71c2190 starrocks:VectorizedFunctionCallExpr:evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x7150efb starrocks::VectorizedNullIfExpr<(starrocks::LogicalType)17>::evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x701c329 starrocks::VectorizedCastExpr<(starrocks::LogicalType)17, (starrocks::LogicalType)7, false>::evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x7171c1f starrocks::VectorizedCoalesceExpr<(starrocks::LogicalType)7>::evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x60f35a4 starrocks::VectorizedArithmeticExpr<(starrocks::LogicalType)7, starrocks:AddOp>:evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x602f38b starrocks:ExprContext:evaluate(starrocks::Expr*, starrocks::Chunk*, unsigned char*) @ 0x602f6cb starrocks:ExprContext:evaluate(starrocks::Chunk*, unsigned char*) @ 0xa23dcac starrocks:pipelineProjectOperator:push_chunk(starrocks::RuntimeState*, std:shared ptr<starrocks:Chunk> const&) @ 0xa2f4846 starrocks:pipelinePipelineDriver:process(starrocks::RuntimeState*, int) @ 0xa81cbe4 starrocks:pipelineGlobalDriverExecutor:_worker_thread() @ 0x8752837 starrocks:ThreadPool:dispatch_thread() @ 0x8749530 starrocks:🧵:supervise_thread(void*) @ 0x14f7fd48a002 start_thread @ 0x14f7fd50f070 __clone3 [1786726151.991][thread: 23053930305088] je_mallctl execute purge success [1786726151.991][thread: 23053930305088] je_mallctl execute dontdump success start time: Sat Aug 15 081447 AM +07 2026, server uptime: 081447 up 245 days, 20:26, 7 users, load average: 0.04, 0.02, 0.00 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' 4.0.12 RELEASE (build 1fe715d distro centos arch x86_64) query_id:01a003bd-01a1-7b79-abe7-d9dacdf1b347, fragment_instance:01a003bd-01a1-7b79-abe7-d9dacdf1b349, plan_node_id:0 * Aborted at 1786769113 (unix time) try "date -d @1786769113" if you are using GNU date * PC: @ 0x8ff4460 starrocks::BinaryColumnBase<unsigned int>::_build_slices() const * SIGSEGV (@0x3) received by PID 675314 (TID 0x15141d3b3640) LWP(675660) from PID 3; stack trace: * @ 0x1514bb88f218 __pthread_once_slow @ 0xdfdcd94 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) @ 0x1514bcec76ce PosixSignals::chained_handler(int, siginfo_t*, void*) [clone .part.0] @ 0x1514bcec81c6 JVM_handle_linux_signal @ 0x1514bb83ebf0 (/usr/lib64/libc.so.6+0x3ebef) @ 0x8ff4460 starrocks::BinaryColumnBase<unsigned int>::_build_slices() const @ 0x8f86568 starrocks::ColumnViewer<(starrocks::LogicalType)17>::ColumnViewer(starrocks:Cow<starrocksColumn>ImmutPtr<starrocks:Column> const&) @ 0x747487f starrocks:StringFunctions:replace(starrocks::FunctionContext*, std:vector<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column>, std:allocator<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column> > > const&) @ 0x602d29a std: Function handler<starrocksStatusOr<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column> > (starrocks::FunctionContext*, std:vector<starrocksCow<starrocksColumn>ImmutPtr<starrocks:Column>, std:allocator<starrocksCow<starrocks:C@ @ 0x71c2190 starrocks:VectorizedFunctionCallExpr:evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x7150efb starrocks::VectorizedNullIfExpr<(starrocks::LogicalType)17>::evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x7027a99 starrocks::VectorizedCastExpr<(starrocks::LogicalType)17, (starrocks::LogicalType)5, false>::evaluate_checked(starrocks::ExprContext*, starrocks::Chunk*) @ 0x602f38b starrocks:ExprContext:evaluate(starrocks::Expr*, starrocks::Chunk*, unsigned char*) @ 0x602f6cb starrocks:ExprContext:evaluate(starrocks::Chunk*, unsigned char*) @ 0xa23dcac starrocks:pipelineProjectOperator:push_chunk(starrocks::RuntimeState*, std:shared ptr<starrocks:Chunk> const&) @ 0xa2f4846 starrocks:pipelinePipelineDriver:process(starrocks::RuntimeState*, int) @ 0xa81cbe4 starrocks:pipelineGlobalDriverExecutor:_worker_thread() @ 0x8752837 starrocks:ThreadPool:dispatch_thread() @ 0x8749530 starrocks:🧵:supervise_thread(void*) @ 0x1514bb88a002 start_thread @ 0x1514bb90f070 __clone3 [1786769113.789][thread: 23176133948992] je_mallctl execute purge success [1786769113.789][thread: 23176133948992] je_mallctl execute dontdump success start time: Sat Aug 15 120027 PM +07 2026, server uptime: 120027 up 246 days, 12 min, 8 users, load average: 0.89, 1.14, 2.22 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Sat Aug 15 120032 PM +07 2026, server uptime: 120032 up 246 days, 12 min, 8 users, load average: 1.06, 1.17, 2.23 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse'

Rocky

08/20/2026, 8:20 AM

The stack traces you provided indicate a Backend (BE) segmentation fault in StarRocks version 4.0.12. Specifically, the crash occurs in the

replace

string function during query execution. Analysis of the Crash The crash is triggered at

starrocks::BinaryColumnBase<unsigned int>::_build_slices()

. Based on the stack trace, the sequence leading to the crash is: 1. A ProjectOperator is evaluating a complex expression. 2. The expression involves a combination of

replace()

nullif()

cast()

, and

coalesce()

. 3. The

replace

function calls

ColumnViewer

to access string data, which then attempts to build "slices" (pointers to the string data). 4. The crash happens because the underlying column data is in an unexpected state (likely due to how nulls or constants are handled when nested inside

COALESCE

CAST

in the vectorized engine). Known Issue This matches a recently reported issue in the StarRocks community: * GitHub Issue: #77858 - BE 4.0.12 crash * Status: Open / Under Investigation. Suggested Workarounds Until a formal patch is released in a newer 4.0.x maintenance version, you can try the following to mitigate the crash: 1. Simplify/Rewrite the Expression: If your query uses a pattern like

COALESCE(CAST(REPLACE(...) AS ...), ...)

NULLIF(REPLACE(...), ...)

, try to break it down. Sometimes changing the order of functions or using a

CASE WHEN

statement instead of

COALESCE/NULLIF

can bypass the specific vectorized code path that causes the crash. _ _Example:* Replace

NULLIF(REPLACE(col, 'a', 'b'), '')

with

CASE WHEN REPLACE(col, 'a', 'b') = '' THEN NULL ELSE REPLACE(col, 'a', 'b') END

. 2. Disable Specific Optimizations (If applicable): If the crash only happens during a specific join or complex projection, you can try to identify if a specific session variable affects it. However, since this is a core string function crash, a rewrite is usually more effective. 3. Blacklist the Query: If this is a recurring query that brings down your BE nodes, you can temporarily add it to the SQL blacklist to prevent it from running and crashing the cluster:

Copy code

sql
    ADD SQLBLACKLIST "regex_matching_your_query";

Recommendation I recommend tracking GitHub issue #77858 for updates. Once a fix is merged, you will likely need to upgrade to the next patch release (e.g., v4.0.13 or v4.0.14) to resolve the issue permanently. If you can provide the specific SQL query causing the crash, I can help you find a safe way to rewrite it.

Lý Thành

08/25/2026, 7:09 AM

@Rocky trim('Abc 123 43') --> ?

Rocky

08/25/2026, 7:09 AM

The result of

trim('Abc 123 43')

is: 'Abc 123 43'
Why? The

trim()

function in StarRocks only removes leading and trailing spaces. It does not remove spaces located in the middle of the string. If you want to remove all spaces (including those in the middle), you should use the

replace()

function:

Copy code

sql
SELECT replace('Abc 123 43', ' ', '');
-- Result: 'Abc12343'

References • sql-reference/sql-functions/string-functions/trim.md • sql-reference/sql-functions/string-functions/replace.md

Lý Thành

08/25/2026, 7:13 AM

@Rocky grant update all tables in database lps to user'hungnm40'@'%'; correct

Rocky

08/25/2026, 7:13 AM

The correct syntax for StarRocks to grant the

UPDATE

privilege on all tables within a specific database is:

Copy code

sql
GRANT UPDATE ON ALL TABLES IN DATABASE lps TO USER 'hungnm40'@'%';

Key Changes: 1. ON
keyword: StarRocks requires the

ON

keyword before the resource. 2. ALL TABLES IN DATABASE
: This is the specific clause required to target everything inside the

lps

database. 3. Space after
USER
: Ensure there is a space between the keyword

USER

and the identifier

'hungnm40'@'%'

. Important Note: You must ensure you have set the correct catalog (usually

default_catalog

) before running this command, or specify it if you are using multiple catalogs:

Copy code

sql
SET CATALOG default_catalog;
GRANT UPDATE ON ALL TABLES IN DATABASE lps TO USER 'hungnm40'@'%';

References • sql-reference/sql-statements/account-management/GRANT.md References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md

2 Views

Open in Slack

Previous Next