← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/optimizer.test

  • Committer: Patrick Crews
  • Date: 2010-07-10 21:56:22 UTC
  • mto: (1657.1.5 build)
  • mto: This revision was merged to the branch mainline in revision 1659.
  • Revision ID: gleebix@gmail.com-20100710215622-3dxgalnw3u1fy348
Added new test main.optimizer.test - added test cases for two recent optimizer bugs -  Bug#600664 and Bug#592473.  Home for optimizer bug fixes

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/optimizer.test
 
1
--echo Bug#592473 
 
2
--echo - Crash/segfault in drizzled::subselect_single_select_engine::exec (this=0x19a7008) at drizzled/item/subselect.cc:2204  
 
3
--disable_warnings
 
4
DROP TABLE IF EXISTS `aa`;
 
5
DROP TABLE IF EXISTS `bb`;
 
6
DROP TABLE IF EXISTS `cc`;
 
7
--enable_warnings
 
8
 
 
9
CREATE TABLE `aa` (
 
10
  `pk` int NOT NULL AUTO_INCREMENT,
 
11
  `col_char_not_null_key` varchar(1) NOT NULL,
 
12
  `col_int` int DEFAULT NULL,
 
13
  `col_bigint` bigint DEFAULT NULL,
 
14
  `col_bigint_key` bigint DEFAULT NULL,
 
15
  `col_int_not_null` int NOT NULL,
 
16
  `col_text_not_null` text NOT NULL,
 
17
  PRIMARY KEY (`pk`),
 
18
  KEY `col_char_not_null_key` (`col_char_not_null_key`),
 
19
  KEY `col_bigint_key` (`col_bigint_key`)
 
20
) ENGINE=InnoDB;
 
21
 
 
22
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,'');
 
23
 
 
24
CREATE TABLE `bb` (
 
25
  `col_int_key` int DEFAULT NULL,
 
26
  `pk` int NOT NULL AUTO_INCREMENT,
 
27
  `col_char` varchar(1) DEFAULT NULL,
 
28
  PRIMARY KEY (`pk`),
 
29
  KEY `col_int_key` (`col_int_key`)
 
30
) ENGINE=InnoDB;
 
31
 
 
32
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');
 
33
 
 
34
CREATE TABLE `cc` (
 
35
  `col_char` varchar(1) DEFAULT NULL,
 
36
  `pk` int NOT NULL AUTO_INCREMENT,
 
37
  PRIMARY KEY (`pk`)
 
38
) ENGINE=InnoDB;
 
39
 
 
40
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);
 
41
 
 
42
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` ;
 
43
 
 
44
DROP TABLE `aa`;
 
45
DROP TABLE `bb`;
 
46
DROP TABLE `cc`;
 
47
 
 
48
--echo End Bug#592473 test
 
49
--echo
 
50
 
 
51
--echo Bug#600664 
 
52
--echo - Crash / segfault in drizzled::optimizer::add_key_part (keyuse_array=<value optimized out>, key_field=0x2d0dc28) at drizzled/optimizer/key_field.cc:63
 
53
--disable_warnings
 
54
DROP TABLE IF EXISTS `bb`, `cc`;
 
55
--enable_warnings
 
56
 
 
57
CREATE TABLE `bb` (
 
58
  `col_text_not_null` text NOT NULL,
 
59
  `pk` int NOT NULL AUTO_INCREMENT,
 
60
  `col_int_not_null` int NOT NULL,
 
61
  `col_bigint` bigint DEFAULT NULL,
 
62
  `col_char_not_null_key` varchar(1) NOT NULL,
 
63
  `col_text` text,
 
64
  `col_text_key` text,
 
65
  PRIMARY KEY (`pk`),
 
66
  KEY `col_char_not_null_key` (`col_char_not_null_key`),
 
67
  KEY `col_text_key` (`col_text_key`(255))
 
68
) ENGINE=InnoDB;
 
69
 
 
70
INSERT INTO `bb` VALUES ('',1,-247005184,4657847914607935488,'q',NULL,NULL),('',2,9,6580884955495137280,'d',NULL,NULL),('',3,9,6,'o',NULL,NULL),('',4,8,NULL,'j',NULL,NULL),('',5,575668224,-7027022793581527040,'c',NULL,NULL),('',6,9,8,'o',NULL,NULL),('',7,1,NULL,'a',NULL,NULL),('',8,7,616430198996336640,'o',NULL,NULL),('',9,1,NULL,'u',NULL,NULL),('',10,130416640,6244803833302614016,'y',NULL,NULL);
 
71
 
 
72
CREATE TABLE `cc` (
 
73
  `pk` int NOT NULL AUTO_INCREMENT,
 
74
  `col_char_not_null_key` varchar(1) NOT NULL,
 
75
  `col_int_key` int DEFAULT NULL,
 
76
  `col_text_key` text,
 
77
  PRIMARY KEY (`pk`),
 
78
  KEY `col_char_not_null_key` (`col_char_not_null_key`),
 
79
  KEY `col_int_key` (`col_int_key`),
 
80
  KEY `col_text_key` (`col_text_key`(255))
 
81
) ENGINE=InnoDB;
 
82
 
 
83
INSERT INTO `cc` VALUES (1,'k',-1711407104,NULL),(2,'k',-1837629440,NULL),(3,'g',2,NULL),(4,'f',5,NULL),(5,'t',-173408256,NULL),(6,'s',-2063663104,NULL),(7,'l',4,NULL),(8,'q',NULL,NULL),(9,'h',-858587136,NULL),(10,'r',-1040056320,NULL),(11,'l',-1812332544,NULL),(12,'o',NULL,NULL),(13,'e',NULL,NULL),(14,'k',4,NULL),(15,'p',456261632,NULL),(16,'j',NULL,NULL),(17,'b',NULL,NULL),(18,'i',4,NULL),(19,'n',NULL,NULL),(20,'n',4,NULL),(21,'m',7,NULL),(22,'i',NULL,NULL),(23,'l',4,NULL),(24,'x',4,NULL),(25,'w',NULL,NULL),(26,'s',4,NULL),(27,'s',6,NULL),(28,'i',NULL,NULL),(29,'j',NULL,NULL),(30,'z',0,NULL),(31,'d',-84738048,NULL),(32,'s',-1456209920,NULL),(33,'o',-1366818816,NULL),(34,'t',2,NULL),(35,'t',3,NULL),(36,'o',5,NULL),(37,'i',NULL,NULL),(38,'v',779943936,NULL),(39,'w',NULL,NULL),(40,'f',7,NULL),(41,'n',1658912768,NULL),(42,'u',NULL,NULL),(43,'g',NULL,NULL),(44,'a',4,NULL),(45,'o',1,NULL),(46,'h',NULL,NULL),(47,'y',892862464,NULL),(48,'m',NULL,NULL),(49,'w',NULL,NULL),(50,'a',9,NULL),(51,'c',NULL,NULL),(52,'r',1758330880,NULL),(53,'s',7,NULL),(54,'n',3,NULL),(55,'e',1,NULL),(56,'c',NULL,NULL),(57,'i',1,NULL),(58,'h',NULL,NULL),(59,'n',1441005568,NULL),(60,'b',9,NULL),(61,'a',8,NULL),(62,'o',1389690880,NULL),(63,'c',2,NULL),(64,'g',7,NULL),(65,'r',0,NULL),(66,'i',2,NULL),(67,'w',NULL,NULL),(68,'s',NULL,NULL),(69,'f',NULL,NULL),(70,'l',1,NULL),(71,'t',-1545666560,NULL),(72,'f',NULL,NULL),(73,'y',1729363968,NULL),(74,'m',-39583744,NULL),(75,'m',5,NULL),(76,'r',-33554432,NULL),(77,'a',7,NULL),(78,'q',1687748608,NULL),(79,'h',NULL,NULL),(80,'x',524877824,NULL),(81,'t',NULL,NULL),(82,'o',NULL,NULL),(83,'a',NULL,NULL),(84,'j',4,NULL),(85,'o',0,NULL),(86,'l',1818296320,NULL),(87,'l',1,NULL),(88,'q',1346699264,NULL),(89,'t',NULL,NULL),(90,'f',1,NULL),(91,'g',584253440,NULL),(92,'s',670564352,NULL),(93,'t',5,NULL),(94,'a',-687013888,NULL),(95,'I',-1482424320,NULL),(96,'d',NULL,NULL),(97,'b',547291136,NULL),(98,'r',779091968,NULL),(99,'j',1062928384,NULL),(100,'s',NULL,NULL);
 
84
 
 
85
SELECT DISTINCT AVG(DISTINCT OUTR . `col_int_not_null` ) AS X FROM `bb` AS OUTR2 LEFT JOIN `bb` AS OUTR ON ( OUTR2 . `col_text` <= OUTR . `col_text_key` ) WHERE ( OUTR . `col_text_not_null` , OUTR . `col_char_not_null_key` ) IN ( SELECT DISTINCT INNR . `col_text_key` AS X , INNR . `col_char_not_null_key` AS Y FROM `cc` AS INNR WHERE INNR . `col_int_key` IS NULL ) AND OUTR . `pk` IS NULL OR NOT OUTR . `col_bigint` = 9 ORDER BY OUTR . `pk` , OUTR . `pk` ;
 
86
 
 
87
DROP TABLE `bb`, `cc`;
 
88
--echo End Bug#600664 test