Improve user-defined variables to support Looker pivoting features

[nferrario]

MySQL uses := for defining variables in SELECT and UPDATE statements rather than =. StarRocks doesn't support this operator and it causes issues with Looker's Pivoting features.

MySQL Docs: https://dev.mysql.com/doc/refman/8.4/en/assignment-operators.html

This is an example query that Looker tries to execute.

SELECT zz1.* FROM (
        SELECT
           @min_rank := IF(!_ct2, _rank, @min_rank) as m_rank
           , zz.*
        FROM (
        SELECT
           (@curType1 IS NOT NULL AND @curType1 = CAST(COALESCE(`performance.mm_date`,'') AS CHAR)) as _ct2
          , @curType1 := CAST(COALESCE(`performance.mm_date`,'') AS CHAR) as _ct1
          , xx.*
        FROM (
            SELECT * FROM (
               SELECT
                     (
                        CASE CAST(COALESCE(`campaigns.frequency_type`,'') AS CHAR)
                        WHEN @curType
                        THEN @curRow := @curRow + 1
                        ELSE @curRow := 1 AND @curType := CAST(COALESCE(`campaigns.frequency_type`,'') AS CHAR) END
                      ) AS _rank,
                      ww.*
               FROM (
SELECT
    `campaigns`.`frequency_type` AS `campaigns.frequency_type`,
        (DATE(`performance`.`mm_date`)) AS `performance.mm_date`,
    COALESCE(SUM(`performance`.`impressions`), 0) AS `performance.total_impressions`
FROM
    `legacy`.`performance` AS `performance`
    LEFT JOIN `changes`.`campaigns` AS `campaigns` ON `performance`.`campaign_id` = `campaigns`.`id`
WHERE ((( `performance`.`mm_date` ) >= ((DATE_ADD(CURDATE(),INTERVAL -29 day))) AND ( `performance`.`mm_date` ) < ((DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -29 day),INTERVAL 30 day)))))
GROUP BY
    1,
    2        ) as ww
        , (SELECT @min_rank := 1000000, @curType1 := NULL, @curRow :=0, @curType := '') r
ORDER BY `campaigns.frequency_type`, `performance.mm_date` DESC
        ) xx1
        ORDER BY `performance.mm_date`, _rank
        ) as xx
        ORDER BY `performance.mm_date`, _rank, _ct2
        ) as zz
        ORDER BY `performance.mm_date`, _rank, _ct2
        ) as zz1
WHERE m_rank <= 500 LIMIT 30000

StarRocks error:

Getting syntax error at line 4, column 21. Detail message: Unexpected input ':', the most similar input is {')'}.

StarRocks version: 3.3.1

[wangsimo0]

hi thanks for your detailed description and example. You are using MySQL connector to connect starrocks from looker right?

[nferrario]

Hi @wangsimo0, that's right. The same error occurs from any MySQL client, it's not specific to Looker