MySQL Bugs: #18643: crazy UNCOMPRESS()

Bug #18643	crazy UNCOMPRESS()
Submitted:	30 Mar 2006 9:03	Modified:	1 May 2006 0:03
Reporter:	Balazs Odor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0.21-BK, 5.0.18-log	OS:	Linux (Linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
> SELECT `id`, UNCOMPRESSED_LENGTH(`user_agent`), UNCOMPRESS(`user_agent`), UNCOMPRESSED_LENGTH(`param_others`), UNCOMPRESS(`param_others`) FROM `logs` ORDER BY `id` DESC;

In the result:
UNCOMPRESS(`user_agent`) = NULL WHERE `id` = 959835, but the value of `user_agent` is a COMPRESS() value!
From here on: (WHERE `id` < 959835)
UNCOMPRESS(`user_agent`) is right, but UNCOMPRESSED_LENGTH(`param_others`) = NULL and UNCOMPRESS(`param_others`) is a number (May be this is the value of UNCOMPRESSED_LENGTH(`param_others`) column!)

How to repeat:
(CREATE TABLE and INSERT INTO data attach the report!)

> SELECT `id`, UNCOMPRESSED_LENGTH(`user_agent`), UNCOMPRESS(`user_agent`), UNCOMPRESSED_LENGTH(`param_others`), UNCOMPRESS(`param_others`) FROM `logs` ORDER BY `id` DESC;

CREATE TABLE and INSERT INTO data

Attachment: table.sql (text/plain), 79.31 KiB.

Thank you for a bug report. Verified just as described on your data with 5.021-BK (ChangeSet@1.2120, 2006-03-30 08:13:49+02:00) on SuSE Linux:

mysql> SELECT `id`, UNCOMPRESSED_LENGTH(`user_agent`), UNCOMPRESS(`user_agent`)
, UNCOMPRESSED_LENGTH(`param_others`), UNCOMPRESS(`param_others`) FROM `logs` W
HERE `id` <= 959835 ORDER BY `id` DESC LIMIT 10\G
*************************** 1. row ***************************
                                 id: 959835
  UNCOMPRESSED_LENGTH(`user_agent`): NULL
           UNCOMPRESS(`user_agent`): NULL
UNCOMPRESSED_LENGTH(`param_others`): 316
         UNCOMPRESS(`param_others`): a:10:{s:5:"order";s:3:"Nйv";s:10:"order_typ
e";s:2:"AZ";s:10:"table_from";s:1:"0";s:10:"filter_nev";s:9:"Krisztina";s:21:"fi
lter_allando_lakcim";s:0:"";s:14:"filter_date_in";s:0:"";s:18:"table_pager_selec
t";s:1:"1";s:19:"display_size_change";s:4:"1024";s:19:"template_style_file";s:3:
"yes";s:9:"dump_send";s:3:"yes";}
*************************** 2. row ***************************
                                 id: 959834
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): 77
*************************** 3. row ***************************
                                 id: 959833
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
*************************** 4. row ***************************
                                 id: 959832
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): 87
*************************** 5. row ***************************
                                 id: 959831
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
*************************** 6. row ***************************
                                 id: 959830
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): 87
*************************** 7. row ***************************
                                 id: 959829
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
*************************** 8. row ***************************
                                 id: 959828
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
*************************** 9. row ***************************
                                 id: 959827
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
*************************** 10. row ***************************
                                 id: 959826
  UNCOMPRESSED_LENGTH(`user_agent`): 55
           UNCOMPRESS(`user_agent`): Mozilla/4.0 (compatible; MSIE 6.0; Windows
NT 5.1; SV1)
UNCOMPRESSED_LENGTH(`param_others`): NULL
         UNCOMPRESS(`param_others`): NULL
10 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.00 sec)

If your data is really created like you described, it is a bug.

Hi Balazs!

Your file (table.sql) contains NULL values, for instance:

mysql> select id, isnull(user_agent) from logs where id=959835;
+--------+--------------------+
| id     | isnull(user_agent) |
+--------+--------------------+
| 959835 | 1                  |
+--------+--------------------+

Looking at the file I see NULL in the record, indeed.
So we get NULLs using UNCOMPRESS() and UNCOMPRESSED_LENGTH() functions (the same for other records).

In reality `user_agent` IS NULL where id = 959835, but:

Query #1:
> SELECT `id`, UNCOMPRESSED_LENGTH(`user_agent`), UNCOMPRESS(`user_agent`), UNCOMPRESSED_LENGTH(`param_others`), UNCOMPRESS(`param_others`) FROM `logs` WHERE `id` <= 959835 ORDER BY `id` DESC limit 2;

Result #1:
UNCOMPRESSED_LENGTH(`param_others`) IS NULL AND UNCOMPRESS(`param_others`) = 77 WHERE `id` = 959834

Query #2:
> SELECT `id`, UNCOMPRESSED_LENGTH(`user_agent`), UNCOMPRESS(`user_agent`), UNCOMPRESSED_LENGTH(`param_others`), UNCOMPRESS(`param_others`) FROM `logs` where `id` = 959834;

Result #2:
UNCOMPRESSED_LENGTH(`param_others`) = 77 AND UNCOMPRESS(`param_others`) = 'a:3:{s:5:"order";s:3:"Név";s:10:"order_type";s:2:"AZ";s:10:"table_from";i:0;}' WHERE `id` = 959834

Why differing result #1 and #2?

Balazs, did you use a debug build?

No, i didn't use a debug build.

I've hit the bug.
Balazs, thanks a lot for your help!

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

fixed in 4.1.19

Is this bug applicable to V5.0.20 also and was it patched?

Noted in 4.1.19, 5.0.21 changelogs.

<literal>UNCOMPRESS(NULL)</literal> could cause subsequent
<literal>UNCOMPRESS()</literal> calls to return
<literal>NULL</literal> for legal non-<literal>NULL</literal>
arguments. (Bug #18643)