Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4410

SELECT bypasses select handler if @variables are involved in the query projection list

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.4.4
    • Fix Version/s: 5.6.1
    • Component/s: None
    • Labels:
      None
    • Environment:
      SkySQL CentOS 7.8
    • Sprint:
      2021-7

      Description

      --------------
      SET @asOfDate='2020-05-21',@asOfDateOffsetMeta='2020-05-21'
      --------------
      Query OK, 0 rows affected (0.000 sec)
      --------------
      EXPLAIN SELECT 
                  ti.type_id AS ti_type_id, ti.sub_type_id AS ti_sub_type_id,
                  th.id AS hdr_id,
                  ti.id AS ti_id,
                  IF(IFNULL(lf.id,0) >0,CONCAT(lf.id,'-',tip.paid_type_id,'-',paid_sub_type.id),CONCAT(th.id,'-',paid_type.id,'-',IFNULL(paid_sub_type.id,0))) AS lf_id,
                  sl.local_name AS lm_name,
                  IF(tip.paid_type_id IN (29,51),'Prin',
                      IF(paid_sub_type.id = 126,'Lien Fee',IF(lf.id IS NULL,paid_type.sub_type,CASE WHEN paid_sub_type.id = 271 AND tip.paid_type_id IN (31,32) THEN 'Pawn Fee' WHEN paid_sub_type.id = 282 AND tip.paid_type_id = 32 THEN 'Expired Fee' END))) AS fee_name,
                  (CASE WHEN paid_sub_type.id = 282 AND tip.paid_type_id = 32 THEN 'Expired Fee' ELSE paid_sub_type.type END) AS sub_type,
                  lf.last_earn_date,
                  lf.fee_apr,
                  IF( tip.paid_sub_type_id IN (214,215,230),0.0000, tip.paid_amt) AS paid_amt,
                  0 AS accrued_amt,
                  (
                      IF(ti.bus_date=@asOfDate AND ti_type.ttl_oper>0,tip.paid_amt,0)
                      *    
                      IF( tip.paid_sub_type_id IN (214,215,230,213,282,145,144,143),0.0000, 1)
                  ) AS applied_amt,
                  IF(ti.bus_date=@asOfDateOffsetMeta AND ti_type.ttl_oper<0,tip.paid_amt,0) AS pmts_applied,
                  th.effective_due_date AS due_date,
                  DATEDIFF(@asOfDate,th.effective_due_date) AS days_late,
                  IF(ti.bus_date=@asOfDateOffsetMeta AND ti_type.ttl_oper<0,ti.over_pmt_amt,0) AS over_pmt_amt,
                  0 AS billed_amt, 
                  0 AS unbilled_amt, 
                  0 AS credit_limit_amt,
                  0 AS available_bal,
                  0 AS average_bal
              FROM 
      cs_trans_item ti 
      INNER JOIN tmp_tb_ldh_GPawnv3 th ON ti.hdr_id=th.id
              INNER JOIN cs_trans_item_type ti_type ON ti.type_id=ti_type.id
              INNER JOIN cs_store_loan sl ON th.loan_model_id=sl.id
              INNER JOIN cs_trans_status ts ON th.status_id=ts.id
              INNER JOIN cs_trans_item_paid tip ON ti.id=tip.trans_id
              INNER JOIN cs_trans_item_type paid_type ON tip.paid_type_id=paid_type.id
              LEFT JOIN cs_loan_fee lf ON tip.paid_lf_id=lf.id
              LEFT JOIN cs_store_loan_fee slf ON lf.store_fee_id=slf.id
              LEFT JOIN cs_trans_item_sub_type AS paid_sub_type ON tip.paid_sub_type_id=paid_sub_type.id
              WHERE ti.is_void=0 
              AND ti.bus_date BETWEEN '2020-08-01' AND '2020-09-21'  
              AND ti_type.ttl_oper<>0
      --------------
      +------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
      | id   | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra                                                                 |
      +------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
      |    1 | SIMPLE      | ti            | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where with pushed condition                                     |
      |    1 | SIMPLE      | th            | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join)                       |
      |    1 | SIMPLE      | ti_type       | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where with pushed condition; Using join buffer (flat, BNL join) |
      |    1 | SIMPLE      | sl            | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join)                       |
      |    1 | SIMPLE      | ts            | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join)                       |
      |    1 | SIMPLE      | tip           | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join)                       |
      |    1 | SIMPLE      | paid_type     | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join)                       |
      |    1 | SIMPLE      | lf            | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where                                                           |
      |    1 | SIMPLE      | slf           | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where                                                           |
      |    1 | SIMPLE      | paid_sub_type | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where                                                           |
      +------+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------+
      10 rows in set (0.060 sec)
      

        Attachments

          Activity

            People

            Assignee:
            dleeyh Daniel Lee
            Reporter:
            Coker Todd Coker
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration