diff options
Diffstat (limited to 'mysql-test/r/union.result')
| -rw-r--r-- | mysql-test/r/union.result | 38 |
1 files changed, 31 insertions, 7 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 4a514e3dd18..23ed06dee6a 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -350,7 +350,7 @@ select found_rows(); found_rows() 4 (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1; -ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use +ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2; a 1 @@ -539,7 +539,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` char(2) NOT NULL default '' + `a` char(2) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select "aa" as a; @@ -550,7 +550,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` char(2) NOT NULL default '' + `a` char(2) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -561,7 +561,7 @@ a show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` double(4,1) NOT NULL default '0.0' + `a` double(4,1) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text); @@ -585,7 +585,7 @@ it2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `it2` int(11) NOT NULL default '0' + `it2` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT i from t2 UNION select f from t2; @@ -799,7 +799,7 @@ select * from t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` bigint(1) NOT NULL default '0' + `1` bigint(1) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 select _latin1"test" union select _latin2"testt" ; @@ -808,7 +808,7 @@ create table t1 select _latin2"test" union select _latin2"testt" ; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` char(5) character set latin2 NOT NULL default '' + `test` char(5) character set latin2 default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (s char(200)); @@ -869,3 +869,27 @@ show status like 'Slow_queries'; Variable_name Value Slow_queries 3 drop table t1; +create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; +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'); +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); +NAME PHONE NAME PHONE +a 111 NULL NULL +b 222 NULL NULL +d 444 d 454 +NULL NULL f 666 +NULL NULL g 777 +drop table t1; +create table t1 ( id int, name char(10) not null, name2 char(10) not null ) engine=innodb; +insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); +select name2 from t1 union all select name from t1 union all select id from t1; +name2 +fff +sss +ttt +first +second +third +1 +2 +3 +drop table t1; |