Assignment Override Flags Are Often Incorrect

[JimEgan]

I'll state up front that I may not understand the purpose of the OVERRIDDEN columns found in the assignment_overrides table.  However, given the documentation associated with the columns, then one or more of the values in the columns appear to be incorrect 57% of the time.

The columns in question are DUE_AT_OVERRIDDEN, UNLOCK_AT_OVERRIDDEN and LOCK_AT_OVERRIDDEN.

I noticed this when quickly looking at the raw data.  I noticed that all three columns were often set to TRUE, which seemed odd.  I will point out that not all the rows are incorrect.

Here's the query I'm using.

SELECT assignment_overrides.ASSIGNMENT_ID,
       due_at_overridden,    assignments.due_at,    assignment_overrides.due_at,
       unlock_at_overridden, assignments.unlock_at, assignment_overrides.unlock_at,
       lock_at_overridden,   assignments.lock_at,   assignment_overrides.lock_at
FROM assignment_overrides
     inner join assignments
       on (assignments.id = assignment_overrides.ASSIGNMENT_ID)
WHERE (assignment_overrides.due_at_overridden = 1
  and  (assignments.due_at = assignment_overrides.due_at
   or   (assignments.due_at is null and assignment_overrides.due_at is null)))
   or (assignment_overrides.unlock_at_overridden = 1
  and  (assignments.unlock_at = assignment_overrides.unlock_at
   or   (assignments.unlock_at is null and assignment_overrides.unlock_at is null)))
   or (assignment_overrides.lock_at_overridden = 1
  and  (assignments.lock_at = assignment_overrides.lock_at
   or   (assignments.lock_at is null and assignment_overrides.lock_at is null)))
;

A 10 row sample from the query is attached to this post.

The data is in an Oracle 19c database.  As the data is imported into Oracle, the TRUE/FALSE values from Canvas are converted to 1/0.

[JimEgan]

Is this something that I should be opening a ticket for?

[stimme]

@JimEgan Thanks for sharing your query to find the records where values for the x_overridden columns don't make sense intuitively. In my CD2 data, over 63% of the records meet those conditions. Your WHERE clauses express the intuition that when a date field is overridden for a student, group, or section, we should see a difference between the value for them & the value for everybody else (the value on the assignments table) by finding the records where overridden is true & there's no difference.

I can only think there is an issue with the current ETL process.

While exploring the data I compared unlock_at values for a couple identical assignments & quizzes, and I noticed that the quiz API returned the unlock_at value for everybody else but the assignment API returned the unlock_at value for the earliest override unlock_at date. I haven't comprehensively explored this phenomenon.

[JimEgan]

Thanks for taking a look at your data.  It gives me confirmation that I may not be on the wrong track.

At this time, I don't do anything through the API.  I work with just the CD2 data.