MySQL Syntax Error in ActiveRelationResource (0.10.x)

[karlwilbur]

This issue is a (choose one):

• Problem/bug report.
• Feature request.
• Request for support. Note: Please try to avoid submitting issues for support requests. Use Gitter instead.

Checklist before submitting:

• I've searched for an existing issue.
• I've asked my question on Gitter and have not received a satisfactory answer.
• I've included a complete bug report template. This step helps us and allows us to see the bug without trying to reproduce the problem from your description. It helps you because you will frequently detect if it's a problem specific to your project.
• The feature I'm asking for is compliant with the JSON:API spec.

Description

Choose one section below and delete the other:

Bug reports:

Version 0.10.5

#<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 '."id" AS "individuals_id", individuals.id FROM `individuals` WHERE `individuals`' at line 1: SELECT  "individuals"."id" AS "individuals_id", individuals.id FROM `individuals` WHERE `individuals`.`deleted_at` IS NULL ORDER BY individuals.id asc LIMIT 10 OFFSET 0>
>> fragments = resource_klass.find_fragments(find_options[:filters], options)

This is related to #1310, but is not a multi-tenancy issue. This a MySQL syntax issue that I believe it created by the ActiveRelationResource#quote method which is uses explicit double quotes for quoting table names in ActiveRelationResource#concat_table_field and ActiveRelationResource#alias_table_field.

By adding MySQL to the CI workflow testing matrix, this issue is easily reproduced in CI testing. I will create a PR for adding MySQL to the testing matrix.

[karlwilbur]

Link to GH Actions for my fork, which include the failing tests and MySQL syntax error message:
https://github.com/K-and-R/jsonapi-resources/runs/2698784105?check_suite_focus=true

[cloke]

(using 0.10.7 release)
This seems to happen in Postgres as well. I've been trying to hack around this for hours, but when I fix one area then something else breaks. All of our tables are in schemas and at different parts of the query builder the third part of the field gets stripped out. So when we filter by something like company our sql generated sql is below which seems to be great.

"SELECT \"my_employee\".\"employee\".* FROM \"tio_employee\".\"employee\" LEFT OUTER JOIN \"my_employee\".\"company\" ON \"my_employee\".\"company\".\"id\" = \"my_employee\".\"employee\".\"company_id\" WHERE \"my_employee\".\"employee\".\"company_id\" = 6698 AND \"my_employee\".\"company\".\"id\" = 6698"

but then in apply_request_settings_to_records calls

84:         unless filters.empty?
85:           records = resource_klass.filter_records(records, filters, options)
86:         end

which results in the new sql

"SELECT \"my_employee\".\"employee\".* FROM \"tio_employee\".\"employee\" LEFT OUTER JOIN \"my_employee\".\"company\" \"company\" ON \"company\".\"id\" = \"my_employee\".\"employee\".\"company_id\" WHERE \"my_employee\".\"employee\".\"company_id\" = 6698 AND \"my_employee\".\"company\".\"id\" = 6698 AND \"employee\".\"is_active\" = TRUE AND \"company\".\"id\" = 6698"

specifically this becomes a problem AND \"company\".\"id\" = 6698"

I don't fully understand what is going on at a low level, but it seems like something extremely simple like highlights that active record is sanitizing the wrong part of the field path.

DigitalAsset.where("my_asset.digital_asset.id" => 1234).to_sql
"SELECT \"my_asset\".\"digital_asset\".* FROM \"my_asset\".\"digital_asset\" WHERE \"my_asset\".\"digital_asset\" = 1234"

Anyway, this might all be gibberish and I am looking at everything wrong but if anyone has some thoughts I would be happy to continue hacking away as we are dealing with a crazy legacy database schema so if there is an edge case, I bet we'll see it.

[daflip]

I too was affected by this issue (i'm using MySQL) and after some digging can confirm it's a regression with version 0.10.7. This release uses a newly added sql_field_with_alias method in lib/jsonapi/active_relation_resource.rb and it defaults to quoting which is a change from previous releases. sql_field_with_alias uses the library defined 'quote' method, which performs hard coded escaping using double quotes:

      def quote(field)
        "\"#{field.to_s}\""
      end

The problem is this quoting is not relying on the database connection adapter. With limited time I couldn't figure out how to obtain the resource class in question so my fix was re-defining the quote method created from this library in my base resource:

 def self.quote(field)                                       
   ActiveRecord::Base.connection.quote_table_name field      
 end

Of course this assumes a single underlying database connection 🤷

[MarcelEeken]

We are also running into this issue, which caused as to be stuck for the moment.

With version 0.10.4 we are running into this bug #1377. But after updating to the latest version we are running in to the bug in this ticket.

I have opened a mr with the proposed fix in this ticket here #1395

[bf4]

ActiveRecord::Base.connection.quote_table_name doesn't seem right to me as the name.. and also Rails actually has a whole bunch of quoting rules.. but in any case, does someone want to right a failing test?

[Kallin]

I've "written" a failing test , linked here: #1395 (comment) ; can we turn on MySQL in the test matrix?

[Kallin]

it looks like mysql being turned on in .11 release: master...v0-11-dev we can close this once that gets merged down I believe