bug in Where Queries?

[José Vilmar Estácio de Souza]

Hi all,

According to the documentation i can code the following:

final query = Person.where {
  age > avg(age)
}

Based in the documentation I tried the following:

		final c = Venda.where {
			total < avg(total)
respond c.list()
		

}
I am receiving the following error when I execute this code:

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Syntax error in SQL statement "SELECT THIS_.ID AS ID1_3_0_, THIS_.VERSION AS VERSION2_3_0_, THIS_.BOOK_NAME AS BOOK_NAM3_3_0_, THIS_.TOTAL AS TOTAL4_3_0_, THIS_.X AS X5_3_0_ FROM VENDA THIS_ WHERE THIS_.TOTAL < (SELECT  FROM VENDA THIS_)[*] "; expected "RIGHT, LEFT, FULL, INNER, JOIN, CROSS, NATURAL, ,, SELECT"; SQL statement:
select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.book_name as book_nam3_3_0_, this_.total as total4_3_0_, this_.x as x5_3_0_ from venda this_ where this_.total < (select  from venda this_) [42001-191]
ERROR org.grails.web.errors.GrailsExceptionResolver - JdbcSQLException occurred when processing request: [GET] /venda/t1
Syntax error in SQL statement "SELECT THIS_.ID AS ID1_3_0_, THIS_.VERSION AS VERSION2_3_0_, THIS_.BOOK_NAME AS BOOK_NAM3_3_0_, THIS_.TOTAL AS TOTAL4_3_0_, THIS_.X AS X5_3_0_ FROM VENDA THIS_ WHERE THIS_.TOTAL < (SELECT  FROM VENDA THIS_)[*] "; expected "RIGHT, LEFT, FULL, INNER, JOIN, CROSS, NATURAL, ,, SELECT"; SQL statement:
select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.book_name as book_nam3_3_0_, this_.total as total4_3_0_, this_.x as x5_3_0_ from venda this_ where this_.total < (select  from venda this_) [42001-191]. Stacktrace follows:

Am I doing something wrong or this is a bug?

Thanks.

--

[José Vilmar Estácio de Souza]

I forgot to mention that I am running grails 3.1.7.

--
You received this message because you are subscribed to the Google Groups "Grails Dev Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to grails-dev-disc...@googlegroups.com.
To post to this group, send email to grails-de...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/grails-dev-discuss/6019fbd1-6d06-e771-011f-6af1b33065b7%40informal.com.br.
For more options, visit https://groups.google.com/d/optout.

[Jeff Scott Brown]

> On May 23, 2016, at 5:06 AM, José Vilmar Estácio de Souza <vil...@informal.com.br> wrote:
>
> I forgot to mention that I am running grails 3.1.7.
>
> On 05/23/2016 06:56 AM, José Vilmar Estácio de Souza wrote:
>> Hi all,
>>
>>
>> According to the documentation i can code the following:
>>
>> final
>> query = Person.where {
>> age > avg(age)
>> }
>>
>> Based in the documentation I tried the following:
>>
>> final c = Venda.where {
>> total < avg(total)
>> respond c.list()
>>
>>
>> }
>> I am receiving the following error when I execute this code:
>>
>> ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Syntax error in SQL statement "SELECT THIS_.ID AS ID1_3_0_, THIS_.VERSION AS VERSION2_3_0_, THIS_.BOOK_NAME AS BOOK_NAM3_3_0_, THIS_.TOTAL AS TOTAL4_3_0_, THIS_.X AS X5_3_0_ FROM VENDA THIS_ WHERE THIS_.TOTAL < (SELECT FROM VENDA THIS_)[*] "; expected "RIGHT, LEFT, FULL, INNER, JOIN, CROSS, NATURAL, ,, SELECT"; SQL statement:
>> select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.book_name as book_nam3_3_0_, this_.total as total4_3_0_, this_.x as x5_3_0_ from venda this_ where this_.total < (select from venda this_) [42001-191]
>> ERROR org.grails.web.errors.GrailsExceptionResolver - JdbcSQLException occurred when processing request: [GET] /venda/t1
>> Syntax error in SQL statement "SELECT THIS_.ID AS ID1_3_0_, THIS_.VERSION AS VERSION2_3_0_, THIS_.BOOK_NAME AS BOOK_NAM3_3_0_, THIS_.TOTAL AS TOTAL4_3_0_, THIS_.X AS X5_3_0_ FROM VENDA THIS_ WHERE THIS_.TOTAL < (SELECT FROM VENDA THIS_)[*] "; expected "RIGHT, LEFT, FULL, INNER, JOIN, CROSS, NATURAL, ,, SELECT"; SQL statement:
>> select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.book_name as book_nam3_3_0_, this_.total as total4_3_0_, this_.x as x5_3_0_ from venda this_ where this_.total < (select from venda this_) [42001-191]. Stacktrace follows:
>>
>> Am I doing something wrong or this is a bug?
>>
>> Thanks.
>>

You should not be calling respond from within the where query closure but I expect that is just a typo in the email.

You may be experiencing the issue reported at https://github.com/grails/grails-core/issues/9931.



JSB

—
Jeff Scott Brown
Principal Software Engineer
Grails Development Team
Object Computing Inc.
http://www.ociweb.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

[José Vilmar Estácio de Souza]

Yes, the respond within the closure is a typo in my e-mail. I was typing from memory.

The problem also happenswhen using postgresql with a differente message but with the same error.

Caused by: org.postgresql.util.PSQLException: ERROR: subquery must return only one column