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;
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp