From 52d954cc8f7b276d80d07e1e01fe2a91a84ab3bd Mon Sep 17 00:00:00 2001
From: Arun Isaac
Date: Thu, 27 Oct 2022 15:57:15 +0530
Subject: Document database defects.

---
 issues/database-defects.gmi | 55 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 55 insertions(+)
 create mode 100644 issues/database-defects.gmi

(limited to 'issues')

diff --git a/issues/database-defects.gmi b/issues/database-defects.gmi
new file mode 100644
index 0000000..270f421
--- /dev/null
+++ b/issues/database-defects.gmi
@@ -0,0 +1,55 @@
+# Errors, defects and missing data in the database
+
+There are a number of errors in the database, which we try to track in this issue. These are best fixed directly in the database rather than by working around them in code.
+
+## LRS values listed as 0.000
+
+Some LRS values in the database are wrongly listed as 0.000. They should be NULLs.
+```
+MariaDB> SELECT COUNT(*) FROM ProbeSetXRef WHERE LRS=0;
++----------+
+| COUNT(*) |
++----------+
+|   237327 |
++----------+
+
+MariaDB> SELECT COUNT(*) FROM PublishXRef WHERE LRS=0;
++----------+
+| COUNT(*) |
++----------+
+|     1687 |
++----------+
+```
+
+## Years are sometimes blank or other strings
+
+Some years are blank strings or strings that contain text other than the year. These should be fixed and the year field must be made an integer.
+```
+MariaDB> SELECT COUNT(*) FROM Publication WHERE CONVERT(Year, UNSIGNED)=0;
++----------+
+| COUNT(*) |
++----------+
+|      854 |
++----------+
+```
+
+## E-mail IDs with spaces
+
+At least one e-mail IDs has spaces in it.
+```
+MariaDB> SELECT * FROM Investigators WHERE Email LIKE '% %';
+```
+
+## Blank E-mail IDs
+
+At least one e-mail ID is blank.
+```
+MariaDB> SELECT * FROM Investigators WHERE Email="";
+```
+
+## Encodings and unprintable characters
+
+At least one last name is encoded incorrectly and appear as unprintable characters.
+```
+MariaDB> SELECT LastName FROM Investigators WHERE FirstName='Yohan';
+```
-- 
cgit v1.2.3