• Arbitrations
• a20130521.1

• Case Number: a20130521.1
• Status: closed
• Claimants: Marcus Mängel
• Respondents: CAcert

• Initial Case Manager: AlexRobertson

• Case Manager: UlrichSchroeter

• Arbitrator: name AlexRobertson

• Date of arbitration start: 2013-05-26
• Date of ruling: 2013-05-27
• Case closed: 2013-05-28
• Complaint: Adhoc SQL query: Dispute to get some statisical data
• Relief: TBD

Before: Arbitrator AlexRobertson (A), Respondent: CAcert (R), Claimant: Marcus Mängel (C), Case: a20130521.1

History Log

• 2013-05-25 (issue.c.o) case s20130521.100

• 2013-05-25 (iCM): added to wiki, request for CM / A
• 2013-05-26 (A) I take this case and appoint Ulrich Schroeter as CM
• 2013-05-26 (A) email to Benny Baumann
• 2013-05-26 (A) email from CM
• 2013-05-26 (A) email from CM
• 2013-05-27 (A) ruling
• 2013-05-27 (A) email to critical-admin team
• 2013-05-27 (A) response from critical-admin team
• 2013-05-28 (A) response from Benny Baumann
• 2013-05-28 (A) closed

Original Dispute

• ---- Forwarded message from "INOPIAE (Marcus)" <inopiae@cacert.org> ---
  
  From: "INOPIAE (Marcus)" <inopiae@cacert.org>
  To: CAcert Support <support@cacert.org>
  Cc: Benny Baumann <benbe@cacert.org>, 'Michael Tänzer' <michael.taenzer@cacert.org>
  ReplyTo: inopiae@cacert.org
  Subject: Dispute to get some statisical data from critical admins regarding PoJAM cases
  Date: 2013-05-21 21:23:44
  
  > Hi guys,
  > 
  > the software team would like to have some information about the 
  > statics of PoJAM users.
  > 
  > Please execute the following sql-statements on the live stytem and 
  > give the results back to the software team.
  > 
  > Select count(1), YEAR(`users`.`dob`) as year from `users` where
  > YEAR(`users`.`dob`)>=1995 Group by YEAR(`users`.`dob`)
  > 
  > 
  > SELECT
  >      count( `temp`.`no` ) AS AffectedUsers,
  >      sum( `temp`.`assurances` ) AS AffectedAssurances,
  >      if(points = 0, "No points", if(points < 50, "1 < x < 50", 
  > if(points
  > 
  > < 100, "50 <= x < 100", "100 <= x"))) AS ReceivedPoints FROM (
  >      SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` 
  > ) AS points
  >      FROM `users`, `notary`
  >      WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to`
  >      GROUP BY `users`.`id`
  >      ) AS `temp`
  > GROUP BY ReceivedPoints
  > 
  > 
  > I accept CCA.
  > 
  > Marcus Mängel
  > CARS
  > 
  > --

Discovery

• 2013-05-26 (A) Part of email from (CM)

  the software-assessors reviewed the sql statements in last Tuesdays
  Software-Assessment project telco (this info is missed in the dispute filing :-P )
  https://wiki.cacert.org/Software/Assessment/20130521-S-A-MiniTOP
  so the sql statements are still "verified" and "tested" by a software-assessor (-> Benny Baumann)

. 2013-05-26 (A) Whilst the statements may have been reviewed, there is no evidence that they have actually been tested. Bugnote 872/4015 only indicates the "combined statement" - request for confirmation of testing sent to Benny Baumann

• 2013-05-26 (A) Part of email from (CM)

  I've just hacked the proposed sql query into the
  dev-image of the local cacert1 test server
  that is available thru https://wiki.cacert.org/SystemAdministration/Systems/Development
  and the http://cacert.nhng.de/cacert-testserver/20120404/cacert1.it-sls.de.ova
  or http://www.avintec.com/it-sls/20120404/cacert1.it-sls.de/
  links
  that results in attached output

• 

• (A) 2013-05-27 Following review of the SQL code by myself and test by (CM), it is clear that the SQL executes correctly and that no personal data is visible. Therefore I will permit the code to be executed against the live system.
• (A) 2013-05-28 Response from Benny Baumann

  I have tested the proposed SQL statement on the testserver (DB) while writing the statement and verified it to produce sane results (as far as can be said from the testserver database). This was done by comparing the unfiltered results of the inner statement and counting the grouped results (comparing they matched). The inner statement is straight forward enough thus can be easily seen that the filtered results from there match (close enough) those accounts we want to get statistics on.

Ruling

Critical team are ordered to execute the SQL provided and to pass the results to Marcus with a copy to myself as (A) and Ulrich as (CM)

Alex Robertson
CAcert Arbitrator
Crewe, UK
27th May 2013

Execution

• (A) 2013-05-27 Mail to critical admin team

  Hi Wytze, Mendel
  
  regarding arbitration case a20130521.1
  https://wiki.cacert.org/Arbitrations/a20130521.1
  
  please execute following two adhoc sql queries
  against the CAcert database:
  
  SELECT count(1), YEAR(`users`.`dob`) as year from `users` WHERE
     YEAR(`users`.`dob`)>=1995 Group by YEAR(`users`.`dob`)
  
  SELECT
        count( `temp`.`no` ) AS AffectedUsers,
        sum( `temp`.`assurances` ) AS AffectedAssurances,
        if(points = 0, "No points", if(points < 50, "1 < x < 50", if(points< 100, "50 <= x < 100", "100 <= x"))) AS ReceivedPoints 
        FROM ( SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points
        FROM `users`, `notary`
        WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to`
        GROUP BY `users`.`id`
        ) AS `temp`
  GROUP BY ReceivedPoints
  
  ....
  
  The expected results do not include any privacy information, therefore the results can be published.
  
  Please can you send the results directly to Marcus with a copy to myself as Arbitrator and Ulrich as Case Manager
  
  Thanks in advance

• (A) 2013-05-27 - response and resultset received from critical-admin team

Similiar Cases

• CategoryArbitration

• CategoryArbCaseSystemTasks