ESCAPE character issue

[Damien9222]

Hi,

Using the ESCAPE character in LIKE clause for 2 columns do not filter anything. To have the LIKE clause correctly applied, we must change the ESCAPE character (for example using ^).

• Replicate the problem in jsfiddle using:

alasql("CREATE TABLE cities (city string, population string)");
alasql("INSERT INTO cities VALUES ('%','1'), ('1%','2'), ('%1','3'), ('1%1','4'), ('_1','5'), ('1^1','6'), ('1!1','7')");

// Case 1 : backslash ESCAPE char:
/* OK: var res = alasql("SELECT * FROM cities WHERE (city LIKE '%^%%' ESCAPE '^') OR (population LIKE '2^%'  ESCAPE '^') ORDER BY population DESC");  // =>returns 5 members */
/* KO: var res = alasql("SELECT * FROM cities WHERE (city LIKE '%\\%%' ESCAPE '\\') OR (population LIKE '2\\%'  ESCAPE '\\') ORDER BY population DESC");  // => no filter */


showResult(res)_

function showResult(x){
    document.getElementById('result').textContent = JSON.stringify(x,  null, '\t');
}

A second point about escaping (no jsfiddle because not a possible to use double quote without escaping it with a backslash!): Adding in the table a member with double quote char (for example: "1 in cities), and searching the double quote char, using the ^ for ESCAPE, executing the following SQL :

'SELECT * FROM cities  WHERE (city LIKE "%^"%" ESCAPE "^" OR population LIKE "%^"%" ESCAPE "^") ORDER BY population DESC"'

return a parsing error: "SyntaxError: Parse error on line 1:..."

Modifying the ESCAPE char to use :

'SELECT * FROM ms_inmemory_member_provider_database_id_0.defaultTable WHERE (name LIKE "%\\"%" OR description LIKE "%\\"%") ORDER BY sortingKey ASC LIMIT 150 OFFSET 0'

returns correct result.
Thanks a lot!
Regards.

[Damien9222]

https://jsfiddle.net/f1c5nsvr/2/

[Charlesnorris509]

Hey @mathiasrw I'm new to this project based and a newbie when it comes to open source but My background as a Data Engineering Senior College Undergrad, and being an SWE Intern with Amazon makes me a great potential contributor over the long run

[Adarsha186]

Hi @mathiasrw, I am looking for some open source projects to work on. If this is issue is still open and hasn't been resolved, I'm interested in contributing to this issue.

[mathiasrw]

@Adarsha186 This is still open. Let me know if you need guidance...