/*
* Copyright 1999-2017 Alibaba Group Holding Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.druid.bvt.sql.mysql;
import java.util.List;
import org.junit.Assert;
import junit.framework.TestCase;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor;
import com.alibaba.druid.sql.parser.SQLStatementParser;
public class StringFunctionsTest extends TestCase {
public void test_0() throws Exception {
String sql = "SELECT ASCII('2');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT ASCII('2');", text);
}
public void test_1() throws Exception {
String sql = "SELECT ASCII(2);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT ASCII(2);", text);
}
public void test_2() throws Exception {
String sql = "SELECT BIN(12);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT BIN(12);", text);
}
public void test_3() throws Exception {
String sql = "SELECT BIT_LENGTH('text');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT BIT_LENGTH('text');", text);
}
public void test_4() throws Exception {
String sql = "SELECT CHAR(77,121,83,81,'76');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CHAR(77, 121, 83, 81, '76');", text);
}
public void test_5() throws Exception {
String sql = "SELECT HEX(CHAR(1,0)), HEX(CHAR(256));";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT HEX(CHAR(1, 0)), HEX(CHAR(256));", text);
}
public void test_6() throws Exception {
String sql = "SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT HEX(CHAR(1, 0, 0)), HEX(CHAR(256 * 256));", text);
}
public void test_7() throws Exception {
String sql = "SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8))";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));", text);
}
public void test_8() throws Exception {
String sql = "SELECT CONCAT(CAST(int_col AS CHAR), char_col)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CONCAT(CAST(int_col AS CHAR), char_col);", text);
}
public void test_9() throws Exception {
String sql = "SELECT 'My' 'S' 'QL'";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CONCAT('My', 'S', 'QL');", text);
}
public void test_10() throws Exception {
String sql = "SELECT CONCAT_WS(',','First name','Second name','Last Name');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CONCAT_WS(',', 'First name', 'Second name', 'Last Name');", text);
}
public void test_11() throws Exception {
String sql = "SELECT CONCAT_WS(',','First name',NULL,'Last Name');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');", text);
}
public void test_12() throws Exception {
String sql = "SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');", text);
}
public void test_13() throws Exception {
String sql = "SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');", text);
}
public void test_14() throws Exception {
String sql = "SELECT EXPORT_SET(5,'Y','N',',',4);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT EXPORT_SET(5, 'Y', 'N', ',', 4);", text);
}
public void test_15() throws Exception {
String sql = "SELECT EXPORT_SET(6,'1','0',',',10);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT EXPORT_SET(6, '1', '0', ',', 10);", text);
}
public void test_16() throws Exception {
String sql = "SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');", text);
}
public void test_17() throws Exception {
String sql = "SELECT FIND_IN_SET('b','a,b,c,d');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT FIND_IN_SET('b', 'a,b,c,d');", text);
}
public void test_18() throws Exception {
String sql = "SELECT FORMAT(12332.123456, 4);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT FORMAT(12332.123456, 4);", text);
}
public void test_19() throws Exception {
String sql = "SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));", text);
}
public void test_20() throws Exception {
String sql = "SELECT HEX(255), CONV(HEX(255),16,10);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT HEX(255), CONV(HEX(255), 16, 10);", text);
}
public void test_21() throws Exception {
String sql = "SELECT INSERT('Quadratic', 3, 4, 'What');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT INSERT('Quadratic', 3, 4, 'What');", text);
}
public void test_22() throws Exception {
String sql = "SELECT INSERT('Quadratic', -1, 4, 'What');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT INSERT('Quadratic', -1, 4, 'What');", text);
}
public void test_23() throws Exception {
String sql = "SELECT INSTR('foobarbar', 'bar');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT INSTR('foobarbar', 'bar');", text);
}
public void test_24() throws Exception {
String sql = "SELECT LEFT('foobarbar', 5);";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LEFT('foobarbar', 5);", text);
}
public void test_25() throws Exception {
String sql = "SELECT LENGTH('text');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LENGTH('text');", text);
}
public void test_26() throws Exception {
String sql = "UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("UPDATE t\nSET blob_col = LOAD_FILE('/tmp/picture')\nWHERE id = 1;", text);
}
public void test_27() throws Exception {
String sql = "SELECT LOCATE('bar', 'foobarbar');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LOCATE('bar', 'foobarbar');", text);
}
public void test_28() throws Exception {
String sql = "SELECT LOWER('QUADRATICALLY');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LOWER('QUADRATICALLY');", text);
}
public void test_29() throws Exception {
String sql = "SELECT LPAD('hi',4,'??');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LPAD('hi', 4, '??');", text);
}
public void test_30() throws Exception {
String sql = "SELECT LTRIM(' barbar')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT LTRIM(' barbar');", text);
}
public void test_31() throws Exception {
String sql = "SELECT MAKE_SET(1,'a','b','c')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT MAKE_SET(1, 'a', 'b', 'c');", text);
}
public void test_32() throws Exception {
String sql = "SELECT MAKE_SET(1 | 4,'hello','nice','world')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT MAKE_SET(1 | 4, 'hello', 'nice', 'world');", text);
}
public void test_33() throws Exception {
String sql = "SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT MAKE_SET(1 | 4, 'hello', 'nice', NULL, 'world');", text);
}
public void test_34() throws Exception {
String sql = "SELECT ORD('2')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT ORD('2');", text);
}
public void test_35() throws Exception {
String sql = "SELECT QUOTE('Don\\'t!')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT QUOTE('Don''t!');", text);
}
public void test_36() throws Exception {
String sql = "SELECT REPEAT('MySQL', 3)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT REPEAT('MySQL', 3);", text);
}
public void test_37() throws Exception {
String sql = "SELECT REVERSE('abc')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT REVERSE('abc');", text);
}
public void test_38() throws Exception {
String sql = "SELECT RIGHT('foobarbar', 4)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT RIGHT('foobarbar', 4);", text);
}
public void test_39() throws Exception {
String sql = "SELECT RPAD('hi',5,'?')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT RPAD('hi', 5, '?');", text);
}
public void test_40() throws Exception {
String sql = "SELECT RTRIM('barbar ')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT RTRIM('barbar ');", text);
}
public void test_41() throws Exception {
String sql = "SELECT SOUNDEX('Hello')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SOUNDEX('Hello');", text);
}
public void test_42() throws Exception {
String sql = "SELECT SPACE(6)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SPACE(6);", text);
}
public void test_43() throws Exception {
String sql = "SELECT SUBSTRING('Quadratically',5)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SUBSTRING('Quadratically', 5);", text);
}
public void test_44() throws Exception {
String sql = "SELECT SUBSTRING('Sakila' FROM -4 FOR 2)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SUBSTRING('Sakila', -4, 2);", text);
}
public void test_45() throws Exception {
String sql = "SELECT SUBSTRING('foobarbar' FROM 4)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SUBSTRING('foobarbar', 4);", text);
}
public void test_46() throws Exception {
String sql = "SELECT SUBSTRING('Quadratically',5,6)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SUBSTRING('Quadratically', 5, 6);", text);
}
public void test_47() throws Exception {
String sql = "SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2)";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);", text);
}
public void test_48() throws Exception {
String sql = "SELECT TRIM(' bar ')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT TRIM(' bar ');", text);
}
public void test_49() throws Exception {
String sql = "SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx')";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');", text);
}
public void test_50() throws Exception {
String sql = "SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');", text);
}
public void test_51() throws Exception {
String sql = "SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');", text);
}
public void test_52() throws Exception {
String sql = "SELECT UNHEX('4D7953514C');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT UNHEX('4D7953514C');", text);
}
public void test_53() throws Exception {
String sql = "SELECT 0x4D7953514C;";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT 0x4D7953514C;", text);
}
public void test_54() throws Exception {
String sql = "SELECT UPPER('Hej');";
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
String text = output(stmtList);
Assert.assertEquals("SELECT UPPER('Hej');", text);
}
private String output(List<SQLStatement> stmtList) {
StringBuilder out = new StringBuilder();
for (SQLStatement stmt : stmtList) {
stmt.accept(new MySqlOutputVisitor(out));
out.append(";");
}
return out.toString();
}
}