List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 7 1999 1:45am
Subject:bug report: ENCODE/DECODE syntax errors
View as plain text  
Bug report


  The function ENCODE() and DECODE() have syntax errors,
  if both patameters are table fields, or
  if at least one parameter is a function call.


  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'. */
   ('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


  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>
>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)
/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

	System: SunOS 5.6

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
bug report: ENCODE/DECODE syntax errorsMartin Ramsch7 Oct
  • bug report: ENCODE/DECODE syntax errorsMichael Widenius7 Oct