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

felix-appsmith commented 1 year ago

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

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

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);

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;

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

Another user has reported this issue.

appsmithorg / appsmith