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

[30 Mar 2006 9:03] Balazs Odor
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;
[30 Mar 2006 9:04] Balazs Odor
CREATE TABLE and INSERT INTO data

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

[30 Mar 2006 11:41] Valeriy Kravchuk
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.
[13 Apr 2006 7:06] Ramil Kalimullin
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).
[13 Apr 2006 8:22] Balazs Odor
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?
[13 Apr 2006 8:43] Ramil Kalimullin
Balazs, did you use a debug build?
[13 Apr 2006 9:12] Balazs Odor
No, i didn't use a debug build.
[13 Apr 2006 9:53] Ramil Kalimullin
I've hit the bug.
Balazs, thanks a lot for your help!
[13 Apr 2006 11:19] 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:

  http://lists.mysql.com/commits/4914
[21 Apr 2006 13:47] Ramil Kalimullin
fixed in 4.1.19
[22 Apr 2006 22:09] Bob Marietta
Is this bug applicable to V5.0.20 also and was it patched?
[1 May 2006 0:03] Paul DuBois
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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.