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

TRIM() can not be used with ColumnStore 1.2.x in sql_mode=oracle

Details

    Description

      One can not use TRIM() function with ColumnStore 1.2.x while sql_mode=oracle. Test case is simple:

      openxs@ao756:/usr/local/mariadb/columnstore/bin$ mcsmysql -uroot test
      		 
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      		 
      Your MariaDB connection id is 10
      		 
      Server version: 10.3.11-MariaDB-log Columnstore 1.2.2-1
      		 
       
      		 
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
      		 
       
      		 
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      		 
       
      		 
      MariaDB [test]> create table tf(file_name varchar(100), c1 int) engine=columnstore;
      		 
      Query OK, 0 rows affected (3,410 sec)
      		 
       
      		 
      MariaDB [test]> insert into tf values('file.txt   ', 1), ('file2.sql ', 2);
      		 
      Query OK, 2 rows affected (1,062 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> select * from tf where trim(file_name) = 'file.txt';
      		 
      +-------------+------+
      		 
      | file_name   | c1   |
      		 
      +-------------+------+
      		 
      | file.txt    |    1 |
      		 
      +-------------+------+
      		 
      1 row in set (0,178 sec)
      		 
       
      		 
      MariaDB [test]> select @@sql_mode;
      		 
      +-----------------------------------------------------------------------+
      		 
      | @@sql_mode                                                            |
      		 
      +-----------------------------------------------------------------------+
      		 
      | ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      		 
      +-----------------------------------------------------------------------+
      		 
      1 row in set (0,002 sec)
      		 
       
      		 
      MariaDB [test]> set session sql_mode=oracle;
      		 
      Query OK, 0 rows affected (0,000 sec)
      		 
       
      		 
      MariaDB [test]> 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,002 sec)
      		 
       
      		 
      MariaDB [test]> select * from tf where trim(file_name) = 'file.txt';
      		 
      ERROR 1815 (HY000): Internal error: IDB-1001: Function 'trim_oracle' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.
      		 
      MariaDB [test]> select * from tf where file_name like 'file.txt%';
      		 
      +-------------+------+
      		 
      | file_name   | c1   |
      		 
      +-------------+------+
      		 
      | file.txt    |    1 |
      		 
      +-------------+------+
      		 
      1 row in set (0,029 sec)

      There was no problem like this with ColumnStore 1.1.x., so it's regression of a kind.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MCOL-2124 substr functions fails with an aggregate function or with GROUP BY cluase with set oracle sql_mode

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            LinuxJedi Andrew Hutchings (Inactive) added a comment -

            Unfortunately can't make this a straight alias as trim_oracle returns NULL on empty result. We might be able to modify the internal trim function to have an extra parameter for this. We do this in a few other functions.

            LinuxJedi Andrew Hutchings (Inactive) added a comment - Unfortunately can't make this a straight alias as trim_oracle returns NULL on empty result. We might be able to modify the internal trim function to have an extra parameter for this. We do this in a few other functions.
            LinuxJedi Andrew Hutchings (Inactive) added a comment -

            Side note: there are several string functions such as ltrim/rtrim, rpad and substr that will have this problem.

            LinuxJedi Andrew Hutchings (Inactive) added a comment - Side note: there are several string functions such as ltrim/rtrim, rpad and substr that will have this problem.
            winstone Zdravelina Sokolovska (Inactive) added a comment - - edited

            recreated with TRIM(TRAILING '.' FROM TRIM()+0 functions that we use for removing not significant zeros after decimal point with oracle sql_mode

            with customer_total_return as
            		 
             (select cr_returning_customer_sk as ctr_customer_sk
            		 
                    ,ca_state as ctr_state,
            		 
                    TRIM(TRAILING '.' FROM TRIM((sum(cr_return_amt_inc_tax))))+0  as ctr_total_return
            		 
             from catalog_returns
            		 
                 ,date_dim
            		 
                 ,customer_address
            		 
             where cr_returned_date_sk = d_date_sk
            		 
               and d_year =2000
            		 
               and cr_returning_addr_sk = ca_address_sk
            		 
             group by cr_returning_customer_sk
            		 
                     ,ca_state )
            		 
              select  c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
            		 
                               ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,TRIM(TRAILING '.' FROM TRIM(ca_gmt_offset))+0 ca_gmt_offset
            		 
                              ,ca_location_type,ctr_total_return
            		 
             from customer_total_return ctr1
            		 
                 ,customer_address
            		 
                 ,customer
            		 
             where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
            		 
                                      from customer_total_return ctr2
            		 
                                      where ctr1.ctr_state = ctr2.ctr_state)
            		 
                   and ca_address_sk = c_current_addr_sk
            		 
                   and ca_state = 'GA'
            		 
                   and ctr1.ctr_customer_sk = c_customer_sk
            		 
             order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
            		 
                               ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
            		 
                              ,ca_location_type,ctr_total_return
            		 
             limit 100;
            		 
             
            		 
             
            		 
            ERROR 1178 (42000) at line 2: The storage engine for the table doesn't support IDB-1001: Function 'rtrim_oracle' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.

            winstone Zdravelina Sokolovska (Inactive) added a comment - - edited recreated with TRIM(TRAILING '.' FROM TRIM()+0 functions that we use for removing not significant zeros after decimal point with oracle sql_mode with customer_total_return as (select cr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, TRIM(TRAILING '.' FROM TRIM((sum(cr_return_amt_inc_tax))))+0 as ctr_total_return from catalog_returns ,date_dim ,customer_address where cr_returned_date_sk = d_date_sk and d_year =2000 and cr_returning_addr_sk = ca_address_sk group by cr_returning_customer_sk ,ca_state ) select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,TRIM(TRAILING '.' FROM TRIM(ca_gmt_offset))+0 ca_gmt_offset ,ca_location_type,ctr_total_return from customer_total_return ctr1 ,customer_address ,customer where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 from customer_total_return ctr2 where ctr1.ctr_state = ctr2.ctr_state) and ca_address_sk = c_current_addr_sk and ca_state = 'GA' and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset ,ca_location_type,ctr_total_return limit 100;     ERROR 1178 (42000) at line 2: The storage engine for the table doesn't support IDB-1001: Function 'rtrim_oracle' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.
            toddstoffel Todd Stoffel (Inactive) added a comment -

            This ticket was opened prior to convergence with the server. It may have been rendered obsolete. If this issue still exists in a modern version, please open a new request.

            toddstoffel Todd Stoffel (Inactive) added a comment - This ticket was opened prior to convergence with the server. It may have been rendered obsolete. If this issue still exists in a modern version, please open a new request.

            People

              Unassigned Unassigned
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.