Overview
This document outlines the results obtained by running the pseudoqueries specified in the ODE Metadata Bug Impact, Resolution, and Prevention document.
...
| Query Number | Query Name | Pseudoquery | Result | Notes |
|---|---|---|---|---|
| 1 | totalRecordCount | SELECT COUNT(*) WHERE AND | 3,601,067 | 28,730 records found in these files DID NOT satisfy the query constraint. |
| 2 | timBroadcastRecordCount | SELECT COUNT(*) WHERE | 95,065 | 3,534,732 did not satisfy constraint |
| 3 | goodOtherRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 2/13/2019 AND metadata.odeReceivedAt < dateOfBugFix AND metadata.payloadType != us.dot.its.jpo.ode.model.OdeBsmPayload AND metadata.recordGeneratedBy != TMC | 0 | 3,629,797 did not satisfy constraint |
| 4 | badBsmRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 12/3/2018 AND metadata.odeReceivedAt < dateOfBugFix AND metadata.payloadType == us.dot.its.jpo.ode.model.OdeBsmPayload | 3,506,002 | 123,795 did not satisfy constraint |
| 5 | badOtherRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 12/3/2018 AND metadata.odeReceivedAt <= 2/12/2019 AND metadata.recordGeneratedBy != TMC AND metadata.payloadType != us.dot.its.jpo.ode.model.OdeBsmPayload | 0 | 3,629,797 did not satisfy constraint |
| 6 | removableRecordCount | removableRecordCount = badBsmRecordCount + badOtherRecordCount | 3,506,002 + 0 = 3,506,002 | |
7 | totalRecordCount (correctness) | totalRecordCount equal to (==) (timBroadcastRecordCount + removableRecordCount + goodOtherRecordCount) | totalRecordCount = 3,601,067 timBroadcastRecordCount + removableRecordCount + goodOtherRecordCount = 95,065 + 3,506,002 + 0 = 3,601,067 | (Correct) |
| 8 | earliestGeneratedAt |
| 1970-01-01 00:00:00 | From null data. |
| 9 | latestGeneratedAt |
| 2019-04-11 15:45:58.766000 | |
| 10 | invalidRecordCount | invalidRecordCount = removableRecordCount | 3,506,002 | For clarification only. |
| 11 | invalidS3FileCount |
| 1,488 files | |
| 12 | invalidS3Files |
| ||
| 13 | listOfLogFilesBefore |
| 4,754 files | |
| 14 | validRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 2/13/2019 AND AND metadata.recordGeneratedBy != TMC AND | 2,012,382 | 1,617,415 records did not satisfy query constraints |
| 15 | validS3FileCount | SELECT COUNT(s3-filename) WHERE metadata.odeReceivedAt >= 2/13/2019 AND AND [ | 920 | |
| 16 | validS3Files | SELECT s3FileName WHERE AND metadata.serialId.bundleSize > 1] | valid_s3_file_list_fixed.txt | |
| 17 | listOfLogFilesAfter | SELECT metadata.logFilename WHERE [ | 3,087 files |
...
| Query Number | Query Name | Pseudoquery | Result | Expected Result | Matches Expected? | Notes |
|---|---|---|---|---|---|---|
| 1 | totalRecordCount | SELECT COUNT(*) WHERE AND | 95,065 | n/aa | 36,127 did not satisfy query constraints 131,192 total records in | |
| 2 | timBroadcastRecordCount | SELECT COUNT(*) WHERE | 95,065 | 95,065 (same as before deletion) | Yes | |
| 3 | goodOtherRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 2/13/2019 AND metadata.odeReceivedAt < dateOfBugFix AND metadata.payloadType != us.dot.its.jpo.ode.model.OdeBsmPayload AND metadata.recordGeneratedBy != TMC | 0 | n/a | Yes | |
| 4 | badBsmRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 12/3/2018 AND metadata.odeReceivedAt < dateOfBugFix AND metadata.payloadType == us.dot.its.jpo.ode.model.OdeBsmPayload | 0 | 0 | Yes | |
| 5 | badOtherRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 12/3/2018 AND metadata.odeReceivedAt <= 2/12/2019 AND metadata.recordGeneratedBy != TMC AND metadata.payloadType != us.dot.its.jpo.ode.model.OdeBsmPayload | 0 | 0 | Yes | |
| 6 | removableRecordCount | removableRecordCount = badBsmRecordCount + badOtherRecordCount | 0 | 0 | Yes | |
7 | totalRecordCount (correctness) | totalRecordCount equal to (==) (timBroadcastRecordCount + removableRecordCount + goodOtherRecordCount) | totalRecordCount = 95,065 timBroadcastRecordCount + removableRecordCount + goodOtherRecordCount = 95,065 + 0 + 0 = 95,065 | (expected to be correct) | Yes | |
| 8 | earliestGeneratedAt |
| 2019-04-11 15:40:30.395000 | 1970-01-01 00:00:00 (same as before deletion) | No | |
| 9 | latestGeneratedAt |
| 2019-04-11 15:40:30.395000 | 2019-04-11 15:45:58.766000 (same as before deletion) | No | |
| 10 | invalidRecordCount | invalidRecordCount = removableRecordCount | 0 | 0 | Yes | |
| 11 | invalidS3FileCount |
| 0 - empty list | 0 | Yes | |
| 12 | invalidS3Files |
| 0 - empty list | (empty list) | Yes | |
| 13 | listOfLogFilesBefore |
| 0 - empty list | 0 (empty list) | Yes | |
| 14 | validRecordCount | SELECT COUNT(*) WHERE metadata.odeReceivedAt >= 2/13/2019 AND AND metadata.recordGeneratedBy != TMC AND | 0 | 0 | Yes | |
| 15 | validS3FileCount | SELECT COUNT(s3-filename) WHERE metadata.odeReceivedAt >= 2/13/2019 AND AND [ | 0 - empty list | 0 - empty list | Yes | |
| 16 | validS3Files | SELECT s3FileName WHERE AND metadata.serialId.bundleSize > 1] | 0 - empty list | 0 - empty list | Yes | |
| 17 | listOfLogFilesAfter | SELECT metadata.logFilename WHERE [ | 0 - empty list | 0 - empty list | Yes |