~drizzle-trunk/drizzle/development : revision 377.1.4

149

`email` varchar(60) character set latin1 NOT NULL default '',

150

PRIMARY KEY (`pseudo`),

151

UNIQUE KEY `email` (`email`)

152

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

152

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

153

154

INSERT INTO t8 (pseudo,email) VALUES ('joce','test');

155

INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');

391

-- error 1096

392

SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);

393

394

CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;

394

CREATE TABLE t2 (id int default NULL, KEY id (id)) ENGINE=MyISAM;

395

INSERT INTO t2 VALUES (1),(2);

396

SELECT * FROM t2 WHERE id IN (SELECT 1);

397

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);

406

-- error 1093

407

INSERT INTO t2 VALUES ((SELECT id FROM t2));

408

SELECT * FROM t2;

409

CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;

409

CREATE TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;

410

INSERT INTO t1 values (1),(1);

411

-- error 1242

412

UPDATE t2 SET id=(SELECT * FROM t1);

521

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

522

KEY `pseudo` (`pseudo`,`date`,`topic`),

523

KEY `topic` (`topic`)

524

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

524

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

525

526

CREATE TABLE `t2` (

527

`mot` varchar(30) character set latin1 NOT NULL default '',

531

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

532

KEY `pseudo` (`pseudo`,`date`,`topic`),

533

KEY `topic` (`topic`)

534

) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

534

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

535

536

CREATE TABLE `t3` (

537

`numeropost` bigint unsigned NOT NULL auto_increment,

538

`maxnumrep` int unsigned NOT NULL default '0',

539

PRIMARY KEY (`numeropost`),

540

UNIQUE KEY `maxnumrep` (`maxnumrep`)

541

) ENGINE=MyISAM CHARSET=latin1;

541

) ENGINE=MyISAM;

542

INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

543

544

INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

644

CREATE TABLE `t1` (

645

`i` int NOT NULL default '0',

646

PRIMARY KEY (`i`)

647

) ENGINE=MyISAM CHARSET=latin1;

647

) ENGINE=MyISAM;

648

649

INSERT INTO t1 VALUES (1);

650

UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));

659

#

660

CREATE TABLE t1 (

661

id int default NULL

662

) ENGINE=MyISAM CHARSET=latin1;

662

) ENGINE=MyISAM;

663

INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);

664

CREATE TABLE t2 (

665

id int default NULL,

666

name varchar(15) default NULL

667

) ENGINE=MyISAM CHARSET=latin1;

667

) ENGINE=MyISAM;

668

669

INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');

670

update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);

726

PRIMARY KEY (`id`),

727

UNIQUE KEY `email` (`email`),

728

UNIQUE KEY `pseudo` (`pseudo`)

729

) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

729

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

730

INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');

731

SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);

732

drop table if exists t1;

899

# correct used_tables()

900

#

901

902

CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;

902

CREATE TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;

903

INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

904

CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint NOT NULL default '0',`secondary_uid` smallint NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;

904

CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint NOT NULL default '0',`secondary_uid` smallint NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;

905

INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);

906

CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` tinyint NOT NULL default '1',`tues` tinyint NOT NULL default '1',`wed` tinyint NOT NULL default '1',`thur` tinyint NOT NULL default '1',`fri` tinyint NOT NULL default '1',`sat` tinyint NOT NULL default '0',`sun` tinyint NOT NULL default '0',`how_often` smallint NOT NULL default '1',`userid` smallint NOT NULL default '0',`active` tinyint NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;

906

CREATE TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` tinyint NOT NULL default '1',`tues` tinyint NOT NULL default '1',`wed` tinyint NOT NULL default '1',`thur` tinyint NOT NULL default '1',`fri` tinyint NOT NULL default '1',`sat` tinyint NOT NULL default '0',`sun` tinyint NOT NULL default '0',`how_often` smallint NOT NULL default '1',`userid` smallint NOT NULL default '0',`active` tinyint NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;

907

INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);

908

CREATE TABLE `t4` (`task_id` smallint NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;

908

CREATE TABLE `t4` (`task_id` smallint NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;

909

INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');

910

select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;

911

SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;

914

#

915

# cardinality check

916

#

917

CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM CHARSET=latin1;

917

CREATE TABLE t1 (id int default NULL) ENGINE=MyISAM;

918

INSERT INTO t1 VALUES (1),(5);

919

CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM CHARSET=latin1;

919

CREATE TABLE t2 (id int default NULL) ENGINE=MyISAM;

920

INSERT INTO t2 VALUES (2),(6);

921

-- error 1241

922

select * from t1 where (1,2,6) in (select * from t2);

935

#

936

# filesort in subquery (restoring join_tab)

937

#

938

CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;

938

CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM;

939

INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

940

CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;

940

CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;

941

INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');

942

select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;

943

drop table t1, t2;

1277

#

1278

# Aggregate function comparation with ALL/ANY/SOME subselect

1279

#

1280

CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1280

CREATE TABLE `t1` ( `a` int default NULL) ENGINE=MyISAM;

1281

insert into t1 values (1);

1282

CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1282

CREATE TABLE `t2` ( `b` int default NULL, `a` int default NULL) ENGINE=MyISAM;

1283

insert into t2 values (1,2);

1284

select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);

1285

drop table t1,t2;

1592

`time` int unsigned NOT NULL default '0',

1593

`data` text collate latin1_general_ci NOT NULL,

1594

PRIMARY KEY (`itemid`)

1595

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1595

);

1596

INSERT INTO `t1` VALUES (1, 1, 1, '');

1597

CREATE TABLE `t2` (

1598

`sessionid` bigint unsigned NOT NULL auto_increment,

1600

`date` int unsigned NOT NULL default '0',

1601

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

1602

PRIMARY KEY (`sessionid`)

1603

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1603

);

1604

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

1605

SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

1606

drop tables t1,t2;