MySQL Bugs: #18761: constant expression as UDF parameters not passed in as constant

Bug #18761	constant expression as UDF parameters not passed in as constant
Submitted:	3 Apr 2006 21:31	Modified:	11 Dec 2006 8:38
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Any (*)
Assigned to:	Chad MILLER	CPU Architecture:	Any

Description:
The UDF documentation says:

  [ http://dev.mysql.com/doc/refman/5.0/en/udf-arguments.html ]

  [...]
  For a constant argument i, args->args[i] points to the argument value. 
  (See below for instructions on how to access the value properly.) 
  For a non-constant argument, args->args[i] is 0. A constant argument is
  an expression that uses only constants, such as 3 or 4*7-2 or SIN(3.14).
  A non-constant argument is an expression that refers to values that may
  change from row to row, such as column names or functions that are called 
  with non-constant arguments.
  [...]

But when testing i only get argument values passed in for literal constants
like 42 or 'test', but not for simple expressions like 2+3 or calls to 
deterministic functions like UPPER('test') or SIN(3.14). 

Even worse: even a float constant like 0.5 or 3.14 is treated as non-constant...

How to repeat:
compile and install the attached UDF source using

  configure --with-mysql=/mysql/path/prefix 
  make
  cp .libs/constant.so /usr/lib    # or some other dir in the library search path

then run the included test case

  mysqltest --test-file=constant.test --result-file=constant.result --database=test

this will result in the following diffs between expeceted (as documented)
and actual result:

 SELECT const_check(3.14);
 const_check(3.14)
-check: constant
+check: not constant
 
 SELECT const_check(2+3);
 const_check(2+3)
-check: constant
+check: not constant
 
 SELECT const_check(SIN(3.14));
 const_check(SIN(3.14))
-check: constant
+check: not constant
 
 SELECT const_check(UPPER('test'));
 const_check(UPPER('test'))
-check: constant
+check: not constant

Suggested fix:
make it behave as documented

UDF source and test case files

Attachment: bug18761.tar.gz (application/x-gzip, text), 150.48 KiB.

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

ChangeSet@1.2333, 2006-11-03 16:28:40-05:00, cmiller@zippy.cornsilk.net +4 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions neglected 
  to take into account some things that could be parameters to a function, 
  namely some kinds of numbers and results of other functions (including the
  implicit ones of Sum for  K0+K1, e.g.).
  
  Added those and inserted a warning to go to the debug log, in case we add 
  more types and such in the future and neglect to account for them.  (I'm 
  wary of ASSERTing this to be true.)

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

ChangeSet@1.2333, 2006-11-03 16:50:32-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions neglected 
  to take into account some things that could be parameters to a function, 
  namely some kinds of numbers and results of other functions (including the
  implicit ones of Sum for  K0+K1, e.g.).
  
  Added those and inserted a warning to go to the debug log, in case we add 
  more types and such in the future and neglect to account for them.  (I'm 
  wary of ASSERTing this to be true.)

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

ChangeSet@1.2333, 2006-11-04 08:23:56-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions neglected 
  to take into account some things that could be parameters to a function, 
  namely some kinds of numbers and results of other functions (including the
  implicit ones of Sum for  K0+K1, e.g.).
  
  Added those and inserted a warning to go to the debug log, in case we add 
  more types and such in the future and neglect to account for them.  (I'm 
  wary of ASSERTing this to be true.)
  
  Also, try out Doxygen for the new UDF functions.

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

ChangeSet@1.2333, 2006-11-06 12:18:54-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions neglected 
  to take into account some things that could be parameters to a function, 
  namely some kinds of numbers and results of other functions (including the
  implicit ones of Sum for  K0+K1, e.g.).
  
  Added those and inserted a warning to go to the debug log, in case we add 
  more types and such in the future and neglect to account for them.  (I'm 
  wary of ASSERTing this to be true.)

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

ChangeSet@1.2333, 2006-11-06 12:26:35-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions was very
  old and analyzed the "Type" of the data that was passed into the UDF, when
  it really should analyze the "return_type", which is hard-coded for simple
  Items and works correctly for complex ones like functions.

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

ChangeSet@1.2333, 2006-11-06 16:20:28-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions was very
  old and analyzed the "Type" of the data that was passed into the UDF, when
  it really should analyze the "return_type", which is hard-coded for simple
  Items and works correctly for complex ones like functions.

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

ChangeSet@1.2333, 2006-11-07 11:14:06-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions was very
  old and analyzed the "Type" of the data that was passed into the UDF, when
  it really should analyze the "return_type", which is hard-coded for simple
  Items and works correctly for complex ones like functions.

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

ChangeSet@1.2333, 2006-11-13 13:13:44-05:00, cmiller@zippy.cornsilk.net +5 -0
  Bug#18761: constant expression as UDF parameters not passed in as constant
  
  The code that set up data to be passed to user-defined functions was very
  old and analyzed the "Type" of the data that was passed into the UDF, when
  it really should analyze the "return_type", which is hard-coded for simple
  Items and works correctly for complex ones like functions.
  ---
  Added test at Sergei's behest.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 5.0.32 and 5.1.14.