~drizzle-trunk/drizzle/development

CREATE TABLE `t1` ( `id` bigint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) 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 ;

903

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 ;

900

904

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

901

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

905

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;

902

906

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

903

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

907

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 ;

904

908

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

905

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

909

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

906

910

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

907

911

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;

908

912

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;

911

915

912

916

# cardinality check

913

917

914

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

918

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

915

919

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

916

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

920

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

917

921

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

918

922

-- error 1241

919

923

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

962

966

SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);

963

967

DROP TABLE t1, t2;

964

968

965

CREATE TABLE t1 (a int(1));

969

CREATE TABLE t1 (a int);

966

970

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

967

971

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

968

972

DROP TABLE t1;

972

976

973

977

974

978

CREATE TABLE `t1` (

975

`id` int(11) NOT NULL auto_increment,

976

`id_cns` tinyint(3) unsigned NOT NULL default '0',

979

`id` int NOT NULL auto_increment,

980

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

977

981

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

978

`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',

979

`particolare` bigint(8) unsigned NOT NULL default '0',

980

`generale` bigint(8) unsigned NOT NULL default '0',

981

`bis` tinyint(3) unsigned NOT NULL default '0',

982

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

983

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

984

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

985

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

982

986

PRIMARY KEY (`id`),

983

987

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

984

988

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

985

989

);

986

990

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

987

991

CREATE TABLE `t2` (

988

`id` tinyint(3) unsigned NOT NULL auto_increment,

989

`max_anno_dep` smallint(6) unsigned NOT NULL default '0',

992

`id` tinyint unsigned NOT NULL auto_increment,

993

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

990

994

PRIMARY KEY (`id`)

991

995

);

992

996

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

1264

1268

# Test problem with NULL and derived tables (Bug #4097)

1265

1269

1266

1270

1267

CREATE TABLE t1 (id int(11) default NULL,name varchar(10) default NULL);

1271

CREATE TABLE t1 (id int default NULL,name varchar(10) default NULL);

1268

1272

INSERT INTO t1 VALUES (1,'Tim'),(2,'Rebecca'),(3,NULL);

1269

CREATE TABLE t2 (id int(11) default NULL, pet varchar(10) default NULL);

1273

CREATE TABLE t2 (id int default NULL, pet varchar(10) default NULL);

1270

1274

INSERT INTO t2 VALUES (1,'Fido'),(2,'Spot'),(3,'Felix');

1271

1275

SELECT a.*, b.* FROM (SELECT * FROM t1) AS a JOIN t2 as b on a.id=b.id;

1272

1276

drop table t1,t2;

1274

1278

1275

1279

# Aggregate function comparation with ALL/ANY/SOME subselect

1276

1280

1277

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

1281

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

1278

1282

insert into t1 values (1);

1279

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

1283

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

1280

1284

insert into t2 values (1,2);

1281

1285

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

1282

1286

drop table t1,t2;

1296

1300

1297

1301

# Optimized IN with compound index

1298

1302

1299

CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1300

CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1303

CREATE TABLE `t1` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1304

CREATE TABLE `t2` ( `aid` int NOT NULL default '0', `bid` int NOT NULL default '0', PRIMARY KEY (`aid`,`bid`));

1301

1305

insert into t1 values (1,1),(1,2),(2,1),(2,2);

1302

1306

insert into t2 values (1,2),(2,2);

1303

1307

select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);

1330

1334

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

1331

1335

# creation

1332

1336

1333

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

1337

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

1334

1338

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

1335

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

1339

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

1336

1340

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

1337

1341

-- error 1054

1338

1342

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;

1382

1386

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

1383

1387

Region char(26) NOT NULL default '',

1384

1388

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

1385

IndepYear smallint(6) default NULL,

1386

Population int(11) NOT NULL default '0',

1389

IndepYear smallint default NULL,

1390

Population int NOT NULL default '0',

1387

1391

LifeExpectancy float(3,1) default NULL,

1388

1392

GNP float(10,2) default NULL,

1389

1393

GNPOld float(10,2) default NULL,

1390

1394

LocalName char(45) NOT NULL default '',

1391

1395

GovernmentForm char(45) NOT NULL default '',

1392

1396

HeadOfState char(60) default NULL,

1393

Capital int(11) default NULL,

1397

Capital int default NULL,

1394

1398

Code2 char(2) NOT NULL default ''

1395

1399

) ENGINE=MyISAM;

1396

1400

INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');

1456

1460

# Test for BUG#8218

1457

1461

1458

1462

CREATE TABLE t1 (

1459

categoryId int(11) NOT NULL,

1460

courseId int(11) NOT NULL,

1463

categoryId int NOT NULL,

1464

courseId int NOT NULL,

1461

1465

startDate datetime NOT NULL,

1462

1466

endDate datetime NOT NULL,

1463

1467

createDate datetime NOT NULL,

1475

1479

(5,12,'2004-02-18','2010-01-01','2004-02-18','2004-02-18','');

1476

1480

1477

1481

CREATE TABLE t2 (

1478

userId int(11) NOT NULL,

1479

courseId int(11) NOT NULL,

1482

userId int NOT NULL,

1483

courseId int NOT NULL,

1480

1484

date datetime NOT NULL

1481

1485

);

1482

1486

INSERT INTO t2 VALUES (5141,71,'2003-11-18'),

1488

1492

1489

1493

1490

1494

CREATE TABLE t3 (

1491

groupId int(11) NOT NULL,

1492

parentId int(11) NOT NULL,

1495

groupId int NOT NULL,

1496

parentId int NOT NULL,

1493

1497

startDate datetime NOT NULL,

1494

1498

endDate datetime NOT NULL,

1495

1499

createDate datetime NOT NULL,

1496

1500

modifyDate timestamp NOT NULL,

1497

ordering int(11)

1501

ordering int

1498

1502

);

1499

1503

INSERT INTO t3 VALUES (12,9,'1000-01-01','3999-12-31','2004-01-29','2004-01-29',NULL);

1500

1504

1501

1505

CREATE TABLE t4 (

1502

id int(11) NOT NULL,

1503

groupTypeId int(11) NOT NULL,

1506

id int NOT NULL,

1507

groupTypeId int NOT NULL,

1504

1508

groupKey varchar(50) NOT NULL,

1505

1509

name text,

1506

ordering int(11),

1510

ordering int,

1507

1511

description text,

1508

1512

createDate datetime NOT NULL,

1509

1513

modifyDate timestamp NOT NULL

1512

1516

(12,5,'group2','group2',0,'group2','2004-01-29','2004-01-29');

1513

1517

1514

1518

CREATE TABLE t5 (

1515

userId int(11) NOT NULL,

1516

groupId int(11) NOT NULL,

1519

userId int NOT NULL,

1520

groupId int NOT NULL,

1517

1521

createDate datetime NOT NULL,

1518

1522

modifyDate timestamp NOT NULL

1519

1523

);

1584

1588

# Item_int_with_ref check (BUG#10020)

1585

1589

1586

1590

CREATE TABLE `t1` (

1587

`itemid` bigint(20) unsigned NOT NULL auto_increment,

1588

`sessionid` bigint(20) unsigned default NULL,

1589

`time` int(10) unsigned NOT NULL default '0',

1591

`itemid` bigint unsigned NOT NULL auto_increment,

1592

`sessionid` bigint unsigned default NULL,

1593

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

1590

1594

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

1591

1595

NULL default '',

1592

1596

`data` text collate latin1_general_ci NOT NULL,

1594

1598

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1595

1599

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

1596

1600

CREATE TABLE `t2` (

1597

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

1598

`pid` int(10) unsigned NOT NULL default '0',

1599

`date` int(10) unsigned NOT NULL default '0',

1601

`sessionid` bigint unsigned NOT NULL auto_increment,

1602

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

1603

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

1600

1604

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

1601

1605

PRIMARY KEY (`sessionid`)

1602

1606

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1707

1711

1708

1712

create table t1 (

1709

1713

retailerID varchar(8) NOT NULL,

1710

statusID int(10) unsigned NOT NULL,

1714

statusID int unsigned NOT NULL,

1711

1715

changed datetime NOT NULL,

1712

1716

UNIQUE KEY retailerID (retailerID, statusID, changed)

1713

1717

);

1898

1902

drop table t1;

1899

1903

1900

1904

CREATE TABLE t1 (

1901

grp int(11) default NULL,

1905

grp int default NULL,

1902

1906

a decimal(10,2) default NULL);

1903

1907

1904

1908

insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL);

1955

1959

# Item_int_with_ref check (BUG#10020)

1956

1960

1957

1961

CREATE TABLE `t1` (

1958

`itemid` bigint(20) unsigned NOT NULL auto_increment,

1959

`sessionid` bigint(20) unsigned default NULL,

1960

`time` int(10) unsigned NOT NULL default '0',

1962

`itemid` bigint unsigned NOT NULL auto_increment,

1963

`sessionid` bigint unsigned default NULL,

1964

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

1961

1965

`type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT

1962

1966

NULL default '',

1963

1967

`data` text collate latin1_general_ci NOT NULL,

1965

1969

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1966

1970

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

1967

1971

CREATE TABLE `t2` (

1968

`sessionid` bigint(20) unsigned NOT NULL auto_increment,

1969

`pid` int(10) unsigned NOT NULL default '0',

1970

`date` int(10) unsigned NOT NULL default '0',

1972

`sessionid` bigint unsigned NOT NULL auto_increment,

1973

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

1974

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

1971

1975

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

1972

1976

PRIMARY KEY (`sessionid`)

1973

1977

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2180

2184

2181

2185

2182

2186

CREATE TABLE t1 (

2183

id bigint(20) unsigned NOT NULL auto_increment,

2187

id bigint unsigned NOT NULL auto_increment,

2184

2188

name varchar(255) NOT NULL,

2185

2189

PRIMARY KEY (id)

2186

2190

);

2188

2192

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

2189

2193

2190

2194

CREATE TABLE t2 (

2191

id bigint(20) unsigned NOT NULL auto_increment,

2192

mid bigint(20) unsigned NOT NULL,

2195

id bigint unsigned NOT NULL auto_increment,

2196

mid bigint unsigned NOT NULL,

2193

2197

date date NOT NULL,

2194

2198

PRIMARY KEY (id)

2195

2199

);

2220

2224

2221

2225

2222

2226

CREATE TABLE t1 (

2223

i1 int(11) NOT NULL default '0',

2224

i2 int(11) NOT NULL default '0',

2227

i1 int NOT NULL default '0',

2228

i2 int NOT NULL default '0',

2225

2229

t datetime NOT NULL default '0000-00-00 00:00:00',

2226

2230

PRIMARY KEY (i1,i2,t)

2227

2231

);

2234

2238

(24,2,'2005-05-27 12:40:06');

2235

2239

2236

2240

CREATE TABLE t2 (

2237

i1 int(11) NOT NULL default '0',

2238

i2 int(11) NOT NULL default '0',

2241

i1 int NOT NULL default '0',

2242

i2 int NOT NULL default '0',

2239

2243

t datetime default NULL,

2240

2244

PRIMARY KEY (i1)

2241

2245

);

2311

2315

CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));

2312

2316

CREATE TABLE t2 (x int auto_increment, y int, z int,

2313

2317

PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));

2314

2315

2318

disable_query_log;

2319

set autocommit=0;

2320

begin;

2316

2321

let $1=3000;

2317

2322

while ($1)

2318

2323

{

2326

2331

}

2327

2332

dec $1;

2328

2333

}

2334

commit;

2335

set autocommit=1;

2329

2336

enable_query_log;

2330

2337

2331

2338

SET SESSION sort_buffer_size = 32 * 1024;

2385

2392

--enable_warnings

2386

2393

2387

2394

CREATE TABLE t1 (

2388

id_1 int(5) NOT NULL,

2395

id_1 int NOT NULL,

2389

2396

t varchar(4) DEFAULT NULL

2390

2397

);

2391

2398

2392

2399

CREATE TABLE t2 (

2393

id_2 int(5) NOT NULL,

2400

id_2 int NOT NULL,

2394

2401

t varchar(4) DEFAULT NULL

2395

2402

);

2396

2403

2397

2404

CREATE TABLE t1xt2 (

2398

id_1 int(5) NOT NULL,

2399

id_2 int(5) NOT NULL

2405

id_1 int NOT NULL,

2406

id_2 int NOT NULL

2400

2407

);

2401

2408

2402

2409

INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

2724

2731

DROP TABLE t1, t2;

2725

2732

2726

2733

2727

# Bug #28076: inconsistent binary/varbinary comparison

2728

2729

2730

CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));

2731

INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);

2732

2733

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

2734

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

2735

2736

CREATE INDEX I1 ON t1 (s1);

2737

CREATE INDEX I2 ON t1 (s2);

2738

2739

SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);

2740

SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);

2741

2742

TRUNCATE t1;

2743

INSERT INTO t1 VALUES (0x41,0x41);

2744

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);

2745

2746

DROP TABLE t1;

2747

2748

CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));

2749

CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));

2750

CREATE TABLE t3 (a3 BINARY(2) default '0');

2751

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

2752

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

2753

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

2754

SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;

2755

SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;

2756

DROP TABLE t1,t2,t3;

2757

2758

CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));

2759

CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);

2760

CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);

2761

INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);

2762

INSERT INTO t2 VALUES (2), (3), (4), (5);

2763

INSERT INTO t3 VALUES (10), (20), (30);

2764

SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;

2765

SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);

2766

DROP TABLE t1, t2, t3;

2767

2768

2769

2734

# Bug #30788: Inconsistent retrieval of char/varchar

2770

2735

2771

2736

Older »