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

    XMLWordPrintable

    Details

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

      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

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration