~drizzle-trunk/drizzle/development

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 AUTO_INCREMENT=2147483647;

904

CREATE TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int 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 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` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int 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;

908

CREATE TABLE `t4` (`task_id` int 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;

976

977

CREATE TABLE `t1` (

978

`id` int NOT NULL auto_increment,

979

`id_cns` tinyint unsigned NOT NULL default '0',

979

`id_cns` int NOT NULL default '0',

980

`tipo` enum('','UNO','DUE') NOT NULL default '',

981

`anno_dep` smallint unsigned NOT NULL default '0',

982

`particolare` bigint unsigned NOT NULL default '0',

983

`generale` bigint unsigned NOT NULL default '0',

984

`bis` tinyint unsigned NOT NULL default '0',

981

`anno_dep` int NOT NULL default '0',

982

`particolare` bigint NOT NULL default '0',

983

`generale` bigint NOT NULL default '0',

984

`bis` int NOT NULL default '0',

985

PRIMARY KEY (`id`),

986

UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),

987

UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)

988

);

989

INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);

990

CREATE TABLE `t2` (

991

`id` tinyint unsigned NOT NULL auto_increment,

992

`max_anno_dep` smallint unsigned NOT NULL default '0',

991

`id` int NOT NULL auto_increment,

992

`max_anno_dep` int NOT NULL default '0',

993

PRIMARY KEY (`id`)

994

);

995

INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);

1287

1288

# BUG#5003 - like in subselect

1289

1290

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);

1290

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);

1291

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

1292

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');

1293

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');

1333

# Test of correct maybe_null flag returning by subquwery for temporary table

1334

# creation

1335

1336

CREATE TABLE `t1` ( `master` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `slave` int unsigned NOT NULL default '0', `access` int unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));

1336

CREATE TABLE `t1` ( `master` int NOT NULL default '0', `map` int NOT NULL default '0', `slave` int NOT NULL default '0', `access` int NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`));

1337

INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400);

1338

CREATE TABLE `t2` ( `id` int unsigned NOT NULL default '0', `pid` int unsigned NOT NULL default '0', `map` smallint unsigned NOT NULL default '0', `level` tinyint unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;

1338

CREATE TABLE `t2` ( `id` int NOT NULL default '0', `pid` int NOT NULL default '0', `map` int NOT NULL default '0', `level` int NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ;

1339

INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a');

1340

-- error 1054

1341

SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b;

1385

Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',

1386

Region char(26) NOT NULL default '',

1387

SurfaceArea float(10,2) NOT NULL default '0.00',

1388

IndepYear smallint default NULL,

1388

IndepYear int default NULL,

1389

Population int NOT NULL default '0',

1390

LifeExpectancy float(3,1) default NULL,

1391

GNP float(10,2) default NULL,

1587

# Item_int_with_ref check (BUG#10020)

1588

1589

#CREATE TABLE `t1` (

1590

# `itemid` bigint unsigned NOT NULL auto_increment,

1591

# `sessionid` bigint unsigned default NULL,

1592

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

1590

# `itemid` bigint NOT NULL auto_increment,

1591

# `sessionid` bigint default NULL,

1592

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

1593

# `data` text collate latin1_general_ci NOT NULL,

1594

# PRIMARY KEY (`itemid`)

1595

#);

1596

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

1597

#CREATE TABLE `t2` (

1598

# `sessionid` bigint unsigned NOT NULL auto_increment,

1599

# `pid` int unsigned NOT NULL default '0',

1600

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

1598

# `sessionid` bigint NOT NULL auto_increment,

1599

# `pid` int NOT NULL default '0',

1600

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

1601

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

1602

# PRIMARY KEY (`sessionid`)

1603

#);

1708

1709

create table t1 (

1710

retailerID varchar(8) NOT NULL,

1711

statusID int unsigned NOT NULL,

1711

statusID int NOT NULL,

1712

changed datetime NOT NULL,

1713

UNIQUE KEY retailerID (retailerID, statusID, changed)

1714

);

2132

2133

# Bug#19700: subselect returning BIGINT always returned it as SIGNED

2134

2135

CREATE TABLE t1 (i BIGINT UNSIGNED);

2136

INSERT INTO t1 VALUES (10000000000000000000); # > MAX SIGNED BIGINT 9323372036854775807

2135

CREATE TABLE t1 (i BIGINT);

2136

INSERT INTO t1 VALUES (10000000000000000); # > MAX SIGNED BIGINT 9323372036854775807

2137

INSERT INTO t1 VALUES (1);

2138

2139

CREATE TABLE t2 (i BIGINT UNSIGNED);

2140

INSERT INTO t2 VALUES (10000000000000000000); # same as first table

2139

CREATE TABLE t2 (i BIGINT);

2140

INSERT INTO t2 VALUES (10000000000000000); # same as first table

2141

INSERT INTO t2 VALUES (1);

2142

2143

/* simple test */

2147

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2148

2149

/* subquery test with cast*/

2150

SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED);

2150

SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2);

2151

2152

DROP TABLE t1;

2153

DROP TABLE t2;

2157

2158

2159

CREATE TABLE t1 (

2160

id bigint unsigned NOT NULL auto_increment,

2160

id bigint NOT NULL auto_increment,

2161

name varchar(255) NOT NULL,

2162

PRIMARY KEY (id)

2163

);

2165

(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');

2166

2167

CREATE TABLE t2 (

2168

id bigint unsigned NOT NULL auto_increment,

2169

mid bigint unsigned NOT NULL,

2168

id bigint NOT NULL auto_increment,

2169

mid bigint NOT NULL,

2170

date date NOT NULL,

2171

PRIMARY KEY (id)

2172

);

Older »