[samigo-team] Samigo Issue: Multiple answers from the same Submission ID

[Karen Tsao]

Hi Bryan,

I ran your query against our production db and found we do have couple
duplicates too. Among all the submissions (since 2006), we have 7
submissions contain duplicate answers (the earliest one is in 2008). I also
found most of these submissions are from test users. We suspect these are
created during load testing and maybe that is why we haven't heard any
reports regarding this issue.

I agree with you that "ASSESSMENTGRADINGID, PUBLISHEDANSWERID" should be
unique. However, because we don't know where these duplicates get created,
I am afraid users will get hibernate exception on UI if we add the unique
constraint without proper error handling. What do you think?

Thanks,
Karen

On Wed, Jun 27, 2012 at 2:32 PM, Bryan Holladay <holladay at longsight.com>wrote:

> Karen,
>
> I've done some data mining and found that this issue has been around
> since October 2010 and is showing up as late as last week.  Here is a
> sample of some data I found:
>
> Institution 1: 678,000 answers and 2 were duplicated at least twice
> Institution 2: 23,379,146 answers and 804 were duplicated at least twice
>
> Here is the query I'm using.  Could you verify that
> "ASSESSMENTGRADINGID, PUBLISHEDANSWERID" should be unique?  Also, do
> you mind looking to see if you also have duplicates?
>
>
> select * from
> (select *, count(*) s
> From SAM_ITEMGRADING_T sit
> where sit.PUBLISHEDANSWERID is not null
> and ASSESSMENTGRADINGID > 0
> and ASSESSMENTGRADINGID < 200000
> Group By ASSESSMENTGRADINGID, PUBLISHEDANSWERID) q
> where q.s > 1
>
> I am using the range in order for the query to actually finish.  It
> would be nice to see if anyone else is getting this issue?  Also, if
> so, could we just add a contraint for ASSESSMENTGRADINGID,
> PUBLISHEDANSWERID?
>
> Thanks in advance,
> Bryan
>
> On Fri, Jun 22, 2012 at 2:39 PM, Bryan Holladay <holladay at longsight.com>
> wrote:
> > We're running a 2.8.x branch with a lot of 2.9.x+ features backported.
> >  I was hoping I wasn't the first to run into this  :)
> >
> > [1]
> https://source.sakaiproject.org/svn/msub/longsight.com/samigo/samigo-2.8.x-tabbed-interface/
> > [2]
> https://source.sakaiproject.org/svn/msub/longsight.com/samigo/samigo-2.8.x-tabbed-interface/CUSTOMIZATIONS.txt
> >
> > On Fri, Jun 22, 2012 at 2:36 PM, Karen Tsao <ktsao at stanford.edu> wrote:
> >> Hi Bryan,
> >>
> >> We haven't heard of this issue. What Samigo version do you have? Except
> I
> >> notice the itemgradingid are in serious, I have no clue at this point.
> >> Please let us know if you have further findings.
> >>
> >> Thanks,
> >> Karen
> >>
> >>
> >> On Fri, Jun 22, 2012 at 6:30 AM, Bryan Holladay <holladay at longsight.com
> >
> >> wrote:
> >>>
> >>> Has anyone seen an issue with Samigo where a single submission will
> >>> have multiple answers to the same question in Multiple Choice Single
> >>> Correct (all default settings)?  This was discovered by noticing that
> >>> a student had an automated score that was higher than the question
> >>> value.  For example, one student had 14 answers to the same question
> >>> worth 2 points and received 28 points for that question.  We have
> >>> found several of these cases.
> >>>
> >>> select * From SAM_ITEMGRADING_T sit
> >>> right join SAM_PUBLISHEDANSWER_T spt on spt.ANSWERID =
> >>> sit.PUBLISHEDANSWERID
> >>> where sit.ASSESSMENTGRADINGID = 1085000
> >>>
> >>> ASSESSMENTGRADINGID     ITEMGRADINGID   AGENTID PUBLISHEDITEMID TEXT
> >>> 1085000 22658677        1273531 2092102 fill color
> >>> 1085000 22658676        1273531 2092102 fill color
> >>> 1085000 22658675        1273531 2092102 fill color
> >>> 1085000 22658674        1273531 2092102 fill color
> >>> 1085000 22658673        1273531 2092102 fill color
> >>> 1085000 22658672        1273531 2092102 fill color
> >>> 1085000 22658671        1273531 2092102 fill color
> >>> 1085000 22658670        1273531 2092102 fill color
> >>> 1085000 22658669        1273531 2092102 fill color
> >>> 1085000 22658668        1273531 2092102 fill color
> >>> 1085000 22658667        1273531 2092102 fill color
> >>> 1085000 22658666        1273531 2092102 fill color
> >>> 1085000 22658665        1273531 2092102 fill color
> >>> 1085000 22658664        1273531 2092102 fill color
> >>>
> >>> Thanks,
> >>> Bryan
> >>> _______________________________________________
> >>> samigo-team mailing list
> >>> samigo-team at collab.sakaiproject.org
> >>> http://collab.sakaiproject.org/mailman/listinfo/samigo-team
> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/samigo-team/attachments/20120719/7f69540a/attachment.html