Assertion failed: virtual bool drizzled::Item_in_subselect::exec(): Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)'

Bug #588408 reported by Patrick Crews on 2010-06-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Fix Released
Medium
Stewart Smith
Dexter
Fix Released
Medium
Stewart Smith

Bug Description

We're seeing this crash on certain queries with our randgen testing:
http://hudson.drizzle.org/job/drizzle-staging-randgen/324/console

This is MySQL bug (with a fix) - Bug #46680 Assertion failed in file item_subselect.cc, line 305 crashing on HAVING subquery
http://bugs.mysql.com/bug.php?id=46680

Test case (taken from the MySQL bug):
CREATE TABLE t1 (
  pk int NOT NULL AUTO_INCREMENT,
  varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
) AUTO_INCREMENT=101 ;

INSERT INTO t1 VALUES (1,'c'), (2, NULL);

CREATE TABLE t2 (
  pk int NOT NULL AUTO_INCREMENT,
  varchar_nokey varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk)
) AUTO_INCREMENT=21 ;

INSERT INTO t2 VALUES
(3,'m'),(4,NULL);

SELECT MIN(t2.pk)
FROM t2 JOIN t1 ON t1.pk=t2.pk
WHERE 'j'
HAVING ('m') IN (
SELECT varchar_nokey
FROM t2);

Related branches

Stewart Smith (stewart) on 2010-06-02
Changed in drizzle:
assignee: nobody → Stewart Smith (stewart-flamingspork)
milestone: none → 2010-06-07
status: New → Confirmed
Stewart Smith (stewart) wrote :

I made an attempt to port the patch over. Also the tests (also attached).

However... not sure if this is anything:

--- /home/stewart/drizzle/working/tests/r/bug588408.result 2010-06-02 06:50:31.242011816 +0300
+++ /home/stewart/drizzle/working/tests/r/bug588408.reject 2010-06-02 06:59:48.430702393 +0300
@@ -118,7 +118,7 @@
 id select_type table type possible_keys key key_len ref rows Extra
 1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+2 DERIVED empty1 ALL NULL NULL NULL NULL 1

 #
 # 5) Test that subquery materialization is setup for query with
@@ -142,8 +142,9 @@
 SELECT v
 FROM t2);
 id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using where; Using index
 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
 #
 # Cleanup for BUG#46680
 #

Stewart Smith (stewart) wrote :
Patrick Crews (patrick-crews) wrote :

I took a look at this as well and it appears that we arrive at the same result sets even if we don't hit the same query plans.

It is my understanding that differences in EXPLAIN plans can be acceptable as the code changes and it might be that we don't need these parts of the test.

The crash is corrected and we produce the same results. However, I'll beat up on the patch with a couple of additional optimizer grammars and post an update on how things went.

On Wed, 02 Jun 2010 15:18:47 -0000, Patrick Crews <email address hidden> wrote:
> The crash is corrected and we produce the same results. However, I'll
> beat up on the patch with a couple of additional optimizer grammars and
> post an update on how things went.

Great.

Perhaps this is one for a comparitive run... maybe involving
explain.... I'm still no optimiser expert :)
--
Stewart Smith

Patrick Crews (patrick-crews) wrote :

I ran through all of the optimizer/drizzle grammars + a couple of additional ones. No differences were reported, no crashes related to this patch were detected (if we saw a crash, both servers died).

I'm feeling comfortable with this despite the change in EXPLAIN output.../me has some negative thoughts on the labyrinthine optimizer code though.

If anyone wants additional testing, I can do an ExecutionTimeComparator run (checks execution time of two servers and reports if the diff is > user_set_threshold. We can also hack our grammars to see how much EXPLAIN output differs, but we're still getting the correct results for a fairly large range of queries.

Stewart Smith (stewart) on 2010-06-04
Changed in drizzle:
status: Confirmed → Fix Committed
importance: Undecided → Medium
Stewart Smith (stewart) on 2010-06-07
Changed in drizzle:
status: Fix Committed → Fix Released
status: Fix Released → Fix Committed
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers