Crash/segfault in drizzled::subselect_single_select_engine::exec (this=0x19a7008) at drizzled/item/subselect.cc:2204

Test case. Attempting to record this test will demonstrate the crash

DROP TABLE IF EXISTS `aa`;
CREATE TABLE `aa` (
  `pk` int NOT NULL AUTO_INCREMENT,
  `col_char_not_null_key` varchar(1) NOT NULL,
  `col_int` int DEFAULT NULL,
  `col_bigint` bigint DEFAULT NULL,
  `col_bigint_key` bigint DEFAULT NULL,
  `col_int_not_null` int NOT NULL,
  `col_text_not_null` text NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_char_not_null_key` (`col_char_not_null_key`),
  KEY `col_bigint_key` (`col_bigint_key`)
) ENGINE=InnoDB;

INSERT INTO `aa` VALUES (1,'a',NULL,2125417549142163456,1747959605373173760,9,''),(2,'n',733020160,5329728684016271360,-3664522721796030464,-2059927552,''),(3,'o',1023148032,4418875659380588544,9,1967128576,''),(4,'r',NULL,NULL,2,6,''),(5,'k',-15728640,6,-421930990089273344,-704249856,''),(6,'p',NULL,NULL,-2842897264777625600,90570752,''),(7,'t',2,-4747356957201924096,-4702602435904929792,1734213632,''),(8,'d',3,-7567454748865986560,NULL,9,''),(9,'t',1505558528,NULL,-5726608401178296320,-1584463872,''),(10,'q',NULL,NULL,541839330168012800,0,'');

DROP TABLE IF EXISTS `bb`;
CREATE TABLE `bb` (
  `col_int_key` int DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  `col_char` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB;

INSERT INTO `bb` VALUES (-622067712,1,'h'),(858587136,2,'p'),(-1110966272,3,'w'),(2,4,'f'),(546701312,5,'k'),(4,6,'m'),(6,7,'z'),(NULL,8,'h'),(2,9,'y'),(NULL,10,'t');

DROP TABLE IF EXISTS `cc`;
CREATE TABLE `cc` (
  `col_char` varchar(1) DEFAULT NULL,
  `pk` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

INSERT INTO `cc` VALUES ('h',1),('m',2),('m',3),('c',4),('h',5),('w',6),('a',7),('I',8),('i',9),('n',10),('s',11),('m',12),('e',13),('f',14),('u',15),('I',16),('v',17),('j',18),('h',19),('l',20),('y',21),('w',22),('n',23),('t',24),('t',25),('f',26),('f',27),('w',28),('y',29),('t',30),('j',31),('i',32),('j',33),('q',34),('s',35),('o',36),('b',37),('s',38),('h',39),('w',40),('g',41),('t',42),('o',43),('h',44),('n',45),('i',46),('y',47),('e',48),('t',49),('n',50),('b',51),('k',52),('h',53),('k',54),('x',55),('f',56),('h',57),('q',58),('b',59),('i',60),('w',61),('c',62),('r',63),('n',64),('v',65),('g',66),('t',67),('e',68),('w',69),('b',70),('y',71),('n',72),('p',73),('r',74),('o',75),('w',76),('y',77),('p',78),('o',79),('w',80),('t',81),('j',82),('o',83),('m',84),('g',85),('x',86),('v',87),('w',88),('m',89),('o',90),('o',91),('w',92),('f',93),('p',94),('I',95),('c',96),('t',97),('a',98),('w',99),('s',100);

SELECT DISTINCT MIN(DISTINCT OUTR . `col_bigint_key` ) AS X FROM AA AS OUTR WHERE ( OUTR . `col_bigint` , OUTR . `col_int_not_null` ) IN ( SELECT DISTINCT INNR . `pk` AS X , INNR . `col_int_key` AS Y FROM CC AS INNR2 LEFT JOIN BB AS INNR ON ( INNR2 . `col_char` <> INNR . `col_char` ) WHERE OUTR . `col_char_not_null_key` IS NULL ) AND OUTR . `col_int` IS NULL XOR OUTR . `col_char_not_null_key` IS NOT NULL HAVING X >= '2000-03-23 22:49:30' ORDER BY OUTR . `col_text_not_null` , OUTR . `pk` ;

DROP TABLE AA;
DROP TABLE BB;
DROP TABLE CC;

I am taking a look at this segfault.

In subselect_single_select_engine::exec,
join object seems to have bad data.
and we segfault while accessing one of its field which is NULL.
Need to look at Plan to understand the reason for this field to be NULL.
Here is some snippet from GDB session

(gdb) p this->join->join_tab
$17 = (drizzled::JoinTable *) 0x0
(gdb) p this->join
$16 = (class drizzled::Join *) 0x8feca78
(gdb) f
#0 0x0829af89 in drizzled::subselect_single_select_engine::exec (this=0x8fd9250) at drizzled/item/subselect.cc:2204
2204 in drizzled/item/subselect.cc

and Here is code snippet.

int subselect_single_select_engine::exec()
{
...............
...............
...............
       for (uint32_t i=join->const_tables ; i < join->tables ; i++)
      {
        JoinTable *tab=join->join_tab+i;
        if (tab && tab->keyuse) // --> Seg fault here
        {
          for (uint32_t key_part= 0;
               key_part < tab->ref.key_parts;
               key_part++)
....
}

Here is root cause:
In drizzled::subselect_single_select_engine::exec,
We invoke Join::optimize method, it correctly identifies "Impossible WHERE" for
inner subquery (I guess due to "`col_char_not_null_key` IS NULL" predicate),
and once it is identified, Join::optimize returns immediately. The code that
is left to executed is the code that initialises the member variable such as
join_tab.
and Later in drizzled::subselect_single_select_engine::exec, when we try to
execute the subquery, qe try access this field leading to seg fault.

Solution:
Once we have cond_value from Join::optimize, we can check if it is false
and simply return from that point, skipping other part of
drizzled::subselect_single_select_engine::exec.
I've attached this patch.

With this patch, query returns empty set, with warning
drizzle> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2000-03-23 22:49:30' |
+---------+------+---------------------------------------------------------+
1 row in set (0 sec)

QA, Can you see if this is expected result ?
Mysql QEE/Optimizer experts, can you have look at the patch and let me know
your review comments?

I'll take a look at the result and will also do some randgen testing of your
patch. This will compare patched and unpatched servers to see if / how many
queries' result sets differ, etc.

Will update the bug as this progresses. Thanks for the work on this.

On Fri, Jun 25, 2010 at 11:22 PM, Prafulla Tekawade <
<email address hidden>> wrote:

> Here is root cause:
> In drizzled::subselect_single_select_engine::exec,
> We invoke Join::optimize method, it correctly identifies "Impossible WHERE"
> for
> inner subquery (I guess due to "`col_char_not_null_key` IS NULL"
> predicate),
> and once it is identified, Join::optimize returns immediately. The code
> that
> is left to executed is the code that initialises the member variable such
> as
> join_tab.
> and Later in drizzled::subselect_single_select_engine::exec, when we try to
> execute the subquery, qe try access this field leading to seg fault.
>
> Solution:
> Once we have cond_value from Join::optimize, we can check if it is false
> and simply return from that point, skipping other part of
> drizzled::subselect_single_select_engine::exec.
> I've attached this patch.
>
> With this patch, query returns empty set, with warning
> drizzle> show warnings;
>
> +---------+------+---------------------------------------------------------+
> | Level | Code | Message
> |
>
> +---------+------+---------------------------------------------------------+
> | Warning | 1292 | Truncated incorrect DOUBLE value: '2000-03-23 22:49:30'
> |
>
> +---------+------+---------------------------------------------------------+
> 1 row in set (0 sec)
>
> QA, Can you see if this is expected result ?
> Mysql QEE/Optimizer experts, can you have look at the patch and let me know
> your review comments?
>
> ** Patch added: "Fix for review"
> http://launchpadlibrarian.net/50931620/drizzle-592473-1.patch
>
> --
> Crash/segfault in drizzled::subselect_single_select_engine::exec
> (this=0x19a7008) at drizzled/item/subselect.cc:2204
> https://bugs.launchpad.net/bugs/592473
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: Confirmed
>
> Bug description:
> The randgen test order_by_validator (using subquery_semijoin_drizzle.yy
> grammar) is failing due to this crash.
>
> Proper test-suite test case is coming
>
> Crashing query:
> SELECT DISTINCT MIN(DISTINCT OUTR . `col_bigint_key` ) AS X FROM AA AS OUTR
> WHERE ( OUTR . `col_bigint` , OUTR . `col_int_not_null` ) IN ( SELECT
> DISTINCT INNR . `pk` AS X , INNR . `col_int_key` AS Y FROM CC AS INNR2 LEFT
> JOIN BB AS INNR ON ( INNR2 . `col_char` <> INNR . `col_char` ) WHERE OUTR .
> `col_char_not_null_key` IS NULL ) AND OUTR . `col_int` IS NULL XOR OUTR .
> `col_char_not_null_key` IS NOT NULL HAVING X >= '2000-03-23 22:49:30' ORDER
> BY OUTR . `col_text_not_null` , OUTR . `pk`
>
>
> Backtrace:
>
> #0 0x0000000000573628 in drizzled::subselect_single_select_engine::exec
> (this=0x19a7008) at drizzled/item/subselect.cc:2204
> #1 0x0000000000575bb8 in drizzled::Item_subselect::exec (this=0x19a6ec8)
> at drizzled/item/subselect.cc:299
> #2 drizzled::Item_in_subselect::exec (thi...

Hi. I took a look at this / did some initial testing and it looks like subselect.test has broken.

The subquery below should return 1 per http://dev.mysql.com/doc/refman/5.1/en/all-subqueries.html (Postgres docs agree).
The case is that the subquery is returning an empty set (not NULL), which should evaluate to TRUE on comparison here (t1 contains the value 1).

Could you take a look at this and I'll retest when you're ready?

main.subselect [ fail ]
--- /patched/tests/r/subselect.result 2010-06-26 20:41:50.616221000 +0300
+++ /patched/tests/r/subselect.reject 2010-06-27 00:14:26.775425019 +0300
@@ -2804,7 +2804,6 @@
 1
 SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
 f1
-1
 INSERT INTO t2 VALUES (1);
 INSERT INTO t2 VALUES (2);
 SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);

drizzletest: Result content mismatch

Hi Patrick,
Thanks for testing my patch.
I have fixed this issue in my local view.
Could you please let me know what all tests I can run on my machine itself that covers subselect and the kind of queries that this bug has ?
That will reduce the time it takes to fix regression if any caused by my patch.

Currenly both the bug tests and subselect test passes in my view.

Currently both the bug tests and subselect test passes in my view.

Hi Prafulla,

No problem.
I would recommend running the following tests (the test suite subselect tests):
 ./test-run --force subselect2 subselect_mat subselect_sj subselect3 subselect_notembedded subselect subselect_innodb subselect_sj2

I also checked out the result your patch is returning and it is correct / matches with MySQL.

As for what types of queries, it is hard to say. We hit this bug with the randgen (Random Query Generator) and the query simplification facility isn't working as it should with Drizzle just yet (it's much harder to pinpoint vital characteristics). So we hit this with a bit of a shotgun approach. We were using the subquery_semijoin_drizzle grammar, which was designed to produce queries with semijoin subqueries : )

If you feel crazy / brave enough to try the randgen, please let me know and I'll provide command lines. Otherwise, if you could link your branch to this bug, I can run your patch through our randgen tests in Hudson. I'll also run some comparison tests of your patched server versus an unpatched server with the randgen on my machine.

Hi Patrick,
I will run following tests and will let you know branch location.

 ./test-run --force subselect2 subselect_mat subselect_sj subselect3 subselect_notembedded subselect subselect_innodb subselect_sj2

Thanks

Not crashing in MySQL 5.1

Hi Patrick,
I have pushed a correct fix at this branch.
http://bazaar.launchpad.net/~prafulla-tekawade/drizzle/dr-bug-592473

and I've tested testcases that you have mentioned. They are all passing.
Bug test case is also passing.
Can you run other tests with it ?

Hi Prafulla,

Thanks very much for this. I'll be taking a look at this soon and will
update the bug with my findings.
Please expect to see something tomorrow.

On Wed, Jun 30, 2010 at 1:02 PM, Prafulla Tekawade <
<email address hidden>> wrote:

> Hi Patrick,
> I have pushed a correct fix at this branch.
> http://bazaar.launchpad.net/~prafulla-tekawade/drizzle/dr-bug-592473
>
> and I've tested testcases that you have mentioned. They are all passing.
> Bug test case is also passing.
> Can you run other tests with it ?
>
> --
> Crash/segfault in drizzled::subselect_single_select_engine::exec
> (this=0x19a7008) at drizzled/item/subselect.cc:2204
> https://bugs.launchpad.net/bugs/592473
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: In Progress
>
> Bug description:
> The randgen test order_by_validator (using subquery_semijoin_drizzle.yy
> grammar) is failing due to this crash.
>
> Proper test-suite test case is coming
>
> Crashing query:
> SELECT DISTINCT MIN(DISTINCT OUTR . `col_bigint_key` ) AS X FROM AA AS OUTR
> WHERE ( OUTR . `col_bigint` , OUTR . `col_int_not_null` ) IN ( SELECT
> DISTINCT INNR . `pk` AS X , INNR . `col_int_key` AS Y FROM CC AS INNR2 LEFT
> JOIN BB AS INNR ON ( INNR2 . `col_char` <> INNR . `col_char` ) WHERE OUTR .
> `col_char_not_null_key` IS NULL ) AND OUTR . `col_int` IS NULL XOR OUTR .
> `col_char_not_null_key` IS NOT NULL HAVING X >= '2000-03-23 22:49:30' ORDER
> BY OUTR . `col_text_not_null` , OUTR . `pk`
>
>
> Backtrace:
>
> #0 0x0000000000573628 in drizzled::subselect_single_select_engine::exec
> (this=0x19a7008) at drizzled/item/subselect.cc:2204
> #1 0x0000000000575bb8 in drizzled::Item_subselect::exec (this=0x19a6ec8)
> at drizzled/item/subselect.cc:299
> #2 drizzled::Item_in_subselect::exec (this=0x19a6ec8) at
> drizzled/item/subselect.cc:348
> #3 0x0000000000572fd7 in drizzled::Item_in_subselect::val_bool
> (this=0x19a6ec8) at drizzled/item/subselect.cc:907
> #4 0x0000000000550c8c in drizzled::Item_in_optimizer::val_int
> (this=0x1abd128) at drizzled/item/cmpfunc.cc:1597
> #5 0x000000000054901c in drizzled::Item::val_bool (this=0x1abd128) at
> drizzled/item.cc:94
> #6 0x00000000005501f9 in drizzled::Item_cond_and::val_int (this=0x19a7208)
> at drizzled/item/cmpfunc.cc:4203
> #7 0x000000000054ffad in drizzled::Item_cond_xor::val_int (this=0x1a773e0)
> at drizzled/item/cmpfunc.cc:4718
> #8 0x0000000000588584 in drizzled::evaluate_join_record (join=0x1a77b28,
> join_tab=0x1ab1b70, error=<value optimized out>) at drizzled/join.cc:2479
> #9 0x00000000006134ee in drizzled::sub_select (join=0x1a77b28,
> join_tab=0x1ab27e8, end_of_records=<value optimized out>) at
> drizzled/sql_select.cc:3564
> #10 0x00000000006185e0 in drizzled::do_select (join=0x1a77b28,
> fields=0x1a791a0, table=0x0) at drizzled/sql_select.cc:3333
> #11 0x000000000058dfbc in drizzled::Join::exec (this=0x1a77b28) at
> drizzled/join.cc:1689
> #12 0x0000000000614822 in drizzled::mysql_select (session=0x7f2eb814a090,
> rref_pointer_array=<value optimized out>, tables=0x19a5968, wild_num=0,
> fields=<value optimized out>,
> conds=...

I have tested this patch as well.

I did result set comparisons of patched vs. unpatched server with several grammars.
No differences were found.
It should be noted that we are unable to have lengthy runs of the randgen due to other crashes, but I did several iterations with seed=time (randomizes queries + data) and nothing was found.

The ExecutionTimeComparator was also tested (compares execution times for the same query against two servers) with the same grammars - no differences reported.

The test case no longer fails / crashes and matches MySQL. It's looking good.

I recommend proposing a merge / will propose one.

Thanks for the work on this, Prafulla.