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

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

905

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;

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

907

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 ;

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

909

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;

910

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

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;

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;

915

916

# cardinality check

917

918

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

918

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

919

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

920

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

920

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

921

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

922

-- error 1241

923

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

966

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

967

DROP TABLE t1, t2;

968

969

CREATE TABLE t1 (a int(1));

969

CREATE TABLE t1 (a int);

970

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

971

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

972

DROP TABLE t1;

976

977

978

CREATE TABLE `t1` (

979

`id` int(11) NOT NULL auto_increment,

980

`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',

981

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

982

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

983

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

984

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

985

`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',

986

PRIMARY KEY (`id`),

987

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

988

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

989

);

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

991

CREATE TABLE `t2` (

992

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

993

`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',

994

PRIMARY KEY (`id`)

995

);

996

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

1268

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

1269

1270

1271

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

1272

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

1273

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

1274

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

1275

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

1276

drop table t1,t2;

1278

1279

# Aggregate function comparation with ALL/ANY/SOME subselect

1280

1281

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;

1282

insert into t1 values (1);

1283

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;

1284

insert into t2 values (1,2);

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

1286

drop table t1,t2;

1300

1301

# Optimized IN with compound index

1302

1303

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

1304

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

1305

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

1306

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

1307

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

1334

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

1335

# creation

1336

1337

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

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

1339

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

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

1341

-- error 1054

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;

1386

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

1387

Region char(26) NOT NULL default '',

1388

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

1389

IndepYear smallint(6) default NULL,

1390

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

1389

IndepYear smallint default NULL,

1390

Population int NOT NULL default '0',

1391

LifeExpectancy float(3,1) default NULL,

1392

GNP float(10,2) default NULL,

1393

GNPOld float(10,2) default NULL,

1394

LocalName char(45) NOT NULL default '',

1395

GovernmentForm char(45) NOT NULL default '',

1396

HeadOfState char(60) default NULL,

1397

Capital int(11) default NULL,

1397

Capital int default NULL,

1398

Code2 char(2) NOT NULL default ''

1399

) ENGINE=MyISAM;

1400

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

1460

# Test for BUG#8218

1461

1462

CREATE TABLE t1 (

1463

categoryId int(11) NOT NULL,

1464

courseId int(11) NOT NULL,

1463

categoryId int NOT NULL,

1464

courseId int NOT NULL,

1465

startDate datetime NOT NULL,

1466

endDate datetime NOT NULL,

1467

createDate datetime NOT NULL,

1479

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

1480

1481

CREATE TABLE t2 (

1482

userId int(11) NOT NULL,

1483

courseId int(11) NOT NULL,

1482

userId int NOT NULL,

1483

courseId int NOT NULL,

1484

date datetime NOT NULL

1485

);

1486

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

1492

1493

1494

CREATE TABLE t3 (

1495

groupId int(11) NOT NULL,

1496

parentId int(11) NOT NULL,

1495

groupId int NOT NULL,

1496

parentId int NOT NULL,

1497

startDate datetime NOT NULL,

1498

endDate datetime NOT NULL,

1499

createDate datetime NOT NULL,

1500

modifyDate timestamp NOT NULL,

1501

ordering int(11)

1501

ordering int

1502

);

1503

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

1504

1505

CREATE TABLE t4 (

1506

id int(11) NOT NULL,

1507

groupTypeId int(11) NOT NULL,

1506

id int NOT NULL,

1507

groupTypeId int NOT NULL,

1508

groupKey varchar(50) NOT NULL,

1509

name text,

1510

ordering int(11),

1510

ordering int,

1511

description text,

1512

createDate datetime NOT NULL,

1513

modifyDate timestamp NOT NULL

1516

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

1517

1518

CREATE TABLE t5 (

1519

userId int(11) NOT NULL,

1520

groupId int(11) NOT NULL,

1519

userId int NOT NULL,

1520

groupId int NOT NULL,

1521

createDate datetime NOT NULL,

1522

modifyDate timestamp NOT NULL

1523

);

1588

# Item_int_with_ref check (BUG#10020)

1589

1590

CREATE TABLE `t1` (

1591

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

1592

`sessionid` bigint(20) unsigned default NULL,

1593

`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',

1594

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

1595

NULL default '',

1596

`data` text collate latin1_general_ci NOT NULL,

1598

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1599

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

1600

CREATE TABLE `t2` (

1601

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

1602

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

1603

`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',

1604

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

1605

PRIMARY KEY (`sessionid`)

1606

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1711

1712

create table t1 (

1713

retailerID varchar(8) NOT NULL,

1714

statusID int(10) unsigned NOT NULL,

1714

statusID int unsigned NOT NULL,

1715

changed datetime NOT NULL,

1716

UNIQUE KEY retailerID (retailerID, statusID, changed)

1717

);

1902

drop table t1;

1903

1904

CREATE TABLE t1 (

1905

grp int(11) default NULL,

1905

grp int default NULL,

1906

a decimal(10,2) default NULL);

1907

1908

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

1959

# Item_int_with_ref check (BUG#10020)

1960

1961

CREATE TABLE `t1` (

1962

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

1963

`sessionid` bigint(20) unsigned default NULL,

1964

`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',

1965

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

1966

NULL default '',

1967

`data` text collate latin1_general_ci NOT NULL,

1969

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1970

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

1971

CREATE TABLE `t2` (

1972

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

1973

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

1974

`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',

1975

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

1976

PRIMARY KEY (`sessionid`)

1977

) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2184

2185

2186

CREATE TABLE t1 (

2187

id bigint(20) unsigned NOT NULL auto_increment,

2187

id bigint unsigned NOT NULL auto_increment,

2188

name varchar(255) NOT NULL,

2189

PRIMARY KEY (id)

2190

);

2192

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

2193

2194

CREATE TABLE t2 (

2195

id bigint(20) unsigned NOT NULL auto_increment,

2196

mid bigint(20) unsigned NOT NULL,

2195

id bigint unsigned NOT NULL auto_increment,

2196

mid bigint unsigned NOT NULL,

2197

date date NOT NULL,

2198

PRIMARY KEY (id)

2199

);

2224

2225

2226

CREATE TABLE t1 (

2227

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

2228

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

2227

i1 int NOT NULL default '0',

2228

i2 int NOT NULL default '0',

2229

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

2230

PRIMARY KEY (i1,i2,t)

2231

);

2238

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

2239

2240

CREATE TABLE t2 (

2241

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

2242

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

2241

i1 int NOT NULL default '0',

2242

i2 int NOT NULL default '0',

2243

t datetime default NULL,

2244

PRIMARY KEY (i1)

2245

);

2392

--enable_warnings

2393

2394

CREATE TABLE t1 (

2395

id_1 int(5) NOT NULL,

2395

id_1 int NOT NULL,

2396

t varchar(4) DEFAULT NULL

2397

);

2398

2399

CREATE TABLE t2 (

2400

id_2 int(5) NOT NULL,

2400

id_2 int NOT NULL,

2401

t varchar(4) DEFAULT NULL

2402

);

2403

2404

CREATE TABLE t1xt2 (

2405

id_1 int(5) NOT NULL,

2406

id_2 int(5) NOT NULL

2405

id_1 int NOT NULL,

2406

id_2 int NOT NULL

2407

);

2408

2409

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

2731

DROP TABLE t1, t2;

2732

2733

2734

# Bug #28076: inconsistent binary/varbinary comparison

2735

2736

2737

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

2738

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

2739

2740

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

2741

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

2742

2743

CREATE INDEX I1 ON t1 (s1);

2744

CREATE INDEX I2 ON t1 (s2);

2745

2746

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

2747

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

2748

2749

TRUNCATE t1;

2750

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

2751

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

2752

2753

DROP TABLE t1;

2754

2755

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

2756

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

2757

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

2758

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

2759

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

2760

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

2761

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

2762

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

2763

DROP TABLE t1,t2,t3;

2764

2765

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

2766

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

2767

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

2768

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

2769

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

2770

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

2771

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

2772

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

2773

DROP TABLE t1, t2, t3;

2774

2775

2776

2734

# Bug #30788: Inconsistent retrieval of char/varchar

2777

2735

2778

2736

Older »