SQL SELECT statements with comments cannot be executed in db runner

[na3shkw]

Overview

Thank you for developing this wonderful tool !

I am currently running a Runbook to execute SQL in db runner. I write my SQL in external files for ease of administration and to benefit from syntax support. When I run the Runbook, I give it as an external variable and run it. However, I noticed that when specifying an SQL file that contains comments and executing it, the SELECT statement is not executed.

How to reproduce

Here is a specific example to reproduce this issue. The version of runn is v0.101.2 and the database that db runner connects to is MySQL Ver8.3.0.

The Runbook sql.yml is as follows:

runners:
  db: 'mysql://developer:password@mysql:3306/test'
vars:
  query: ${QUERY}
steps:
  executeSql:
    db:
      query: '{{ vars.query }}'
  showResult:
    dump: steps.executeSql

The SQL file to be executed, selectUser.sql, is as follows:

-- Get user with first name "John"
SELECT * FROM users
WHERE first_name = 'John';

The Runbook is executed as follows:

QUERY=$(cat selectUser.sql) runn run sql.yml

Upon execution, it is observed that the data is not retrieved, resulting in the following output:

{
  "last_insert_id": 0,
  "outcome": "success",
  "rows_affected": 0,
  "run": true
}
.

1 scenario, 0 skipped, 0 failures

However, when the comments are removed from the same SQL file, the results are retrieved as follows:

{
  "outcome": "success",
  "rows": [
    {
      "created_at": "2024-03-30T08:20:08Z",
      "email": "john.doe@example.com",
      "first_name": "John",
      "last_name": "Doe",
      "password": "letmein789",
      "updated_at": "2024-03-30T08:20:08Z",
      "user_id": 3
    }
  ],
  "run": true
}
.

1 scenario, 0 skipped, 0 failures

What I tried

Here is a summary of what I have tried to find out about the conditions under which this issue occurs.
Upon investigation, I found that this issue occurs when there are comments before or after the SQL statement, but not when there are comments within the SQL statement.

1. Writing comments after the SELECT statement

SELECT * FROM users
WHERE first_name = 'John';
-- Get user with first name "John"

Results were not retrieved in this case as well. Adding empty lines before or after the comments did not change the outcome.

2. Writing SQL directly in the Runbook

I modified the vars section in the sql.yaml as follows and executed it:

vars:
  query: |
    -- Get user with first name John
    SELECT * FROM users
    WHERE first_name = 'John';

However, the records were still not retrieved.

3. Writing comments within the SELECT statement

SELECT * FROM users
-- Get user with first name "John"
WHERE first_name = 'John';

In this case, the records were successfully retrieved.

4. Executing statements other than SELECT in SQL files containing comments

I tried INSERT and UPDATE statements:

-- Create user
INSERT INTO users (first_name, last_name, email, password)
VALUES (
  'Zachary', 'Henderson',
  CONCAT('zachary.henderson.', ROUND(RAND() * 100000), '@example.com'),
  'password'
);

-- Update user password
UPDATE users
SET password = CONCAT('secret', ROUND(RAND() * 100000))
WHERE user_id = 4;

Both cases executed successfully.

[k1LoW]

@na3shkw Thank you for your report!