MySQL Bugs: #33266: regression test subselect fails

Bug #33266	regression test subselect fails
Submitted:	16 Dec 2007 7:33	Modified:	2 Apr 2008 15:41
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S7 (Test Cases)
Version:	5.0.54,5.1.23, 5.1.24-BK, 6.0-BK	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	regression, subselect, test

Description:
Regression test subselect fails in 5.0.54 (ChangeSet@1.2572.1.12, 2007-12-14 16:58:12+01:00)

subselect                      [ fail ]

mysqltest: At line 3010: query 'select sum(a),a from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1
)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1) 
group by a' failed: 1436: Thread stack overrun:  185852 bytes used of a 196608 byte stack, and 24000 bytes needed.  Use 'mysqld -O thread_stack=#' to specify a bigger stack.

The result from queries just before the failure was:
< snip >
ERROR 21000: Subquery returns more than 1 row
SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
(SELECT SUM(t1.a) FROM t2 WHERE a=1)
3
DROP TABLE t1,t2;
create table t1(a int,b int,key(a),key(b));
insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
(6,7),(7,4),(5,3);
select sum(a),a from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1
)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1) 
group by a;

How to repeat:
./mtr subselect

in 5.1.23 (ChangeSet@1.2635.1.11, 2007-12-14 18:03:44+01:00) the same error occurs in test subselect_notembedded

Thank you for a bug report. Do you use a debug build?

Tested with non-debug builds.

For all versions:
* on Linux, I used ./BUILD/compile-pentium-max
* on Mac OS X, the following script (which is the compile-pentium-max 
  script with the Pentium specific options removed)

#! /bin/sh

path=`dirname $0`
. "$path/SETUP.sh"

extra_flags=" $fast_cflags $max_cflags -g"
extra_configs=" $max_configs"

. "$path/FINISH.sh"

This also occurred in the 5.0.54 release build but went unreported (it seems),
and I have it in the 5.0.54a release builds as well.

subselect_notembedded test still failing using 5.1.23-RC binaries on Mac OS-X

Verified just as described with latest 5..24-BK on 32-bit SuSE 9:

openxs@linux:~/dbs/5.1/mysql-test> ./mysql-test-run.pl subselect
Logging: ./mysql-test-run.pl subselect
MySQL Version 5.1.24
Using dynamic switching of binlog format
Using ndbcluster when necessary, mysqld supports it
Skipping SSL, mysqld not compiled with SSL
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests
 will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.subselect                 [ pass ]          46395
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 46.395 of 50 seconds executing testcases

openxs@linux:~/dbs/5.1/mysql-test> ./mysql-test-run.pl subselect_notembedded
Logging: ./mysql-test-run.pl subselect_notembedded
MySQL Version 5.1.24
Using dynamic switching of binlog format
Using ndbcluster when necessary, mysqld supports it
Skipping SSL, mysqld not compiled with SSL
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests
 will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.subselect_notembedded     [ fail ]

mysqltest: At line 18: query 'select sum(a),a from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1
)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1)
group by a' failed: 1436: Thread stack overrun:  173840 bytes used of a 196608 b
yte stack, and 24000 bytes needed.  Use 'mysqld -O thread_stack=#' to specify a
bigger stack.

The result from queries just before the failure was:
purge master logs before (select adddate(current_timestamp(), interval -4 day));

ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'select addda
te(current_timestamp(), interval -4 day))' at line 1
purge master logs before adddate(current_timestamp(), interval -4 day);
create table t1(a int,b int,key(a),key(b));
insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5),
(6,7),(7,4),(5,3);
select sum(a),a from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1 where a> (
select sum(a) from t1 where a> ( select sum(a) from t1
)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1
)group by b limit 1)group by b limit 1)group by b limit 1)
group by a;

More results from queries before failure can be found in /home/openxs/dbs/5.1/my
sql-test/var/log/subselect_notembedded.log

Aborting: main.subselect_notembedded failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers

So, subselect_notembedded still fails.

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

ChangeSet@1.2583, 2008-02-20 17:41:39+03:00, evgen@moonbone.local +2 -0
  Bug#33266: Incorrect test case for the bug#31048 failing on some platforms.
  
  The test case for the bug#31048 checks that there is no crash on stack
  overrun. But due to different stack sizes on different platforms it failed
  on some of them.
  
  The new test case check that a query with at least 4 level subquery nesting
  works without the stack overrun nesting and other levels of nesting doesn't
  cause a crash.

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

ChangeSet@1.2599, 2008-02-29 16:32:06+02:00, gkodinov@magare.gmz +5 -0
  Bug #33266: suspend the test case for 6.0 until proper merge is done.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Fix in tests only, no user changes to document. Closed.