[MsSQL] Metadata `check` warns about working `TOP` statement

[Quafadas]

I struggled with this fragment, giving me this message; def sqlLimit(limit: Int): Fragment = fr"TOP $limit "

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

Which I think I managed to track back to this stackoverflow; https://stackoverflow.com/questions/7038818/ms-sql-exception-incorrect-syntax-near-p0

So, in SQL server apparently, this fragment should be

def sqlLimit(limit: Int): Fragment = fr"TOP ( $limit )"

Because "limit" is a variable, it must have brackets (apparently) - and then query worked. I then come to trying to unit test it...

Adding the brackets into the "TOP" fragment, appears to generate this error in Test.

  ? SQL Compiles and TypeChecks
    The number of rows provided for a TOP or FETCH clauses row count
    parameter must be an integer.

It's obviously a niche use case!

Based on a discord exchange;

this is probably a bug in the mssql jdbc driver doobie can't do anything about it

Filed here for posterity - if I can figure out a way to show it's the msql driver, then I'll file it upstream, but not quite sure how to write that our properly right now.

[Daenyth]

check analysis works by inspecting the jdbc java.sql.ParameterMetaData given by the jdbc driver - that's what the driver is returning incorrectly

[Quafadas]

Found against ;

ivy"com.microsoft.sqlserver:mssql-jdbc:10.2.0.jre17",

[Quafadas]

This query fails in test, but runs in main;

  test("fails?") {
    val limit = 1
    val queryMeLessWorking = sql" SELECT TOP ( $limit ) val FROM (values (3),(5)) as t(val) ".query[Int]
    check(queryMeLessWorking)
  }

==> X db.AnalysisTestSuite.fails?  0.102s munit.FailException: C:\temp\spot\test\src\DBTest.scala:34
33:    val queryMeLessWorking = sql" SELECT TOP ( $limit ) val FROM (values (3),(5)) as t(val) ".query[Int]
34:    check(queryMeLessWorking)
35:  }
  Query0[scala.Int] defined at DBTest.scala:33
  SELECT TOP ( ? ) val FROM (values (3),(5)) as t(val)
  ? SQL Compiles and TypeChecks
    The number of rows provided for a TOP or FETCH clauses row count
    parameter must be an integer.

In main;

[84/84] scripts.run 
Mar 25, 2022 3:27:11 PM doobie.util.log$LogHandler$ $init$$$anonfun$2
INFO: Successful Statement Execution:

   SELECT TOP ( ? ) val FROM (values (3),(5)) as t(val)

 arguments = [1]
   elapsed = 66 ms exec + 19 ms processing (86 ms total)

List(3)

[Quafadas]

@Daenyth - At length, MS fixed this upstream - thanks for the help 18 months ago :-).