Query Rows does not work with bindings when using a MySQL datasource

[FlaminWrap]

Hosting

• Self
  • Method: BudiCLI
  • Budibase Version: 1.0.178
  • App Version: 1.0.178

Describe the bug Query Rows does not work with bindings when using a MySQL datasource. Instead it returns all rows until the row limit is hit.

To Reproduce Steps to reproduce the behavior:

1. Create a new app with a MySQL data source
2. Create two tables Record and RecordType
3. Create two columns in RecordType: Type/Text , Value/Number
4. Create at least two rows in the with both columns filled with different values in the RecordType table
5. Create the following relationsip between the tables: One RecordType row > many Record rows
6. Create a column in the Record table called Value and make it a number type
7. Create an automation to launch when creating a row
8. Create a Query Rows step in the automation to query the RecordType table and filter it by the foreign key from the trigger row
9. Create an Update Rows step in the automation to update the Record Table
10. Set the RecordType field to return $("trigger.row.RecordType");
11. Set the Fk_RecordType_Record field to return $("trigger.row.fk_RecordType_Record");
12. Set the Value field to return $("steps.1.rows.0.Value");
13. Set the RowID field to return $("trigger.id");
14. Press Run test at the top of the automation page and select anything but the first record in the RecordType table created earlier
15. The first value will have been pulled through instead of the correct value.

Query Row Input:

{ "tableId": "datasource_plus_fc2c66277ba54f2caac0dc36d5efc531__RecordType", "filters": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "id": null }, "notEqual": {}, "empty": {}, "notEmpty": {}, "contains": {}, "notContains": {} }, "filters-def": [ { "id": "Aa5fR_PiF", "field": "id", "operator": "equal", "value": "{{ js \"cmV0dXJuICQoInRyaWdnZXIucm93LmZrX1JlY29yZFR5cGVfUmVjb3JkIik7\" }}", "valueType": "Binding", "type": "number" } ], "sortColumn": "id", "sortOrder": "ascending", "limit": "50" }

Query Row Output:

{ "rows": [ { "id": 1, "Type": "Type1", "Value": 1, "_id": "%5B1%5D", "tableId": "datasource_plus_fc2c66277ba54f2caac0dc36d5efc531__RecordType", "_rev": "rev" }, { "id": 2, "Type": "Type2", "Value": 2, "_id": "%5B2%5D", "tableId": "datasource_plus_fc2c66277ba54f2caac0dc36d5efc531__RecordType", "_rev": "rev" }, { "id": 3, "Type": "Type3", "Value": 3, "_id": "%5B3%5D", "tableId": "datasource_plus_fc2c66277ba54f2caac0dc36d5efc531__RecordType", "_rev": "rev", "Record": [ { "_id": "%5B18%5D" }, { "_id": "%5B19%5D" }, { "_id": "%5B20%5D" }, { "_id": "%5B21%5D" } ] } ], "success": true }

Update Row Output:

{ "row": { "id": 22, "fk_RecordType_Record": 3, "Value": 1, "_id": "%5B22%5D", "tableId": "datasource_plus_fc2c66277ba54f2caac0dc36d5efc531__Record", "_rev": "rev", "RecordType": [ { "_id": "%5B22%5D" } ] }, "response": "Record saved successfully", "id": "%5B22%5D", "revision": "rev", "success": true } Expected behavior The correct values have been filtered to the expected position. e.g. in the outputs above the expected value is 3 instead of 1

Desktop (please complete the following information):

• OS: Windows 10
• Browser: Chrome
• Version: 101.0.4951.67

Additional context Both handlebars and javascript bindings have this issue

[melohagan]

The binding within the filter appears not to be working:

Does not work

Works

[xnohat]

I confirm this bug still appear on 1.0.206 and also affect to Internal Database too #6329

[melohagan]

This appears to be fixed in the latest version: v2.1.31

[louis-relandeau]

I'm using the online app builder with version 2.6.11. I'm using a Budibase DB and have the same issue as this initial post. Following is the procedure I followed:

1. Create a new app
2. Add an internal Budibase DB with a single required column of numbers and some sample rows
3. Create a new automation with trigger App Action
4. Add a single field
5. Add query rows as a step 1 of the automation
6. Fill in appropriately the table reference and define the filter to have the column equal to a binding and the binding is to the field created in point 4
7. Finish and test automation inputing a value that exists in the table

There is no output rows with the bindings, same behaviour as described by @melohagan (comment), if the binding in the filter is replaced by a number it works fine. Can anyone verify that it has been fixed?

[melohagan]

Re-introduced in v2.2.1

Think it was in this commit: eca7af83f4330963a66a53e488781a09aef0d125