Not sure if this is technically a bug or missing feature, but scientific notation is not recognized when filtering from metadata. I've attached a simple metadata file for which I would expect the following two commands to give the same results
However, the very small p-value in scientific notation does not evaluate to less than .05 in the query. I was able to work around it by adding the (poorly named) NullHyp column, but was still bummed that I couldn't use the compound query.
@Chris_Hemmerich — you will need to cast your numeric data to numeric types in order to use those SQLite comparison operators. Check out this post for an example. Without a CAST, those values are actually being compared as strings — this means your greater than and less than operators are probably using the string length, instead of the numeric value. Give that a shot and let us know how it goes!
PS - you might also need to experiment with quoting your column names in the query — check out the sqlite.org docs for more info. Also, I didn’t see any examples of scientific notation in either of your examples, just ordinary real numbers in decimal representation.
Thanks, I will give this a shot and report back. The third data row is the scientific notation, R went a little nuts with significant digits. In my full data, the p-values are a mixture of decimal and scientific notation and the query works fine for the values in decimal notation, but I’ll get in the habit of using cast since this isn’t expected to work.
Thanks for the clarification! SQLite supports scientific notation — my earlier post was just to make you aware of the fact that all the data is loaded initially as strings — right now it is up to you to tell SQLite what “type” a particular column is. You should be able to leave the data formatted as-is and just update the queries to CAST those columns as numeric datatypes.
This is definitely something we need to document, and hopefully we will have better (more transparent) support for metadata typing in the future!