Help Center/ Data Warehouse Service / Developer Guide/ Developer Guide (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: "Row () Expression" Can Not Be Shipped
Updated on 2026-01-04 GMT+08:00
View PDF
Share

Case: "Row () Expression" Can Not Be Shipped

Possible Cause

The statement includes a row expression, typically shown as "(...)"in the targetlist( between SELECT and FROM).

Solution: Rewrite the SQL statement to eliminate the row expression.

Case 1: Using Row Expression (k4.businessID, arriveDate) in the targetlist

Original statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT
    date_format(arriveDate, '%Y-%m-%d') AS "date",
    sales,
    COUNT(DISTINCT (k4.businessID, arriveDate)/*row expression*/)
FROM (
    SELECT k1.businessID, k2.arriveDate, k1.sales
    FROM potential_customers k1
    LEFT JOIN flow_reception k2
        ON k1.businessID = k2.businessID
    WHERE k2.arriveDate BETWEEN '2024-08-31' AND '2024-09-02'
) k4
GROUP BY arriveDate, sales;

Solution: Do not output the row expression directly. Instead, rewrite the row expression as a character string.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT
    date_format(arriveDate, '%Y-%m-%d') AS "date",
    sales,
    COUNT(DISTINCT k4.businessID ||'-' ||  arriveDate/*Eliminate row expressions*/)
FROM (
    SELECT k1.businessID, k2.arriveDate, k1.sales
    FROM potential_customers k1
    LEFT JOIN flow_reception k2
        ON k1.businessID = k2.businessID
    WHERE k2.arriveDate BETWEEN '2024-08-31' AND '2024-09-02'
) k4
GROUP BY arriveDate, sales;

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down