| 1 | -- SQL invoked from regtest/aports-html.sh
|
| 2 |
|
| 3 | update diff_merged
|
| 4 | set
|
| 5 | baseline_HREF = printf('%s/%s', shard, baseline_HREF),
|
| 6 | osh_as_sh_HREF = printf('%s/%s', shard, osh_as_sh_HREF),
|
| 7 | error_grep_HREF = printf('%s/%s', shard, error_grep_HREF),
|
| 8 | -- note: suite/suite_HREF are sometimes empty
|
| 9 | suite_HREF = printf('%s/%s', shard, suite_HREF);
|
| 10 |
|
| 11 | -- Useful queries to verify the result:
|
| 12 | -- SELECT COUNT(*) as total_rows FROM diff_merged;
|
| 13 | -- SELECT shard, COUNT(*) as row_count FROM diff_merged GROUP BY shard ORDER BY shard;
|
| 14 | -- .schema diff_merged
|
| 15 |
|
| 16 | create table notable_disagree as
|
| 17 | select *
|
| 18 | from diff_merged
|
| 19 | where disagree == 1 and status1 == 0 and timeout == 0;
|
| 20 |
|
| 21 | create table timeout_disagree as
|
| 22 | select *
|
| 23 | from diff_merged
|
| 24 | where disagree == 1 and timeout == 1;
|
| 25 |
|
| 26 | create table baseline_only as
|
| 27 | select *
|
| 28 | from diff_merged
|
| 29 | where disagree == 1 and status2 == 0 and timeout == 0;
|
| 30 |
|
| 31 | create table both_timeout as
|
| 32 | select *
|
| 33 | from diff_merged
|
| 34 | where disagree == 0 and timeout == 1;
|
| 35 |
|
| 36 | create table both_fail as
|
| 37 | select *
|
| 38 | from diff_merged
|
| 39 | where disagree == 0 and timeout == 0;
|
| 40 |
|
| 41 | -- Drop 2 columns from each of 3 tables (sqlite is verbose)
|
| 42 |
|
| 43 | alter table notable_disagree
|
| 44 | drop column disagree;
|
| 45 | alter table notable_disagree
|
| 46 | drop column timeout;
|
| 47 |
|
| 48 | alter table timeout_disagree
|
| 49 | drop column disagree;
|
| 50 | alter table timeout_disagree
|
| 51 | drop column timeout;
|
| 52 |
|
| 53 | alter table baseline_only
|
| 54 | drop column disagree;
|
| 55 | alter table baseline_only
|
| 56 | drop column timeout;
|
| 57 |
|
| 58 | alter table both_fail
|
| 59 | drop column disagree;
|
| 60 | alter table both_fail
|
| 61 | drop column timeout;
|
| 62 |
|
| 63 | alter table both_timeout
|
| 64 | drop column disagree;
|
| 65 | alter table both_timeout
|
| 66 | drop column timeout;
|
| 67 |
|
| 68 | -- Create cause histogram
|
| 69 |
|
| 70 | create table cause_hist as
|
| 71 | -- sqlite is dumb: count(*) doesn't have integer type by default, you have to cast it!
|
| 72 | select cast(count(*) as integer) as num, cause, cause_HREF
|
| 73 | from notable_disagree
|
| 74 | group by cause
|
| 75 | having count(*) > 1 -- causes that happen more than once
|
| 76 | order by num desc;
|