Bug report
==========
>Description:
The function ENCODE() and DECODE() have syntax errors,
if both patameters are table fields, or
if at least one parameter is a function call.
>How-To-Repeat:
USE test;
CREATE TABLE logins (
username CHAR(16) NOT NULL PRIMARY KEY,
encdbpwd CHAR(16) NOT NULL, /* encoded database password */
encusrpwd CHAR(16) NOT NULL /* encoded user password */
);
/* insert some data with database password 'dbpw'. */
INSERT INTO logins VALUES
('martin', ENCODE('dbpw','martinspw'), ENCODE('martinspw','dbpw'))
,('peter', ENCODE('dbpw','peterspw' ), ENCODE('peterspw', 'dbpw'))
,('anna', ENCODE('dbpw','annaspw' ), ENCODE('annaspw', 'dbpw'));
/* testing the data with some selects ... */
SELECT DECODE(encusrpwd, 'dbpw')
FROM logins;
+---------------------------+
| DECODE(encusrpwd, 'dbpw') |
+---------------------------+
| martinspw |
| peterspw |
| annaspw |
+---------------------------+
SELECT DECODE(encdbpwd, 'martinspw')
FROM logins
WHERE username='martin';
+-------------------------------+
| DECODE(encdbpwd, 'martinspw') |
+-------------------------------+
| dbpw |
+-------------------------------+
/* Error example 1: */
SELECT DECODE( encdbpwd, DECODE(encusrpwd, 'dbpw') ) FROM logins;
==>
ERROR 1064: You have an error in your SQL syntax near 'DECODE(encusrpwd, 'dbpw') ) FROM
logins' at line 1
/* Error example 2: */
UPDATE logins
SET encdbpwd = ENCODE( 'new', DECODE(encusrpwd, 'dbpw') )
, encusrpwd = ENCODE( DECODE(encusrpwd, 'dbpw'), 'new' );
==>
ERROR 1064: You have an error in your SQL syntax near 'DECODE(encusrpwd, 'dbpw') )
, encusrpwd = ENCODE( DECODE(encusrpwd, 'dbpw')' at line 2
/* Error example 3: */
UPDATE logins SET encdbpwd = ENCODE(username, encusrpw);
==>
ERROR 1064: You have an error in your SQL syntax near 'encusrpw)' at line 1
>Fix:
No fix known.
Only work-around seems to be to calculate the string parameters in
advance and use ENCODE/DECODE with these string constants as
parameters only.
>Submitter-Id: Martin Ramsch <m.ramsch@stripped>
>Originator:
>Organization:
>MySQL support: none
>Synopsis: ENCODE/DECODE syntax errors with non-constant parameters
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.19b (yes)
>Server:
/public/bin/mysqladmin Ver 7.8 Distrib 3.22.19b, for sun-solaris2.6 on sparc
TCX Datakonsult AB, by Monty
Server version 3.22.19b
Protocol version 10
Connection mysql via TCP/IP
TCP port 3306
Uptime: 13 days 1 hour 42 min 47 sec
>Environment:
System: SunOS 5.6
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7
