Bug #15100 View with IN and ORDER BY crashes server

• Submitted: 21 Nov 2005 14:34
• Modified: 4 Apr 2006 20:42
• Reporter: Hakan Küçükyılmaz
• Status: Can't repeat
• Category: MySQL Server: Views
• Severity: S2 (Serious)
• Version: 5.1.2-alpha-debug
• OS: Linux (Linux)
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[21 Nov 2005 14:34] Hakan Küçükyılmaz

Description:
View with IN and ORDER BY crashes server on local connection.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;

CREATE TABLE t1 (a int);
CREATE VIEW v1 AS
SELECT a FROM t1
  WHERE a IN(1)
  ORDER BY a;

SELECT * FROM v1; -- This works
SELECT * FROM v1; -- This crashes server

[15:21] hakan@test>SELECT * FROM v1; -- This crashes server
ERROR 2013 (HY000): Lost connection to MySQL server during query
5.1.2-alpha-debug-log

From remote connection it does not crash for me

Backtrace from err.log:
0x81a865d handle_segfault + 437
0xffffe420 _end + -141853488
0x910f1d0 _end + 10259072
0x825d1f7 check_grant(THD*, unsigned long, st_table_list*, unsigned int, unsigned int, bool) + 485
0x81c980b check_table_access(THD*, unsigned long, st_table_list*, bool) + 709
0x82c0636 Query_cache::send_result_to_client(THD*, char*, unsigned int) + 2492
0x81ca7d5 mysql_parse(THD*, char*, unsigned int) + 121
0x81bfab5 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 2041
0x81bf2b1 do_command(THD*) + 577
0x81be342 handle_one_connection + 836
0xb7e81aa7 _end + -1351371945
0xb7db1c2e _end + -1352223522

[22 Nov 2005 0:24] Jorge del Conde

Verified under FC4 with a recent 5.1 pull

[10 Mar 2006 17:06] rsi rsi

I have the same problem but only when I define a view with GROUP BY clause, no ORDER BY neither IN.

[4 Apr 2006 19:43] Evgeny Potemkin

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW v1 AS
    -> SELECT a FROM t1
    ->   WHERE a IN(1)
    ->   ORDER BY a;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from v1;
Empty set (0.00 sec)

mysql> select * from v1;
Empty set (0.00 sec)

mysql> select * from v1;
Empty set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
+------+
1 row in set (0.01 sec)

mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select a from t1 group by a;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> select * from v1;
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> delete from t1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from v1;
Empty set (0.00 sec)

mysql> select * from v1;
Empty set (0.00 sec)

mysql> select * from v1;
Empty set (0.00 sec)

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

[4 Apr 2006 20:42] Hakan Küçükyılmaz

Does not crash for me anymore in 5.1.9

[22:41] root@test>CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.21 sec)

5.1.9-beta-debug-log
[22:41] root@test>CREATE VIEW v1 AS
    -> SELECT a FROM t1
    ->   WHERE a IN(1)
    ->   ORDER BY a;
Query OK, 0 rows affected (0.01 sec)

5.1.9-beta-debug-log
[22:41] root@test>SELECT * FROM v1;
Empty set (0.00 sec)

5.1.9-beta-debug-log
[22:41] root@test>SELECT * FROM v1;
Empty set (0.00 sec)

5.1.9-beta-debug-log
[22:41] root@test>SELECT * FROM v1;
Empty set (0.00 sec)

Thanks, Hakan