====
---- 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
====