Bug #23254 COMPRESS(NULL) makes all futher COMPRESS() calls on same Item return NULL
Submitted: 13 Oct 2006 10:12 Modified: 10 Nov 2006 20:39
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1-bk, 5.0-bk, 5.1-bk OS:Any (Any)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: compress, corruption

[13 Oct 2006 10:12] Domas Mituzas
Description:
If a trigger:

SET NEW.field=COMPRESS(NEW.field)

gets NULL value, all subsequent COMPRESS() calls in that trigger will return NULL, thus destroying all data that is being inserted.

How to repeat:
#
# Test of COMPRESS(NULL) side effects in triggers
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (
        a int primary key auto_increment,
        b longblob
) engine=InnoDB;

create trigger t1_insert before insert on t1 for each row set new.b=compress(new.b);

insert into t1 (b) values (NULL);
insert into t1 (b) values ('value');

select * from t1 where b is not null;

# There are no non-null entries :(

Suggested fix:
-
[13 Oct 2006 10:37] Andrey Hristov
5.1-bk seems unaffected.
mysql> create table t1 (
    ->         a int primary key auto_increment,
    ->         b longblob
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.33 sec)

mysql> create trigger t1_insert before insert on t1 for each row set
    -> new.b=compress(new.b);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (b) values (NULL);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 (b) values ('value');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1 where b is not null;
+---+-------------------+
| a | b                 |
+---+-------------------+
| 2 |    x+K�M j |
+---+-------------------+
1 row in set (0.03 sec)
[13 Oct 2006 10:38] Andrey Hristov
Reproducible on 5.0.27-bk, yesterday pull
[13 Oct 2006 10:45] Domas Mituzas
I reproduced it on today's pulls. Anyway, the patch is:

===== item_strfunc.cc 1.294 vs edited =====
--- 1.294/sql/item_strfunc.cc   2006-10-13 13:44:48 +03:00
+++ edited/item_strfunc.cc      2006-10-13 13:43:12 +03:00
@@ -2959,7 +2959,7 @@
   Byte *body;
   char *tmp, *last_char;
   DBUG_ASSERT(fixed == 1);
-
+  null_value= 0;
   if (!(res= args[0]->val_str(str)))
   {
     null_value= 1;
[13 Oct 2006 10:53] Domas Mituzas
A bit more comments on why the patch.

Triggers do cache Items, and though separate invocations to compress would have Items created each time (with null_value=0), for triggers subsequent operations reuse same variable, which was not reset.

FLUSH TABLES did reset the state, as triggers were reloaded, therefore this behavior disappeared after FLUSH TABLES, until next NULL value passed to COMPRESS.
[13 Oct 2006 11:13] Domas Mituzas
It does not require triggers to appear, simply SELECT COMPRESS(field) on multi-line resultset would do. 

Additionally, it is crash bug:

mysql> select compress(b) from ctest;
+---------------+
| compress(b)   |
+---------------+
|    x?K  b b | 
| NULL          | 
|    x?K  d d | 
+---------------+
3 rows in set (0.00 sec)

mysql> select compress(b) is null from ctest;
+---------------------+
| compress(b) is null |
+---------------------+
|                   0 | 
|                   1 | 
|                   1 | 
+---------------------+
3 rows in set (0.00 sec)

mysql> select compress(b) is null from ctest;
+---------------------+
| compress(b) is null |
+---------------------+
|                   0 | 
|                   1 | 
|                   1 | 
+---------------------+
3 rows in set (0.01 sec)

mysql> select a,b,compress(b),compress(b) is null from ctest;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 061013 14:13:18  mysqld restarted

mysql>
[13 Oct 2006 11:17] Domas Mituzas
backtrace for above crash (with current 4.1):

(gdb) bt
#0  0x9003d1dc in kill ()
#1  0x9010f2af in raise ()
#2  0x9010de02 in abort ()
#3  0x0031e743 in __eprintf () at tztime.cc:1673
#4  0x00059e7b in Protocol_simple::store_longlong (this=0x302b1f0, from=1, unsigned_flag=0) at protocol.cc:834
#5  0x00009459 in Item::send (this=0x3049548, protocol=0x302b1f0, buffer=0xb03e81cc) at item.cc:2451
#6  0x0005459e in select_send::send_data (this=0x3049688, items=@0x302ab94) at sql_class.cc:832
#7  0x000a27e2 in end_send (join=0x3049698, join_tab=0x304a4dc, end_of_records=0) at sql_select.cc:6776
#8  0x000a7f78 in sub_select (join=0x3049698, join_tab=0x304a398, end_of_records=0) at sql_select.cc:6195
#9  0x000ae3c7 in do_select (join=0x3049698, fields=0x302ab94, table=0x0, procedure=0x0) at sql_select.cc:6081
#10 0x000bbafc in JOIN::exec (this=0x3049698) at sql_select.cc:1584
#11 0x000bc145 in mysql_select (thd=0x302aa00, rref_pointer_array=0x302ac18, tables=0x3049620, wild_num=0, fields=@0x302ab94, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344, result=0x3049688, unit=0x302aa48, select_lex=0x302ab28) at sql_select.cc:1704
#12 0x000bc65c in handle_select (thd=0x302aa00, lex=0x302aa3c, result=0x3049688) at sql_select.cc:192
#13 0x00077708 in mysql_execute_command (thd=0x302aa00) at sql_parse.cc:2127
#14 0x0007c1d4 in mysql_parse (thd=0x302aa00, inBuf=0x3049210 "select a,b,compress(b),compress(b) is null from ctest", length=53) at sql_parse.cc:4368
#15 0x0007cacb in dispatch_command (command=COM_QUERY, thd=0x302aa00, packet=0x1feb001 "", packet_length=54) at sql_parse.cc:1530
#16 0x0007ddfa in do_command (thd=0x302aa00) at sql_parse.cc:1331
#17 0x0007e9b9 in handle_one_connection (arg=0x302aa00) at sql_parse.cc:1063
#18 0x90023d87 in _pthread_body ()
[13 Oct 2006 13:41] 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/13656

ChangeSet@1.2587, 2006-10-13 18:40:43+05:00, ramil@mysql.com +3 -0
  Fix for bug #23254: COMPRESS(NULL) makes all futher COMPRESS() calls on same Item return NULL
  
  We don't set null_value to 0 in the Item_func_compress::val_str() for 
  not-NULL results.
[13 Oct 2006 14:06] 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/13658

ChangeSet@1.2587, 2006-10-13 19:05:54+05:00, ramil@mysql.com +3 -0
  Fix for bug #23254: COMPRESS(NULL) makes all futher COMPRESS() calls on same Item return NULL
  
  We don't set null_value to 0 in the Item_func_compress::val_str() for 
  not-NULL results.
[10 Nov 2006 20:39] Paul Dubois
Noted in 4.1.23, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

If COMPRESS() returned NULL, subsequent invocations of COMPRESS()
within a result set or within a trigger also returned NULL.