[Bug]: Error in the execution of SQL queries that use variables and subqueries in the FROM clause

[felix-appsmith]

Is there an existing issue for this?

• [X] I have searched the existing issues

Description

A user tries to execute a query that uses variables and subqueries, this to make a mathematical calculation, the problem is that in Appsmith this query fails showing this error:Parameter at position 0 is not set and grew it in a database manager like dbeaver, it runs successfully.

This query fails when using a MariaDB database.

Steps To Reproduce

1. Create a MariaDB database can use this docker-compose.yml

version: '3'
services:
mariadb:
  image: mariadb:latest
  environment:
    MYSQL_ROOT_PASSWORD: mysecretpassword
    MYSQL_DATABASE: mydb
    MYSQL_USER: myuser
    MYSQL_PASSWORD: mypassword
  ports:
    - "3307:3306"
  volumes:
    - ./data:/var/lib/mysql  

2. Create the table and insert the data for the query to work use this query

CREATE TABLE mytable (
  id INT,
  amt DECIMAL(10,2),
  qty INT
);

INSERT INTO mytable (id, amt, qty)
VALUES (1, 100, 2);

3. Now run this query in Appsmith and see the error:Parameter at position 0

SELECT id, @amount := amt as amount, @quantity := qty as quantity, @amount * @quantity as charge FROM (
   SELECT id, amt, qty FROM mytable
) tmp;

4. Run the same query in a database manager like DBeaver.

Public Sample App

No response

Issue video log

No response

Version

Self-hosted Appsmith Community v1.9.4

[sumitsum]

Adding the critical label since user has mentioned that this used to work before but has stopped working now. https://app.frontapp.com/inboxes/teammates/12984076/inbox/open/0/search/local/sql%20variable/69375622604?around=250654512908

[sumitsum]

It seems that the issue is with the r2dbc MariaDB driver that we use. The driver is not able to work when the assignment operator := is present. For example, the following simple sql statement also fails: SELECT @amount := 10; . I have raised a bug on the driver's repository: https://github.com/mariadb-corporation/mariadb-connector-r2dbc/issues/58

From above, it seems like the only option apart from waiting for the maintainers of the driver to fix and release a new driver version would be to replace the underlying driver. Hence, removing the critical tag and replacing with High since the fix would involve larger planning and timeline i.e. a quick fix does not seem possible here.

[ame-appsmith]

Another user has reported this issue.