| ==== |
| ---- QUERY: TPCH-Q20 |
| # Q20 - Potential Part Promotion Query |
| # Note: Tricky rewrite from the original to avoid mixing |
| # correlated and uncorrelated table refs in a subquery. |
| select distinct |
| s_name, |
| s_address |
| from |
| supplier s, |
| s.s_partsupps ps, |
| region.r_nations n |
| where |
| ps_partkey in ( |
| select |
| p_partkey |
| from |
| part p |
| where |
| p_name like 'forest%' |
| ) |
| and ps_availqty > ( |
| select |
| 0.5 * sum(l_quantity) |
| from |
| customer.c_orders.o_lineitems l |
| where |
| l_partkey = ps_partkey |
| and l_suppkey = s_suppkey |
| and l_shipdate >= '1994-01-01' |
| and l_shipdate < '1995-01-01' |
| ) |
| and s_nationkey = n_nationkey |
| and n_name = 'CANADA' |
| order by |
| s_name |
| ---- RESULTS |
| 'Supplier#000000020','iybAE,RmTymrZVYaFZva2SH,j' |
| 'Supplier#000000091','YV45D7TkfdQanOOZ7q9QxkyGUapU1oOWU6q3' |
| 'Supplier#000000205','rF uV8d0JNEk' |
| 'Supplier#000000285','Br7e1nnt1yxrw6ImgpJ7YdhFDjuBf' |
| 'Supplier#000000287','7a9SP7qW5Yku5PvSg' |
| 'Supplier#000000354','w8fOo5W,aS' |
| 'Supplier#000000378','FfbhyCxWvcPrO8ltp9' |
| 'Supplier#000000402','i9Sw4DoyMhzhKXCH9By,AYSgmD' |
| 'Supplier#000000530','0qwCMwobKY OcmLyfRXlagA8ukENJv,' |
| 'Supplier#000000555','TfB,a5bfl3Ah 3Z 74GqnNs6zKVGM' |
| 'Supplier#000000640','mvvtlQKsTOsJj5Ihk7,cq' |
| 'Supplier#000000729','pqck2ppy758TQpZCUAjPvlU55K3QjfL7Bi' |
| 'Supplier#000000736','l6i2nMwVuovfKnuVgaSGK2rDy65DlAFLegiL7' |
| 'Supplier#000000761','zlSLelQUj2XrvTTFnv7WAcYZGvvMTx882d4' |
| 'Supplier#000000887','urEaTejH5POADP2ARrf' |
| 'Supplier#000000935','ij98czM 2KzWe7dDTOxB8sq0UfCdvrX' |
| 'Supplier#000000975',',AC e,tBpNwKb5xMUzeohxlRn, hdZJo73gFQF8y' |
| 'Supplier#000001263','rQWr6nf8ZhB2TAiIDIvo5Io' |
| 'Supplier#000001367','42YSkFcAXMMcucsqeEefOE4HeCC' |
| 'Supplier#000001426','bPOCc086oFm8sLtS,fGrH' |
| 'Supplier#000001446','lch9HMNU1R7a0LIybsUodVknk6' |
| 'Supplier#000001500','wDmF5xLxtQch9ctVu,' |
| 'Supplier#000001602','uKNWIeafaM644' |
| 'Supplier#000001626','UhxNRzUu1dtFmp0' |
| 'Supplier#000001682','pXTkGxrTQVyH1Rr' |
| 'Supplier#000001700','7hMlCof1Y5zLFg' |
| 'Supplier#000001726','TeRY7TtTH24sEword7yAaSkjx8' |
| 'Supplier#000001730','Rc8e,1Pybn r6zo0VJIEiD0UD vhk' |
| 'Supplier#000001746','qWsendlOekQG1aW4uq06uQaCm51se8lirv7 hBRd' |
| 'Supplier#000001806','M934fuZSnLW' |
| 'Supplier#000001855','MWk6EAeozXb' |
| 'Supplier#000001931','FpJbMU2h6ZR2eBv8I9NIxF' |
| 'Supplier#000002022',' dwebGX7Id2pc25YvY33' |
| 'Supplier#000002036','20ytTtVObjKUUI2WCB0A' |
| 'Supplier#000002096','kuxseyLtq QPLXxm9ZUrnB6Kkh92JtK5cQzzXNU ' |
| 'Supplier#000002117','MRtkgKolHJ9Wh X9J,urANHKDzvjr' |
| 'Supplier#000002204','uYmlr46C06udCqanj0KiRsoTQakZsEyssL' |
| 'Supplier#000002218','nODZw5q4dx kp0K5' |
| 'Supplier#000002243','nSOEV3JeOU79' |
| 'Supplier#000002245','hz2qWXWVjOyKhqPYMoEwz6zFkrTaDM' |
| 'Supplier#000002282','ES21K9dxoW1I1TzWCj7ekdlNwSWnv1Z 6mQ,BKn' |
| 'Supplier#000002303','nCoWfpB6YOymbgOht7ltfklpkHl' |
| 'Supplier#000002331','WRh2w5WFvRg7Z0S1AvSvHCL' |
| 'Supplier#000002373','RzHSxOTQmElCjxIBiVA52Z JB58rJhPRylR' |
| 'Supplier#000002419','qydBQd14I5l5mVXa4fYY' |
| 'Supplier#000002571','JZUugz04c iJFLrlGsz9O N,W 1rVHNIReyq' |
| 'Supplier#000002585','CsPoKpw2QuTY4AV1NkWuttneIa4SN' |
| 'Supplier#000002629','0Bw,q5Zp8su9XrzoCngZ3cAEXZwZ' |
| 'Supplier#000002721','HVdFAN2JHMQSpKm' |
| 'Supplier#000002730','lIFxR4fzm31C6,muzJwl84z' |
| 'Supplier#000002775','yDclaDaBD4ihH' |
| 'Supplier#000002799','lwr, 6L3gdfc79PQut,4XO6nQsTJY63cAyYO' |
| 'Supplier#000002934','m,trBENywSArwg3DhB' |
| 'Supplier#000002941','Naddba 8YTEKekZyP0' |
| 'Supplier#000003028','jouzgX0WZjhNMWLaH4fy' |
| 'Supplier#000003095','HxON3jJhUi3zjt,r mTD' |
| 'Supplier#000003143','hdolgh608uTkHh7t6qfSqkifKaiFjnCH' |
| 'Supplier#000003185','hMa535Cbf2mj1Nw4OWOKWVrsK0VdDkJURrdjSIJe' |
| 'Supplier#000003189','DWdPxt7 RnkZv6VOByR0em' |
| 'Supplier#000003201','E87yws6I,t0qNs4QW7UzExKiJnJDZWue' |
| 'Supplier#000003213','pxrRP4irQ1VoyfQ,dTf3' |
| 'Supplier#000003275','9xO4nyJ2QJcX6vGf' |
| 'Supplier#000003288','EDdfNt7E5Uc,xLTupoIgYL4yY7ujh,' |
| 'Supplier#000003314','jnisU8MzqO4iUB3zsPcrysMw3DDUojS4q7LD' |
| 'Supplier#000003373','iy8VM48ynpc3N2OsBwAvhYakO2us9R1bi' |
| 'Supplier#000003421','Sh3dt9W5oeofFWovnFhrg,' |
| 'Supplier#000003422','DJoCEapUeBXoV1iYiCcPFQvzsTv2ZI960' |
| 'Supplier#000003441','zvFJIzS,oUuShHjpcX' |
| 'Supplier#000003590','sy79CMLxqb,Cbo' |
| 'Supplier#000003607','lNqFHQYjwSAkf' |
| 'Supplier#000003625','qY588W0Yk5iaUy1RXTgNrEKrMAjBYHcKs' |
| 'Supplier#000003723','jZEp0OEythCLcS OmJSrFtxJ66bMlzSp' |
| 'Supplier#000003849','KgbZEaRk,6Q3mWvwh6uptrs1KRUHg 0' |
| 'Supplier#000003894','vvGC rameLOk' |
| 'Supplier#000003941','Pmb05mQfBMS618O7WKqZJ 9vyv' |
| 'Supplier#000004059','umEYZSq9RJ2WEzdsv9meU8rmqwzVLRgiZwC' |
| 'Supplier#000004207','tF64pwiOM4IkWjN3mS,e06WuAjLx' |
| 'Supplier#000004236','dl,HPtJmGipxYsSqn9wmqkuWjst,mCeJ8O6T' |
| 'Supplier#000004278','bBddbpBxIVp Di9' |
| 'Supplier#000004281','1OwPHh Pgiyeus,iZS5eA23JDOipwk' |
| 'Supplier#000004304','hQCAz59k,HLlp2CKUrcBIL' |
| 'Supplier#000004346','S3076LEOwo' |
| 'Supplier#000004406','Ah0ZaLu6VwufPWUz,7kbXgYZhauEaHqGIg' |
| 'Supplier#000004430','yvSsKNSTL5HLXBET4luOsPNLxKzAMk' |
| 'Supplier#000004527','p pVXCnxgcklWF6A1o3OHY3qW6' |
| 'Supplier#000004655','67NqBc4 t3PG3F8aO IsqWNq4kGaPowYL' |
| 'Supplier#000004851','Rj,x6IgLT7kBL99nqp' |
| 'Supplier#000004871',',phpt6AWEnUS8t4Avb50rFfdg7O9c6nU8xxv8eC5' |
| 'Supplier#000004884','42Z1uLye9nsn6aTGBNd dI8 x' |
| 'Supplier#000004975','GPq5PMKY6Wy' |
| 'Supplier#000005076','Xl7h9ifgvIHmqxFLgWfHK4Gjav BkP' |
| 'Supplier#000005195','Woi3b2ZaicPh ZSfu1EfXhE' |
| 'Supplier#000005256','Onc3t57VAMchm,pmoVLaU8bONni9NsuaM PzMMFz' |
| 'Supplier#000005257','f9g8SEHB7obMj3QXAjXS2vfYY22' |
| 'Supplier#000005300','gXG28YqpxU' |
| 'Supplier#000005323','tMCkdqbDoyNo8vMIkzjBqYexoRAuv,T6 qzcu' |
| 'Supplier#000005386','Ub6AAfHpWLWP' |
| 'Supplier#000005426','9Dz2OVT1q sb4BK71ljQ1XjPBYRPvO' |
| 'Supplier#000005465','63cYZenZBRZ613Q1FaoG0,smnC5zl9' |
| 'Supplier#000005484','saFdOR qW7AFY,3asPqiiAa11Mo22pCoN0BtPrKo' |
| 'Supplier#000005505','d2sbjG43KwMPX' |
| 'Supplier#000005506','On f5ypzoWgB' |
| 'Supplier#000005631','14TVrjlzo2SJEBYCDgpMwTlvwSqC' |
| 'Supplier#000005642','ZwKxAv3V40tW E8P7Qwu,zlu,kPsL' |
| 'Supplier#000005686','f2RBKec2T1NIi7yS M' |
| 'Supplier#000005730','5rkb0PSews HvxkL8JaD41UpnSF2cg8H1' |
| 'Supplier#000005736','2dq XTYhtYWSfp' |
| 'Supplier#000005737','dmEWcS32C3kx,d,B95 OmYn48' |
| 'Supplier#000005797',',o,OebwRbSDmVl9gN9fpWPCiqB UogvlSR' |
| 'Supplier#000005875','lK,sYiGzB94hSyHy9xvSZFbVQNCZe2LXZuGbS' |
| 'Supplier#000005974','REhR5jE,lLusQXvf54SwYySgsSSVFhu' |
| 'Supplier#000006059','4m0cv8MwJ9yX2vlwI Z' |
| 'Supplier#000006065','UiI2Cy3W4Tu5sLk LuvXLRy6KihlGv' |
| 'Supplier#000006093','KJNUg1odUT2wtCS2s6PrH3D6fd' |
| 'Supplier#000006099','aZilwQKYDTVPoK' |
| 'Supplier#000006109','rY5gbfh3dKHnylcQUTPGCwnbe' |
| 'Supplier#000006217','RVN23SYT9jenUeaWGXUd' |
| 'Supplier#000006297','73VRDOO56GUCyvc40oYJ' |
| 'Supplier#000006435','xIgE69XszYbnO4Eon7cHHO8y' |
| 'Supplier#000006463','7 wkdj2EO49iotley2kmIM ADpLSszGV3RNWj' |
| 'Supplier#000006478','bQYPnj9lpmW3U' |
| 'Supplier#000006521','b9 2zjHzxR' |
| 'Supplier#000006642','N,CUclSqRLJcS8zQ' |
| 'Supplier#000006659','iTLsnvD8D2GzWNUv kRInwRjk5rDeEmfup1' |
| 'Supplier#000006669','NQ4Yryj624p7K53' |
| 'Supplier#000006748','rC,2rEn8gKDIS5Q0dJEoiF' |
| 'Supplier#000006761','n4jhxGMqB5prD1HhpLvwrWStOLlla' |
| 'Supplier#000006808','HGd2Xo 9nEcHJhZvXjXxWKIpApT' |
| 'Supplier#000006858','fnlINT885vBBhsWwTGiZ0o22thwGY16h GHJj21' |
| 'Supplier#000006946','To6Slo0GJTqcIvD' |
| 'Supplier#000006949','mLxYUJhsGcLtKe ,GFirNu183AvT' |
| 'Supplier#000007072','2tRyX9M1a 4Rcm57s779F1ANG9jlpK' |
| 'Supplier#000007098','G3j8g0KC4OcbAu2OVoPHrXQWMCUdjq8wgCHOExu' |
| 'Supplier#000007132','xonvn0KAQIL3p8kYk HC1FSSDSUSTC' |
| 'Supplier#000007135','ls DoKV7V5ulfQy9V' |
| 'Supplier#000007147','Xzb16kC63wmLVYexUEgB0hXFvHkjT5iPpq' |
| 'Supplier#000007160','TqDGBULB3cTqIT6FKDvm9BS4e4v,zwYiQPb' |
| 'Supplier#000007169','tEc95D2moN9S84nd55O,dlnW' |
| 'Supplier#000007278','I2ae3rS7KVF8GVHtB' |
| 'Supplier#000007365','51xhROLvQMJ05DndtZWt' |
| 'Supplier#000007398','V8eE6oZ00OFNU,' |
| 'Supplier#000007402','4UVv58ery1rjmqSR5' |
| 'Supplier#000007448','yhhpWiJi7EJ6Q5VCaQ' |
| 'Supplier#000007458','BYuucapYkptZl6fnd2QaDyZmI9gR1Ih16e' |
| 'Supplier#000007477','9m9j0wfhWzCvVHxkU,PpAxwSH0h' |
| 'Supplier#000007509','q8,V6LJRoHJjHcOuSG7aLTMg' |
| 'Supplier#000007561','rMcFg2530VC' |
| 'Supplier#000007616','R IovIqzDi3,QHnaqZk1xS4hGAgelhP4yj' |
| 'Supplier#000007760','JsPE18PvcdFTK' |
| 'Supplier#000007801','69fi,U1r6enUb ' |
| 'Supplier#000007865','5cDGCS,T6N' |
| 'Supplier#000007885','u3sicchh5ZpyTUpN1cJKNcAoabIWgY' |
| 'Supplier#000007926','ErzCF80K9Uy' |
| 'Supplier#000007998','LnASFBfYRFOo9d6d,asBvVq9Lo2P' |
| 'Supplier#000008090','eonbJZvoDFYBNUinYfp6yERIg' |
| 'Supplier#000008224','TWxt9f,LVER' |
| 'Supplier#000008231','IK7eGw Yj90sTdpsP,vcqWxLB' |
| 'Supplier#000008243','2AyePMkDqmzVzjGTizXthFLo8h EiudCMxOmIIG' |
| 'Supplier#000008323','75I18sZmASwm POeheRMdj9tmpyeQ,BfCXN5BIAb' |
| 'Supplier#000008366','h778cEj14BuW9OEKlvPTWq4iwASR6EBBXN7zeS8' |
| 'Supplier#000008532','Uc29q4,5xVdDOF87UZrxhr4xWS0ihEUXuh' |
| 'Supplier#000008595','MH0iB73GQ3z UW3O DbCbqmc' |
| 'Supplier#000008610','SgVgP90vP452sUNTgzL9zKwXHXAzV6tV' |
| 'Supplier#000008683','gLuGcugfpJSeGQARnaHNCaWnGaqsNnjyl20' |
| 'Supplier#000008705','aE,trRNdPx,4yinTD9O3DebDIp' |
| 'Supplier#000008742','HmPlQEzKCPEcTUL14,kKq' |
| 'Supplier#000008841','I 85Lu1sekbg2xrSIzm0' |
| 'Supplier#000008872','8D 45GgxJO2OwwYP9S4AaXJKvDwPfLM' |
| 'Supplier#000008879','rDSA,D9oPM,65NMWEFrmGKAu' |
| 'Supplier#000008967','2kwEHyMG 7FwozNImAUE6mH0hYtqYculJM' |
| 'Supplier#000008972','w2vF6 D5YZO3visPXsqVfLADTK' |
| 'Supplier#000009032','qK,trB6Sdy4Dz1BRUFNy' |
| 'Supplier#000009043','57OPvKH4qyXIZ7IzYeCaw11a5N1Ki9f1WWmVQ,' |
| 'Supplier#000009278','RqYTzgxj93CLX 0mcYfCENOefD' |
| 'Supplier#000009326','XmiC,uy36B9,fb0zhcjaagiXQutg' |
| 'Supplier#000009430','igRqmneFt ' |
| 'Supplier#000009549','h3RVchUf8MzY46IzbZ0ng09' |
| 'Supplier#000009601','51m637bO,Rw5DnHWFUvLacRx9' |
| 'Supplier#000009709','rRnCbHYgDgl9PZYnyWKVYSUW0vKg' |
| 'Supplier#000009753','wLhVEcRmd7PkJF4FBnGK7Z' |
| 'Supplier#000009799',' 4wNjXGa4OKWl' |
| 'Supplier#000009811','E3iuyq7UnZxU7oPZIe2Gu6' |
| 'Supplier#000009812','APFRMy3lCbgFga53n5t9DxzFPQPgnjrGt32' |
| 'Supplier#000009846','57sNwJJ3PtBDu,hMPP5QvpcOcSNRXn3PypJJrh' |
| 'Supplier#000009899','7XdpAHrzr1t,UQFZE' |
| 'Supplier#000009974','7wJ,J5DKcxSU4Kp1cQLpbcAvB5AsvKT' |
| ---- TYPES |
| string, string |
| ==== |
