[MCOL-2116] TRIM() can not be used with ColumnStore 1.2.x in sql_mode=oracle Created: 2019-01-28  Updated: 2023-03-06  Resolved: 2023-03-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: 1.2.1, 1.2.2
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: oracle, sql_mode

Issue Links:
Relates
relates to MCOL-2124 substr functions fails with an aggre... Closed
Epic Link: ColumnStore Compatibility Improvements

 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.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2019-01-28 ]

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.

Comment by Andrew Hutchings (Inactive) [ 2019-01-28 ]

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

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-29 ]

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.

Comment by Todd Stoffel (Inactive) [ 2023-03-06 ]

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.

Generated at Thu Feb 08 02:33:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.