This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-2.0-alpha in repository https://gitbox.apache.org/repos/asf/doris.git
commit f10f9c640e1e18c0ce80cf095219dcb63f0f6978 Author: yagagagaga <[email protected]> AuthorDate: Sun Apr 16 18:19:52 2023 +0800 [regression-test] add some suites (#18286) test_join_result_count.groovy --- .../query_p0/join/test_join_result_count.groovy | 198 +++++++++++++++++++++ 1 file changed, 198 insertions(+) diff --git a/regression-test/suites/query_p0/join/test_join_result_count.groovy b/regression-test/suites/query_p0/join/test_join_result_count.groovy new file mode 100644 index 0000000000..775d9b37dd --- /dev/null +++ b/regression-test/suites/query_p0/join/test_join_result_count.groovy @@ -0,0 +1,198 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("test_join_result_count", "query,p0") { + + sql "drop table if exists t3;" + + sql """ + CREATE TABLE `t3` ( +`c0` datev2 NOT NULL , +`c1` varchar(50) NOT NULL , +`c2` varchar(50) NOT NULL , +`c3` varchar(20) NOT NULL , +`c4` varchar(50) NOT NULL , +`c5` varchar(20) NOT NULL , +`c6` varchar(100) NOT NULL , +`c7` decimalv3(38, 18) NOT NULL , +`c8` decimalv3(38, 18) NOT NULL , +`c9` decimalv3(38, 18) NOT NULL , +`c10` decimalv3(38, 18) NOT NULL , +`c11` decimalv3(38, 18) NOT NULL , +`c12` decimalv3(38, 9) NOT NULL , +`c13` decimalv3(38, 18) NOT NULL , +`c14` decimalv3(10, 0) NOT NULL , +`c15` decimalv3(38, 18) NOT NULL , +`c16` decimalv3(38, 18) NOT NULL , +`c17` decimalv3(38, 18) NOT NULL , +`c18` varchar(200) NOT NULL , +`c19` varchar(60) NOT NULL , +`c20` varchar(60) NOT NULL , +`c21` smallint(6) NOT NULL , +`c22` char(2) NOT NULL , +`c23` datev2 NOT NULL +) ENGINE=OLAP +DUPLICATE KEY(`c0`, `c1`, `c2`, `c3`) +PARTITION BY RANGE(`c0`) +( +PARTITION p_2000 VALUES [('2000-01-01'), ('2099-01-01')) +) +DISTRIBUTED BY HASH(`c0`, `c1`, `c2`, `c3`) BUCKETS AUTO +PROPERTIES ( +"replication_allocation" = "tag.location.default: 1", +"in_memory" = "false", +"storage_format" = "V2", +"light_schema_change" = "true", +"disable_auto_compaction" = "false" +); +""" + + sql "drop table if exists t4;" + + sql """ +CREATE TABLE `t4` +( +`d0` varchar(50) NULL , +`d1` varchar(80) NULL , +`d2` varchar(40) NULL , +`d3` varchar(30) NULL , +`d4` varchar(50) NULL , +`d5` varchar(8) NULL , +`d6` varchar(100) NULL , +`d7` varchar(40) NULL , +`d8` varchar(200) NULL , +`d9` varchar(3) NULL , +`d10` varchar(10) NULL , +`d11` varchar(20) NULL , +`d12` varchar(10) NULL , +`d13` varchar(20) NULL , +`d14` varchar(200) NULL , +`d15` varchar(100) NULL , +`d16` varchar(100) NULL , +`d17` varchar(50) NULL , +`d18` datev2 NULL , +`d19` datev2 NULL , +`d20` varchar(2) NULL , +`d21` varchar(20) NULL , +`d22` varchar(50) NULL , +`d23` varchar(50) NULL , +`d24` varchar(50) NULL , +`d25` varchar(50) NULL , +`d26` varchar(50) NULL , +`d27` varchar(50) NULL , +`d28` varchar(50) NULL , +`d29` varchar(50) NULL , +`d30` varchar(8) NULL , +`d31` varchar(100) NULL , +`d32` datev2 NULL , +`d33` datev2 NULL +) ENGINE=OLAP +DUPLICATE KEY(`d0`) +DISTRIBUTED BY HASH(`d0`) BUCKETS AUTO +PROPERTIES ( +"replication_allocation" = "tag.location.default: 1", +"in_memory" = "false", +"storage_format" = "V2", +"light_schema_change" = "true", +"disable_auto_compaction" = "false" +); +""" + + sql """ +INSERT INTO `t3` VALUES ('2017-01-01','73','1','xy','XYZ','zQ','0N',78.326461744024730726,78.148863384057232844,62.576725243619092236,40.441486336294099290,31.469270014735277558,28.782245553,33.829453793315923236,39,89.415314680352419216,48.277770337435450545,39.771029459737232017,'A3','OGK_345_DF3G','zZ',54,'Sc','2010-01-01'), +('2017-01-01','C7','1','qR','CDE','LV','hN',81.216425648502760142,28.925014306186767820,52.402422792994751075,5.812755708462516834,53.223879424390418910,13.433030608,1.481331564085870790,10,24.769926557473260971,88.539161343479310865,24.541794550608993058,'rW','MNH_34S_REKK','Gh',45,'Hu','2010-01-01'), +('2017-01-01','58','1','Yk','CDE','aH','XQ',78.392219414044635220,17.836054009716235976,75.521757437152712728,88.114569368422740929,62.297035646689863337,80.231758806,17.353573976555423210,18,79.688952848768112904,71.647546361197629529,44.315339148367524439,'n2','OGK_345_DF3G','hF',18,'Or','2010-01-01'), +('2017-01-01','Dg','1','tx','PLM','AZ','Uq',34.250098363937564556,79.587227354840741959,31.814047037176354965,59.917666377146970604,13.834441510087425615,0.365281261,85.497153321232268530,36,77.963919462338067371,32.149317431964937960,17.429028523835712229,'LT','MNH_34S_REKK','LS',15,'Vl','2010-01-01'), +('2018-01-01','If','1','np','CDE','wb','so',68.279310735621318674,36.879973446170546149,65.513020649488707476,49.847202243832249046,86.194752199665599617,50.454361838,43.347186613370912871,3,49.790111820361468719,74.289699744482439840,66.638144755346800134,'28','MNH_34S_REKK','73',96,'6C','2010-01-01'), +('2018-01-01','i3','1','jh','ABC','t6','XI',5.138878908729890884,84.930864324459359970,64.637955032208415597,54.938326563130267689,33.923818238453330567,26.320989671,12.294925863562896321,19,12.457533760912251474,61.360584268926811330,88.976385655941290403,'TE','AFD_GFFD_234DF','Ox',14,'fZ','2010-01-01'), +('2018-01-01','qe','1','kb','XYZ','IL','aT',84.433049408592361382,25.208840938251258951,7.515469155188074817,71.508519492161355211,4.440055388630681670,81.855700857,0.767694961897832592,53,70.124435774461415165,6.105987429865320820,0.993866026878107445,'wt','AFD_GFFD_234DF','MY',41,'mI','2010-01-01'), +('2017-01-01','cm','1','21','XYZ','H7','Df',12.177693662735298656,61.752358034653687543,79.822770795436730436,14.882751629037019505,70.928139172708888073,53.543966255,41.841997784000600470,35,6.767427047172696420,33.907540824720293700,23.183909440666088700,'bD','MNH_34S_REKK','ra',15,'af','2010-01-01'), +('2018-01-01','iR','1','C0','ABC','dw','Tk',55.633829470415250135,59.719272511914569753,11.808129171772744906,25.101202231834535087,9.607696585312472635,75.722867715,65.379149305517739900,36,36.564316122969582638,30.299285714344106376,46.966155570132298566,'oZ','MNH_34S_REKK','2n',81,'sk','2010-01-01'), +('2018-01-01','jT','1','wl','CDE','Wd','oy',70.617199985608908954,68.327012036528644761,23.199845522983460080,77.263245236932631416,13.412770480640487497,22.716667783,32.602714815263571998,77,53.321220219577682622,75.939119586784967887,34.188018831298959993,'Ce','AFD_GFFD_234DF','No',42,'Ki','2010-01-01'), +('2018-01-01','Pk','1','Lt','PLM','YZ','OZ',2.350255364296265998,11.625233757619484026,35.169240131797152394,5.277131874113814564,30.263333772552171727,20.564065177,67.735031914847684541,75,83.386604866065732868,80.167835905015604074,10.797712531398997108,'dX','MNH_34S_REKK','L8',11,'z5','2010-01-01'), +('2018-01-01','FT','1','Ej','CDE','1d','Ei',61.332057782663242036,31.402224136695730958,7.906350886877649168,89.782195394735600976,70.969055142406905184,44.520140148,76.315077292503175735,47,57.177942058642784670,4.166306008337098985,52.310617728176608418,'1D','OGK_345_DF3G','7M',32,'5T','2010-01-01'), +('2018-01-01','l7','1','Hw','XYZ','2w','yW',29.229702085581495287,42.869940413826561091,61.100646671045591915,21.242820597861463490,17.550717841445098757,3.116980760,10.426251340837983888,32,0.844693797947813335,33.770096134014698518,88.114100062344658926,'1F','OGK_345_DF3G','gJ',22,'Yv','2010-01-01'), +('2017-01-01','bw','1','9W','PLM','DE','Rn',6.665548240305814678,65.867289932367603820,87.515469708592898440,29.137475673560345400,23.581006750858248039,19.827642528,5.331209584153457481,86,74.978579652457219227,42.531435691822733006,66.316141574245043058,'WV','OGK_345_DF3G','ED',87,'hY','2010-01-01'), +('2017-01-01','lJ','1','zH','ABC','ZH','bB',71.643453760727270970,11.104257350875398143,27.181730008078343707,15.918035675856784986,41.190128258548230995,28.773219100,62.534434037399076284,79,51.370128418839532597,48.411691292347722825,24.353513847677076092,'1d','MNH_34S_REKK','FI',40,'Ka','2010-01-01'), +('2017-01-01','NT','1','jx','PLM','hX','C3',79.419242678302615841,1.751866939232817946,90.584881785853143175,17.383802609241839689,38.124354795655609389,39.959859245,64.824394171184935885,15,25.207455534423336448,83.738023834531470686,31.736267149099521231,'jP','AFD_GFFD_234DF','J9',64,'yd','2010-01-01'), +('2017-01-01','iP','1','ti','ABC','ma','Bc',25.475019320992927219,19.800921759804310891,46.728343199913874520,69.444029483172017716,17.449813589090254197,11.487363501,71.394627195137949046,73,44.499383143299315225,71.769214206702996328,25.758292436698892000,'8j','OGK_345_DF3G','V4',83,'Di','2010-01-01'), +('2018-01-01','4r','1','sy','XYZ','Z9','BL',81.469765872134634393,36.585506940599653559,76.780193929080580285,71.588580352270616740,54.363836157836653350,5.821007704,44.261248744523421346,23,39.678999169479683211,11.918334409614122400,38.409104983585979992,'yd','MNH_34S_REKK','NP',21,'B6','2010-01-01'), +('2018-01-01','1b','1','LB','PLM','tF','0L',22.968282723551851720,4.694502179661971135,13.604949793483747676,89.642556141327328669,80.332132691245881875,24.369953840,42.722950814067730171,53,68.136452671337581430,49.680663165585190430,43.994169430634118873,'2h','OGK_345_DF3G','dn',53,'tV','2010-01-01'), +('2018-01-01','DA','1','De','PLM','1q','vE',78.929890910221051612,57.448260906845065691,41.710661063478039380,46.680683333626305567,62.602229366062085466,44.209749163,2.904019766920477559,73,60.235917448111860609,70.862527329647133511,31.636736114626720026,'7U','AFD_GFFD_234DF','8p',12,'So','2010-01-01'), +('2017-01-01','YT','1','MP','ABC','oX','yr',49.956518890640005315,37.957476083415637945,88.263561377769121760,38.453515337978225849,79.825383407768070345,90.561833806,34.242907580080859872,31,18.809502615686008996,4.232083780384746761,7.936532248786515038,'nc','AFD_GFFD_234DF','oW',53,'18','2010-01-01'), +('2017-01-01','i3','1','67','CDE','kK','3l',17.600011795822952986,1.785512178350065206,7.329570249035338382,11.978802709459287245,17.519415048345563747,76.201670796,63.330001040764342975,32,56.455032520773707978,69.500928788023164024,46.453886654269917419,'Pa','AFD_GFFD_234DF','Je',80,'xd','2010-01-01'), +('2018-01-01','hT','1','np','ABC','7j','uD',87.782639454391268394,64.784308112165164957,64.768052876040344764,48.476538057833478220,68.275842269683556270,40.788627052,33.645915656090134178,26,57.363241951015704537,54.593769007141402501,38.281041982557567754,'N9','OGK_345_DF3G','bX',84,'dY','2010-01-01'), +('2017-01-01','c4','1','4z','XYZ','m2','3x',45.641858931001769490,78.434525445976652527,45.900952883254343920,5.513576956457100970,73.709033426570321194,15.259848945,42.373420393218204258,18,3.666100558275775415,25.135890572510077029,59.910840447327477497,'se','AFD_GFFD_234DF','z9',58,'08','2010-01-01'); +""" + + sql """ +INSERT INTO `t4` VALUES (NULL,'4M','DORIS','td','1','kQ','6G','XM','k3','Jp','02','xA','xj','yK','v3','GR','es','CO','2010-01-01','2010-01-01','9y','Yi','l6','AR','VC','Yp','MK','m7','Kq','da','XYZ','gU','2010-01-01','2010-01-01'), +(NULL,'Pq','DORIS','Ys','1','yR','FV','kA','z9','1e','02','j2','WB','2g','bW','ve','Md','CO','2010-01-01','2010-01-01','xp','pz','QR','ET','BB','qX','U5','tu','zp','UW','CDE','1L','2010-01-01','2010-01-01'), +(NULL,'Iz','DORIS','7G','1','pl','Tb','ga','gJ','7W','02','Lu','SI','pi','P5','IP','DW','CO','2010-01-01','2010-01-01','pe','di','EU','TW','SU','N5','7v','cP','jn','Hq','ABC','Ub','2010-01-01','2010-01-01'), +(NULL,'xz','DORIS','am','1','d2','4N','Us','wt','yN','02','Al','3d','k8','GR','rD','De','CO','2010-01-01','2010-01-01','LG','bM','Cy','lQ','8E','8p','0b','Xz','Ed','c0','PLM','zz','2010-01-01','2010-01-01'), +(NULL,'WP','DORIS','Xm','1','hr','ee','xv','N7','9K','08','fO','NS','sy','DD','wR','O5','CO','2010-01-01','2010-01-01','Xe','bd','pf','J6','wB','rj','0V','fh','oF','zc','XYZ','W8','2010-01-01','2010-01-01'), +(NULL,'Ae','DORIS','5t','1','qW','tZ','2Q','JX','7w','08','qG','GJ','gd','3s','Z3','wT','CO','2010-01-01','2010-01-01','UM','6v','Q3','Vh','AA','3F','AU','4i','Wt','4A','CDE','Cn','2010-01-01','2010-01-01'), +(NULL,'Qd','DORIS','tw','1','S3','Kq','aU','Ks','Oc','08','Dz','AD','s2','ei','g3','qJ','CO','2010-01-01','2010-01-01','63','Aj','7C','Gw','YL','zf','Cq','gs','mT','to','ABC','Qu','2010-01-01','2010-01-01'), +(NULL,'fy','DORIS','sq','1','RE','PO','eV','Cn','2j','08','Ah','Tr','KT','Ho','6q','YD','CO','2010-01-01','2010-01-01','9q','05','KC','1a','Bp','zD','om','mp','zQ','qC','PLM','1c','2010-01-01','2010-01-01'); +""" + + sql """ +set parallel_fragment_exec_instance_num = 1; +""" + sql """ +set enable_pipeline_engine = 0; +""" + sql """ +set enable_profile = 1; +""" + + test { + sql """ +SELECT DISTINCT +A.c0 e0 +,B.d0 e1 +,B.d2 e2 +,B.d14 e3 +,A.c2 d4 +,A.c5 d9 +,A.c4 e6 +,A.c7 e7 +,A.c8 e8 +,A.c10 e9 +,A.c11 e10 +,A.c9 e11 +,A.c9-A.c7 e12 +,A.c15/100 e13 +,A.c12 e14 +,A.c13 e15 +,NULL e16 +,NULL AS e17 +,NULL AS e18 +,A.c4 d30 +,NULL d31 +FROM t3 A +INNER JOIN t4 B +ON A.c2=B.d4 +AND B.d30='XYZ' +WHERE +A.c19 ='AFD_GFFD_234DF' +AND A.c0 BETWEEN '2000-01-01' AND '2022-01-01' +; +""" + rowNum 16 + } +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
