~drizzle-trunk/drizzle/development : revision 223

13

--enable_warnings

14

15

CREATE TABLE t1 (

16

Period smallint(4) unsigned DEFAULT '0' NOT NULL,

17

Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL

16

Period smallint unsigned DEFAULT '0' NOT NULL,

17

Varor_period smallint unsigned DEFAULT '0' NOT NULL

18

);

19

20

INSERT INTO t1 VALUES (9410,9412);

29

30

CREATE TABLE t2 (

31

auto int not null auto_increment,

32

fld1 int(6) unsigned DEFAULT '0' NOT NULL,

33

companynr tinyint(2) unsigned DEFAULT '0' NOT NULL,

32

fld1 int unsigned DEFAULT '0' NOT NULL,

33

companynr tinyint unsigned DEFAULT '0' NOT NULL,

34

fld3 char(30) DEFAULT '' NOT NULL,

35

fld4 char(35) DEFAULT '' NOT NULL,

36

fld5 char(35) DEFAULT '' NOT NULL,

1496

#

1497

1498

create table t4 (

1499

companynr tinyint(2) unsigned NOT NULL default '0',

1499

companynr tinyint unsigned NOT NULL default '0',

1500

companyname char(30) NOT NULL default '',

1501

PRIMARY KEY (companynr),

1502

UNIQUE KEY companyname(companyname)

1771

#

1772

1773

CREATE TABLE t1 (

1774

id bigint(8) unsigned NOT NULL auto_increment,

1774

id bigint unsigned NOT NULL auto_increment,

1775

pseudo varchar(35) NOT NULL default '',

1776

PRIMARY KEY (id),

1777

UNIQUE KEY pseudo (pseudo)

1785

# Test of bug with SUM(CASE...)

1786

#

1787

1788

CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1788

CREATE TABLE t1 (gvid int unsigned default NULL, hmid int unsigned default NULL, volid int unsigned default NULL, mmid int unsigned default NULL, hdid int unsigned default NULL, fsid int unsigned default NULL, ctid int unsigned default NULL, dtid int unsigned default NULL, cost int unsigned default NULL, performance int unsigned default NULL, serialnumber bigint unsigned default NULL, monitored tinyint unsigned default '1', removed tinyint unsigned default '0', target tinyint unsigned default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1789

INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);

1790

CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1790

CREATE TABLE t2 ( hmid int unsigned default NULL, volid int unsigned default NULL, sampletid smallint unsigned default NULL, sampletime datetime default NULL, samplevalue bigint unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1791

INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1792

# Disable PS becasue we get more warnings from PS than from normal execution

1793

--disable_ps_protocol

1801

# Test of bigint comparision

1802

#

1803

1804

create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0');

1804

create table t1 ( A_Id bigint NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint NOT NULL default '0', A_UpdateSerial int NOT NULL default '0', other_types bigint NOT NULL default '0', wss_type bigint NOT NULL default '0');

1805

INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

1806

select wss_type from t1 where wss_type ='102935229216544106';

1807

select wss_type from t1 where wss_type ='102935229216544105';

1859

select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;

1860

drop table t1;

1861

1862

CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

1862

CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

1863

INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);

1864

CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

1864

CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

1865

INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

1866

select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;

1867

drop table t1,t2;

1920

#

1921

1922

CREATE TABLE t1 (

1923

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

1923

i int NOT NULL default '0',

1924

c char(10) NOT NULL default '',

1925

PRIMARY KEY (i),

1926

UNIQUE KEY c (c)

1970

# Bug#7425 inconsistent sort order on unsigned columns result of substraction

1971

#

1972

1973

create table t1 (a int(11) unsigned, b int(11) unsigned);

1973

create table t1 (a int unsigned, b int unsigned);

1974

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

1975

select a-b from t1 order by 1;

1976

select a-b , (a-b < 0) from t1 order by 1;

1982

#

1983

# Bug#8733 server accepts malformed query (multiply mentioned distinct)

1984

#

1985

create table t1 (a int(11));

1985

create table t1 (a int);

1986

select all all * from t1;

1987

select distinct distinct * from t1;

1988

--error 1221

1995

# Test for BUG#10095

1996

#

1997

CREATE TABLE t1 (

1998

kunde_intern_id int(10) unsigned NOT NULL default '0',

1999

kunde_id int(10) unsigned NOT NULL default '0',

2000

FK_firma_id int(10) unsigned NOT NULL default '0',

1998

kunde_intern_id int unsigned NOT NULL default '0',

1999

kunde_id int unsigned NOT NULL default '0',

2000

FK_firma_id int unsigned NOT NULL default '0',

2001

aktuell enum('Ja','Nein') NOT NULL default 'Ja',

2002

vorname varchar(128) NOT NULL default '',

2003

nachname varchar(128) NOT NULL default '',

2056

# Test for Bug#8009, SELECT failed on bigint unsigned when using HEX

2057

#

2058

2059

CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));

2059

CREATE TABLE t1 (b BIGINT UNSIGNED NOT NULL, PRIMARY KEY (b));

2060

INSERT INTO t1 VALUES (0x8000000000000000);

2061

SELECT b FROM t1 WHERE b=0x8000000000000000;

2062

DROP TABLE t1;

2064

#

2065

# IN with outer join condition (BUG#9393)

2066

#

2067

CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);

2067

CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

2068

2069

CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);

2069

CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);

2070

INSERT INTO `t2` VALUES (0,'READ');

2071

2072

CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);

2072

CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);

2073

INSERT INTO `t3` VALUES (1,'fs');

2074

2075

select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);

2078

2079

# Test for BUG#11700

2080

CREATE TABLE t1 (

2081

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

2082

profile_id smallint(6) default NULL,

2081

acct_id int NOT NULL default '0',

2082

profile_id smallint default NULL,

2083

UNIQUE KEY t1$acct_id (acct_id),

2084

KEY t1$profile_id (profile_id)

2085

);

2086

INSERT INTO t1 VALUES (132,17),(133,18);

2087

2088

CREATE TABLE t2 (

2089

profile_id smallint(6) default NULL,

2090

queue_id int(11) default NULL,

2091

seq int(11) default NULL,

2089

profile_id smallint default NULL,

2090

queue_id int default NULL,

2091

seq int default NULL,

2092

KEY t2$queue_id (queue_id)

2093

);

2094

INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);

2095

2096

CREATE TABLE t3 (

2097

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

2098

qtype int(11) default NULL,

2099

seq int(11) default NULL,

2100

warn_lvl int(11) default NULL,

2101

crit_lvl int(11) default NULL,

2102

rr1 tinyint(4) NOT NULL default '0',

2103

rr2 int(11) default NULL,

2104

default_queue tinyint(4) NOT NULL default '0',

2097

id int NOT NULL default '0',

2098

qtype int default NULL,

2099

seq int default NULL,

2100

warn_lvl int default NULL,

2101

crit_lvl int default NULL,

2102

rr1 tinyint NOT NULL default '0',

2103

rr2 int default NULL,

2104

default_queue tinyint NOT NULL default '0',

2105

KEY t3$qtype (qtype),

2106

KEY t3$id (id)

2107

);

2334

CREATE TABLE t1 (

2335

K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',

2336

K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',

2337

F2I4 int(11) NOT NULL default '0'

2337

F2I4 int NOT NULL default '0'

2338

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2339

2340

INSERT INTO t1 VALUES

2424

#

2425

# Bug #11398 Bug in field_conv() results in wrong result of join with index

2426

#

2427

create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);

2427

create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);

2428

create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

2429

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

2430

insert into t2 values (" 2", " one "),(" 2", " two ");

2446

# incorrect transformation to join ... on.

2447

#

2448

2449

create table t1 (a int(10), t1_val int(10));

2450

create table t2 (b int(10), t2_val int(10));

2451

create table t3 (a int(10), b int(10));

2449

create table t1 (a int, t1_val int);

2450

create table t2 (b int, t2_val int);

2451

create table t3 (a int, b int);

2452

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

2453

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

2454

insert into t3 values (1,1),(2,1),(3,1),(4,1);

2496

# Bug #13067 JOIN xxx USING is case sensitive

2497

#

2498

2499

create table t1 (a int(10),b int(10));

2500

create table t2 (a int(10),b int(10));

2499

create table t1 (a int,b int);

2500

create table t2 (a int,b int);

2501

insert into t1 values (1,10),(2,20),(3,30);

2502

insert into t2 values (1,10);

2503

# both queries should produce the same result

2545

# nested right join.

2546

#

2547

2548

create table t1 (id int(11) not null default '0');

2548

create table t1 (id int not null default '0');

2549

insert into t1 values (123),(191),(192);

2550

create table t2 (id char(16) character set utf8 not null);

2551

insert into t2 values ('58013'),('58014'),('58015'),('58016');

2552

create table t3 (a_id int(11) not null, b_id char(16) character set utf8);

2552

create table t3 (a_id int not null, b_id char(16) character set utf8);

2553

insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2554

2555

# both queries are equivalent

3198

#

3199

3200

CREATE TABLE t1 (

3201

c1 int(11) NOT NULL AUTO_INCREMENT,

3201

c1 int NOT NULL AUTO_INCREMENT,

3202

c2 varchar(1000) DEFAULT NULL,

3203

c3 bigint(20) DEFAULT NULL,

3204

c4 bigint(20) DEFAULT NULL,

3203

c3 bigint DEFAULT NULL,

3204

c4 bigint DEFAULT NULL,

3205

PRIMARY KEY (c1)

3206

);

3207