Skip to content

Upgrade SQLite JDBC to 3.51.3 and add new SQL features #1300

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
mrigger wants to merge 1 commit into
base: main
Choose a base branch
from
Open

Upgrade SQLite JDBC to 3.51.3 and add new SQL features #1300

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion pom.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -299,7 +299,7 @@
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.49.1.0</version>
<version>3.51.3.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
Expand Down
2 changes: 0 additions & 2 deletions src/sqlancer/sqlite3/SQLite3Errors.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -57,9 +57,7 @@ public static List<String> getExpectedExpressionErrors() {
errors.add("JSON cannot hold BLOB values");
errors.add("JSON path error");
errors.add("bad JSON path");
errors.add("json_insert() needs an odd number of arguments");
errors.add("json_object() labels must be TEXT");
errors.add("json_object() requires an even number of arguments");
errors.add("argument of ntile must be a positive integer");

// fts5 functions
Expand Down
7 changes: 7 additions & 0 deletions src/sqlancer/sqlite3/ast/SQLite3Aggregate.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -83,6 +83,13 @@ public SQLite3Constant apply(SQLite3Constant exprVal) {
}
}

},
// string_agg(value, separator) - added in 3.44.0, requires 2 args
STRING_AGG() {
@Override
public SQLite3Constant apply(SQLite3Constant exprVal) {
return null;
}
};

public abstract SQLite3Constant apply(SQLite3Constant exprVal);
Expand Down
26 changes: 26 additions & 0 deletions src/sqlancer/sqlite3/ast/SQLite3Expression.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -900,6 +900,19 @@ SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right, SQLite3Collat
}

},
// Standard SQL NULL-safe comparison operators (3.39.0)
IS_DISTINCT_FROM("IS DISTINCT FROM") {
@Override
SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right, SQLite3CollateSequence collate) {
return null;
}
},
IS_NOT_DISTINCT_FROM("IS NOT DISTINCT FROM") {
@Override
SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right, SQLite3CollateSequence collate) {
return null;
}
},
LIKE("LIKE") {
@Override
public boolean shouldApplyAffinity() {
Expand Down Expand Up @@ -1242,6 +1255,19 @@ SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right) {
}

},
// JSON extraction operators (3.38.0)
JSON_EXTRACT_JSON("->") {
@Override
SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right) {
return null;
}
},
JSON_EXTRACT_VALUE("->>") {
@Override
SQLite3Constant apply(SQLite3Constant left, SQLite3Constant right) {
return null;
}
},
AND("AND") {

@Override
Expand Down
108 changes: 98 additions & 10 deletions src/sqlancer/sqlite3/gen/SQLite3ExpressionGenerator.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -350,12 +350,11 @@ private SQLite3Expression getAggregateFunction(int depth) {

private SQLite3Expression getAggregate(int depth, SQLite3AggregateFunction random) {
int nrArgs;
// if (random == SQLite3AggregateFunction.ZIPFILE) {
// nrArgs = Randomly.fromOptions(2, 4);
// } else {
// nrArgs = 1;
// }
nrArgs = 1;
if (random == SQLite3AggregateFunction.STRING_AGG) {
nrArgs = 2; // string_agg(value, separator) requires exactly 2 args
} else {
nrArgs = 1;
}
return new SQLite3Aggregate(getRandomExpressions(nrArgs, depth + 1), random);
}

Expand Down Expand Up @@ -472,16 +471,105 @@ List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3Expressi
JSON("json", 1), //
JSON_ARRAY("json_array", 2, Attribute.VARIADIC), JSON_ARRAY_LENGTH("json_array_length", 1), //
JSON_ARRAY_LENGTH2("json_array_length", 2), //
JSON_EXTRACT("json_extract", 2, Attribute.VARIADIC), JSON_INSERT("json_insert", 3, Attribute.VARIADIC),
JSON_OBJECT("json_object", 2, Attribute.VARIADIC), JSON_PATCH("json_patch", 2),
JSON_REMOVE("json_remove", 2, Attribute.VARIADIC), JSON_TYPE("json_type", 1), //
JSON_EXTRACT("json_extract", 2, Attribute.VARIADIC),
// json_insert/set/replace need odd total arg count: json + (path,value)+ pairs
JSON_INSERT("json_insert", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
// json_object needs even arg count: (key,value)+ pairs
JSON_OBJECT("json_object", 2, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 != 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
JSON_PATCH("json_patch", 2), JSON_REMOVE("json_remove", 2, Attribute.VARIADIC), JSON_TYPE("json_type", 1), //
JSON_VALID("json_valid", 1), //
JSON_QUOTE("json_quote", 1), //

RTREENODE("rtreenode", 2),

// FTS
HIGHLIGHT("highlight", 4);
HIGHLIGHT("highlight", 4),

// math functions (3.35.0, always available in the Xerial JDBC driver)
ACOS("acos", 1), ACOSH("acosh", 1), ASIN("asin", 1), ASINH("asinh", 1), //
ATAN("atan", 1), ATAN2("atan2", 2), ATANH("atanh", 1), //
CEIL("ceil", 1), CEILING("ceiling", 1), COS("cos", 1), COSH("cosh", 1), //
DEGREES("degrees", 1), EXP("exp", 1), FLOOR("floor", 1), //
LN("ln", 1), LOG("log", 1), LOG2("log2", 1), LOG10("log10", 1), //
MOD("mod", 2), PI("pi", 0), POW("pow", 2), POWER("power", 2), //
RADIANS("radians", 1), SIN("sin", 1), SINH("sinh", 1), SQRT("sqrt", 1), //
TAN("tan", 1), TANH("tanh", 1), TRUNC("trunc", 1), //

// format() - alias for printf() (3.38.0)
FORMAT("format", 1, Attribute.VARIADIC), //
// unixepoch() date function (3.38.0)
UNIXEPOCH("unixepoch", 0, Attribute.NONDETERMINISTIC, Attribute.VARIADIC), //

// unhex() (3.41.0)
UNHEX("unhex", 1), UNHEX2("unhex", 2), //

// octet_length() and timediff() (3.43.0)
OCTET_LENGTH("octet_length", 1), TIMEDIFF("timediff", 2), //

// concat() and concat_ws() (3.44.0)
CONCAT("concat", 2, Attribute.VARIADIC), //
CONCAT_WS("concat_ws", 2, Attribute.VARIADIC), //

// json_replace and json_set (missing from prior list): odd arg count
JSON_REPLACE("json_replace", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
JSON_SET("json_set", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},

// JSONB scalar functions (3.45.0)
JSONB("jsonb", 1), //
JSONB_ARRAY("jsonb_array", 2, Attribute.VARIADIC), //
JSONB_EXTRACT("jsonb_extract", 2, Attribute.VARIADIC), //
JSONB_INSERT("jsonb_insert", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
JSONB_OBJECT("jsonb_object", 2, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 != 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
JSONB_PATCH("jsonb_patch", 2), //
JSONB_REMOVE("jsonb_remove", 2, Attribute.VARIADIC), //
JSONB_REPLACE("jsonb_replace", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},
JSONB_SET("jsonb_set", 3, Attribute.VARIADIC) {
@Override
List<SQLite3Expression> generateArguments(int nrArgs, int depth, SQLite3ExpressionGenerator gen) {
return super.generateArguments(nrArgs % 2 == 0 ? nrArgs - 1 : nrArgs, depth, gen);
}
},

// json_pretty() (3.46.0)
JSON_PRETTY("json_pretty", 1), //

// unistr() and unistr_quote() (3.50.0)
UNISTR("unistr", 1), UNISTR_QUOTE("unistr_quote", 1);

// testing functions
// EXPR_COMPARE("expr_compare", 2), EXPR_IMPLIES_EXPR("expr_implies_expr", 2);
Expand Down
Loading