Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
1.2.1, 1.2.2
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
-
-
- Closed
-
Activity
Side note: there are several string functions such as ltrim/rtrim, rpad and substr that will have this problem.
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.
|
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.
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.