Pull every 'A to B' where A exceeds B: 55 of them, and 23 hide a real error

About a 4 minute read.

At the end of last time I mentioned something small: two records give an evaluation concentration of 1000%, and a concentration cannot exceed 100%.

That error is easy to catch because the field has a physical ceiling. Which made me wonder: what other checks need no chemistry at all?

This article runs three.

Check one: a physical ceiling

The assay (purity) field has 27,221 values, and purity cannot exceed 100%.

Exactly one record does: zinc oxide (#42155, 30 suppliers), reading 800.00 to 100.00.

One. Out of 27,221. This field is remarkably clean.

Check two: a plausible range

Refractive index has 4,609 values. A liquid's refractive index cannot fall below 1 (that would mean light travelling faster in it than in vacuum), and aroma materials sit between about 1.3 and 1.6.

Pulling everything outside 1.0–1.8 gave 5 records — and one of them was my own probe being wrong.

Methyl mercaptan's refractive index reads "1.43000 to 1.43600 @ 5.80 °C", which is entirely normal; my regular expression picked up the 5.80 from the temperature.

Worth writing down: when sweeping a database, strip everything after the @ first. I made a similar mistake in the material name matching article — a probe generates its own false positives, and what it pulls only counts once a person has read it.

Check three: range ordering (the most useful one)

This one requires no knowledge of what the field means.

If a value is written "A to B", then A should be less than or equal to B. Whether A is a purity, a density, a temperature or a refractive index.

Across the five fields there are 42,802 such ranges:

Field	Ranges	A > B	Share
assay	27,221	6	0.02%
specific_gravity	4,184	23	0.55%
refractive_index	4,113	12	0.29%
bp	6,199	13	0.21%
mp	1,085	1	0.09%
Total	42,802	55	0.13%

55 of them, across 55 records. And dividing the larger by the smaller splits them cleanly in two.

Kind one: merely written backwards (32)

Ratio ≤1.15 — both numbers are plausible, they are just in the wrong order:

Suppliers	Material	Field	Value
107	Juniper berry oil	Specific gravity	0.86900 to 0.85900
50	Camphor tree bark oil	Specific gravity	0.88500 to 0.87500
26	Curled parsley leaf oil	Specific gravity	0.98000 to 0.94000
19	2-thiophene thiol	Specific gravity	1.25000 to 1.23500
16	2,6-dimethyl thiophenol	Refractive index	1.52700 to 1.52100

This kind is harmless to a person. You see 0.869 to 0.859 and read it as 0.859 to 0.869.

But it breaks any program. Code that takes the first number as the lower bound gets a lower bound above its upper bound on juniper berry oil — a record with 107 suppliers, not an obscure one.

Kind two: the inversion exposes a real error (23)

Ratio above 1.15. And within this kind there are three recognisable signatures.

Signature one: a lost decimal place (ratio of almost exactly ten).

Suppliers	Material	Value	Ratio
31	Isoamyl cinnamate	0.99200 to 0.09970	9.9
25	Muguet carboxaldehyde	9.93000 to 1.00100	9.9
3	Methyl 3-(methylthio) butanoate	1.03400 to 0.10400	9.9
2	Dimethyl benzyl carbinyl crotonate	0.99500 to 0.10030	9.9
4	Ethyl 2-mercapto-2-methyl propionate	0.96100 to 0.10810	8.9

Five records, all in specific gravity, all a factor of ten out. The correct values should be 0.9970, 0.9930, 1.0400, 1.0030 and 1.0810.

Signature two: a dropped zero in the purity field.

Suppliers	Material	Value
7	Sodium ascorbyl phosphate	95.00 to 10.00
3	Pullulan	90.00 to 10.00
2	Methyl 2-(methylthio) propionate	95.00 to 10.00

"95.00 to 10.00" is almost certainly "95.00 to 100.00." The same error appears three times — that is not a random slip, it is a fixed failure in one transcription process.

(Zinc oxide's 800.00 to 100.00 runs the other way: one zero too many, and should read 80.00.)

Signature three: something else has leaked into the boiling point field.

Suppliers	Material	Value
36	Zingerone	141.00 to 0.50
5	4-methyl salicylaldehyde	760.00 to 223.00

Zingerone's 0.50 is almost certainly a pressure (0.50 mm Hg) with the separating @ lost. And 760.00 is standard atmospheric pressure in millimetres of mercury — in both cases a pressure has landed where a temperature belongs.

The largest of them is the pear ester (46 suppliers), whose refractive index reads 1.48000 to 1.14860. 1.14860 is too low for a liquid; 1.4860 lands exactly where it should. One extra 1. (That material has its own article.)

What these three checks share

None of them requires knowing what the field is about.

• Purity cannot exceed 100 — because that is what a percentage means
• A refractive index cannot fall below 1 — because that is what a refractive index means
• In "A to B", A cannot exceed B — because that is what "to" means

I looked up no material's correct value and compared against no outside source, and found 55 problems.

The acetophenone unit error I found earlier (3200 ml/kg converting to 3.3× the animal's body weight) and those two 1000% concentrations came from the same kind of reasoning.

If you keep your own materials sheet, run these three first. They cost almost nothing.

What this doesn't establish

• I swept only five fields. Other fields with ranges (flash point, vapour pressure, logP) I did not run.
• "A to B" is matched by a regular expression, so ranges written differently (a hyphen, a comma) are missed and 55 is a floor.
• Every correct value here is inferred from the ratio. I consulted no supplier specification and no handbook. Those inferences may be wrong — especially the three "95.00 to 10.00 should be 100.00" records, where I only recognised the pattern.
• The 1.15 boundary between the two kinds is arbitrary, and a different threshold reclassifies records.
• A 0.13% error rate does not mean the database is 0.13% wrong. These checks see only shape errors. A record reading 0.85 to 0.87 that should read 0.95 to 0.97 is completely invisible here.
• My own probe produced a false positive (reading a temperature after @ as a refractive index), so any automated sweep needs a human to read the output.