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

substr functions fails with an aggregate function or with GROUP BY cluase with set oracle sql_mode

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: Icebox
    • Component/s: ExeMgr
    • Labels:
      None

      Description

      substr functions fails with an aggregate function or with GROUP BY cluase with set oracle sql_mode

      note: not observed with dev v1.1.7-1

      set sql_mode=oracle and run the queries

      MariaDB [(none)]> select @@sql_mode;
      ----------------------------------------------------------------------------------------------------------------------------------------------

      @@sql_mode

      ----------------------------------------------------------------------------------------------------------------------------------------------

      PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT

      ----------------------------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.001 sec)

      #  mcsmysql -u root -p1 tpcds_1<<rr
      > select  ca_zip
      >        ,sum(cs_sales_price)
      >  from catalog_sales
      >      ,customer
      >      ,customer_address
      >      ,date_dim
      >  where cs_bill_customer_sk = c_customer_sk
      >         and c_current_addr_sk = ca_address_sk
      >         and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
      >                                    '85392', '85460', '80348', '81792')
      >               or ca_state in ('CA','WA','GA')
      >               or cs_sales_price > 500)
      >         and cs_sold_date_sk = d_date_sk
      >         and d_qoy = 2 and d_year = 2001
      >  group by ca_zip
      >  order by ca_zip
      >  limit 100;
      > rr
      ERROR 1815 (HY000) at line 1: Internal error: IDB-1001: Function 'substr_oracle' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.
      [root@columnstore validate_mdb_query_templates]#
      
      

      select
         substr(w_warehouse_name,1,20)
        ,sm_type
        ,cc_name
        ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end)  as "30 days"
        ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and
                       (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end )  as "31-60 days"
        ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and
                       (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end)   as "61-90 days"
        ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and
                       (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end)  as "91-120 days"
        ,sum(case when (cs_ship_date_sk - cs_sold_date_sk  > 120) then 1 else 0 end)  as ">120 days"
      from
         catalog_sales
        ,warehouse
        ,ship_mode
        ,call_center
        ,date_dim
      where
          d_month_seq between 1200 and 1200 + 11
      and cs_ship_date_sk   = d_date_sk
      and cs_warehouse_sk   = w_warehouse_sk
      and cs_ship_mode_sk   = sm_ship_mode_sk
      and cs_call_center_sk = cc_call_center_sk
      group by
         substr(w_warehouse_name,1,20)
        ,sm_type
        ,cc_name
      order by substr(w_warehouse_name,1,20)
              ,sm_type
              ,binary cc_name
      limit 100;
       
       
      ERROR 1178 (42000) at line 2: The storage engine for the table doesn't support IDB-2016: Non supported item 'substr(w_warehouse_name,1,20)' on the GROUP BY list.
       
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              winstone Zdravelina Sokolovska (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration