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

cut off string from union with sql-mode oracle

    XMLWordPrintable

    Details

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

      Description

      cut off string from union with sql-mode oracle

      set sql-mode=oracle
      and execute the query

       set infinidb_use_decimal_scale=1;
       set infinidb_decimal_scale=9 ;
       
      select channel, item, TRIM(TRAILING '.' FROM TRIM(return_ratio))+0 as return_ratio, return_rank, currency_rank from
       (select
       'web' as channel
       ,web.item
       ,web.return_ratio
       ,web.return_rank
       ,web.currency_rank
       from (
              select
               item
              ,return_ratio
              ,currency_ratio
              ,rank() over (order by return_ratio) as return_rank
              ,rank() over (order by currency_ratio) as currency_rank
              from
              (       select ws.ws_item_sk as item
                      ,(cast(sum(coalesce(wr.wr_return_quantity,0)) as decimal(15,4))/
                      cast(sum(coalesce(ws.ws_quantity,0)) as decimal(15,4) )) as return_ratio
                      ,(cast(sum(coalesce(wr.wr_return_amt,0)) as decimal(15,4))/
                      cast(sum(coalesce(ws.ws_net_paid,0)) as decimal(15,4) )) as currency_ratio
                      from
                       web_sales ws left outer join web_returns wr
                              on (ws.ws_order_number = wr.wr_order_number and
                              ws.ws_item_sk = wr.wr_item_sk)
                       ,date_dim
                      where
                              wr.wr_return_amt > 10000
                              and ws.ws_net_profit > 1
                               and ws.ws_net_paid > 0
                               and ws.ws_quantity > 0
                               and ws_sold_date_sk = d_date_sk
                               and d_year = 2001
                               and d_moy = 12
                      group by ws.ws_item_sk
              ) in_web
       ) web
       where
       (
       web.return_rank <= 10
       or
       web.currency_rank <= 10
       )
       union
       select
       'catalog' as channel
       ,catalog.item
       ,catalog.return_ratio
       ,catalog.return_rank
       ,catalog.currency_rank
       from (
              select
               item
              ,return_ratio
              ,currency_ratio
              ,rank() over (order by return_ratio) as return_rank
              ,rank() over (order by currency_ratio) as currency_rank
              from
              (       select
                      cs.cs_item_sk as item
                      ,(cast(sum(coalesce(cr.cr_return_quantity,0)) as decimal(15,4))/
                      cast(sum(coalesce(cs.cs_quantity,0)) as decimal(15,4) )) as return_ratio
                      ,(cast(sum(coalesce(cr.cr_return_amount,0)) as decimal(15,4))/
                      cast(sum(coalesce(cs.cs_net_paid,0)) as decimal(15,4) )) as currency_ratio
                      from
                      catalog_sales cs left outer join catalog_returns cr
                              on (cs.cs_order_number = cr.cr_order_number and
                              cs.cs_item_sk = cr.cr_item_sk)
                      ,date_dim
                      where
                              cr.cr_return_amount > 10000
                              and cs.cs_net_profit > 1
                               and cs.cs_net_paid > 0
                               and cs.cs_quantity > 0
                               and cs_sold_date_sk = d_date_sk
                               and d_year = 2001
                               and d_moy = 12
                       group by cs.cs_item_sk
              ) in_cat
       ) catalog
       where
       (
       catalog.return_rank <= 10
       or
       catalog.currency_rank <=10
       )
       union
       select
       'store' as channel
       ,store.item
       ,store.return_ratio
       ,store.return_rank
       ,store.currency_rank
       from (
              select
               item
              ,return_ratio
              ,currency_ratio
              ,rank() over (order by return_ratio) as return_rank
              ,rank() over (order by currency_ratio) as currency_rank
              from
              (       select sts.ss_item_sk as item
                      ,(cast(sum(coalesce(sr.sr_return_quantity,0)) as decimal(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) as decimal(15,4) )) as return_ratio
                      ,(cast(sum(coalesce(sr.sr_return_amt,0)) as decimal(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) as decimal(15,4) )) as currency_ratio
                      from
                      store_sales sts left outer join store_returns sr
                              on (sts.ss_ticket_number = sr.sr_ticket_number and sts.ss_item_sk = sr.sr_item_sk)
                      ,date_dim
                      where
                              sr.sr_return_amt > 10000
                              and sts.ss_net_profit > 1
                               and sts.ss_net_paid > 0
                               and sts.ss_quantity > 0
                               and ss_sold_date_sk = d_date_sk
                               and d_year = 2001
                               and d_moy = 12
                      group by sts.ss_item_sk
              ) in_store
       ) store
       where  (
       store.return_rank <= 10
       or
       store.currency_rank <= 10
       )
       )f
       order by 1,4,5,2
       limit 100;
      
      

      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)
      
      

      query returns cut off string in the channel columns

      channel item    return_ratio    return_rank     currency_rank
      cat     17543   0.571428571     1       1
      cat     14513   0.635416667     2       2
      cat     12577   0.655913978     3       3
      cat     3411    0.71641791      4       4
      cat     361     0.746478873     5       5
      cat     8189    0.746987952     6       6
      cat     8929    0.7625  7       7
      cat     14869   0.77173913      8       8
      cat     9295    0.778947368     9       9
      cat     16215   0.790697674     10      10
      sto     9471    0.775   1       1
      sto     9797    0.8     2       2
      sto     12641   0.816091954     3       3
      sto     15839   0.816326531     4       4
      sto     1171    0.824175824     5       5
      sto     11589   0.826530612     6       6
      sto     6661    0.922077922     7       7
      sto     13013   0.942028986     8       8
      sto     14925   0.964705882     9       9
      sto     4063    1       10      10
      sto     9029    1       10      10
      web     7539    0.59    1       1
      web     3337    0.626506024     2       2
      web     15597   0.661971831     3       3
      web     2915    0.698630137     4       4
      web     11933   0.717171717     5       5
      web     3305    0.7375  6       16
      web     483     0.8     7       6
      web     85      0.857142857     8       7
      web     97      0.903614458     9       8
      web     117     0.925   10      9
      web     5299    0.927083333     11      10
      
      

      when set infinidb_vtable_mode = 0 with sql-mode oracle
      or with infinidb_vtable_mode = 1 and w/o oracle mode
      the longest string is displayed

      channel item    return_ratio    return_rank     currency_rank
      catalog 17543   0.57142857      1       1
      catalog 14513   0.63541667      2       2
      catalog 12577   0.65591398      3       3
      catalog 3411    0.71641791      4       4
      catalog 361     0.74647887      5       5
      catalog 8189    0.74698795      6       6
      catalog 8929    0.7625  7       7
      catalog 14869   0.77173913      8       8
      catalog 9295    0.77894737      9       9
      catalog 16215   0.79069767      10      10
      store   9471    0.775   1       1
      store   9797    0.8     2       2
      store   12641   0.81609195      3       3
      store   15839   0.81632653      4       4
      store   1171    0.82417582      5       5
      store   11589   0.82653061      6       6
      store   6661    0.92207792      7       7
      store   13013   0.94202899      8       8
      store   14925   0.96470588      9       9
      store   4063    1       10      10
      store   9029    1       10      10
      web     7539    0.59    1       1
      web     3337    0.62650602      2       2
      web     15597   0.66197183      3       3
      web     2915    0.69863014      4       4
      web     11933   0.71717172      5       5
      web     3305    0.7375  6       16
      web     483     0.8     7       6
      web     85      0.85714286      8       7
      web     97      0.90361446      9       8
      web     117     0.925   10      9
      web     5299    0.92708333      11      10
      
      

        Attachments

          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