[MCOL-4044] Built In SQL Functions not working with sql_mode=ORACLE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 5.6.1
Component/s: None
Labels:
None

Sprint:
2021-3, 2021-4, 2021-5, 2021-6, 2021-7

Description

Some basic sql functions such as:

TRIM
CONCAT
REPLACE
ENCODE
DECODE

are not working in ColumnStore when the server is set to sql_mode=Oracle

MariaDB [(none)]> create database tests;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use tests;

Database changed

MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb;

Query OK, 0 rows affected (0.012 sec)

MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore;

Query OK, 0 rows affected (0.285 sec)

MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius');

Query OK, 1 row affected (0.003 sec)

MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius');

Query OK, 1 row affected (15.549 sec)

MariaDB [tests]> SET SESSION sql_mode=ORACLE;

Query OK, 0 rows affected (0.000 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo;

+-------------------+

| CONCAT(id,myname) |

+-------------------+

| 1Monty Widenius     |

+-------------------+

1 row in set (0.001 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar;

+-------------------+

| CONCAT(id,myname) |

+-------------------+

| 1                 |

+-------------------+

1 row in set (0.073 sec)

Attachments

Issue Links

is duplicated by

MCOL-4587 sql_mode=ORACLE specific functions do not work with ColumnStore tables

Closed

relates to

MDEV-10342 Providing compatibility for basic SQL built-in functions

Closed

Activity

Ascending order - Click to sort in descending order

Todd Stoffel (Inactive) created issue - 2020-06-05 17:23

Todd Stoffel (Inactive) made changes - 2020-06-05 17:26

Field	Original Value	New Value
Description	Some basic sql functions such as: TRIM CONCAT REPLACE DECODE are not working in ColumnStore when the server is set to sql_mode=Oracle {code:java} MariaDB [(none)]> create database tests -> ; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use tests; Database changed MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb; Query OK, 0 rows affected (0.012 sec) MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore; Query OK, 0 rows affected (0.285 sec) MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius'); Query OK, 1 row affected (0.003 sec) MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius'); Query OK, 1 row affected (15.549 sec) MariaDB [tests]> SET SESSION sql_mode=ORACLE; Query OK, 0 rows affected (0.000 sec) MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo; +-------------------+ \| CONCAT(id,myname) \| +-------------------+ \| 1Monty Widenius \| +-------------------+ 1 row in set (0.001 sec) MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar; +-------------------+ \| CONCAT(id,myname) \| +-------------------+ \| 1 \| +-------------------+ 1 row in set (0.073 sec) {code}	Some basic sql functions such as: TRIM CONCAT REPLACE DECODE are not working in ColumnStore when the server is set to sql_mode=Oracle {code:java} MariaDB [(none)]> create database tests; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use tests; Database changed MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb; Query OK, 0 rows affected (0.012 sec) MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore; Query OK, 0 rows affected (0.285 sec) MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius'); Query OK, 1 row affected (0.003 sec) MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius'); Query OK, 1 row affected (15.549 sec) MariaDB [tests]> SET SESSION sql_mode=ORACLE; Query OK, 0 rows affected (0.000 sec) MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo; +-------------------+ \| CONCAT(id,myname) \| +-------------------+ \| 1Monty Widenius \| +-------------------+ 1 row in set (0.001 sec) MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar; +-------------------+ \| CONCAT(id,myname) \| +-------------------+ \| 1 \| +-------------------+ 1 row in set (0.073 sec) {code}

Todd Stoffel (Inactive) made changes - 2020-06-05 17:29

Link

This issue relates to ~~MDEV-10342~~ [ ~~MDEV-10342~~ ]

Todd Stoffel (Inactive) made changes - 2020-06-05 17:38

Fix Version/s

1.5 [ 22800 ]

Todd Stoffel (Inactive) made changes - 2020-06-05 17:40

Description

Some basic sql functions such as:

TRIM
CONCAT
REPLACE
DECODE

are not working in ColumnStore when the server is set to sql_mode=Oracle

{code:java}
MariaDB [(none)]> create database tests;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use tests;
Database changed
MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb;
Query OK, 0 rows affected (0.012 sec)

MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore;
Query OK, 0 rows affected (0.285 sec)

MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius');
Query OK, 1 row affected (0.003 sec)

MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius');
Query OK, 1 row affected (15.549 sec)

MariaDB [tests]> SET SESSION sql_mode=ORACLE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1Monty Widenius |
+-------------------+
1 row in set (0.001 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.073 sec)
{code}

Some basic sql functions such as:

TRIM
CONCAT
REPLACE
DECODE

are not working in ColumnStore when the server is set to sql_mode=Oracle

{code:java}
MariaDB [(none)]> create database tests;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use tests;
Database changed

MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb;
Query OK, 0 rows affected (0.012 sec)

MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore;
Query OK, 0 rows affected (0.285 sec)

MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius');
Query OK, 1 row affected (0.003 sec)

MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius');
Query OK, 1 row affected (15.549 sec)

MariaDB [tests]> SET SESSION sql_mode=ORACLE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1Monty Widenius |
+-------------------+
1 row in set (0.001 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.073 sec)
{code}

Todd Stoffel (Inactive) made changes - 2020-06-05 17:41

Assignee

Todd Stoffel [ toddstoffel ]

Todd Stoffel (Inactive) made changes - 2020-06-05 17:41

Assignee

Todd Stoffel [ toddstoffel ]

David Hall [ david.hall ]

Todd Stoffel (Inactive) made changes - 2020-06-25 16:59

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-08-27 01:27

Status

Open [ 1 ]

Confirmed [ 10101 ]

Todd Stoffel (Inactive) made changes - 2020-09-01 00:09

Fix Version/s		1.5.5 [ 24414 ]
Fix Version/s	1.5 [ 22800 ]

Todd Stoffel (Inactive) made changes - 2020-09-01 01:15

Priority

Major [ 3 ]

Critical [ 2 ]

Todd Stoffel (Inactive) made changes - 2020-09-21 23:42

Fix Version/s		5.5.1 [ 25030 ]
Fix Version/s		5.5.1 [ 25030 ]
Fix Version/s	1.5.5 [ 24414 ]

Todd Stoffel (Inactive) made changes - 2020-10-07 02:02

Rank

Ranked higher

David Hall (Inactive) made changes - 2020-11-04 17:18

Description

Some basic sql functions such as:

TRIM
CONCAT
REPLACE
DECODE

are not working in ColumnStore when the server is set to sql_mode=Oracle

{code:java}
MariaDB [(none)]> create database tests;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use tests;
Database changed

MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb;
Query OK, 0 rows affected (0.012 sec)

MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore;
Query OK, 0 rows affected (0.285 sec)

MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius');
Query OK, 1 row affected (0.003 sec)

MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius');
Query OK, 1 row affected (15.549 sec)

MariaDB [tests]> SET SESSION sql_mode=ORACLE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1Monty Widenius |
+-------------------+
1 row in set (0.001 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.073 sec)
{code}

Some basic sql functions such as:

TRIM
CONCAT
REPLACE
ENCODE
DECODE

are not working in ColumnStore when the server is set to sql_mode=Oracle

{code:java}
MariaDB [(none)]> create database tests;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use tests;
Database changed

MariaDB [tests]> create table foo (id INT, myname varchar(30))engine = innodb;
Query OK, 0 rows affected (0.012 sec)

MariaDB [tests]> create table bar (id INT, myname varchar(30))engine = columnstore;
Query OK, 0 rows affected (0.285 sec)

MariaDB [tests]> INSERT INTO foo VALUES (1,'Monty Widenius');
Query OK, 1 row affected (0.003 sec)

MariaDB [tests]> INSERT INTO bar VALUES (1,'Monty Widenius');
Query OK, 1 row affected (15.549 sec)

MariaDB [tests]> SET SESSION sql_mode=ORACLE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM foo;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1Monty Widenius |
+-------------------+
1 row in set (0.001 sec)

MariaDB [tests]> SELECT CONCAT(id,myname) FROM bar;
+-------------------+
| CONCAT(id,myname) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.073 sec)
{code}

David Hall (Inactive) made changes - 2020-11-04 17:19

Assignee

David Hall [ david.hall ]

Ben Thompson [ ben.thompson ]

David Hall (Inactive) made changes - 2020-11-04 17:21

Priority

Critical [ 2 ]

Blocker [ 1 ]

David Hall (Inactive) made changes - 2020-11-09 17:08

Priority

Blocker [ 1 ]

Major [ 3 ]

Todd Stoffel (Inactive) made changes - 2020-11-12 03:14

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-11-16 21:24

Rank

Ranked lower

Todd Stoffel (Inactive) made changes - 2020-11-16 21:27

Rank

Ranked higher

David Hall (Inactive) made changes - 2020-12-03 21:12

Fix Version/s		6.1 [ 25201 ]
Fix Version/s	5.5.1 [ 25030 ]

David Hall (Inactive) made changes - 2020-12-03 21:12

Fix Version/s

5.6.1 [ 25031 ]

Todd Stoffel (Inactive) made changes - 2020-12-09 08:29

Fix Version/s

6.1 [ 25201 ]

Todd Stoffel (Inactive) made changes - 2021-01-25 17:54

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-02-22 15:10

Sprint

2021-3 [ 498 ]

Gregory Dorman (Inactive) made changes - 2021-02-22 15:10

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-02-22 17:19

Sprint

2021-3 [ 498 ]

2021-3, 2021-4 [ 498, 499 ]

Todd Stoffel (Inactive) made changes - 2021-02-22 17:21

Rank

Ranked higher

Ben Thompson (Inactive) made changes - 2021-02-24 18:32

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2021-03-05 18:57

Link

This issue is duplicated by ~~MCOL-4587~~ [ ~~MCOL-4587~~ ]

Gregory Dorman (Inactive) made changes - 2021-03-08 15:41

Rank

Ranked lower

Todd Stoffel (Inactive) made changes - 2021-03-08 17:03

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-03-08 17:09

Sprint

2021-3, 2021-4 [ 498, 499 ]

2021-3, 2021-4, 2021-5 [ 498, 499, 504 ]

Gregory Dorman (Inactive) made changes - 2021-04-05 16:03

Sprint

2021-3, 2021-4, 2021-5 [ 498, 499, 504 ]

2021-3, 2021-4, 2021-5, 2021-6 [ 498, 499, 504, 509 ]

Todd Stoffel (Inactive) made changes - 2021-04-05 16:57

Rank

Ranked higher

Gregory Dorman (Inactive) made changes - 2021-04-19 16:04

Sprint

2021-3, 2021-4, 2021-5, 2021-6 [ 498, 499, 504, 509 ]

2021-3, 2021-4, 2021-5, 2021-6, 2021-7 [ 498, 499, 504, 509, 514 ]

Ben Thompson (Inactive) made changes - 2021-04-29 19:49

Assignee	Ben Thompson [ ben.thompson ]	David Hall [ david.hall ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Gregory Dorman (Inactive) made changes - 2021-04-29 21:36

Fix Version/s		6.1.1 [ 25600 ]
Fix Version/s	5.6.1 [ 25031 ]

David Hall (Inactive) made changes - 2021-04-30 15:14

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Gregory Dorman (Inactive) made changes - 2021-04-30 15:19

Fix Version/s		5.6.1 [ 25031 ]
Fix Version/s	6.1.1 [ 25600 ]

David Hall (Inactive) made changes - 2021-04-30 15:25

Assignee

David Hall [ david.hall ]

Daniel Lee [ dleeyh ]

Daniel Lee (Inactive) added a comment - 2021-05-05 15:17

Build verified: 5.6.1 ( Drone #2325 )

Verified all mentioned functions return same result as InnoDB.

Daniel Lee (Inactive) added a comment - 2021-05-05 15:17 Build verified: 5.6.1 ( Drone #2325 ) Verified all mentioned functions return same result as InnoDB.

Daniel Lee (Inactive) made changes - 2021-05-05 15:17

Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Jira Automation (IT) made changes - 2024-07-06 14:35

Zendesk Related Tickets

122588 169063 116013

People

Assignee:: Daniel Lee (Inactive)

Reporter:: Todd Stoffel (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2020-06-05 17:23

Updated:: 2024-07-08 02:01

Resolved:: 2021-05-05 15:17

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB ColumnStore

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration