~drizzle-trunk/drizzle/development

Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))

1118

drop table t1;

1119

CREATE TABLE t1 (

1120

ID int unsigned NOT NULL auto_increment,

1121

SUB_ID int unsigned NOT NULL default '0',

1122

REF_ID int unsigned default NULL,

1123

REF_SUB int unsigned default '0',

1120

ID int NOT NULL auto_increment,

1121

SUB_ID int NOT NULL default '0',

1122

REF_ID int default NULL,

1123

REF_SUB int default '0',

1124

PRIMARY KEY (ID,SUB_ID),

1125

UNIQUE KEY t1_PK (ID,SUB_ID),

1126

KEY t1_FK (REF_ID,REF_SUB),

1144

3 1

1145

drop table t1, t2;

1146

CREATE TABLE `t1` (

1147

`id` bigint unsigned NOT NULL auto_increment,

1147

`id` bigint NOT NULL auto_increment,

1148

`pseudo` varchar(35) NOT NULL default '',

1149

`email` varchar(60) NOT NULL default '',

1150

PRIMARY KEY (`id`),

1521

DROP TABLE t1;

1522

CREATE TABLE `t1` (

1523

`id` int NOT NULL auto_increment,

1524

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

1524

`id_cns` int NOT NULL default '0',

1525

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

1526

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

1527

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

1528

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

1529

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

1526

`anno_dep` int NOT NULL default '0',

1527

`particolare` bigint NOT NULL default '0',

1528

`generale` bigint NOT NULL default '0',

1529

`bis` int NOT NULL default '0',

1530

PRIMARY KEY (`id`),

1531

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

1532

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

1533

);

1534

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);

1535

CREATE TABLE `t2` (

1536

`id` int unsigned NOT NULL auto_increment,

1537

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

1536

`id` int NOT NULL auto_increment,

1537

`max_anno_dep` int NOT NULL default '0',

1538

PRIMARY KEY (`id`)

1539

);

1540

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

2231

select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x;

2232

(select b.x from t1 as b where b.x=a.x)

2233

drop table t1;

2234

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

2234

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

2235

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);

2236

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

2236

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

2237

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');

2238

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;

2239

ERROR 42S22: Unknown column 'b.sc' in 'field list'

2618

drop table t1;

2619

create table t1 (

2620

retailerID varchar(8) NOT NULL,

2621

statusID int unsigned NOT NULL,

2621

statusID int NOT NULL,

2622

changed datetime NOT NULL,

2623

UNIQUE KEY retailerID (retailerID, statusID, changed)

2624

);

3108

min(i)

3109

3110

drop table t1;

3111

CREATE TABLE t1 (i BIGINT UNSIGNED);

3112

INSERT INTO t1 VALUES (10000000000000000000);

3111

CREATE TABLE t1 (i BIGINT);

3112

INSERT INTO t1 VALUES (10000000000000000);

3113

INSERT INTO t1 VALUES (1);

3114

CREATE TABLE t2 (i BIGINT UNSIGNED);

3115

INSERT INTO t2 VALUES (10000000000000000000);

3114

CREATE TABLE t2 (i BIGINT);

3115

INSERT INTO t2 VALUES (10000000000000000);

3116

INSERT INTO t2 VALUES (1);

3117

/* simple test */

3118

SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i;

3119

3120

10000000000000000000

3120

10000000000000000

3121

3122

/* subquery test */

3123

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

3124

3125

10000000000000000000

3125

10000000000000000

3126

/* subquery test with cast*/

3127

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

3127

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

3128

3129

10000000000000000000

3129

10000000000000000

3130

DROP TABLE t1;

3131

DROP TABLE t2;

3132

CREATE TABLE t1 (

3133

id bigint unsigned NOT NULL auto_increment,

3133

id bigint NOT NULL auto_increment,

3134

name varchar(255) NOT NULL,

3135

PRIMARY KEY (id)

3136

);

3137

INSERT INTO t1 VALUES

3138

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

3139

CREATE TABLE t2 (

3140

id bigint unsigned NOT NULL auto_increment,

3141

mid bigint unsigned NOT NULL,

3140

id bigint NOT NULL auto_increment,

3141

mid bigint NOT NULL,

3142

date date NOT NULL,

3143

PRIMARY KEY (id)

3144

);

Older »