MySQL Bugs: #22684: BENCHMARK, ENCODE, DECODE and FORMAT are not real functions

Bug #22684	BENCHMARK, ENCODE, DECODE and FORMAT are not real functions
Submitted:	25 Sep 2006 21:25	Modified:	13 Dec 2006 21:52
Reporter:	Marc ALFF	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1, 5.0.26	OS:	Linux (Linux)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
The "functions" ENCODE, DECODE and FORMAT accept only a litteral constant for
some of the parameters:
- TEXT_STRING_literal for the second parameter in ENCODE/DECODE
- NUM for the first parameter in FORMAT

Passing a variable, or an expression in general, is not possible,
making the usefulness of the "function" questionable,
as only hard coded constants are allowed.

How to repeat:
set pwd="xxx";
set dec=2;
select ENCODE("foo", pwd), DECODE("foo", pwd), FORMAT(dec, pi());

Suggested fix:
Allow to pass any expression as parameter

Thank you for the report.

Verified on Linux using last BK sources:
mysql> set @pwd="xxx";
Query OK, 0 rows affected (0.00 sec)

mysql> set @dec=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select ENCODE("foo", "xxx");
+----------------------+
| ENCODE("foo", "xxx") |
+----------------------+
| !Ь┴                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select DECODE("foo", "xxx");
+----------------------+
| DECODE("foo", "xxx") |
+----------------------+
| KР                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select ENCODE("foo", @pwd);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@pwd)' at line 1

mysql> select DECODE("foo", @pwd);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@pwd)' at line 1

mysql> select FORMAT(2,3.14);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3.14)' at line 1

mysql> select FORMAT(@dec, 3);
+-----------------+
| FORMAT(@dec, 3) |
+-----------------+
| 2.000           |
+-----------------+
1 row in set (0.00 sec)

As you see only first 2 functions are affected.

The report was incorrect about format, it's not the first but the second paramater which is affected

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.12-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)

mysql> select format(pi(), 2);
+-----------------+
| format(pi(), 2) |
+-----------------+
| 3.14            |
+-----------------+
1 row in set (0.00 sec)

mysql> select format(pi(), @dec);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@dec)' at line 1

Also, I found these cannot be prepared either:

###works:
mysql> prepare stmt from "select encode('string','password')";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

###works:
mysql> prepare stmt from "select encode(?,'password')";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

###doesn't work:
mysql> prepare stmt from "select encode('string',?)";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1
mysql>

Yes, the issue with prepared statements is also caused by the syntax.

Adding BENCHMARK() to the list.

Hi Timothy, I'm stealing this, in his patch for Bug#21114 Marc introduces a change that I would like to go away quickly, and fixing this bug is a way to remove the change.

Thanks.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15058

ChangeSet@1.2343, 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.

See related Bug#20253

Reviewed over IRC with minor remarks.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15436

ChangeSet@1.2343, 2006-11-16 09:03:47-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.
  
  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.

Pushed into 5.1.13

This bug was fixed in 5.1 only, 5.1.13

Noted in 5.1.14 changelog.

BENCHMARK, ENCODE, DECODE and FORMAT could only accept a constant for
some parameters, and could not be used in prepared statements.

Setting report to NDI pending backport of patch to 5.0.x.

This fix is available in 5.1 only, starting with 5.1.14.

For the 5.0 release, this fix will not be back ported, for two main reasons:

1) From a technical point of view, the implementation relies on infrastructure
changes introduced by Bug#21114, which includes non compatible changes and
can not be back ported in 5.0 without risk for existing applications in production.

2) From the use case point of view, the limitations of accepting certain
parameters as constants instead of accepting general parameters did exist
since the functions were introduced, so at no point in time was a regression
introduced in 5.0, that would justify backporting the code.
What this fix does is enhance the functions by removing existing limitations,
and implements a behavior more consistent with other functions.

And before starting a debate as to whether this report is a bug or a feature,
what this fix *really* does is remove ugly code and clean up
the implementation, so it's more maintainable.

Okay, given that there will be no backport to 5.0,
I'll close this report.  Thanks.