1--disable_warnings 2DROP TABLE IF EXISTS t1; 3--enable_warnings 4 5CREATE TABLE t1 ( 6 cont_nr int(11) NOT NULL auto_increment, 7 ver_nr int(11) NOT NULL default '0', 8 aufnr int(11) NOT NULL default '0', 9 username varchar(50) NOT NULL default '', 10 hdl_nr int(11) NOT NULL default '0', 11 eintrag date NOT NULL default '0000-00-00', 12 st_klasse varchar(40) NOT NULL default '', 13 st_wert varchar(40) NOT NULL default '', 14 st_zusatz varchar(40) NOT NULL default '', 15 st_bemerkung varchar(255) NOT NULL default '', 16 kunden_art varchar(40) NOT NULL default '', 17 mcbs_knr int(11) default NULL, 18 mcbs_aufnr int(11) NOT NULL default '0', 19 schufa_status char(1) default '?', 20 bemerkung text, 21 wirknetz text, 22 wf_igz int(11) NOT NULL default '0', 23 tarifcode varchar(80) default NULL, 24 recycle char(1) default NULL, 25 sim varchar(30) default NULL, 26 mcbs_tpl varchar(30) default NULL, 27 emp_nr int(11) NOT NULL default '0', 28 laufzeit int(11) default NULL, 29 hdl_name varchar(30) default NULL, 30 prov_hdl_nr int(11) NOT NULL default '0', 31 auto_wirknetz varchar(50) default NULL, 32 auto_billing varchar(50) default NULL, 33 touch timestamp NOT NULL, 34 kategorie varchar(50) default NULL, 35 kundentyp varchar(20) NOT NULL default '', 36 sammel_rech_msisdn varchar(30) NOT NULL default '', 37 p_nr varchar(9) NOT NULL default '', 38 suffix char(3) NOT NULL default '', 39 PRIMARY KEY (cont_nr), 40 KEY idx_aufnr(aufnr), 41 KEY idx_hdl_nr(hdl_nr), 42 KEY idx_st_klasse(st_klasse), 43 KEY ver_nr(ver_nr), 44 KEY eintrag_idx(eintrag), 45 KEY emp_nr_idx(emp_nr), 46 KEY wf_igz(wf_igz), 47 KEY touch(touch), 48 KEY hdl_tag(eintrag,hdl_nr), 49 KEY prov_hdl_nr(prov_hdl_nr), 50 KEY mcbs_aufnr(mcbs_aufnr), 51 KEY kundentyp(kundentyp), 52 KEY p_nr(p_nr,suffix) 53) ENGINE=MyISAM; 54 55INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 56INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 57INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 58INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 59INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007'); 60INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. m�chte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 61INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und gepr�ft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); 62 63# This died because we used the field Kundentyp twice 64SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; 65 66drop table t1; 67 68# 69# test case for #570 70# 71 72CREATE TABLE t1 ( 73 AUFNR varchar(12) NOT NULL default '', 74 PLNFL varchar(6) NOT NULL default '', 75 VORNR varchar(4) NOT NULL default '', 76 xstatus_vor smallint(5) unsigned NOT NULL default '0' 77); 78 79INSERT INTO t1 VALUES ('40004712','000001','0010',9); 80INSERT INTO t1 VALUES ('40004712','000001','0020',0); 81 82UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = 83"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr 84ASC LIMIT 1; 85 86drop table t1; 87 88# End of 4.1 tests 89 90# 91# Bug#27249 table_wild with alias: select t1.* as something 92# 93 94--disable_warnings 95drop table if exists t1,t2,t3; 96--enable_warnings 97 98create table t1 (a int, b int, c int); 99create table t2 (d int); 100create table t3 (a1 int, b1 int, c1 int); 101insert into t1 values(1,2,3); 102insert into t1 values(11,22,33); 103insert into t2 values(99); 104 105# Invalid queries with alias on wild 106--error ER_PARSE_ERROR 107select t1.* as 'with_alias' from t1; 108--error ER_PARSE_ERROR 109select t2.* as 'with_alias' from t2; 110--error ER_PARSE_ERROR 111select t1.*, t1.* as 'with_alias' from t1; 112--error ER_PARSE_ERROR 113select t1.* as 'with_alias', t1.* from t1; 114--error ER_PARSE_ERROR 115select t1.* as 'with_alias', t1.* as 'alias2' from t1; 116--error ER_PARSE_ERROR 117select t1.* as 'with_alias', a, t1.* as 'alias2' from t1; 118 119# other fields without alias 120--error ER_PARSE_ERROR 121select a, t1.* as 'with_alias' from t1; 122--error ER_PARSE_ERROR 123select t1.* as 'with_alias', a from t1; 124--error ER_PARSE_ERROR 125select a, t1.* as 'with_alias', b from t1; 126--error ER_PARSE_ERROR 127select (select d from t2 where d > a), t1.* as 'with_alias' from t1; 128--error ER_PARSE_ERROR 129select t1.* as 'with_alias', (select a from t2 where d > a) from t1; 130 131# other fields with alias 132--error ER_PARSE_ERROR 133select a as 'x', t1.* as 'with_alias' from t1; 134--error ER_PARSE_ERROR 135select t1.* as 'with_alias', a as 'x' from t1; 136--error ER_PARSE_ERROR 137select a as 'x', t1.* as 'with_alias', b as 'x' from t1; 138--error ER_PARSE_ERROR 139select (select d from t2 where d > a) as 'x', t1.* as 'with_alias' from t1; 140--error ER_PARSE_ERROR 141select t1.* as 'with_alias', (select a from t2 where d > a) as 'x' from t1; 142 143# some more subquery 144--error ER_PARSE_ERROR 145select (select t2.* as 'x' from t2) from t1; 146--error ER_PARSE_ERROR 147select a, (select t2.* as 'x' from t2) from t1; 148--error ER_PARSE_ERROR 149select t1.*, (select t2.* as 'x' from t2) from t1; 150 151# insert 152--error ER_PARSE_ERROR 153insert into t3 select t1.* as 'with_alias' from t1; 154--error ER_PARSE_ERROR 155insert into t3 select t2.* as 'with_alias', 1, 2 from t2; 156--error ER_PARSE_ERROR 157insert into t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; 158--error ER_PARSE_ERROR 159insert into t3 select t2.*, t2.* as 'with_alias', 3 from t2; 160 161# create 162--error ER_PARSE_ERROR 163create table t3 select t1.* as 'with_alias' from t1; 164--error ER_PARSE_ERROR 165create table t3 select t2.* as 'with_alias', 1, 2 from t2; 166--error ER_PARSE_ERROR 167create table t3 select t2.* as 'with_alias', d as 'x', d as 'z' from t2; 168--error ER_PARSE_ERROR 169create table t3 select t2.*, t2.* as 'with_alias', 3 from t2; 170 171# 172# Valid queries without alias on wild 173# (proof the above fail due to invalid aliasing) 174# 175 176select t1.* from t1; 177select t2.* from t2; 178select t1.*, t1.* from t1; 179select t1.*, a, t1.* from t1; 180 181# other fields without alias 182select a, t1.* from t1; 183select t1.*, a from t1; 184select a, t1.*, b from t1; 185select (select d from t2 where d > a), t1.* from t1; 186select t1.*, (select a from t2 where d > a) from t1; 187 188# other fields with alias 189select a as 'x', t1.* from t1; 190select t1.*, a as 'x' from t1; 191select a as 'x', t1.*, b as 'x' from t1; 192select (select d from t2 where d > a) as 'x', t1.* from t1; 193select t1.*, (select a from t2 where d > a) as 'x' from t1; 194 195# some more subquery 196select (select t2.* from t2) from t1; 197select a, (select t2.* from t2) from t1; 198select t1.*, (select t2.* from t2) from t1; 199 200# insert 201insert into t3 select t1.* from t1; 202insert into t3 select t2.*, 1, 2 from t2; 203insert into t3 select t2.*, d as 'x', d as 'z' from t2; 204insert into t3 select t2.*, t2.*, 3 from t2; 205 206# create 207create table t4 select t1.* from t1; 208drop table t4; 209create table t4 select t2.*, 1, 2 from t2; 210drop table t4; 211create table t4 select t2.*, d as 'x', d as 'z' from t2; 212drop table t4; 213 214# end 215drop table t1,t2,t3; 216 217# End of 5.2 tests 218