maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #11153
Re: MDEV 10754 - Patch for trim functions
Hello Jerome,
Thank you for the contribution.
The patch looks fine. I have only some proposals.
1. We don't use bb-10.2-ext any more. Please use 10.3 instead.
2. You mapped "TRIM_ORACLE" to SYM(TRIM).
I guess you wanted to avoid grammar duplication
for "TRIM" and "TRIM_ORACLE".
It's good to avoid grammar duplication.
However, this way introduces some problems:
- using yyval directly won't work in all cases
- "TRIM_ORACLE" becomes a reserved word.
It must be non-reserved.
So I propose to do the same thing in a different way:
- Let's introduce a new rule "trim_operands"
and a new structure Lex_trim_st to store TRIM operands.
- Let's have a separate token TRIM_ORACLE
3. Create_func_trim_oracle is not really needed.
Please review a modified patch attached.
Thanks!
On 03/15/2018 11:22 AM, jerome brauge wrote:
> Hello Alexander,
>
> Can you review this patch for trim functions for Oracle ?
>
>
>
> Regards,
>
> Jérôme.
>
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index f197fbe..5141fa2 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -1328,3 +1328,8 @@ CREATE TABLE raw (raw int);
DROP TABLE raw;
CREATE TABLE varchar2 (varchar2 int);
DROP TABLE varchar2;
+#
+# MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+#
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;
diff --git a/mysql-test/suite/compat/oracle/r/func_trim.result b/mysql-test/suite/compat/oracle/r/func_trim.result
new file mode 100644
index 0000000..bed8dad
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_trim.result
@@ -0,0 +1,170 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+#
+SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM(' '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
+TRIM('abc') TRIM('abc ')||'.' '.'||TRIM(' abc ')||'.' TRIM(' ') TRIM(NULL) TRIM(SPACE(0)) TRIM(SPACE(10))
+abc abc. .abc. NULL NULL NULL NULL
+SELECT TRIM(TRAILING 'abc' FROM 'abc');
+TRIM(TRAILING 'abc' FROM 'abc')
+NULL
+SELECT TRIM(TRAILING 'abc' FROM 'abc ');
+TRIM(TRAILING 'abc' FROM 'abc ')
+abc
+SELECT TRIM(TRAILING 'abc' FROM ' abc');
+TRIM(TRAILING 'abc' FROM ' abc')
+
+SELECT TRIM(LEADING 'abc' FROM 'abc');
+TRIM(LEADING 'abc' FROM 'abc')
+NULL
+SELECT TRIM(LEADING 'abc' FROM 'abc ');
+TRIM(LEADING 'abc' FROM 'abc ')
+
+SELECT TRIM(LEADING 'abc' FROM ' abc');
+TRIM(LEADING 'abc' FROM ' abc')
+ abc
+SELECT TRIM(BOTH 'abc' FROM 'abc');
+TRIM(BOTH 'abc' FROM 'abc')
+NULL
+SELECT TRIM(BOTH 'abc' FROM 'abc ');
+TRIM(BOTH 'abc' FROM 'abc ')
+
+SELECT TRIM(BOTH 'abc' FROM ' abc');
+TRIM(BOTH 'abc' FROM ' abc')
+
+SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM(' '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
+RTRIM('abc') RTRIM('abc ')||'.' RTRIM(' abc ')||'.' RTRIM(' ') RTRIM(NULL) RTRIM(SPACE(0)) RTRIM(SPACE(10))
+abc abc. abc. NULL NULL NULL NULL
+SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM(' '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
+LTRIM('abc') LTRIM('abc ') LTRIM(' abc ') LTRIM(' ') LTRIM(NULL) LTRIM(SPACE(0)) LTRIM(SPACE(10))
+abc abc abc NULL NULL NULL NULL
+CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
+INSERT INTO t1 VALUES ('abc',1);
+INSERT INTO t1 VALUES (SPACE(0),2);
+INSERT INTO t1 VALUES ('',3);
+INSERT INTO t1 VALUES (' ',4);
+INSERT INTO t1 VALUES (' ',5);
+INSERT INTO t1 VALUES (' a ',6);
+INSERT INTO t1 VALUES ('aa',7);
+INSERT INTO t1 VALUES ('aabb',8);
+INSERT INTO t1 VALUES ('bbaa',9);
+INSERT INTO t1 VALUES ('aabbaa',10);
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord '['||c1||']' '.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.'
+1 [abc] .bc.
+2 [] .NULL.
+3 [] .NULL.
+4 [ ] . .
+5 [ ] . .
+6 [ a ] . a .
+7 [aa] .NULL.
+8 [aabb] .bb.
+9 [bbaa] .bbaa.
+10 [aabbaa] .bbaa.
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord '['||c1||']' '.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.'
+1 [abc] .abc.
+2 [] .NULL.
+3 [] .NULL.
+4 [ ] . .
+5 [ ] . .
+6 [ a ] . a .
+7 [aa] .NULL.
+8 [aabb] .aabb.
+9 [bbaa] .bb.
+10 [aabbaa] .aabb.
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord '['||c1||']' '.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.'
+1 [abc] .bc.
+2 [] .NULL.
+3 [] .NULL.
+4 [ ] . .
+5 [ ] . .
+6 [ a ] . a .
+7 [aa] .NULL.
+8 [aabb] .bb.
+9 [bbaa] .bb.
+10 [aabbaa] .bb.
+SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
+ord '['||c1||']' COALESCE(LTRIM(c1),'NULL')
+1 [abc] abc
+2 [] NULL
+3 [] NULL
+4 [ ] NULL
+5 [ ] NULL
+6 [ a ] a
+7 [aa] aa
+8 [aabb] aabb
+9 [bbaa] bbaa
+10 [aabbaa] aabbaa
+SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord '['||c1||']' COALESCE(RTRIM(c1),'NULL')||'.'
+1 [abc] abc.
+2 [] NULL.
+3 [] NULL.
+4 [ ] NULL.
+5 [ ] NULL.
+6 [ a ] a.
+7 [aa] aa.
+8 [aabb] aabb.
+9 [bbaa] bbaa.
+10 [aabbaa] aabbaa.
+EXPLAIN EXTENDED SELECT TRIM('abc'),
+TRIM(BOTH 'a' FROM 'abc'),
+TRIM(LEADING 'a' FROM 'abc'),
+TRIM(TRAILING 'a' FROM 'abc') ;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select trim_oracle('abc') AS "TRIM('abc')",trim_oracle(both 'a' from 'abc') AS "TRIM(BOTH 'a' FROM 'abc')",trim_oracle(leading 'a' from 'abc') AS "TRIM(LEADING 'a' FROM 'abc')",trim_oracle(trailing 'a' from 'abc') AS "TRIM(TRAILING 'a' FROM 'abc')"
+EXPLAIN EXTENDED SELECT RTRIM('abc'),
+LTRIM('abc');
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')"
+CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
+'['||c1||']',
+TRIM(LEADING 'a' FROM c1),
+TRIM(TRAILING 'a' FROM c1),
+TRIM(BOTH 'a' FROM c1),
+LTRIM(c1),
+RTRIM(c1)
+FROM t1 ORDER BY ord ;
+SHOW CREATE VIEW v1;
+View Create View character_set_client collation_connection
+v1 CREATE VIEW "v1" AS select "t1"."ord" AS "ord",trim_oracle('abc') AS "TRIM('abc')",rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')",concat_operator_oracle(concat_operator_oracle('[',"t1"."c1"),']') AS "'['||c1||']'",trim_oracle(leading 'a' from "t1"."c1") AS "TRIM(LEADING 'a' FROM c1)",trim_oracle(trailing 'a' from "t1"."c1") AS "TRIM(TRAILING 'a' FROM c1)",trim_oracle(both 'a' from "t1"."c1") AS "TRIM(BOTH 'a' FROM c1)",ltrim_oracle("t1"."c1") AS "LTRIM(c1)",rtrim_oracle("t1"."c1") AS "RTRIM(c1)" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci
+SELECT * FROM v1;
+ord TRIM('abc') RTRIM('abc') LTRIM('abc') '['||c1||']' TRIM(LEADING 'a' FROM c1) TRIM(TRAILING 'a' FROM c1) TRIM(BOTH 'a' FROM c1) LTRIM(c1) RTRIM(c1)
+1 abc abc abc [abc] bc abc bc abc abc
+2 abc abc abc [] NULL NULL NULL NULL NULL
+3 abc abc abc [] NULL NULL NULL NULL NULL
+4 abc abc abc [ ] NULL NULL
+5 abc abc abc [ ] NULL NULL
+6 abc abc abc [ a ] a a a a a
+7 abc abc abc [aa] NULL NULL NULL aa aa
+8 abc abc abc [aabb] bb aabb bb aabb aabb
+9 abc abc abc [bbaa] bbaa bb bb bbaa bbaa
+10 abc abc abc [aabbaa] bbaa aabb bb aabbaa aabbaa
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
+CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
+TRIM(TRAILING 'a' FROM c1) AS C2,
+TRIM(BOTH 'a' FROM c1) AS C3,
+LTRIM(c1) AS C4,
+RTRIM(c1) AS C5
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "C1" varchar(10) DEFAULT NULL,
+ "C2" varchar(10) DEFAULT NULL,
+ "C3" varchar(10) DEFAULT NULL,
+ "C4" varchar(10) DEFAULT NULL,
+ "C5" varchar(10) DEFAULT NULL
+)
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;
diff --git a/mysql-test/suite/compat/oracle/t/func_trim.test b/mysql-test/suite/compat/oracle/t/func_trim.test
new file mode 100644
index 0000000..153238f
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_trim.test
@@ -0,0 +1,77 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+--echo #
+
+SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM(' '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
+
+SELECT TRIM(TRAILING 'abc' FROM 'abc');
+SELECT TRIM(TRAILING 'abc' FROM 'abc ');
+SELECT TRIM(TRAILING 'abc' FROM ' abc');
+
+SELECT TRIM(LEADING 'abc' FROM 'abc');
+SELECT TRIM(LEADING 'abc' FROM 'abc ');
+SELECT TRIM(LEADING 'abc' FROM ' abc');
+
+SELECT TRIM(BOTH 'abc' FROM 'abc');
+SELECT TRIM(BOTH 'abc' FROM 'abc ');
+SELECT TRIM(BOTH 'abc' FROM ' abc');
+
+SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM(' '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
+SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM(' '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
+
+CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
+INSERT INTO t1 VALUES ('abc',1);
+INSERT INTO t1 VALUES (SPACE(0),2);
+INSERT INTO t1 VALUES ('',3);
+INSERT INTO t1 VALUES (' ',4);
+INSERT INTO t1 VALUES (' ',5);
+INSERT INTO t1 VALUES (' a ',6);
+INSERT INTO t1 VALUES ('aa',7);
+INSERT INTO t1 VALUES ('aabb',8);
+INSERT INTO t1 VALUES ('bbaa',9);
+INSERT INTO t1 VALUES ('aabbaa',10);
+
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
+
+EXPLAIN EXTENDED SELECT TRIM('abc'),
+ TRIM(BOTH 'a' FROM 'abc'),
+ TRIM(LEADING 'a' FROM 'abc'),
+ TRIM(TRAILING 'a' FROM 'abc') ;
+
+EXPLAIN EXTENDED SELECT RTRIM('abc'),
+ LTRIM('abc');
+
+
+CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
+ '['||c1||']',
+ TRIM(LEADING 'a' FROM c1),
+ TRIM(TRAILING 'a' FROM c1),
+ TRIM(BOTH 'a' FROM c1),
+ LTRIM(c1),
+ RTRIM(c1)
+ FROM t1 ORDER BY ord ;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
+CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
+ TRIM(TRAILING 'a' FROM c1) AS C2,
+ TRIM(BOTH 'a' FROM c1) AS C3,
+ LTRIM(c1) AS C4,
+ RTRIM(c1) AS C5
+ FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index 98eaa7a..642928f 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -1349,3 +1349,9 @@ DROP TABLE raw;
CREATE TABLE varchar2 (varchar2 int);
DROP TABLE varchar2;
+
+--echo #
+--echo # MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+--echo #
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 385d8de..a07e4c9 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -2218,6 +2218,19 @@ class Create_func_ltrim : public Create_func_arg1
};
+class Create_func_ltrim_oracle : public Create_func_arg1
+{
+public:
+ virtual Item *create_1_arg(THD *thd, Item *arg1);
+
+ static Create_func_ltrim_oracle s_singleton;
+
+protected:
+ Create_func_ltrim_oracle() {}
+ virtual ~Create_func_ltrim_oracle() {}
+};
+
+
class Create_func_makedate : public Create_func_arg2
{
public:
@@ -2658,6 +2671,19 @@ class Create_func_rtrim : public Create_func_arg1
};
+class Create_func_rtrim_oracle : public Create_func_arg1
+{
+public:
+ virtual Item *create_1_arg(THD *thd, Item *arg1);
+
+ static Create_func_rtrim_oracle s_singleton;
+
+protected:
+ Create_func_rtrim_oracle() {}
+ virtual ~Create_func_rtrim_oracle() {}
+};
+
+
class Create_func_sec_to_time : public Create_func_arg1
{
public:
@@ -5821,7 +5847,16 @@ Create_func_ltrim Create_func_ltrim::s_singleton;
Item*
Create_func_ltrim::create_1_arg(THD *thd, Item *arg1)
{
- return new (thd->mem_root) Item_func_ltrim(thd, arg1);
+ return Lex_trim(TRIM_LEADING, arg1).make_item_func_trim(thd);
+}
+
+
+Create_func_ltrim_oracle Create_func_ltrim_oracle::s_singleton;
+
+Item*
+Create_func_ltrim_oracle::create_1_arg(THD *thd, Item *arg1)
+{
+ return new (thd->mem_root) Item_func_ltrim_oracle(thd, arg1);
}
@@ -6318,7 +6353,16 @@ Create_func_rtrim Create_func_rtrim::s_singleton;
Item*
Create_func_rtrim::create_1_arg(THD *thd, Item *arg1)
{
- return new (thd->mem_root) Item_func_rtrim(thd, arg1);
+ return Lex_trim(TRIM_TRAILING, arg1).make_item_func_trim(thd);
+}
+
+
+Create_func_rtrim_oracle Create_func_rtrim_oracle::s_singleton;
+
+Item*
+Create_func_rtrim_oracle::create_1_arg(THD *thd, Item *arg1)
+{
+ return new (thd->mem_root) Item_func_rtrim_oracle(thd, arg1);
}
@@ -6978,6 +7022,7 @@ static Native_func_registry func_array[] =
{ { STRING_WITH_LEN("LOWER") }, BUILDER(Create_func_lcase)},
{ { STRING_WITH_LEN("LPAD") }, BUILDER(Create_func_lpad)},
{ { STRING_WITH_LEN("LTRIM") }, BUILDER(Create_func_ltrim)},
+ { { STRING_WITH_LEN("LTRIM_ORACLE") }, BUILDER(Create_func_ltrim_oracle)},
{ { STRING_WITH_LEN("MAKEDATE") }, BUILDER(Create_func_makedate)},
{ { STRING_WITH_LEN("MAKETIME") }, BUILDER(Create_func_maketime)},
{ { STRING_WITH_LEN("MAKE_SET") }, BUILDER(Create_func_make_set)},
@@ -7042,6 +7087,7 @@ static Native_func_registry func_array[] =
{ { STRING_WITH_LEN("ROUND") }, BUILDER(Create_func_round)},
{ { STRING_WITH_LEN("RPAD") }, BUILDER(Create_func_rpad)},
{ { STRING_WITH_LEN("RTRIM") }, BUILDER(Create_func_rtrim)},
+ { { STRING_WITH_LEN("RTRIM_ORACLE") }, BUILDER(Create_func_rtrim_oracle)},
{ { STRING_WITH_LEN("SEC_TO_TIME") }, BUILDER(Create_func_sec_to_time)},
{ { STRING_WITH_LEN("SHA") }, BUILDER(Create_func_sha)},
{ { STRING_WITH_LEN("SHA1") }, BUILDER(Create_func_sha)},
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 7c32fde..c7a45c5 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -2116,6 +2116,7 @@ void Item_func_trim::print(String *str, enum_query_type query_type)
return;
}
str->append(Item_func_trim::func_name());
+ str->append(func_name_ext());
str->append('(');
str->append(mode_name());
str->append(' ');
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 4efc153..f2aa54f 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -539,6 +539,9 @@ class Item_func_trim :public Item_str_func
String remove;
String *trimmed_value(String *res, uint32 offset, uint32 length)
{
+ if (length == 0)
+ return make_empty_result();
+
tmp_value.set(*res, offset, length);
/*
Make sure to return correct charset and collation:
@@ -552,6 +555,7 @@ class Item_func_trim :public Item_str_func
{
return trimmed_value(res, 0, res->length());
}
+ virtual const char *func_name_ext() const { return ""; }
public:
Item_func_trim(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {}
Item_func_trim(THD *thd, Item *a): Item_str_func(thd, a) {}
@@ -565,6 +569,27 @@ class Item_func_trim :public Item_str_func
};
+class Item_func_trim_oracle :public Item_func_trim
+{
+protected:
+ String *make_empty_result()
+ { null_value= 1; return NULL; }
+ const char *func_name_ext() const { return "_oracle"; }
+public:
+ Item_func_trim_oracle(THD *thd, Item *a, Item *b):
+ Item_func_trim(thd, a, b) {}
+ Item_func_trim_oracle(THD *thd, Item *a): Item_func_trim(thd, a) {}
+ const char *func_name() const { return "trim_oracle"; }
+ void fix_length_and_dec()
+ {
+ Item_func_trim::fix_length_and_dec();
+ maybe_null= true;
+ }
+ Item *get_copy(THD *thd)
+ { return get_item_copy<Item_func_trim_oracle>(thd, this); }
+};
+
+
class Item_func_ltrim :public Item_func_trim
{
public:
@@ -578,6 +603,27 @@ class Item_func_ltrim :public Item_func_trim
};
+class Item_func_ltrim_oracle :public Item_func_ltrim
+{
+protected:
+ String *make_empty_result()
+ { null_value= 1; return NULL; }
+ const char *func_name_ext() const { return "_oracle"; }
+public:
+ Item_func_ltrim_oracle(THD *thd, Item *a, Item *b):
+ Item_func_ltrim(thd, a, b) {}
+ Item_func_ltrim_oracle(THD *thd, Item *a): Item_func_ltrim(thd, a) {}
+ const char *func_name() const { return "ltrim_oracle"; }
+ void fix_length_and_dec()
+ {
+ Item_func_ltrim::fix_length_and_dec();
+ maybe_null= true;
+ }
+ Item *get_copy(THD *thd)
+ { return get_item_copy<Item_func_ltrim_oracle>(thd, this); }
+};
+
+
class Item_func_rtrim :public Item_func_trim
{
public:
@@ -591,6 +637,26 @@ class Item_func_rtrim :public Item_func_trim
};
+class Item_func_rtrim_oracle :public Item_func_rtrim
+{
+protected:
+ String *make_empty_result()
+ { null_value= 1; return NULL; }
+ const char *func_name_ext() const { return "_oracle"; }
+public:
+ Item_func_rtrim_oracle(THD *thd, Item *a, Item *b):
+ Item_func_rtrim(thd, a, b) {}
+ Item_func_rtrim_oracle(THD *thd, Item *a): Item_func_rtrim(thd, a) {}
+ const char *func_name() const { return "rtrim_oracle"; }
+ void fix_length_and_dec()
+ {
+ Item_func_rtrim::fix_length_and_dec();
+ maybe_null= true;
+ }
+ Item *get_copy(THD *thd)
+ { return get_item_copy<Item_func_rtrim_oracle>(thd, this); }
+};
+
/*
Item_func_password -- new (4.1.1) PASSWORD() function implementation.
Returns strcat('*', octet2hex(sha1(sha1(password)))). '*' stands for new
diff --git a/sql/lex.h b/sql/lex.h
index 9833809..7be3e4c 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -771,6 +771,7 @@ static SYMBOL sql_functions[] = {
{ "SYSDATE", SYM(SYSDATE)},
{ "SYSTEM_USER", SYM(USER_SYM)},
{ "TRIM", SYM(TRIM)},
+ { "TRIM_ORACLE", SYM(TRIM_ORACLE)},
{ "VARIANCE", SYM(VARIANCE_SYM)},
{ "VAR_POP", SYM(VARIANCE_SYM)},
{ "VAR_SAMP", SYM(VAR_SAMP_SYM)},
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b169b9e..6799a7a 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7754,3 +7754,64 @@ bool SELECT_LEX::vers_push_field(THD *thd, TABLE_LIST *table, const LEX_CSTRING
return false;
}
+
+Item *Lex_trim_st::make_item_func_trim_std(THD *thd) const
+{
+ if (m_remove)
+ {
+ switch (m_spec) {
+ case TRIM_BOTH:
+ return new (thd->mem_root) Item_func_trim(thd, m_source, m_remove);
+ case TRIM_LEADING:
+ return new (thd->mem_root) Item_func_ltrim(thd, m_source, m_remove);
+ case TRIM_TRAILING:
+ return new (thd->mem_root) Item_func_rtrim(thd, m_source, m_remove);
+ }
+ }
+
+ switch (m_spec) {
+ case TRIM_BOTH:
+ return new (thd->mem_root) Item_func_trim(thd, m_source);
+ case TRIM_LEADING:
+ return new (thd->mem_root) Item_func_ltrim(thd, m_source);
+ case TRIM_TRAILING:
+ return new (thd->mem_root) Item_func_rtrim(thd, m_source);
+ }
+ DBUG_ASSERT(0);
+ return NULL;
+}
+
+
+Item *Lex_trim_st::make_item_func_trim_oracle(THD *thd) const
+{
+ if (m_remove)
+ {
+ switch (m_spec) {
+ case TRIM_BOTH:
+ return new (thd->mem_root) Item_func_trim_oracle(thd, m_source, m_remove);
+ case TRIM_LEADING:
+ return new (thd->mem_root) Item_func_ltrim_oracle(thd, m_source, m_remove);
+ case TRIM_TRAILING:
+ return new (thd->mem_root) Item_func_rtrim_oracle(thd, m_source, m_remove);
+ }
+ }
+
+ switch (m_spec) {
+ case TRIM_BOTH:
+ return new (thd->mem_root) Item_func_trim_oracle(thd, m_source);
+ case TRIM_LEADING:
+ return new (thd->mem_root) Item_func_ltrim_oracle(thd, m_source);
+ case TRIM_TRAILING:
+ return new (thd->mem_root) Item_func_rtrim_oracle(thd, m_source);
+ }
+ DBUG_ASSERT(0);
+ return NULL;
+}
+
+
+Item *Lex_trim_st::make_item_func_trim(THD *thd) const
+{
+ return (thd->variables.sql_mode & MODE_ORACLE) ?
+ make_item_func_trim_oracle(thd) :
+ make_item_func_trim_std(thd);
+}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 35ec2d2..80c9a30 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -788,6 +788,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr)
Lex_dyncol_type_st Lex_dyncol_type;
Lex_for_loop_st for_loop;
Lex_for_loop_bounds_st for_loop_bounds;
+ Lex_trim_st trim;
struct
{
LEX_CSTRING name;
@@ -1554,6 +1555,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
%token TRIGGERS_SYM
%token TRIGGER_SYM /* SQL-2003-R */
%token TRIM /* SQL-2003-N */
+%token TRIM_ORACLE
%token TRUE_SYM /* SQL-2003-R */
%token TRUNCATE_SYM
%token TYPES_SYM
@@ -1987,6 +1989,7 @@ END_OF_INPUT
%type <spvar> sp_param_name sp_param_name_and_type
%type <for_loop> sp_for_loop_index_and_bounds
%type <for_loop_bounds> sp_for_loop_bounds
+%type <trim> trim_operands
%type <num> opt_sp_for_loop_direction
%type <spvar_mode> sp_opt_inout
%type <index_hint> index_hint_type
@@ -9895,6 +9898,17 @@ dyncall_create_list:
}
;
+trim_operands:
+ expr { $$.set(TRIM_BOTH, $1); }
+ | LEADING expr FROM expr { $$.set(TRIM_LEADING, $2, $4); }
+ | TRAILING expr FROM expr { $$.set(TRIM_TRAILING, $2, $4); }
+ | BOTH expr FROM expr { $$.set(TRIM_BOTH, $2, $4); }
+ | LEADING FROM expr { $$.set(TRIM_LEADING, $3); }
+ | TRAILING FROM expr { $$.set(TRIM_TRAILING, $3); }
+ | BOTH FROM expr { $$.set(TRIM_BOTH, $3); }
+ | expr FROM expr { $$.set(TRIM_BOTH, $1, $3); }
+ ;
+
/*
Expressions that the parser allows in a column DEFAULT clause
without parentheses. These expressions cannot end with a COLLATE clause.
@@ -10263,52 +10277,9 @@ function_call_keyword:
{
$$= $1;
}
- | TRIM '(' expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $3);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' LEADING expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_ltrim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' TRAILING expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_rtrim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' BOTH expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' LEADING FROM expr ')'
+ | TRIM '(' trim_operands ')'
{
- $$= new (thd->mem_root) Item_func_ltrim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' TRAILING FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_rtrim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' BOTH FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $5, $3);
- if ($$ == NULL)
+ if (!($$= $3.make_item_func_trim(thd)))
MYSQL_YYABORT;
}
| USER_SYM '(' ')'
@@ -10487,6 +10458,11 @@ function_call_nonkeyword:
if ($$ == NULL)
MYSQL_YYABORT;
}
+ | TRIM_ORACLE '(' trim_operands ')'
+ {
+ if (!($$= $3.make_item_func_trim_oracle(thd)))
+ MYSQL_YYABORT;
+ }
| UTC_DATE_SYM optional_braces
{
$$= new (thd->mem_root) Item_func_curdate_utc(thd);
@@ -15788,6 +15764,7 @@ keyword_sp_not_data_type:
| TRANSACTION_SYM {}
| TRANSACTIONAL_SYM {}
| TRIGGERS_SYM {}
+ | TRIM_ORACLE {}
| TIMESTAMP_ADD {}
| TIMESTAMP_DIFF {}
| TYPES_SYM {}
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index c8f93c1..d2f0f9d 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -181,6 +181,7 @@ void ORAerror(THD *thd, const char *s)
Lex_dyncol_type_st Lex_dyncol_type;
Lex_for_loop_st for_loop;
Lex_for_loop_bounds_st for_loop_bounds;
+ Lex_trim_st trim;
struct
{
LEX_CSTRING name;
@@ -945,6 +946,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
%token TRIGGERS_SYM
%token TRIGGER_SYM /* SQL-2003-R */
%token TRIM /* SQL-2003-N */
+%token TRIM_ORACLE
%token TRUE_SYM /* SQL-2003-R */
%token TRUNCATE_SYM
%token TYPES_SYM
@@ -1406,6 +1408,7 @@ END_OF_INPUT
%type <spvar> sp_param_name sp_param_name_and_type
%type <for_loop> sp_for_loop_index_and_bounds
%type <for_loop_bounds> sp_for_loop_bounds
+%type <trim> trim_operands
%type <num> opt_sp_for_loop_direction
%type <spvar_mode> sp_opt_inout
%type <index_hint> index_hint_type
@@ -9676,6 +9679,17 @@ explicit_cursor_attr:
}
;
+trim_operands:
+ expr { $$.set(TRIM_BOTH, $1); }
+ | LEADING expr FROM expr { $$.set(TRIM_LEADING, $2, $4); }
+ | TRAILING expr FROM expr { $$.set(TRIM_TRAILING, $2, $4); }
+ | BOTH expr FROM expr { $$.set(TRIM_BOTH, $2, $4); }
+ | LEADING FROM expr { $$.set(TRIM_LEADING, $3); }
+ | TRAILING FROM expr { $$.set(TRIM_TRAILING, $3); }
+ | BOTH FROM expr { $$.set(TRIM_BOTH, $3); }
+ | expr FROM expr { $$.set(TRIM_BOTH, $1, $3); }
+ ;
+
/*
Expressions that the parser allows in a column DEFAULT clause
without parentheses. These expressions cannot end with a COLLATE clause.
@@ -10066,52 +10080,9 @@ function_call_keyword:
if ($$ == NULL)
MYSQL_YYABORT;
}
- | TRIM '(' expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $3);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' LEADING expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_ltrim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' TRAILING expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_rtrim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' BOTH expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $6, $4);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' LEADING FROM expr ')'
+ | TRIM '(' trim_operands ')'
{
- $$= new (thd->mem_root) Item_func_ltrim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' TRAILING FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_rtrim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' BOTH FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $5);
- if ($$ == NULL)
- MYSQL_YYABORT;
- }
- | TRIM '(' expr FROM expr ')'
- {
- $$= new (thd->mem_root) Item_func_trim(thd, $5, $3);
- if ($$ == NULL)
+ if (!($$= $3.make_item_func_trim(thd)))
MYSQL_YYABORT;
}
| USER_SYM '(' ')'
@@ -10272,6 +10243,11 @@ function_call_nonkeyword:
if ($$ == NULL)
MYSQL_YYABORT;
}
+ | TRIM_ORACLE '(' trim_operands ')'
+ {
+ if (!($$= $3.make_item_func_trim_oracle(thd)))
+ MYSQL_YYABORT;
+ }
| UTC_DATE_SYM optional_braces
{
$$= new (thd->mem_root) Item_func_curdate_utc(thd);
@@ -15598,6 +15574,7 @@ keyword_sp_not_data_type:
| TRANSACTION_SYM {}
| TRANSACTIONAL_SYM {}
| TRIGGERS_SYM {}
+ | TRIM_ORACLE {}
| TIMESTAMP_ADD {}
| TIMESTAMP_DIFF {}
| TYPES_SYM {}
diff --git a/sql/structs.h b/sql/structs.h
index 9bcbd1b..49292ea 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -723,6 +723,37 @@ struct Lex_for_loop_st
};
+enum trim_spec { TRIM_LEADING, TRIM_TRAILING, TRIM_BOTH };
+
+struct Lex_trim_st
+{
+public:
+ Item *m_remove;
+ Item *m_source;
+ trim_spec m_spec;
+ void set(trim_spec spec, Item *remove, Item *source)
+ {
+ m_spec= spec;
+ m_remove= remove;
+ m_source= source;
+ }
+ void set(trim_spec spec, Item *source)
+ {
+ set(spec, NULL, source);
+ }
+ Item *make_item_func_trim_std(THD *thd) const;
+ Item *make_item_func_trim_oracle(THD *thd) const;
+ Item *make_item_func_trim(THD *thd) const;
+};
+
+
+class Lex_trim: public Lex_trim_st
+{
+public:
+ Lex_trim(trim_spec spec, Item *source) { set(spec, source); }
+};
+
+
struct Lex_string_with_pos_st: public LEX_CSTRING
{
const char *m_pos;
Follow ups
References
