diff options
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/func_pad.result | 71 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/func_pad.test | 31 | ||||
| -rw-r--r-- | sql/item_create.cc | 112 | ||||
| -rw-r--r-- | sql/item_strfunc.h | 40 |
4 files changed, 246 insertions, 8 deletions
diff --git a/mysql-test/suite/compat/oracle/r/func_pad.result b/mysql-test/suite/compat/oracle/r/func_pad.result new file mode 100644 index 00000000000..ca7d52cd542 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/func_pad.result @@ -0,0 +1,71 @@ +SET sql_mode=ORACLE; +# +# MDEV-15739 - sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string +# +SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ; +RPAD('a',0) RPAD('abc',1) RPAD('abc',2) +NULL a ab +SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ; +RPAD('a',0,'.') RPAD('abc',1,'.') RPAD('abc',2,'.') +NULL a ab +SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ; +LPAD('a',0) LPAD('abc',1) LPAD('abc',2) +NULL a ab +SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ; +LPAD('a',0,'.') LPAD('abc',1,'.') LPAD('abc',2,'.') +NULL a ab +CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER); +INSERT INTO t1 VALUES ('a',1,null,1); +INSERT INTO t1 VALUES ('a',null,'.',2); +INSERT INTO t1 VALUES (null,1,'.',3); +INSERT INTO t1 VALUES ('a',-1,'.',4); +INSERT INTO t1 VALUES ('a',0,'.',5); +INSERT INTO t1 VALUES ('a',1,'.',6); +INSERT INTO t1 VALUES ('a',2,'.',7); +SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord; +LPAD(c1,c2,c3) LPAD(c1,c2) +NULL a +NULL NULL +NULL NULL +NULL NULL +NULL NULL +a a +.a a +SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; +RPAD(c1,c2,c3) RPAD(c1,c2) +NULL a +NULL NULL +NULL NULL +NULL NULL +NULL NULL +a a +a. a +EXPLAIN EXTENDED SELECT RPAD('a',0,'.'), LPAD('a',0,'.'), LPAD(c1,c2,c3), LPAD(c1,c2), RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using filesort +Warnings: +Note 1003 select rpad_oracle('a',0,'.') AS "RPAD('a',0,'.')",lpad_oracle('a',0,'.') AS "LPAD('a',0,'.')",lpad_oracle("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "LPAD(c1,c2,c3)",lpad_oracle("test"."t1"."c1","test"."t1"."c2") AS "LPAD(c1,c2)",rpad_oracle("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "RPAD(c1,c2,c3)",rpad_oracle("test"."t1"."c1","test"."t1"."c2") AS "RPAD(c1,c2)" from "test"."t1" order by "test"."t1"."ord" +CREATE VIEW v1 AS SELECT RPAD('a',0,'.') AS "C1", LPAD('a',0,'.') AS "C2", LPAD(c1,c2,c3) AS "C3", LPAD(c1,c2) AS "C4", RPAD(c1,c2,c3) AS "C5", RPAD(c1,c2) AS "C6" FROM t1 ORDER BY ord; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE VIEW "v1" AS select rpad_oracle('a',0,'.') AS "C1",lpad_oracle('a',0,'.') AS "C2",lpad_oracle("t1"."c1","t1"."c2","t1"."c3") AS "C3",lpad_oracle("t1"."c1","t1"."c2") AS "C4",rpad_oracle("t1"."c1","t1"."c2","t1"."c3") AS "C5",rpad_oracle("t1"."c1","t1"."c2") AS "C6" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci +SELECT * FROM v1; +C1 C2 C3 C4 C5 C6 +NULL NULL NULL a NULL a +NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL +NULL NULL a a a a +NULL NULL .a a a. a +SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1; +c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 +---a--a +----- +----- +----- +----- +--a-a-a-a +--.a- a-a.-a +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/func_pad.test b/mysql-test/suite/compat/oracle/t/func_pad.test new file mode 100644 index 00000000000..bc33a9fa4f8 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/func_pad.test @@ -0,0 +1,31 @@ +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-15739 - sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string +--echo # + +SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ; +SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ; +SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ; +SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ; + +CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER); +INSERT INTO t1 VALUES ('a',1,null,1); +INSERT INTO t1 VALUES ('a',null,'.',2); +INSERT INTO t1 VALUES (null,1,'.',3); +INSERT INTO t1 VALUES ('a',-1,'.',4); +INSERT INTO t1 VALUES ('a',0,'.',5); +INSERT INTO t1 VALUES ('a',1,'.',6); +INSERT INTO t1 VALUES ('a',2,'.',7); + +SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord; +SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; + +EXPLAIN EXTENDED SELECT RPAD('a',0,'.'), LPAD('a',0,'.'), LPAD(c1,c2,c3), LPAD(c1,c2), RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord; + +CREATE VIEW v1 AS SELECT RPAD('a',0,'.') AS "C1", LPAD('a',0,'.') AS "C2", LPAD(c1,c2,c3) AS "C3", LPAD(c1,c2) AS "C4", RPAD(c1,c2,c3) AS "C5", RPAD(c1,c2) AS "C6" FROM t1 ORDER BY ord; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/sql/item_create.cc b/sql/item_create.cc index a07e4c9c16b..901dfa06f40 100644 --- a/sql/item_create.cc +++ b/sql/item_create.cc @@ -2195,13 +2195,30 @@ class Create_func_lpad : public Create_native_func { public: virtual Item *create_native(THD *thd, LEX_CSTRING *name, - List<Item> *item_list); - + List<Item> *item_list) + { + return thd->variables.sql_mode & MODE_ORACLE ? + create_native_oracle(thd, name, item_list) : + create_native_std(thd, name, item_list); + } static Create_func_lpad s_singleton; protected: Create_func_lpad() {} virtual ~Create_func_lpad() {} + Item *create_native_std(THD *thd, LEX_CSTRING *name, List<Item> *items); + Item *create_native_oracle(THD *thd, LEX_CSTRING *name, List<Item> *items); +}; + + +class Create_func_lpad_oracle : public Create_func_lpad +{ +public: + Item *create_native(THD *thd, LEX_CSTRING *name, List<Item> *item_list) + { + return create_native_oracle(thd, name, item_list); + } + static Create_func_lpad_oracle s_singleton; }; @@ -2648,13 +2665,30 @@ class Create_func_rpad : public Create_native_func { public: virtual Item *create_native(THD *thd, LEX_CSTRING *name, - List<Item> *item_list); - + List<Item> *item_list) + { + return thd->variables.sql_mode & MODE_ORACLE ? + create_native_oracle(thd, name, item_list) : + create_native_std(thd, name, item_list); + } static Create_func_rpad s_singleton; protected: Create_func_rpad() {} virtual ~Create_func_rpad() {} + Item *create_native_std(THD *thd, LEX_CSTRING *name, List<Item> *items); + Item *create_native_oracle(THD *thd, LEX_CSTRING *name, List<Item> *items); +}; + + +class Create_func_rpad_oracle : public Create_func_rpad +{ +public: + Item *create_native(THD *thd, LEX_CSTRING *name, List<Item> *item_list) + { + return create_native_oracle(thd, name, item_list); + } + static Create_func_rpad_oracle s_singleton; }; @@ -5810,9 +5844,11 @@ Create_func_log2::create_1_arg(THD *thd, Item *arg1) Create_func_lpad Create_func_lpad::s_singleton; +Create_func_lpad_oracle Create_func_lpad_oracle::s_singleton; + Item* -Create_func_lpad::create_native(THD *thd, LEX_CSTRING *name, - List<Item> *item_list) +Create_func_lpad::create_native_std(THD *thd, LEX_CSTRING *name, + List<Item> *item_list) { Item *func= NULL; int arg_count= item_list ? item_list->elements : 0; @@ -5842,6 +5878,34 @@ Create_func_lpad::create_native(THD *thd, LEX_CSTRING *name, } +Item* +Create_func_lpad::create_native_oracle(THD *thd, LEX_CSTRING *name, + List<Item> *item_list) +{ + int arg_count= item_list ? item_list->elements : 0; + switch (arg_count) { + case 2: + { + Item *param_1= item_list->pop(); + Item *param_2= item_list->pop(); + return new (thd->mem_root) Item_func_lpad_oracle(thd, param_1, param_2); + } + case 3: + { + Item *param_1= item_list->pop(); + Item *param_2= item_list->pop(); + Item *param_3= item_list->pop(); + return new (thd->mem_root) Item_func_lpad_oracle(thd, param_1, + param_2, param_3); + } + default: + my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name->str); + break; + } + return NULL; +} + + Create_func_ltrim Create_func_ltrim::s_singleton; Item* @@ -6316,9 +6380,11 @@ Create_func_round::create_native(THD *thd, LEX_CSTRING *name, Create_func_rpad Create_func_rpad::s_singleton; +Create_func_rpad_oracle Create_func_rpad_oracle::s_singleton; + Item* -Create_func_rpad::create_native(THD *thd, LEX_CSTRING *name, - List<Item> *item_list) +Create_func_rpad::create_native_std(THD *thd, LEX_CSTRING *name, + List<Item> *item_list) { Item *func= NULL; int arg_count= item_list ? item_list->elements : 0; @@ -6348,6 +6414,34 @@ Create_func_rpad::create_native(THD *thd, LEX_CSTRING *name, } +Item* +Create_func_rpad::create_native_oracle(THD *thd, LEX_CSTRING *name, + List<Item> *item_list) +{ + int arg_count= item_list ? item_list->elements : 0; + switch (arg_count) { + case 2: + { + Item *param_1= item_list->pop(); + Item *param_2= item_list->pop(); + return new (thd->mem_root) Item_func_rpad_oracle(thd, param_1, param_2); + } + case 3: + { + Item *param_1= item_list->pop(); + Item *param_2= item_list->pop(); + Item *param_3= item_list->pop(); + return new (thd->mem_root) Item_func_rpad_oracle(thd, param_1, + param_2, param_3); + } + default: + my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name->str); + break; + } + return NULL; +} + + Create_func_rtrim Create_func_rtrim::s_singleton; Item* @@ -7021,6 +7115,7 @@ static Native_func_registry func_array[] = { { STRING_WITH_LEN("LOG2") }, BUILDER(Create_func_log2)}, { { STRING_WITH_LEN("LOWER") }, BUILDER(Create_func_lcase)}, { { STRING_WITH_LEN("LPAD") }, BUILDER(Create_func_lpad)}, + { { STRING_WITH_LEN("LPAD_ORACLE") }, BUILDER(Create_func_lpad_oracle)}, { { 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)}, @@ -7086,6 +7181,7 @@ static Native_func_registry func_array[] = { { STRING_WITH_LEN("REVERSE") }, BUILDER(Create_func_reverse)}, { { STRING_WITH_LEN("ROUND") }, BUILDER(Create_func_round)}, { { STRING_WITH_LEN("RPAD") }, BUILDER(Create_func_rpad)}, + { { STRING_WITH_LEN("RPAD_ORACLE") }, BUILDER(Create_func_rpad_oracle)}, { { 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)}, diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 4e1514c5476..18cda491efd 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -1120,6 +1120,26 @@ public: }; +class Item_func_rpad_oracle :public Item_func_rpad +{ + String *make_empty_result() + { null_value= 1; return NULL; } +public: + Item_func_rpad_oracle(THD *thd, Item *arg1, Item *arg2, Item *arg3): + Item_func_rpad(thd, arg1, arg2, arg3) {} + Item_func_rpad_oracle(THD *thd, Item *arg1, Item *arg2): + Item_func_rpad(thd, arg1, arg2) {} + void fix_length_and_dec() + { + Item_func_rpad::fix_length_and_dec(); + maybe_null= true; + } + const char *func_name() const { return "rpad_oracle"; } + Item *get_copy(THD *thd) + { return get_item_copy<Item_func_rpad_oracle>(thd, this); } +}; + + class Item_func_lpad :public Item_func_pad { public: @@ -1134,6 +1154,26 @@ public: }; +class Item_func_lpad_oracle :public Item_func_lpad +{ + String *make_empty_result() + { null_value= 1; return NULL; } +public: + Item_func_lpad_oracle(THD *thd, Item *arg1, Item *arg2, Item *arg3): + Item_func_lpad(thd, arg1, arg2, arg3) {} + Item_func_lpad_oracle(THD *thd, Item *arg1, Item *arg2): + Item_func_lpad(thd, arg1, arg2) {} + void fix_length_and_dec() + { + Item_func_lpad::fix_length_and_dec(); + maybe_null= true; + } + const char *func_name() const { return "lpad_oracle"; } + Item *get_copy(THD *thd) + { return get_item_copy<Item_func_lpad_oracle>(thd, this); } +}; + + class Item_func_conv :public Item_str_func { public: |