[Bug]: mo-tester Failure to Parse `enclosed by '\''` - Reproduction and Root Cause Analysis

[flypiggyyoyoyo]

Issue Description

The SQL parser (ScriptParser.java) of mo-tester fails to properly handle the valid SQL syntax of '\'' (escaping single quotes with a backslash within a single-quoted string). When an SQL file contains this syntax, the parser halts execution at the corresponding line, preventing subsequent SQL statements from running.

Prerequisites

1. MO database is running and listening on 127.0.0.1:6001
2. The mo-tester repository has been cloned locally

I. Issue Reproduction

Step 1: Create Test SQL File

Execute the following command to create a test SQL file containing the enclosed by '\'' syntax:

cat > /tmp/escape_test.sql << 'EOF'
drop database if exists escape_test_db;
create database escape_test_db;
use escape_test_db;
create table t1 (id int, name varchar(50));
insert into t1 values (1, 'Alice');
drop stage if exists escape_stage;
create stage escape_stage URL='file:///tmp/escape_output';
select * from t1 into outfile 'stage://escape_stage/output.csv' format'csv' fields enclosed by '\'' header'true';
select'SUCCESS: mo-tester continued' as status;
drop table t1;
drop stage escape_stage;
drop database escape_test_db;
EOF

Step 2: Create Output Directory

Create the target directory for CSV export to avoid file write failures:

mkdir -p /tmp/escape_output

Step 3: Verify MO Database Can Execute the SQL Normally

Validate the validity of the SQL syntax by directly connecting to the MO database via the mysql client:

mysql -h 127.0.0.1 -P 6001 -u dump -p111 < /tmp/escape_test.sql

Expected Result: Execution succeeds, and the terminal outputs the following content:

status
SUCCESS: mo-tester continued

Step 4: Execute the Same SQL File with mo-tester

Navigate to the mo-tester repository directory and run the test SQL via the script:

cd ~/Documents/GitHub/mo-tester/mo-tester
./run.sh -p /tmp/escape_test.sql -m genrs

Step 5: Check the Generated Result File

View the result file generated after mo-tester execution:

cat /tmp/escape_test.result

Actual Result: The file only contains the first 7 SQL statements, missing subsequent lines:

drop database if exists escape_test_db;
create database escape_test_db;
use escape_test_db;
create table t1 (id int, name varchar(50));
insert into t1 values (1, 'Alice');
drop stage if exists escape_stage;
create stage escape_stage URL='file:///tmp/escape_output';

Expected Result: The file should contain all 12 statements and the query result of select 'SUCCESS: mo-tester continued'.

II. Root Cause Analysis

Three controlled experiments were conducted to pinpoint the root cause as the parsing exception of escaped single quotes in enclosed by '\''.

Controlled Experiment 1: Remove the enclosed by Option

Create a test file without the enclosed by syntax:

cat > /tmp/test_without_enclosed.sql << 'EOF'
drop database if exists escape_test_db;
create database escape_test_db;
use escape_test_db;
create table t1 (id int, name varchar(50));
insert into t1 values (1, 'Alice');
drop stage if exists escape_stage;
create stage escape_stage URL='file:///tmp/escape_output';
select * from t1 into outfile 'stage://escape_stage/output1.csv' format 'csv' header'true';
select 'SUCCESS: mo-tester continued' as status;
drop table t1;
drop stage escape_stage;
drop database escape_test_db;
EOF

Execute and check the result:

cd ~/Documents/GitHub/mo-tester/mo-tester
./run.sh -p /tmp/test_without_enclosed.sql -m genrs
cat /tmp/test_without_enclosed.result

Result: The generated result file contains 14 lines, ✅ all statements are executed completely.

Controlled Experiment 2: Use enclosed by '"' (Double Quotes)

Create a test file using double quotes as the enclosure character:

cat > /tmp/test_enclosed_double.sql << 'EOF'
drop database if exists escape_test_db;
create database escape_test_db;
use escape_test_db;
create table t1 (id int, name varchar(50));
insert into t1 values (1, 'Alice');
drop stage if exists escape_stage;
create stage escape_stage URL='file:///tmp/escape_output';
select * from t1 into outfile 'stage://escape_stage/output2.csv' format 'csv' fields enclosed by '"' header 'true';
select 'SUCCESS: mo-tester continued' as status;
drop table t1;
drop stage escape_stage;
drop database escape_test_db;
EOF

Execute and check the result:

cd ~/Documents/GitHub/mo-tester/mo-tester
./run.sh -p /tmp/test_enclosed_double.sql -m genrs
cat /tmp/test_enclosed_double.result

Result: The generated result file contains 14 lines, ✅ all statements are executed completely.

Controlled Experiment 3: Use enclosed by '\'' (Escaped Single Quotes)

Create a test file containing escaped single quotes (consistent with the reproduction steps):

cat > /tmp/test_enclosed_single.sql << 'EOF'
drop database if exists escape_test_db;
create database escape_test_db;
use escape_test_db;
create table t1 (id int, name varchar(50));
insert into t1 values (1, 'Alice');
drop stage if exists escape_stage;
create stage escape_stage URL='file:///tmp/escape_output';
select * from t1 into outfile 'stage://escape_stage/output3.csv' format 'csv' fields enclosed by '\'' header 'true';
select 'SUCCESS: mo-tester continued' as status;
drop table t1;
drop stage escape_stage;
drop database escape_test_db;
EOF

Execute and check the result:

cd ~/Documents/GitHub/mo-tester/mo-tester
./run.sh -p /tmp/test_enclosed_single.sql -m genrs
cat /tmp/test_enclosed_single.result

Result: The generated result file contains only 7 lines, ❌ execution halts at the line with enclosed by '\''.

III. Conclusion

Experiment	Value of enclosed by	Number of Lines in Result File	Outcome
Experiment 1	None	14	✅ Full Execution
Experiment 2	"	14	✅ Full Execution
Experiment 3	\'	7	❌ Execution Halted

Issue Attribution

The SQL parser of mo-tester cannot properly handle the '\'' (escaped single quote within a single-quoted string) syntax, leading to parsing interruption and failure to execute subsequent statements.

Supplementary Note

The MO database itself fully supports the enclosed by '\'' syntax. When executing the SQL via direct connection with the mysql client, all statements run completely and output the expected results. The issue is isolated to the parsing logic of the mo-tester tool.