-- Tests for Spectrum User Defined Data Handling.

-- 1 invalid Character Handling
-- 1.1 DISABLED which is the same as SET_TO_NULL
alter table schema_spectrum_uddh.soccer_league
set table properties ('invalid_char_handling'='DISABLED');

Select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 1.2 DROP_ROW
alter table schema_spectrum_uddh.soccer_league 
set table properties ('invalid_char_handling'='DROP_ROW');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 1.3 FAIL
alter table schema_spectrum_uddh.soccer_league 
set table properties ('invalid_char_handling'='FAIL');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 1.4 REPLACE with '?'
alter  table spectrum_schema_uddh.Tokyo2021Medals
set table properties ('invalid_char_handling'='REPLACE','replacement_char'='?');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;


-- 2- Surplus Charcter Handling
-- 2.1- DISABLED which is the same as TRUNCATE

alter table schema_spectrum_uddh.soccer_league 
set table properties ('surplus_char_handling' = 'DISABLED');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;


-- 2.2 SET_TO_NULL

alter table schema_spectrum_uddh.soccer_league 
set table properties ('surplus_char_handling' = 'SET_TO_NULL');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 2.3 DROP_ROW

alter table schema_spectrum_uddh.soccer_league 
set table properties ('surplus_char_handling' = 'DROP_ROW');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;


-- 2.4 FAIL

alter table schema_spectrum_uddh.soccer_league 
set table properties ('surplus_char_handling' = 'FAIL');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- reset the data handling so we can continue testing the other scenario
alter table schema_spectrum_uddh.soccer_league 
set table properties ('surplus_char_handling' = 'DISABLED');

-- 3- Numeric OverFlow Handling
-- 3.1- DISABLED will truncate to smallint max.

alter table schema_spectrum_uddh.soccer_league 
set table properties ('numeric_overflow_handling' = 'DISABLED');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 3.2 SET_TO_NULL

alter table schema_spectrum_uddh.soccer_league 
set table properties ('numeric_overflow_handling' = 'SET_TO_NULL');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 3.3. DROP_ROW

alter table schema_spectrum_uddh.soccer_league 
set table properties ('numeric_overflow_handling' = 'DROP_ROW');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 3.4 FAIL

alter table schema_spectrum_uddh.soccer_league 
set table properties ('numeric_overflow_handling' = 'FAIL');

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- disable user defined data handling so we can test the next feature.
alter table schema_spectrum_uddh.soccer_league
set table properties ('numeric_overflow_handling' = 'DISABLED');


-- 4- Setting Maximum Error Threshold
-- first pass test

Set spectrum_query_maxerror to 7;

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- second fail test

Set spectrum_query_maxerror to 6;

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

-- 5- Query svl_spectrum_scan_error table

SELECT *
FROM svl_spectrum_scan_error
where location = 's3://uddh-soccer/league/spi_global_rankings.csv'