1
$\begingroup$

I have a perfectly good SQLSelect query that doesn't work if and only if it contains an extra < condition on a column already used. Details follow...

This SQLselect shows that the expected data exists in the database...

SQLSelect[conn, 
  "USDCHF_Transformed" , {"dateyyyymmdd", "Time", "msSinceEpoch", 
   "Bid"} , 
  SQLColumn["City"] == "AKL" && SQLColumn["Region"] == "ASI", 
  "Distinct" -> False, "MaxRows" -> 10]

returning,

{{"2017-04-02", "18:00:00.1240000", 3700144800124, 1.00266}, {"2017-04-02", "18:00:00.1470000", 3700144800147, 1.00263}, {"2017-04-02", "18:00:00.2510000", 3700144800251, 1.00258}, {"2017-04-02", "18:00:00.2730000", 3700144800273, 1.0023}, {"2017-04-02", "18:00:00.5380000", 3700144800538, 1.00261}, {"2017-04-02", "18:00:00.5440000", 3700144800544, 1.00264}, {"2017-04-02", "18:00:00.5780000", 3700144800578, 1.00245}, {"2017-04-02", "18:00:00.9000000", 3700144800900, 1.00258}, {"2017-04-02", "18:00:00.9200000", 3700144800920, 1.00254}, {"2017-04-02", "18:00:01.0940000", 3700144801094, 1.00233}}

I have defined a more complex function to return rows within a certain period of a start date/time

getCityDataFromDateTimeByMilliseconds[connection_, tableName_, cols_List, city_String, startDate_, startTime_, millis_Integer]:=
    Module[{msStart,msEnd},
        msStart = dateTimeToMs[startDate,startTime];
        msEnd = msStart + millis
        Print[msStart];
        Print[(msStart +millis)];
        SQLSelect[connection,tableName,cols, SQLColumn["City"]==city && msStart <=SQLColumn["msSinceEpoch"] && SQLColumn["msSinceEpoch"] <msEnd,"Distinct"->False, "MaxRows"->10]
        ]

When called as

getCityDataFromDateTimeByMilliseconds[conn, "USDCHF_Transformed", {"dateyyyymmdd", "Time", "msSinceEpoch", "Bid"} , "AKL", "2017-04-02", "18:00:00", 60000]

It returns no results - unless I remove the condition "&& SQLColumn["msSinceEpoch"] < msEnd", in which case it returns the same results as above.

Now, it is clear that the msStart and msEnd millisecond values are correct because they are, respectively, 3700144800000 and 3700144860000 from the debug print statements and the data shown clearly falls within those limits.

Question Why does the extra and valid < condition on SQLColumn["msSinceEpoch"] cause no results to be returned - and how do I fix it. (Is it a bug, or merely user error?)

For info: the JDBC connection is to MSSQL Server 2017 Developer Edition, the database has about 20 million rows, and I am using Mathematica 11.0.1.0

UPDATE Title changed to probable bug because, as described in comments there is a workaround: forcing the numeric reference value for comparison to Real on a database column that is in fact bigint. Thx to @Hans for prompting re-examination of the source code.

UPDATE 2 Has now been reported to Wolfram as a probable bug; if confirmed I will write an answer including the workaround.

$\endgroup$
  • $\begingroup$ There seems to be some typos in some of the pasted items. Missing open { in the "getCityDataFromDateTimeByMilliseconds" call. You may want a semicolon at msEnd assignment. I would suggest that you keep all SQLColumn on the left side of the value being compared.You may want to test with an equivalent SQLExecute[] $\endgroup$ – Hans Sep 2 '18 at 17:07
  • $\begingroup$ Try something like SQLExecute[conn, "SELECT TOP 10 dateyyyymmdd, Time, msSinceEpoch FROM USDCHF_Transformed WHERE (City = 'AKL' AND Region = 'ASI') AND (msSinceEpoch >= `1` AND msSinceEpoch < `2`)", {start,end}] $\endgroup$ – Hans Sep 2 '18 at 17:22
  • $\begingroup$ @Hans Thanks for the input, but no change... "{" typo corrected. ";" missing typo after msEnd added. No change. Tried SQLExecute[conn, "SELECT * FROM USDCHF_Transformed WHERE msSinceEpoch >3700144800000 AND msSinceEpoch <3700144860000"] and received 873 results (in line with expectations). Keeping all SQLColumn on LHS (note: MMA examples show e.g. a < sqlColumn < b so it shouldn't make any difference)... doesn't make any difference. Net: pure SQL works, SQLColumn doesn't. (NB how do you get the code highlight in comments?) $\endgroup$ – Julian Moore Sep 2 '18 at 18:56
  • $\begingroup$ @Hans note: it is the SQLColumn["msSinceEpoch"] <msEnd that leads to Null result; if it is present on its own (without msStart <=SQLColumn["msSinceEpoch"]) or the City condition the result is still Null. $\endgroup$ – Julian Moore Sep 2 '18 at 19:03
  • $\begingroup$ Found the example you speak of in your note here DatabaseLink/tutorial/SelectingData they do use x<SQLColumn< y. I would suggest then that you follow that style. (msStart <= SQLColumn["msSinceEpoch"] < msEnd) $\endgroup$ – Hans Sep 2 '18 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.