Bug #6598 uservariable set to CAST(NULL AS SIGNED INTEGER) has wrong properties
Submitted: 12 Nov 2004 16:44 Modified: 30 Apr 2005 0:07
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:
Assigned to: Ramil Kalimullin CPU Architecture:Any

[12 Nov 2004 16:44] Matthias Leich
I expect that 
  "set @var_0= -1",  
  "set @var_1= CAST(-1 AS SIGNED INTEGER)" and 
produce uservariables with maybe not totally equal, but similar properties. 
These similarities should be visible, if I execute
- "SELECT @var_0, @var1, ..."   (Metadataoutput!!)   or
- "CREATE TABLE T1 AS SELECT @var_0 as f0, @var_1 as f1, ..."  or
I especially expect, that the metadata output/created columns differ from
strings/text columns.
But a uservariable produced via "SET @var=  CAST(NULL AS SIGNED INTEGER)"
shows the typical properties of the data type LONGTEXT.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1655, 2004-11-12
        Version 4.1 ChangeSet@1.2090.3.1, 2004-11-11 

How to repeat:
Please use my attached test file ml10.test , copy it to mysql-test/t
  ./mysql-test-run ml10

The attached ml10.reject contains my protocol with some comments.
[12 Nov 2004 16:46] Matthias Leich
test case

Attachment: ml10.test (application/octet-stream, text), 827 bytes.

[12 Nov 2004 17:10] Matthias Leich
my test result with comments

Attachment: ml10.reject (application/octet-stream, text), 2.18 KiB.

[30 Apr 2005 0:07] Michael Widenius
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 bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

The proposed patch fixes the problem but changes the current behavior
that setting a variable to NULL keeps the result type of the variable.

I did change the current patch slightly to keep the current behavior
but also support that one can affect the result type with CAST(NULL as integer)

Patch should be in 5.0.6
[30 Apr 2005 0:07] 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: