empty course_id returned by UDW course query; and unreleased grade returned by UDP submission query

[zqian]

Problem 1: Expected behavior (A description of what you expected to happen) :

We observed this problem with UDW query run on certain course, that the course_id field is returned as empty.

For example, try execute the following UDW query for Users table, you can see two records return with null course_id values

		with
        enroll_data as (select id as enroll_id, user_id, type from enrollment_dim where course_id in ('17700000000000256')
                        and type in ('StudentEnrollment', 'TaEnrollment', 'TeacherEnrollment') and workflow_state= 'active'),
        user_info as (select p.sis_user_id, u.id as user_id, u.global_canvas_id
                    from (SELECT ROW_NUMBER() OVER (PARTITION BY user_id order by sis_user_id asc) AS row_number, * FROM pseudonym_dim) as p
                    join user_dim u on u.id = p.user_id WHERE row_number = 1),
        user_enroll as (select u.sis_user_id, u.user_id, e.enroll_id,
                        u.global_canvas_id, e.type from enroll_data e join user_info u on e.user_id= u.user_id),
        course_fact as (select enrollment_id, course_id, current_score, final_score from course_score_fact
                        where course_id  in ('17700000000000256')),
        final as (select cast(u.global_canvas_id as BIGINT) as user_id,
                c.course_id as course_id, c.current_score as current_grade, c.final_score as final_grade,
                u.type as enrollment_type
                from user_enroll u left join course_fact c on u.enroll_id= c.enrollment_id)
        select * from final
        order by user_id

Problem 1: Steps to Reproduce:

1. add MyLA to certain course (id=256) for example
2. run MyLA UDW query
3. notice some of the records in Users table has null course_id values

Problem 12 Expected behavior (A description of what you expected to happen) :

We observed this problem with UDP submission query returns grade for a unreleased student grade with partial auto-graded quiz

Problem 1: Steps to Reproduce:

1. we used a certain MyLA test course. It contains a quiz with auto-grade for three questions, and one manual grade item
2. teacher did not release the manual grade item grade yet; student can see the grade in Quiz tool, but not in Gradebook
3. UDW submission query did not return this grade, while UDP submission query returns it

[jennlove-um]

I've tested both problems in myla-beta. Testing passes.

1. All users have a course id. There are no nulls in the course_id column.
2. UDP query now correctly filters out the grade for the student who has not had their quiz graded.

[jennlove-um]

I've tested the UDW issue in beta - it passes again. All users have a course id in the user table.

[jennlove-um]

I've tested both problems in myla-test. Testing passes.

1. All users have a course id. There are no nulls in the course_id column.
2. UDP query now correctly filters out the grade for the student who has not had their quiz graded.