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
Triage: D3 (Medium)

[16 Dec 2007 7:33] Giuseppe Maxia
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
[16 Dec 2007 7:38] Giuseppe Maxia
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
[16 Dec 2007 17:13] Valeriy Kravchuk
Thank you for a bug report. Do you use a debug build?
[16 Dec 2007 18:49] Giuseppe Maxia
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"
[14 Jan 2008 12:59] Joerg Bruehe
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.
[14 Feb 2008 2:02] Giuseppe Maxia
subselect_notembedded test still failing using 5.1.23-RC binaries on Mac OS-X
[16 Feb 2008 16:59] Valeriy Kravchuk
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.
[20 Feb 2008 14:48] 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/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.
[29 Feb 2008 14:33] 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/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.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:43] Bugs System
Pushed into 5.0.60
[2 Apr 2008 15:41] Jon Stephens
Fix in tests only, no user changes to document. Closed.