[GROOVY-6983] SQL firstRow behavior with some nested user-defined functions broken in 2.3.1 +

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.3.1, 2.3.2, 2.3.3, 2.3.4, 2.3.5, 2.3.6
• Component/s: SQL processing
• Environment: 64-bit Windows 8.1 ; 64-bit JDK 1.8.0_11; Microsoft SQL Server 2012 - 11.0.3000.0; net.sourceforge.jtds:jtds:1.3.1
• Assignee: Unassigned
• Reporter: Martin Ahrens
• Created: 29/Jul/14 16:19
• Updated: 20/Jan/16 18:46

Description

I've been using Groovy professionally for some time now and really enjoy it. While testing out a dependencies upgrade I came across a regression in SQL behavior. The problem occurs with Groovy 2.3.1 and builds thereafter. The issue occurs when calling a couple of user-defined SQL functions on the database. Groovy was working as expected from our previous version 2.2.2 up through 2.3.0. Also, the problem only occurs when calling sql.firstRow instead of sql.eachRow.

Below I include a boiled-down example of SQL user-defined functions which lead to the problem with Groovy. I tried to reduce the complexity as much as possible while still producing the problem. The SQL code below (re-)creates two functions: testFunc and testFunc2. testFunc2 returns a table with three rows with values updated to 7. testFunc selects a row field from testFunc2() with a where clause to pick which row.

The expected result is 7 but since Groovy 2.3.1 the firstRow function returns 1. However iterating with eachRow produces the correct result!
I was trying to look over the diff of Groovy's Sql.java between 2.3.0 and 2.3.1 but I haven't yet been able to grok it.

SQL generation code for 2 functions

IF OBJECT_ID('dbo.testFunc2', 'TF') IS NOT NULL DROP FUNCTION dbo.testFunc2
GO

CREATE FUNCTION dbo.testFunc2()
RETURNS @table TABLE ( name VARCHAR(50), num INT)
AS BEGIN
  INSERT INTO @table VALUES ('a', 2), ('b', 2), ('c', 2)
  UPDATE @table SET num = 7
RETURN END
GO


IF OBJECT_ID('dbo.testFunc') IS NOT NULL DROP FUNCTION dbo.testFunc
GO

CREATE FUNCTION dbo.testFunc()
RETURNS int
AS BEGIN
  DECLARE @result int = 1
  SELECT TOP 1 @result = num FROM dbo.testFunc2() WHERE name = 'c'
RETURN @result END
GO


USE livendb GRANT EXEC ON dbo.testFunc TO PUBLIC
GO

and here is some groovy code to produce the conflicting output

problem example

    def sql = Sql.newInstance([
      driver: /*TODO*/,
      url: /*TODO*/,
      user: /*TODO*/,
      password: /*TODO*/
    ])
    println 'should be: 7'
    String q = 'SELECT dbo.testFunc() as answer'
    println "firstrow: ${sql.firstRow(q).answer}" // prints 1 for Groovy 2.3.1 +
    sql.eachRow(q) { println "eachRow: $it.answer" } // prints 7 correctly !