## Probable Bug in SQLColumn condition < upper limit for SQLSelect

1

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.

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[] – Hans – 2018-09-02T17:07:58.443

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}] – Hans – 2018-09-02T17:22:32.110

@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?) – Julian Moore – 2018-09-02T18:56:48.390

@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. – Julian Moore – 2018-09-02T19:03:52.683

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) – Hans – 2018-09-02T19:12:48.300

@Hans I have a fix which reinforces the idea this is a bug. Replace the msEnd assignment with msEnd = msStart + millis + 0.; to make msEnd Real rather than Integer and then the SQLSelect function works. Note that the msSinceEpoch is a bigint and prior to this both msStart and msEnd were Integer. – Julian Moore – 2018-09-02T19:12:52.710

What is the data type msSinceEpoch in the native database? – Hans – 2018-09-02T19:27:11.927

As to code highlight in comments; code in between an opening  ( tickmarks) and closing  should be highlighted. Escape tickmarks using a backslash. – Hans – 2018-09-02T19:30:56.317

@Hans MS SQL bigint - definitely; just checked in SSMS. – Julian Moore – 2018-09-02T19:31:00.087

I figured as the msSinceEpoch value you use is above the SQL INT range. So may just be data type conversion impedance. However, does not explain why it works with msStart. – Hans – 2018-09-02T19:35:02.700

@Hans Indeed/exactly; but SQLDataTypeNames[conn] correctly returns datatypes so MMA should not be ignorant of the dialect - and msStart works :) – Julian Moore – 2018-09-02T19:37:16.740

– Hans – 2018-09-02T19:38:19.543

On test table in MSSQL with milisec field. SQLSelect[conn, "test", {"TableSID", "millisecs"}, (N[3692218891840] <= SQLColumn["millisecs"] && SQLColumn["millisecs"] < N[3692218891860] )] SQLSelect[conn, "test", {"TableSID", "millisecs"}, ("3692218891840" <= SQLColumn["millisecs"] && SQLColumn["millisecs"] < "3692218891860" )] I was able to get results with N[] or wrapping the milliseconds since... in quotes. https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-data-type-conversions?view=sql-server-2017 String to bigint conversion is data dependent

– Hans – 2018-09-03T11:03:37.930

Ok, but I'm not using String so the original issue remains - and why did it work with one mma int but not another.hoping wri will shed light on it! – Julian Moore – 2018-09-03T11:07:29.953