Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27073

Crash when a date conversion function is the return value of DECODE() in a virtual column

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6, 10.7, 10.6.4, 10.6.5
    • Fix Version/s: 10.6, 10.7
    • Component/s: Virtual Columns
    • Labels:
      None

      Description

      In Oracle compatibility mode it is possible to create a virtual column which value is computed by calling the DECODE() function that returns the value from a real column from the same table.

      However, if the return value of DECODE() contains a date conversion function (that still uses a real column as a source), selecting the data immediately after it has been inserted results in a crash.

      If the tables are flushed, or if server get restarted before the SELECT query, data is returned properly.

      If the date conversion function is first put into another derived column, it seems the crash is also avoided.

      Tested on both latest ES 10.6.4 and Community 10.6.5.

      All examples below require SQL_MODE='oracle':

      – Example 1: crash in SELECT right after the INSERT

      CREATE TABLE uld11 (
      	schedule_date DATE NOT NULL,
      	hold_position VARCHAR(5),
      	uld_type VARCHAR(3),
      	hld_unique_vcol VARCHAR(160) AS 
      		(DECODE 
      			(uld_type, 'HLD', 
      				SUBSTR(TO_CHAR(schedule_date,'mmddyyyy')||hold_position,1,10), NULL
      			)
      		)
      );
       
      INSERT INTO uld11 (SCHEDULE_DATE) VALUES ('2011-06-20');
       
      MariaDB [test]> select * from uld11;
      ERROR 2013 (HY000): Lost connection to server during query
      

      – Example 2: after the server is restarted or FLUSH TABLES is ran, the same query returns proper result:

      MariaDB [test]> select * from uld11;
      +---------------------+---------------+----------+-----------------+
      | schedule_date       | hold_position | uld_type | hld_unique_vcol |
      +---------------------+---------------+----------+-----------------+
      | 2011-06-20 00:00:00 | NULL          | NULL     | NULL            |
      +---------------------+---------------+----------+-----------------+
      1 row in set (0.001 sec)
      

      – Example 3: moving the date function into another virtual column "tmp1" seems to avoid the crash:

      CREATE TABLE uld12 (
      	schedule_date DATE NOT NULL,
      	hold_position VARCHAR(5),
      	uld_type VARCHAR(3),
      	tmp1  VARCHAR(160) AS (SUBSTR(TO_CHAR(schedule_date,'mmddyyyy')||hold_position,1,10)),
      	hld_unique_vcol VARCHAR(160) AS 
      		(DECODE (uld_type, 'HLD', tmp1, NULL))
      );
       
      INSERT INTO uld12 (SCHEDULE_DATE) VALUES ('2011-06-20');
       
      MariaDB [test]> select * from uld12;
      +---------------------+---------------+----------+----------+-----------------+
      | schedule_date       | hold_position | uld_type | tmp1     | hld_unique_vcol |
      +---------------------+---------------+----------+----------+-----------------+
      | 2011-06-20 00:00:00 | NULL          | NULL     | 06202011 | NULL            |
      +---------------------+---------------+----------+----------+-----------------+
      1 row in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nikitamalyavin Nikita Malyavin
              Reporter:
              assen.totin Assen Totin
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration