3
$\begingroup$

I've noticed a rather inconvenient (assumed) bug when selecting data joined across multiple SQL tables.

Example:

Needs["DatabaseLink`"]
conn = OpenSQLConnection["publisher"]

Selecting from a single table works fine:

SQLSelect[conn, "ROYSCHED"] // Short

{{BS1011,0,5000,0.1},{BS1011,5001,50000,0.12},<<48>>,{PY2008,0,50000,0.1}}

However, when joining across multiple tables, I run into issues:

SQLSelect[conn, {"TITLES","ROYSCHED"}, {{"TITLES","TITLE"},{"TITLES","TITLE_ID"},{"ROYSCHED","ROYALTY"}},
 SQLColumn[{"TITLES","TITLE_ID"}]==SQLColumn[{"ROYSCHED","TITLE_ID"}]]//Short[#, 15]&

>>If[DatabaseLink`SQL`Private`invalidTableNameQ[{TITLES,ROYSCHED}],Message[SQLTable::tablename,If[Head[{TITLES,ROYSCHED}]===SQLTable,First[{TITLES,ROYSCHED}],{TITLES,ROYSCHED}]],<<1>>]

It appears the private function invalidTableNameQ cannot handle lists. Also verified when you surround a single table name with brackets (ie {"ROYSCHED"}).

This seems like an obvious bug to me. I can fix it rather easy by navigating to $DatabaseLinkDirectory/Kernel/SCUD.m and manually add a DownValue for this function.

invalidTableNameQ[name_List]:=Or@@(invalidTableNameQ/@name)

Any ideas or other verifications before I slap on the bugs tag?

$\endgroup$
  • $\begingroup$ Does this happen if you write the join as plain SQL? $\endgroup$ – rm -rf Oct 21 '16 at 15:55
  • $\begingroup$ @R.M. Nope. If I write out the whole string and execute using SQLExecute it works fine. $\endgroup$ – kale Oct 21 '16 at 15:56
  • 2
    $\begingroup$ @kale This issue is known and a fix is on the cards(either through paclet update or in next incremental version of Mathematica). $\endgroup$ – dsingh Oct 21 '16 at 18:50

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.