Below is the list of changes that have just been committed into a local
5.1 repository of marcsql. When marcsql does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-11-08 20:24:09-07:00, malff@weblab.(none) +11 -0
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
Before this change, the functions BENCHMARK, ENCODE, DECODE and FORMAT could
only accept a constant for some parameters.
After this change, this restriction has been removed. An implication is that
these functions can also be used in prepared statements.
The change consist of changing the following classes:
- Item_func_benchmark
- Item_func_encode
- Item_func_decode
- Item_func_format
to:
- only accept Item* in the constructor,
- and evaluate arguments during calls to val_xxx()
which fits the general design of all the other functions.
The 'TODO' items identified in item_create.cc during the work done for
Bug 21114 are addressed by this fix, as a natural consequence of aligning
the design.
By removing the following class members:
- Item_func_encode::sql_crypt (also used in Item_func_decode)
- Item_func_benchmark::loop_count
the classes Item_func_encode, Item_func_decode and Item_func_benchmark
changed from being statefull and constant to stateless, and can be reused
between threads.
In the 'func_str' test, a single very long test line involving an explain
extended select with many functions has been rewritten into multiple
separate tests, to improve maintainability.
The result of explain extended select decode(encode(...)) has changed,
since the encode and decode functions now print all their parameters.
mysql-test/r/func_str.result@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +335 -2
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
mysql-test/r/parser.result@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +0 -13
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
mysql-test/r/ps.result@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +46 -0
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
mysql-test/t/func_str.test@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +108 -1
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
mysql-test/t/parser.test@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +0 -15
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
mysql-test/t/ps.test@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +35 -0
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
sql/item_create.cc@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +4 -38
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
sql/item_func.cc@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +16 -10
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
sql/item_func.h@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +2 -3
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
sql/item_strfunc.cc@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +61 -18
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
sql/item_strfunc.h@stripped, 2006-11-08 20:21:42-07:00, malff@weblab.(none) +5 -13
Bug#22684 (BENCHMARK, ENCODE, DECODE and FORMAT are not real functions)
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: malff
# Host: weblab.(none)
# Root: /home/marcsql/TREE/mysql-5.1-22684
--- 1.67/sql/item_create.cc 2006-11-08 20:24:15 -07:00
+++ 1.68/sql/item_create.cc 2006-11-08 20:24:15 -07:00
@@ -2612,15 +2612,8 @@ Create_func_benchmark Create_func_benchm
Item*
Create_func_benchmark::create(THD *thd, Item *arg1, Item *arg2)
{
- /* TODO: Known limitation, see Bug#22684 */
- if ((arg1->type() != Item::INT_ITEM) || ! arg1->basic_const_item())
- {
- my_error(ER_WRONG_PARAMETERS_TO_NATIVE_FCT, MYF(0), "BENCHMARK");
- return NULL;
- }
-
thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
- return new (thd->mem_root) Item_func_benchmark(arg1->val_int(), arg2);
+ return new (thd->mem_root) Item_func_benchmark(arg1, arg2);
}
@@ -2887,17 +2880,7 @@ Create_func_decode Create_func_decode::s
Item*
Create_func_decode::create(THD *thd, Item *arg1, Item *arg2)
{
- /* TODO: Known limitation, see Bug#22684 */
- if ((arg2->type() != Item::STRING_ITEM) || ! arg2->basic_const_item())
- {
- my_error(ER_WRONG_PARAMETERS_TO_NATIVE_FCT, MYF(0), "DECODE");
- return NULL;
- }
-
- String dummy;
- String *val = arg2->val_str(& dummy);
- DBUG_ASSERT(val);
- return new (thd->mem_root) Item_func_decode(arg1, val->c_ptr());
+ return new (thd->mem_root) Item_func_decode(arg1, arg2);
}
@@ -3033,17 +3016,7 @@ Create_func_encode Create_func_encode::s
Item*
Create_func_encode::create(THD *thd, Item *arg1, Item *arg2)
{
- /* TODO: Known limitation, see Bug#22684 */
- if ((arg2->type() != Item::STRING_ITEM) || ! arg2->basic_const_item())
- {
- my_error(ER_WRONG_PARAMETERS_TO_NATIVE_FCT, MYF(0), "ENCODE");
- return NULL;
- }
-
- String dummy;
- String *val = arg2->val_str(& dummy);
- DBUG_ASSERT(val);
- return new (thd->mem_root) Item_func_encode(arg1, val->c_ptr());
+ return new (thd->mem_root) Item_func_encode(arg1, arg2);
}
@@ -3235,14 +3208,7 @@ Create_func_format Create_func_format::s
Item*
Create_func_format::create(THD *thd, Item *arg1, Item *arg2)
{
- /* TODO: Known limitation, see Bug#22684 */
- if ((arg2->type() != Item::INT_ITEM) || ! arg2->basic_const_item())
- {
- my_error(ER_WRONG_PARAMETERS_TO_NATIVE_FCT, MYF(0), "FORMAT");
- return NULL;
- }
-
- return new (thd->mem_root) Item_func_format(arg1, arg2->val_int());
+ return new (thd->mem_root) Item_func_format(arg1, arg2);
}
--- 1.327/sql/item_func.cc 2006-11-08 20:24:15 -07:00
+++ 1.328/sql/item_func.cc 2006-11-08 20:24:15 -07:00
@@ -3390,18 +3390,28 @@ longlong Item_func_benchmark::val_int()
char buff[MAX_FIELD_WIDTH];
String tmp(buff,sizeof(buff), &my_charset_bin);
THD *thd=current_thd;
+ ulong loop_count;
+ loop_count= args[0]->val_int();
+
+ if (args[0]->null_value)
+ {
+ null_value= 1;
+ return 0;
+ }
+
+ null_value=0;
for (ulong loop=0 ; loop < loop_count && !thd->killed; loop++)
{
- switch (args[0]->result_type()) {
+ switch (args[1]->result_type()) {
case REAL_RESULT:
- (void) args[0]->val_real();
+ (void) args[1]->val_real();
break;
case INT_RESULT:
- (void) args[0]->val_int();
+ (void) args[1]->val_int();
break;
case STRING_RESULT:
- (void) args[0]->val_str(&tmp);
+ (void) args[1]->val_str(&tmp);
break;
case ROW_RESULT:
default:
@@ -3417,13 +3427,9 @@ longlong Item_func_benchmark::val_int()
void Item_func_benchmark::print(String *str)
{
str->append(STRING_WITH_LEN("benchmark("));
- char buffer[20];
- // my_charset_bin is good enough for numbers
- String st(buffer, sizeof(buffer), &my_charset_bin);
- st.set((ulonglong)loop_count, &my_charset_bin);
- str->append(st);
- str->append(',');
args[0]->print(str);
+ str->append(',');
+ args[1]->print(str);
str->append(')');
}
--- 1.151/sql/item_func.h 2006-11-08 20:24:15 -07:00
+++ 1.152/sql/item_func.h 2006-11-08 20:24:15 -07:00
@@ -924,10 +924,9 @@ public:
class Item_func_benchmark :public Item_int_func
{
- ulong loop_count;
public:
- Item_func_benchmark(ulong loop_count_arg,Item *expr)
- :Item_int_func(expr), loop_count(loop_count_arg)
+ Item_func_benchmark(Item *count_expr, Item *expr)
+ :Item_int_func(count_expr, expr)
{}
longlong val_int();
const char *func_name() const { return "benchmark"; }
--- 1.295/sql/item_strfunc.cc 2006-11-08 20:24:15 -07:00
+++ 1.296/sql/item_strfunc.cc 2006-11-08 20:24:15 -07:00
@@ -1627,15 +1627,26 @@ void Item_func_encode::fix_length_and_de
String *Item_func_encode::val_str(String *str)
{
- DBUG_ASSERT(fixed == 1);
String *res;
+ String password_buffer;
+ String *password;
+ DBUG_ASSERT(fixed == 1);
+
if (!(res=args[0]->val_str(str)))
{
null_value=1; /* purecov: inspected */
return 0; /* purecov: inspected */
}
+
+ if (!(password=args[1]->val_str(& password_buffer)))
+ {
+ null_value=1;
+ return 0;
+ }
+
null_value=0;
res=copy_if_not_alloced(str,res,res->length());
+ SQL_CRYPT sql_crypt(password->ptr());
sql_crypt.init();
sql_crypt.encode((char*) res->ptr(),res->length());
res->set_charset(&my_charset_bin);
@@ -1644,15 +1655,26 @@ String *Item_func_encode::val_str(String
String *Item_func_decode::val_str(String *str)
{
- DBUG_ASSERT(fixed == 1);
String *res;
+ String password_buffer;
+ String *password;
+ DBUG_ASSERT(fixed == 1);
+
if (!(res=args[0]->val_str(str)))
{
null_value=1; /* purecov: inspected */
return 0; /* purecov: inspected */
}
+
+ if (!(password=args[1]->val_str(& password_buffer)))
+ {
+ null_value=1;
+ return 0;
+ }
+
null_value=0;
res=copy_if_not_alloced(str,res,res->length());
+ SQL_CRYPT sql_crypt(password->ptr());
sql_crypt.init();
sql_crypt.decode((char*) res->ptr(),res->length());
return res;
@@ -1822,9 +1844,19 @@ String *Item_func_soundex::val_str(Strin
** This should be 'internationalized' sometimes.
*/
-Item_func_format::Item_func_format(Item *org,int dec) :Item_str_func(org)
+const int FORMAT_MAX_DECIMALS= 30;
+
+Item_func_format::Item_func_format(Item *org, Item *dec)
+: Item_str_func(org, dec)
+{
+}
+
+void Item_func_format::fix_length_and_dec()
{
- decimals=(uint) set_zone(dec,0,30);
+ collation.set(default_charset());
+ uint char_length= args[0]->max_length/args[0]->collation.collation->mbmaxlen;
+ max_length= ((char_length + (char_length-args[0]->decimals)/3) *
+ collation.collation->mbmaxlen);
}
@@ -1835,10 +1867,25 @@ Item_func_format::Item_func_format(Item
String *Item_func_format::val_str(String *str)
{
- uint32 length, str_length ,dec;
+ uint32 length;
+ uint32 str_length;
+ /* Number of decimal digits */
+ int dec;
+ /* Number of characters used to represent the decimals, including '.' */
+ uint32 dec_length;
int diff;
DBUG_ASSERT(fixed == 1);
- dec= decimals ? decimals+1 : 0;
+
+ dec= args[1]->val_int();
+ if (args[1]->null_value)
+ {
+ null_value=1;
+ return NULL;
+ }
+
+ dec= set_zone(dec, 0, FORMAT_MAX_DECIMALS);
+ dec_length= dec ? dec+1 : 0;
+ null_value=0;
if (args[0]->result_type() == DECIMAL_RESULT ||
args[0]->result_type() == INT_RESULT)
@@ -1847,7 +1894,7 @@ String *Item_func_format::val_str(String
res= args[0]->val_decimal(&dec_val);
if ((null_value=args[0]->null_value))
return 0; /* purecov: inspected */
- my_decimal_round(E_DEC_FATAL_ERROR, res, decimals, false, &rnd_dec);
+ my_decimal_round(E_DEC_FATAL_ERROR, res, dec, false, &rnd_dec);
my_decimal2string(E_DEC_FATAL_ERROR, &rnd_dec, 0, 0, 0, str);
str_length= str->length();
if (rnd_dec.sign())
@@ -1858,9 +1905,9 @@ String *Item_func_format::val_str(String
double nr= args[0]->val_real();
if ((null_value=args[0]->null_value))
return 0; /* purecov: inspected */
- nr= my_double_round(nr, decimals, FALSE);
+ nr= my_double_round(nr, dec, FALSE);
/* Here default_charset() is right as this is not an automatic conversion */
- str->set_real(nr,decimals, default_charset());
+ str->set_real(nr, dec, default_charset());
if (isnan(nr))
return str;
str_length=str->length();
@@ -1868,13 +1915,13 @@ String *Item_func_format::val_str(String
str_length--; // Don't count sign
}
/* We need this test to handle 'nan' values */
- if (str_length >= dec+4)
+ if (str_length >= dec_length+4)
{
char *tmp,*pos;
- length= str->length()+(diff=((int)(str_length- dec-1))/3);
+ length= str->length()+(diff=((int)(str_length- dec_length-1))/3);
str= copy_if_not_alloced(&tmp_str,str,length);
str->length(length);
- tmp= (char*) str->ptr()+length - dec-1;
+ tmp= (char*) str->ptr()+length - dec_length-1;
for (pos= (char*) str->ptr()+length-1; pos != tmp; pos--)
pos[0]= pos[-diff];
while (diff)
@@ -1898,12 +1945,8 @@ void Item_func_format::print(String *str
{
str->append(STRING_WITH_LEN("format("));
args[0]->print(str);
- str->append(',');
- // my_charset_bin is good enough for numbers
- char buffer[20];
- String st(buffer, sizeof(buffer), &my_charset_bin);
- st.set((ulonglong)decimals, &my_charset_bin);
- str->append(st);
+ str->append(',');
+ args[1]->print(str);
str->append(')');
}
--- 1.120/sql/item_strfunc.h 2006-11-08 20:24:15 -07:00
+++ 1.121/sql/item_strfunc.h 2006-11-08 20:24:15 -07:00
@@ -360,11 +360,9 @@ public:
class Item_func_encode :public Item_str_func
{
- protected:
- SQL_CRYPT sql_crypt;
public:
- Item_func_encode(Item *a, char *seed):
- Item_str_func(a),sql_crypt(seed) {}
+ Item_func_encode(Item *a, Item *seed):
+ Item_str_func(a, seed) {}
String *val_str(String *);
void fix_length_and_dec();
const char *func_name() const { return "encode"; }
@@ -374,7 +372,7 @@ public:
class Item_func_decode :public Item_func_encode
{
public:
- Item_func_decode(Item *a, char *seed): Item_func_encode(a,seed) {}
+ Item_func_decode(Item *a, Item *seed): Item_func_encode(a, seed) {}
String *val_str(String *);
const char *func_name() const { return "decode"; }
};
@@ -507,15 +505,9 @@ class Item_func_format :public Item_str_
{
String tmp_str;
public:
- Item_func_format(Item *org,int dec);
+ Item_func_format(Item *org, Item *dec);
String *val_str(String *);
- void fix_length_and_dec()
- {
- collation.set(default_charset());
- uint char_length= args[0]->max_length/args[0]->collation.collation->mbmaxlen;
- max_length= ((char_length + (char_length-args[0]->decimals)/3) *
- collation.collation->mbmaxlen);
- }
+ void fix_length_and_dec();
const char *func_name() const { return "format"; }
void print(String *);
};
--- 1.1/mysql-test/r/parser.result 2006-11-08 20:24:15 -07:00
+++ 1.2/mysql-test/r/parser.result 2006-11-08 20:24:15 -07:00
@@ -296,11 +296,6 @@ select atan();
ERROR 42000: Incorrect parameter count in the call to native function 'atan'
select atan2(1, 2, 3);
ERROR 42000: Incorrect parameter count in the call to native function 'atan2'
-select benchmark(10, 1+1);
-benchmark(10, 1+1)
-0
-select benchmark(5+5, 2);
-ERROR 42000: Incorrect parameters in the call to native function 'BENCHMARK'
select concat();
ERROR 42000: Incorrect parameter count in the call to native function 'concat'
select concat("foo");
@@ -310,11 +305,6 @@ select concat_ws();
ERROR 42000: Incorrect parameter count in the call to native function 'concat_ws'
select concat_ws("foo");
ERROR 42000: Incorrect parameter count in the call to native function 'concat_ws'
-set @pwd="my password";
-select encode("secret", @pwd);
-ERROR 42000: Incorrect parameters in the call to native function 'ENCODE'
-select decode("encoded-secret", @pwd);
-ERROR 42000: Incorrect parameters in the call to native function 'DECODE'
select encrypt();
ERROR 42000: Incorrect parameter count in the call to native function 'encrypt'
select encrypt(1, 2, 3);
@@ -339,9 +329,6 @@ select field();
ERROR 42000: Incorrect parameter count in the call to native function 'field'
select field("p1");
ERROR 42000: Incorrect parameter count in the call to native function 'field'
-set @dec=2;
-select format(pi(), @dec);
-ERROR 42000: Incorrect parameters in the call to native function 'FORMAT'
select from_unixtime();
ERROR 42000: Incorrect parameter count in the call to native function 'from_unixtime'
select from_unixtime(1, 2, 3);
--- 1.1/mysql-test/t/parser.test 2006-11-08 20:24:15 -07:00
+++ 1.2/mysql-test/t/parser.test 2006-11-08 20:24:15 -07:00
@@ -399,11 +399,6 @@ select atan();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select atan2(1, 2, 3);
-select benchmark(10, 1+1);
-
--- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
-select benchmark(5+5, 2);
-
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select concat();
select concat("foo");
@@ -413,12 +408,6 @@ select concat_ws();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select concat_ws("foo");
-set @pwd="my password";
--- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
-select encode("secret", @pwd);
--- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
-select decode("encoded-secret", @pwd);
-
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select encrypt();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
@@ -447,10 +436,6 @@ select export_set("p1", "p2", "p3", "p4"
select field();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select field("p1");
-
-set @dec=2;
--- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
-select format(pi(), @dec);
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select from_unixtime();
--- 1.127/mysql-test/r/func_str.result 2006-11-08 20:24:15 -07:00
+++ 1.128/mysql-test/r/func_str.result 2006-11-08 20:24:15 -07:00
@@ -813,11 +813,231 @@ drop table t7;
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2) substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2)
1abcd;2abcd 3abcd;4abcd
-explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a
'),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
+explain extended select md5('hello');
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 md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `b
inary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace
('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")`
+Note 1003 select md5(_latin1'hello') AS `md5('hello')`
+explain extended select sha('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 sha(_latin1'abc') AS `sha('abc')`
+explain extended select sha1('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 sha(_latin1'abc') AS `sha1('abc')`
+explain extended select soundex('');
+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 soundex(_latin1'') AS `soundex('')`
+explain extended select 'mood' sounds like 'mud';
+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 (soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`
+explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');
+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 aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`
+explain extended select concat('*',space(5),'*');
+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 concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`
+explain extended select reverse('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 reverse(_latin1'abc') AS `reverse('abc')`
+explain extended select rpad('a',4,'1');
+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 rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`
+explain extended select lpad('a',4,'1');
+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 lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`
+explain extended select concat_ws(',','',NULL,'a');
+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 concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`
+explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');
+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 make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a', _latin2'b', _latin2'c')`
+explain extended select elt(2,1);
+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 elt(2,1) AS `elt(2,1)`
+explain extended select locate("a","b",2);
+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 locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`
+explain extended select format(130,10);
+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 format(130,10) AS `format(130,10)`
+explain extended select char(0);
+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 char(0) AS `char(0)`
+explain extended select conv(130,16,10);
+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 conv(130,16,10) AS `conv(130,16,10)`
+explain extended select hex(130);
+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 hex(130) AS `hex(130)`
+explain extended select binary 'HE';
+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 cast(_latin1'HE' as char charset binary) AS `binary 'HE'`
+explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');
+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 export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y', _latin2'n', _latin2' ')`
+explain extended select FIELD('b' COLLATE latin1_bin,'A','B');
+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 field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`
+explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');
+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 find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B', _latin1'a,b,c,d')`
+explain extended select collation(conv(130,16,10));
+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 collation(conv(130,16,10)) AS `collation(conv(130,16,10))`
+explain extended select coercibility(conv(130,16,10));
+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 coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`
+explain extended select length('\n\t\r\b\0\_\%\\');
+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 length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`
+explain extended select bit_length('\n\t\r\b\0\_\%\\');
+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 bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
+explain extended select bit_length('\n\t\r\b\0\_\%\\');
+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 bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
+explain extended select concat('monty',' was here ','again');
+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 concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`
+explain extended select length('hello');
+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 length(_latin1'hello') AS `length('hello')`
+explain extended select char(ascii('h'));
+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 char(ascii(_latin1'h')) AS `char(ascii('h'))`
+explain extended select ord('h');
+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 ord(_latin1'h') AS `ord('h')`
+explain extended select quote(1/0);
+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 quote((1 / 0)) AS `quote(1/0)`
+explain extended select crc32("123");
+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 crc32(_latin1'123') AS `crc32("123")`
+explain extended select replace('aaaa','a','b');
+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 replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`
+explain extended select insert('txs',2,1,'hi');
+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 insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`
+explain extended select left(_latin2'a',1);
+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 left(_latin2'a',1) AS `left(_latin2'a',1)`
+explain extended select right(_latin2'a',1);
+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 right(_latin2'a',1) AS `right(_latin2'a',1)`
+explain extended select lcase(_latin2'a');
+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 lcase(_latin2'a') AS `lcase(_latin2'a')`
+explain extended select ucase(_latin2'a');
+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 ucase(_latin2'a') AS `ucase(_latin2'a')`
+explain extended select SUBSTR('abcdefg',3,2);
+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 substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`
+explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
+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 substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`
+explain extended select trim(_latin2' a ');
+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(_latin2' a ') AS `trim(_latin2' a ')`
+explain extended select ltrim(_latin2' a ');
+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 ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`
+explain extended select rtrim(_latin2' a ');
+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(_latin2' a ') AS `rtrim(_latin2' a ')`
+explain extended select decode(encode(repeat("a",100000),"monty"),"monty");
+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 decode(encode(repeat(_latin1'a',100000),_latin1'monty'),_latin1'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")`
SELECT lpad(12345, 5, "#");
lpad(12345, 5, "#")
12345
@@ -1148,4 +1368,117 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
DROP TABLE t1,t2;
+select encode(NULL, NULL);
+encode(NULL, NULL)
+NULL
+select encode("data", NULL);
+encode("data", NULL)
+NULL
+select encode(NULL, "password");
+encode(NULL, "password")
+NULL
+select decode(NULL, NULL);
+decode(NULL, NULL)
+NULL
+select decode("data", NULL);
+decode("data", NULL)
+NULL
+select decode(NULL, "password");
+decode(NULL, "password")
+NULL
+select format(NULL, NULL);
+format(NULL, NULL)
+NULL
+select format(pi(), NULL);
+format(pi(), NULL)
+NULL
+select format(NULL, 2);
+format(NULL, 2)
+NULL
+select benchmark(NULL, NULL);
+benchmark(NULL, NULL)
+NULL
+select benchmark(0, NULL);
+benchmark(0, NULL)
+0
+select benchmark(100, NULL);
+benchmark(100, NULL)
+0
+select benchmark(NULL, 1+1);
+benchmark(NULL, 1+1)
+NULL
+set @password="password";
+set @my_data="clear text to encode";
+select md5(encode(@my_data, "password"));
+md5(encode(@my_data, "password"))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, _utf8 "password"));
+md5(encode(@my_data, _utf8 "password"))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, binary "password"));
+md5(encode(@my_data, binary "password"))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, _latin1 "password"));
+md5(encode(@my_data, _latin1 "password"))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, _koi8r "password"));
+md5(encode(@my_data, _koi8r "password"))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, (select "password" from dual)));
+md5(encode(@my_data, (select "password" from dual)))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, concat("pass", "word")));
+md5(encode(@my_data, concat("pass", "word")))
+44320fd2b4a0ec92faa2da2122def917
+select md5(encode(@my_data, @password));
+md5(encode(@my_data, @password))
+44320fd2b4a0ec92faa2da2122def917
+set @my_data="binary encoded data";
+select md5(decode(@my_data, "password"));
+md5(decode(@my_data, "password"))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, _utf8 "password"));
+md5(decode(@my_data, _utf8 "password"))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, binary "password"));
+md5(decode(@my_data, binary "password"))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, _latin1 "password"));
+md5(decode(@my_data, _latin1 "password"))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, _koi8r "password"));
+md5(decode(@my_data, _koi8r "password"))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, (select "password" from dual)));
+md5(decode(@my_data, (select "password" from dual)))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, concat("pass", "word")));
+md5(decode(@my_data, concat("pass", "word")))
+5bea8c394368dbc03b76684483b7756b
+select md5(decode(@my_data, @password));
+md5(decode(@my_data, @password))
+5bea8c394368dbc03b76684483b7756b
+set @dec=5;
+select format(pi(), (1+1));
+format(pi(), (1+1))
+3.14
+select format(pi(), (select 3 from dual));
+format(pi(), (select 3 from dual))
+3.142
+select format(pi(), @dec);
+format(pi(), @dec)
+3.14159
+set @bench_count=10;
+select benchmark(10, pi());
+benchmark(10, pi())
+0
+select benchmark(5+5, pi());
+benchmark(5+5, pi())
+0
+select benchmark((select 10 from dual), pi());
+benchmark((select 10 from dual), pi())
+0
+select benchmark(@bench_count, pi());
+benchmark(@bench_count, pi())
+0
End of 5.0 tests
--- 1.94/mysql-test/t/func_str.test 2006-11-08 20:24:15 -07:00
+++ 1.95/mysql-test/t/func_str.test 2006-11-08 20:24:15 -07:00
@@ -460,7 +460,51 @@ drop table t7;
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
-explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a
'),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
+explain extended select md5('hello');
+explain extended select sha('abc');
+explain extended select sha1('abc');
+explain extended select soundex('');
+explain extended select 'mood' sounds like 'mud';
+explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');
+explain extended select concat('*',space(5),'*');
+explain extended select reverse('abc');
+explain extended select rpad('a',4,'1');
+explain extended select lpad('a',4,'1');
+explain extended select concat_ws(',','',NULL,'a');
+explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');
+explain extended select elt(2,1);
+explain extended select locate("a","b",2);
+explain extended select format(130,10);
+explain extended select char(0);
+explain extended select conv(130,16,10);
+explain extended select hex(130);
+explain extended select binary 'HE';
+explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');
+explain extended select FIELD('b' COLLATE latin1_bin,'A','B');
+explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');
+explain extended select collation(conv(130,16,10));
+explain extended select coercibility(conv(130,16,10));
+explain extended select length('\n\t\r\b\0\_\%\\');
+explain extended select bit_length('\n\t\r\b\0\_\%\\');
+explain extended select bit_length('\n\t\r\b\0\_\%\\');
+explain extended select concat('monty',' was here ','again');
+explain extended select length('hello');
+explain extended select char(ascii('h'));
+explain extended select ord('h');
+explain extended select quote(1/0);
+explain extended select crc32("123");
+explain extended select replace('aaaa','a','b');
+explain extended select insert('txs',2,1,'hi');
+explain extended select left(_latin2'a',1);
+explain extended select right(_latin2'a',1);
+explain extended select lcase(_latin2'a');
+explain extended select ucase(_latin2'a');
+explain extended select SUBSTR('abcdefg',3,2);
+explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
+explain extended select trim(_latin2' a ');
+explain extended select ltrim(_latin2' a ');
+explain extended select rtrim(_latin2' a ');
+explain extended select decode(encode(repeat("a",100000),"monty"),"monty");
#
# lpad returns incorrect result (Bug #2182)
@@ -779,5 +823,68 @@ SELECT * FROM t1 INNER JOIN t2 ON code=i
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
DROP TABLE t1,t2;
+
+#
+# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions
+#
+
+select encode(NULL, NULL);
+select encode("data", NULL);
+select encode(NULL, "password");
+
+select decode(NULL, NULL);
+select decode("data", NULL);
+select decode(NULL, "password");
+
+select format(NULL, NULL);
+select format(pi(), NULL);
+select format(NULL, 2);
+
+select benchmark(NULL, NULL);
+select benchmark(0, NULL);
+select benchmark(100, NULL);
+select benchmark(NULL, 1+1);
+
+#
+# Please note:
+# 1) The collation of the password is irrelevant, the encryption uses
+# the binary representation of the string without charset/collation.
+# 2) These tests can not print the encoded text directly, because it's binary,
+# and doing this would cause problems with source control.
+# Instead, an md5() checksum is used, to verify the result indirectly.
+# 3) Each md5() result must be identical.
+# 4) The md5() result must never change, and must be stable across releases.
+#
+set @password="password";
+set @my_data="clear text to encode";
+select md5(encode(@my_data, "password"));
+select md5(encode(@my_data, _utf8 "password"));
+select md5(encode(@my_data, binary "password"));
+select md5(encode(@my_data, _latin1 "password"));
+select md5(encode(@my_data, _koi8r "password"));
+select md5(encode(@my_data, (select "password" from dual)));
+select md5(encode(@my_data, concat("pass", "word")));
+select md5(encode(@my_data, @password));
+
+set @my_data="binary encoded data";
+select md5(decode(@my_data, "password"));
+select md5(decode(@my_data, _utf8 "password"));
+select md5(decode(@my_data, binary "password"));
+select md5(decode(@my_data, _latin1 "password"));
+select md5(decode(@my_data, _koi8r "password"));
+select md5(decode(@my_data, (select "password" from dual)));
+select md5(decode(@my_data, concat("pass", "word")));
+select md5(decode(@my_data, @password));
+
+set @dec=5;
+select format(pi(), (1+1));
+select format(pi(), (select 3 from dual));
+select format(pi(), @dec);
+
+set @bench_count=10;
+select benchmark(10, pi());
+select benchmark(5+5, pi());
+select benchmark((select 10 from dual), pi());
+select benchmark(@bench_count, pi());
--echo End of 5.0 tests
--- 1.87/mysql-test/r/ps.result 2006-11-08 20:24:15 -07:00
+++ 1.88/mysql-test/r/ps.result 2006-11-08 20:24:15 -07:00
@@ -2391,3 +2391,49 @@ Level Code Message
Note 1051 Unknown table 't1'
Note 1051 Unknown table 't2'
deallocate prepare abc;
+set @my_password="password";
+set @my_data="clear text to encode";
+prepare stmt1 from 'select decode(encode(?, ?), ?)';
+execute stmt1 using @my_data, @my_password, @my_password;
+decode(encode(?, ?), ?)
+clear text to encode
+set @my_data="more text to encode";
+execute stmt1 using @my_data, @my_password, @my_password;
+decode(encode(?, ?), ?)
+more text to encode
+set @my_password="new password";
+execute stmt1 using @my_data, @my_password, @my_password;
+decode(encode(?, ?), ?)
+more text to encode
+deallocate prepare stmt1;
+set @to_format="123456789.123456789";
+set @dec=0;
+prepare stmt2 from 'select format(?, ?)';
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+123,456,789
+set @dec=4;
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+123,456,789.1235
+set @dec=6;
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+123,456,789.123457
+set @dec=2;
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+123,456,789.12
+set @to_format="100";
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+100.00
+set @to_format="1000000";
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+1,000,000.00
+set @to_format="10000";
+execute stmt2 using @to_format, @dec;
+format(?, ?)
+10,000.00
+deallocate prepare stmt2;
--- 1.86/mysql-test/t/ps.test 2006-11-08 20:24:15 -07:00
+++ 1.87/mysql-test/t/ps.test 2006-11-08 20:24:15 -07:00
@@ -2414,3 +2414,38 @@ execute abc;
drop table if exists t1, t2;
execute abc;
deallocate prepare abc;
+
+#
+# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions
+#
+
+set @my_password="password";
+set @my_data="clear text to encode";
+
+prepare stmt1 from 'select decode(encode(?, ?), ?)';
+execute stmt1 using @my_data, @my_password, @my_password;
+set @my_data="more text to encode";
+execute stmt1 using @my_data, @my_password, @my_password;
+set @my_password="new password";
+execute stmt1 using @my_data, @my_password, @my_password;
+deallocate prepare stmt1;
+
+set @to_format="123456789.123456789";
+set @dec=0;
+
+prepare stmt2 from 'select format(?, ?)';
+execute stmt2 using @to_format, @dec;
+set @dec=4;
+execute stmt2 using @to_format, @dec;
+set @dec=6;
+execute stmt2 using @to_format, @dec;
+set @dec=2;
+execute stmt2 using @to_format, @dec;
+set @to_format="100";
+execute stmt2 using @to_format, @dec;
+set @to_format="1000000";
+execute stmt2 using @to_format, @dec;
+set @to_format="10000";
+execute stmt2 using @to_format, @dec;
+deallocate prepare stmt2;
+
| Thread |
|---|
| • bk commit into 5.1 tree (malff:1.2343) BUG#22684 | marc.alff | 9 Nov |
