- Case Number: a20091221.1
- Status: closed
- Claimants: Werner Dworak (SE)
- Respondents: CAcert
- Case Manager: Lambert Hofstra
Arbitrator: UlrichSchroeter
- Date of arbitration start: 2009-12-21
- Date of ruling: 2009-12-23
- Case closed: 2009-12-23
- Complaint: Adhoc SQL-query about U18 cases
please demand a sql query over the CAcert live database to show how many assured members at present are below 18 years of age. And if possible ask how many members have been below age of 18 at the time of their first assurance. Further ask how many assurers are or have been below age of 18 years. Yes, I agree to CCA and DRP.
- Relief: TBD
Before: Arbitrator UlrichSchroeter (A), Respondent: CAcert (R), Claimant: Werner Dworak (C), Case: a20091221.1
History Log
2009-12-21 (UlrichSchroeter): added to wiki, request for CM / A
- 2009-12-21 (C): I accept CCA/DRP under this arbitration
2009-12-22 (A), (AlexanderPrinsier): deployment of sql queries
- 2009-12-22 (A): testing of deployed sql queries against a local testsystem database
- 2009-12-23 (A): sending sql query exec request to sysadmin team
- 2009-12-23 (A): rcvd answer with adhoc sql query results from sysadmin team
- 2009-12-23 (A): finishing ruling
Discovery
"please demand a sql query over the CAcert live database to show how many - assured - members at present are below 18 years of age"
- q1:
- Description: Total users
- Query .....: SELECT * FROM cacert.users;
- q2:
- Description: count users U18
Query .....: SELECT count(id) FROM cacert.users where dob > '1991-12-21';
- "Further ask how many assurers are or have been below age of 18 years"
- q3:
- Description: count users U18 and Is Assurer
Query .....: SELECT count(id) FROM cacert.users where dob > '1991-12-21' and assurer=1;
"please demand a sql query over the CAcert live database to show how many assured members at present are below 18 years of age" (see q1, q2)
- q4:
Query: select count(*) from (SELECT count(users.id) FROM cacert.users right join cacert.notary on cacert.notary.to=cacert.users.id where cacert.users.dob > '1991-12-21' group by cacert.users.id) as subq;
"ask how many members have been below age of 18 at the time of their first assurance"
- q5:
Description: by aphexer, untested, amount of members who were assured when they were <18 years old)
Query .....: SELECT count(*) FROM users,notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0;
- q6:
- Desrciption: (tested query) total count assurances before reaching age of 18 years
Query .....: SELECT count(*) FROM cacert.users, cacert.notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0;
- q7:
- Description: (tested query) total count of users who were assured before they were 18 years or older
Query .....: select count(*) from (SELECT count(users.id) FROM cacert.users, cacert.notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0 group by users.id) as subq;
- The count base of users (q1) includes disabled users, locked users, user accounts that probably were not be enabled, so it includes many inactive accounts. Should this count as the base ?
the Statistics script lists (Dec 22nd, 2009) round about 22,000 (22K) individual assured users:
Users with 1-49 Points
5,137
Users with 50-99 Points
3,974
Assurer Candidates
9,880
Assurers with test
3,151
- The new users statistics for 2009 displays 29,252 and 161,402 total new users (2002-2009) so limiting query1 to the assured users base gives the best realistic results
- q1b:
- Description: counts total, unique assured users
Query .....: select count(*) from (select count(cacert.notary.to) from cacert.notary group by cacert.notary.to having sum(cacert.notary.points) > 0) as subq;
- Original questions relates to q4, q7, q3
- Addtl question q1b results in the baseline of all assured users
Query Results includes only statistical data like the Statistics script. No PII or other security issues are affected by the adhoc queries. No laws and policies to the protection of persons under age or against discrimination are affected.
The Adhoc query is the result of the direct policy work on Policy On Junior Members and Assurers (2) to get a count base they are work for
A: This is expressly provided for in Policy. The Role of Arbitration in cases such as this is to provide for governance and control. Therefore I issue the following:
Ruling
Dear Sysadmin Team, please execute following 4 adhoc sql queries against the CAcert database:
query 1 (reference: q1b) select count(*) from (select count(cacert.notary.to) from cacert.notary group by cacert.notary.to having sum(cacert.notary.points) > 0) as subq;
query 2 (reference: q4) select count(*) from (SELECT count(users.id) FROM cacert.users right join cacert.notary on cacert.notary.to=cacert.users.id where cacert.users.dob > '1991-12-21' group by cacert.users.id) as subq;
query 3 (reference: q7) select count(*) from (SELECT count(users.id) FROM cacert.users, cacert.notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0 group by users.id) as subq;
query 4 (reference: q3) SELECT count(id) FROM cacert.users where dob > '1991-12-21' and assurer=1;
The expected results doesn't include any privacy informations. So therefor, the results can probably be published. But, before publishing the results, Lambert (CM) and I (A) will have to do a review on the results. So please, send us the results by replying to this email.
There is no data included in the results, that prevents publishing of this data by any policy, or that breaches any rules or policys. So the results can be published like other statistical data under the CAcert main website.
Frankfurt/Main, Dec 23th 2009
Execution
- 2009-12-23 (A) sent results to (C)
2009-12-23 (C): published result on cacert-policy mailing list
- 2009-12-23 (A): case closed
Similiar Cases