~drizzle-trunk/drizzle/development

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` int unsigned NOT NULL auto_increment,

992

`max_anno_dep` int 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);

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` int 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` int unsigned NOT NULL default '0', `level` int 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;

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 »