* 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 [[https://issue.cacert.org/otrs/index.pl?Action=AgentZoom&TicketID=223211|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)" --- From: "INOPIAE (Marcus)" To: CAcert Support Cc: Benny Baumann , 'Michael Tänzer' 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. [[https://bugs.cacert.org/view.php?id=872#c4015 | 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 }}} . {{attachment:testresult.jpg}} . (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 == || [[Arbitrations/a20090424.1|a20090424.1]] || [[Arbitrations/a20090424.1|Ad hoc SQL query requested]] || || [[Arbitrations/a20090427.2|a20090427.2]] || [[Arbitrations/a20090427.2|Ad hoc SQL query requested]] || || [[Arbitrations/a20090518.2 |a20090518.2 ]] || [[Arbitrations/a20090518.2 |SQL: mail addresses of former assurers without the CATS passed]] || || [[Arbitrations/a20090525.1|a20090525.1]] || [[Arbitrations/a20090525.1|Event officer request recurrent notification to assurers near the location of the following ATEs]] || || [[Arbitrations/a20090810.3|a20090810.3]] || [[Arbitrations/a20090810.3|User requests a list of people who have more than 150 points]] || || [[Arbitrations/a20090902.1|a20090902.1]] || [[Arbitrations/a20090902.1|request list of OA]] || || [[Arbitrations/a20091221.1|a20091221.1]] || [[Arbitrations/a20091221.1|U18 query]] || || [[Arbitrations/a20100822.1|a20100822.1]] || [[Arbitrations/a20100822.1|SQL query]] || || [[Arbitrations/a20101114.1|a20101114.1]] || [[Arbitrations/a20101114.1|Addtl. adhoc interactive sql-query]] || || [[Arbitrations/a20110413.1|a20110413.1]] || [[Arbitrations/a20110413.1|How many users using sample pwd]] || ---- . CategoryArbitration . CategoryArbCaseSystemTasks