Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.6.4, 10.6.5, 10.6, 10.7(EOL)
-
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
- duplicates
-
-
- Closed
-