Bug #2529 problem with subqueries and length(...) with InnoDB tables

• Submitted: 27 Jan 2004 1:38
• Modified: 27 Jan 2004 8:46
• Reporter: Zoltán Boros
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.1a-alpha-max-nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[27 Jan 2004 1:38] Zoltán Boros

Description:
Server crashes when the following select executed. The problem does not occur if you select a numeric field instead of length(txt).

How to repeat:
create table t1 (txt varchar(100)) type=InnoDB;
insert into table1 values('a');
insert into table1 values('bb');
insert into table1 values('cc');
select * from t1 where length(txt)<=(select avg(length(txt)) from t1);

[27 Jan 2004 8:46] Heikki Tuuri

Hi!

This may be one of the subquery bugs which were caused by MySQL too early releasing the 'intention' table lock on a table.

Testing with 4.1.2 on Linux I do not get a seg fault:

heikki@hundin:~/mysql-4.1/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.2-alpha-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table table1 (txt varchar(100)) type=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table1 values('bb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table1 values('cc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1 where length(txt)<=(select avg(length(txt)) from tab
le1);
+------+
| txt  |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql>

When 4.1.2 is released, please test again!

Best regards,

Heikki