test_query_regressions.py 9.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197
  1. """Adversarial API inputs, independent Decimal oracles and pagination/cache regressions."""
  2. import base64
  3. import json
  4. import random
  5. import sqlite3
  6. from collections import OrderedDict
  7. from concurrent.futures import ThreadPoolExecutor
  8. from decimal import Decimal
  9. import pytest
  10. from fastapi.testclient import TestClient
  11. from toolsvc.api import app
  12. from toolsvc.config import settings
  13. from toolsvc.objects import store
  14. from toolsvc.readers import read_rows, ReadError
  15. @pytest.fixture(scope='module')
  16. def client():
  17. with TestClient(app,raise_server_exceptions=False) as c:yield c
  18. @pytest.fixture(scope='module')
  19. def rows_object():
  20. ws=store.create_workspace('扩展查询回归')
  21. return store.register_upload(ws['id'],'regression.csv',('YEAR,WEEK,MONORSUM,COUNT,G_MODEL\n'+''.join(f'2025,{i%52+1},{i}.01,{i+1},MODEL-{i:04}\n' for i in range(257))).encode())
  22. @pytest.mark.parametrize('change',[
  23. {'aggregate':{'group_by':[{}],'metrics':[{'fn':'count'}]}},
  24. {'aggregate':{'group_by':'YEAR','metrics':[{'fn':'count'}]}},
  25. {'aggregate':{'metrics':['sum']}},
  26. {'aggregate':{'metrics':{'fn':'count'}}},
  27. {'aggregate':{'sum':'MONORSUM'}},
  28. {'aggregate':{'group_by':[['YEAR']],'metrics':[{'fn':'count'}]}},
  29. {'order_by':[{'column':{},'dir':'asc'}]},
  30. {'order_by':[{'column':'YEAR','dir':'sideways'}]},
  31. {'filters':[{'column':'YEAR','op':'in','value':[]}]},
  32. {'filters':[{'column':'YEAR','op':'in','value':list(range(501))}]},
  33. {'filters':[{'column':'YEAR','op':'between','value':[2025,2026]}]},
  34. {'filters':[{'column':'YEAR','value':2025}]*33},
  35. {'count_mode':'sometimes'},
  36. {'offset':-1},
  37. {'offset':2**80},
  38. {'limit':0},
  39. {'limit':-3},
  40. ])
  41. def test_invalid_api_queries_return_client_error_not_500(client,rows_object,change):
  42. response=client.post('/tools/data_read',json={'object_id':rows_object['id'],**change})
  43. assert response.status_code in (400,422),response.text
  44. @pytest.mark.parametrize('cursor',['%%%','e30=',base64.urlsafe_b64encode(b'[]').decode(),'A'*5000])
  45. def test_bad_cursor_shapes(client,rows_object,cursor):
  46. response=client.post('/tools/data_read',json={'object_id':rows_object['id'],'cursor':cursor})
  47. assert response.status_code==400
  48. @pytest.mark.parametrize('page_size',[1,2,17,200])
  49. def test_all_page_boundaries_and_filtered_cursor(rows_object,page_size):
  50. args=dict(object_id=rows_object['id'],filters=[{'column':'WEEK','op':'>=','value':25}],limit=page_size,use_cache=False)
  51. seen=[]; result=read_rows(**args)
  52. while True:
  53. seen.extend(r['_trace']['row_index'] for r in result['rows'])
  54. if result['next_cursor'] is None:break
  55. result=read_rows(**args,cursor=result['next_cursor'])
  56. expected=[i for i in range(257) if i%52+1>=25]
  57. assert seen==expected and len(seen)==len(set(seen))
  58. assert result['exhausted'] and not result['truncated']
  59. def test_byte_limited_cursor_does_not_skip_consumed_lookahead(rows_object,monkeypatch):
  60. monkeypatch.setattr(settings,'query_max_bytes',360)
  61. args=dict(object_id=rows_object['id'],columns=['G_MODEL'],limit=200,use_cache=False)
  62. seen=[];result=read_rows(**args)
  63. assert result['truncation_reason']=='byte_limit'
  64. while True:
  65. seen.extend(r['_trace']['row_index'] for r in result['rows'])
  66. if result['next_cursor'] is None:break
  67. result=read_rows(**args,cursor=result['next_cursor'])
  68. assert seen==list(range(257))
  69. @pytest.mark.parametrize('mode',['auto','none','exact'])
  70. def test_empty_filtered_results_distinguish_unknown_count_from_zero(rows_object,mode):
  71. q=read_rows(rows_object['id'],filters=[{'column':'G_MODEL','value':'absent'}],count_mode=mode)
  72. assert q['rows']==[] and q['exhausted'] and q['next_cursor'] is None
  73. assert q['total']==(0 if mode=='exact' else None)
  74. @pytest.mark.parametrize('seed',[7,31,197])
  75. def test_random_decimal_aggregates_match_independent_python_oracle(seed):
  76. rng=random.Random(seed)
  77. groups={str(g):[] for g in range(7)};records=[]
  78. for i in range(350):
  79. value=Decimal(rng.randrange(-10**18,10**18))/Decimal(1000)
  80. group=str(i%7);groups[group].append(value);records.append(f'{group},{value}')
  81. ws=store.create_workspace('decimal oracle')
  82. obj=store.register_upload(ws['id'],'decimal.csv',('group,amount\n'+'\n'.join(records)+'\n').encode())
  83. result=read_rows(obj['id'],aggregate={'group_by':['group'],'metrics':[{'fn':fn,'column':'amount','alias':fn} for fn in ('sum','min','max','avg')]},limit=20)
  84. for row in result['rows']:
  85. data=row['values'];values=groups[data['group']]
  86. assert Decimal(data['sum'])==sum(values)
  87. assert Decimal(data['min'])==min(values)
  88. assert Decimal(data['max'])==max(values)
  89. assert Decimal(data['avg'])==sum(values)/len(values)
  90. assert not result['truncated']
  91. @pytest.mark.parametrize('fn',['sum','avg','min','max'])
  92. def test_unknown_member_propagates_for_each_exact_aggregate(fn):
  93. ws=store.create_workspace('unknown')
  94. obj=store.register_upload(ws['id'],'bad.csv',b'g,amount\na,1.5\na,masked-range\nb,0\n')
  95. result=read_rows(obj['id'],aggregate={'group_by':['g'],'metrics':[{'fn':fn,'column':'amount','alias':'v'}]})
  96. values={r['values']['g']:r['values']['v'] for r in result['rows']}
  97. assert values=={'a':None,'b':'0'}
  98. def test_parallel_cache_reads_are_isolated(rows_object):
  99. def query(_):return read_rows(rows_object['id'],limit=11)
  100. with ThreadPoolExecutor(max_workers=8) as pool: results=list(pool.map(query,range(32)))
  101. expected=[r['_trace']['row_hash'] for r in results[0]['rows']]
  102. assert all([r['_trace']['row_hash'] for r in q['rows']]==expected for q in results)
  103. results[0]['rows'][0]['values']['G_MODEL']='mutated'
  104. assert read_rows(rows_object['id'],limit=11)['rows'][0]['values']['G_MODEL']=='MODEL-0000'
  105. def test_cache_eviction_is_bounded(rows_object,monkeypatch):
  106. import toolsvc.readers as readers
  107. monkeypatch.setattr(readers,'_CACHE',OrderedDict());monkeypatch.setattr(readers,'_CACHE_BYTES',0)
  108. for offset in range(70):read_rows(rows_object['id'],limit=2,offset=offset)
  109. assert len(readers._CACHE)<=64 and readers._CACHE_BYTES<=16*1024*1024
  110. assert not read_rows(rows_object['id'],limit=2,offset=0)['cache_hit']
  111. def test_timeout_http_408_then_connection_recovers(client,rows_object,monkeypatch):
  112. monkeypatch.setattr(settings,'query_timeout_ms',0)
  113. q={'object_id':rows_object['id'],'count_mode':'exact','use_cache':False}
  114. assert client.post('/tools/data_read',json=q).status_code==408
  115. monkeypatch.setattr(settings,'query_timeout_ms',5000)
  116. good=client.post('/tools/data_read',json=q)
  117. assert good.status_code==200 and good.json()['total']==257
  118. def test_cache_hits_still_record_distinct_data_plane_calls(client,rows_object):
  119. q={'object_id':rows_object['id'],'limit':3,'session_id':'cache-ledger-regression'}
  120. first=client.post('/tools/data_read',json={**q,'call_id':'regression_read_1'}).json()
  121. second=client.post('/tools/data_read',json={**q,'call_id':'regression_read_2'}).json()
  122. assert second['cache_hit'] and first['rows']==second['rows']
  123. calls=client.get('/ledger/calls',params={'session_id':q['session_id']}).json()['items']
  124. assert {'regression_read_1','regression_read_2'} <= {c['call_id'] for c in calls}
  125. def test_sql_injection_is_a_literal_filter_and_cannot_change_schema(client,rows_object):
  126. q={'object_id':rows_object['id'],'filters':[{'column':'G_MODEL','value':"' OR 1=1 --"}],'count_mode':'exact'}
  127. result=client.post('/tools/data_read',json=q)
  128. assert result.status_code==200 and result.json()['total']==0
  129. q['filters']=[{'column':'G_MODEL); DROP TABLE raw;--','value':'x'}]
  130. assert client.post('/tools/data_read',json=q).status_code==400
  131. assert read_rows(rows_object['id'],limit=1)['total']==257
  132. @pytest.fixture(scope='module')
  133. def stat_object(processed,sample_csv):
  134. ws=store.create_workspace('nonadditive policy')
  135. source=store.register_upload(ws['id'],'sample.csv',sample_csv.read_bytes())
  136. return store.register_processed(ws['id'],source['id'],'stat',processed['output'],processed['result'].to_dict())
  137. @pytest.mark.parametrize('metric',['价格指数','金额指数','数量指数','金额占比','数量占比','金额环比','数量环比','平均单价(美元/成交数量单位)'])
  138. @pytest.mark.parametrize('fn',['sum','avg'])
  139. def test_all_nonadditive_business_metrics_rejected(client,stat_object,metric,fn):
  140. q={'object_id':stat_object['id'],'table':'1.0-3.0_MLCC','aggregate':{'metrics':[{'fn':fn,'column':metric}]}}
  141. response=client.post('/tools/data_read',json=q)
  142. assert response.status_code==400 and '不能' in response.json()['detail']
  143. def test_external_masked_stat_has_no_automatic_additivity(client):
  144. ws=store.create_workspace('external masked')
  145. obj=store.register_upload(ws['id'],'已遮掩.csv','实际金额(美元)\n100-200\n'.encode(),kind='stat')
  146. response=client.post('/tools/data_read',json={'object_id':obj['id'],'aggregate':{'metrics':[{'fn':'sum','column':'实际金额(美元)'}]}})
  147. assert response.status_code==400
  148. def test_browser_fixture_totals_are_a_fixed_oracle(tmp_path,root):
  149. from toolsvc.customs import CustomsOptions,CustomsProcessRequest,load_processor
  150. from toolsvc.customs.engine.numbers import StrictSum
  151. expected=json.loads((root/'samples/ui_regression.expected.json').read_text())
  152. result=load_processor().process(CustomsProcessRequest(str(root/'samples/ui_regression.csv'),str(tmp_path/'ui'),options=CustomsOptions(directions=['I'],cutoff_date='2026-12-31')))
  153. assert [result.stats[k] for k in ('total','included','excluded')]==[63,60,3]
  154. with sqlite3.connect(result.sqlite_path) as db:
  155. db.create_aggregate('dsum',1,StrictSum)
  156. for table in ('q_class_month','q_model_month'):
  157. amount,quantity,n=db.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {table}').fetchone()
  158. assert Decimal(amount)==Decimal(expected['amount_usd'])
  159. assert Decimal(quantity)==Decimal(expected['quantity'])
  160. assert n==expected['included']