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

[25 Sep 2006 21:25] Marc Alff
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
[26 Sep 2006 7:45] Sveta Smirnova
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.
[26 Sep 2006 14:27] Marc Alff
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
[19 Oct 2006 9:27] Shane Bester
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>
[26 Oct 2006 22:27] Marc Alff
Yes, the issue with prepared statements is also caused by the syntax.

Adding BENCHMARK() to the list.
[1 Nov 2006 21:09] Konstantin Osipov
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.
[9 Nov 2006 3:24] Bugs System
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.
[10 Nov 2006 0:28] Marc Alff
See related Bug#20253
[16 Nov 2006 14:35] Konstantin Osipov
Reviewed over IRC with minor remarks.
[16 Nov 2006 16:05] Bugs System
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.
[30 Nov 2006 1:36] Konstantin Osipov
Pushed into 5.1.13
[30 Nov 2006 2:12] Konstantin Osipov
This bug was fixed in 5.1 only, 5.1.13
[13 Dec 2006 19:50] Paul Dubois
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.
[13 Dec 2006 21:42] Marc Alff
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.
[13 Dec 2006 21:45] Marc Alff
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.
[13 Dec 2006 21:52] Paul Dubois
Okay, given that there will be no backport to 5.0,
I'll close this report.  Thanks.