ruby find_by_sql string parameters converting int parameter into string throws error in limit mysql-CodePudding

I have a ruby on rails Rails 7.0.2.2 webapp (ruby version 3.0.3) with a query to mysql 8.0.18 for macos10.14 on x86_64 database like:

pagIndex = params[:pagIndex].to_i
limitBoats = 20
offset = pagIndex * 20  
values = Array.new  
indexValues = 0

sQuery = "SELECT DISTINCT b.id, ... FROM ... WHERE ... LIMIT ? OFFSET ?"

values.insert(indexValues   1, limitBoats)
values.insert(indexValues   2, offset)    
values.shift    
values.insert(0, sQuery)
@result = Entity.find_by_sql(values)

This code is working perfectly and it´s translating to a query like:

SELECT ... LIMIT '20' OFFSET '0';

where limit and offset are strings.

This has been working perfectly for years in my previous ruby 2.4, rails 4 and mysql 5.5. However, after migrating to ruby 3, Rails 7 and mysql 8, I get error in the limit and offset parameters, because mysql is expecting an integer.

If I just remove the '', the query works. How can I get this working with find_by_sql?

I guess I´m missing something as I don´t even find any other issues like this one.

Note: limit and offset are part of a pagination, so they are coming from variables and I cannot just use these values as constant integers.

UPDATE According to @muistooshort comment, just to debug it easier, if I just create a method with:

@result = Entity.find_by_sql(['select * from entity limit ?', 11])
    logger.debug "Entities="   @result.inspect

I´m getting this output:

DEBUG -- :   Entity Load (0.5ms)  select * from boats limit '11'
Completed 500 Internal Server Error in 1ms (ActiveRecord: 0.5ms | Allocations: 324)


  
ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''11'' at line 1):

As you can see it´s changing the int to string and mysql 8 complains.

CodePudding user response：

Totally strange to me, but I created next bug in Rails: https://github.com/rails/rails/issues/44853

and they answered they needed to do this on purpose, so now the only way to send a query using find_by_sql with limit is with a workaround defined here: https://github.com/rails/rails/issues/44312