~drizzle-trunk/drizzle/development

Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc

(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2;

105

106

explain select a,b from t1 union all select a,b from t2;

107

id select_type table type possible_keys key key_len ref rows Extra

108

1 PRIMARY t1 ALL NULL NULL NULL NULL #

109

2 UNION t2 ALL NULL NULL NULL NULL #

110

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL #

108

1 PRIMARY t1 ALL NULL NULL NULL NULL 4

109

2 UNION t2 ALL NULL NULL NULL NULL 4

110

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL

111

explain select xx from t1 union select 1;

112

ERROR 42S22: Unknown column 'xx' in 'field list'

113

explain select a,b from t1 union select 1;

159

`same` int NOT NULL default '1',

160

PRIMARY KEY (`pseudo1`),

161

KEY `pseudo` (`pseudo`)

162

);

162

) ENGINE=MyISAM;

163

INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);

164

SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';

165

pseudo

450

2 UNION t1 ALL NULL NULL NULL NULL 4

451

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort

452

drop table t1;

453

CREATE TEMPORARY TABLE t1 ( id int default '0') ENGINE=MyISAM;

453

CREATE TABLE t1 ( id int default '0') ENGINE=MyISAM;

454

INSERT INTO t1 (id) VALUES("1");

455

CREATE TEMPORARY TABLE t2 ( id int default '0', id_master int default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

455

CREATE TABLE t2 ( id int default '0', id_master int default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

456

INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",

457

"foo1", "bar1");

458

INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",

804

FLUSH STATUS;

805

show status like 'Slow_queries';

806

Variable_name Value

807

Slow_queries #

807

Slow_queries 0

808

select count(*) from t1 where a=7;

809

count(*)

810

811

show status like 'Slow_queries';

812

Variable_name Value

813

Slow_queries #

813

Slow_queries 0

814

select count(*) from t1 where b=13;

815

count(*)

816

817

show status like 'Slow_queries';

818

Variable_name Value

819

Slow_queries #

819

Slow_queries 0

820

select count(*) from t1 where b=13 union select count(*) from t1 where a=7;

821

count(*)

822

823

824

show status like 'Slow_queries';

825

Variable_name Value

826

Slow_queries #

826

Slow_queries 0

827

select count(*) from t1 where a=7 union select count(*) from t1 where b=13;

828

count(*)

829

830

831

show status like 'Slow_queries';

832

Variable_name Value

833

Slow_queries #

833

Slow_queries 0

834

flush status;

835

select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);

836

846

847

show status like 'Slow_queries';

848

Variable_name Value

849

Slow_queries #

849

Slow_queries 0

850

drop table t1;

851

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null);

851

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;

852

insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');

853

select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);

854

NAME PHONE NAME PHONE

1027

show create table t1;

1028

Table Create Table

1029

t1 CREATE TABLE `t1` (

1030

`'test' collate utf8_bin` varchar(4) COLLATE utf8_bin DEFAULT NULL

1030

`'test' collate utf8_bin` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1031

) ENGINE=DEFAULT

1032

select count(*) from t1;

1033

count(*)

1040

show create table t1;

1041

Table Create Table

1042

t1 CREATE TABLE `t1` (

1043

`test` varchar(4) COLLATE utf8_bin DEFAULT NULL

1043

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1044

) ENGINE=DEFAULT

1045

select count(*) from t1;

1046

count(*)

1053

show create table t1;

1054

Table Create Table

1055

t1 CREATE TABLE `t1` (

1056

`test` varchar(4) COLLATE utf8_bin DEFAULT NULL

1056

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1057

) ENGINE=DEFAULT

1058

select count(*) from t1;

1059

count(*)

1081

show create table t1;

1082

Table Create Table

1083

t1 CREATE TABLE `t1` (

1084

`a` varchar(1) COLLATE utf8_swedish_ci DEFAULT NULL

1084

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL

1085

) ENGINE=DEFAULT

1086

drop table t1;

1087

create table t1 as

1091

show create table t1;

1092

Table Create Table

1093

t1 CREATE TABLE `t1` (

1094

`a` varchar(1) COLLATE utf8_spanish_ci DEFAULT NULL

1094

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL

1095

) ENGINE=DEFAULT

1096

drop table t1;

1097

drop table t2;

1102

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

1103

order by f2, a1;

1104

show columns from t2;

1105

Field Type Null Default Default_is_NULL On_Update

1106

f2 DATE TRUE TRUE

1107

a1 INTEGER TRUE TRUE

1105

Field Type Null Key Default Extra

1106

f2 date YES NULL

1107

a1 int YES NULL

1108

drop table t1, t2;

1109

create table t1 (f1 int);

1110

create table t2 (f1 int, f2 int ,f3 date);

1128

)

1129

order by sdate;

1130

show columns from t4;

1131

Field Type Null Default Default_is_NULL On_Update

1132

sdate DATE TRUE TRUE

1131

Field Type Null Key Default Extra

1132

sdate date YES NULL

1133

drop table t1, t2, t3, t4;

1134

create table t1 (a int not null, b char (10) not null);

1135

insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');

1203

t2 CREATE TABLE `t2` (

1204

`a` varchar(5) DEFAULT NULL

1205

) ENGINE=DEFAULT

1206

select row_format from data_dictionary.TABLES where table_schema="test" and table_name="t2";

1206

select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";

1207

row_format

1208

DEFAULT

1208

Compact

1209

alter table t2 ROW_FORMAT=fixed;

1210

Warnings:

1211

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

1212

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

1209

1213

show create table t2;

1210

1214

Table Create Table

1211

1215

t2 CREATE TABLE `t2` (

1212

1216

`a` varchar(5) DEFAULT NULL

1213

) ENGINE=DEFAULT

1217

) ENGINE=DEFAULT ROW_FORMAT=FIXED

1214

1218

drop table t1,t2;

1215

1219

CREATE TABLE t1 (a mediumtext);

1216

1220

CREATE TABLE t2 (b varchar(20));

1438

1442

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

1439

1443

CREATE TABLE t2 SELECT * FROM (SELECT NULL) a UNION SELECT a FROM t1;

1440

1444

DESC t2;

1441

Field Type Null Default Default_is_NULL On_Update

1442

NULL INTEGER TRUE TRUE

1445

Field Type Null Key Default Extra

1446

NULL int YES NULL

1443

1447

CREATE TABLE t3 SELECT a FROM t1 UNION SELECT * FROM (SELECT NULL) a;

1444

1448

DESC t3;

1445

Field Type Null Default Default_is_NULL On_Update

1446

a INTEGER TRUE TRUE

1449

Field Type Null Key Default Extra

1450

a int YES NULL

1447

1451

CREATE TABLE t4 SELECT NULL;

1448

1452

DESC t4;

1449

Field Type Null Default Default_is_NULL On_Update

1450

NULL VARCHAR TRUE TRUE

1453

Field Type Null Key Default Extra

1454

NULL varbinary(0) YES NULL

1451

1455

CREATE TABLE t5 SELECT NULL UNION SELECT NULL;

1452

1456

DESC t5;

1453

Field Type Null Default Default_is_NULL On_Update

1454

NULL VARCHAR TRUE TRUE

1457

Field Type Null Key Default Extra

1458

NULL varbinary(0) YES NULL

1455

1459

CREATE TABLE t6

1456

1460

SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;

1457

1461

DESC t6;

1458

Field Type Null Default Default_is_NULL On_Update

1459

NULL INTEGER TRUE TRUE

1462

Field Type Null Key Default Extra

1463

NULL int YES NULL

1460

1464

DROP TABLE t1, t2, t3, t4, t5, t6;

1461

1465

End of 5.0 tests

Older »