Skip to content

Support CODDTest in sqlancer #1054

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
DerZc wants to merge 19 commits into
base: main
Choose a base branch
from
Open

Support CODDTest in sqlancer #1054

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
19 commits
Select commit Hold shift + click to select a range
6b86175
add the implementation of CODDTest for SQLite3
DerZc Jan 3, 2025
c6f2c4a
fix a typo and remove some redundant code of CODDTest for sqlite3
DerZc Jan 5, 2025
7a754ba
add CODDTest for DuckDB
DerZc Jan 5, 2025
f18638e
Merge remote-tracking branch 'upstream/main'
DerZc Jan 5, 2025
5db6e3c
enable CTE and insert test in CODDTest for DuckDB
DerZc Jan 11, 2025
dbd8c89
support CODDTest for CockroachDB
DerZc Jan 12, 2025
0a8f9a1
fix some false positive in CODDTest for DuckDB
DerZc Jan 13, 2025
9596426
fix the false positive in CODDTest for SQLite
DerZc Jan 14, 2025
d4e0f8f
remove a piece of redundant code
DerZc Jan 16, 2025
4122731
remove some radundant log code in CODDTest for CockroachDB
DerZc Jan 16, 2025
bc53382
support CODDTest for TiDB
DerZc Jan 16, 2025
e9f1418
support mysql
DerZc Mar 4, 2025
412f69e
fix a typo
DerZc Mar 4, 2025
2d74254
add comments for ExpressionBag
DerZc Mar 4, 2025
9db8ae0
remove deleteCharAt to simplify code logic
DerZc Mar 4, 2025
9581dbb
modify DuckDBConstantWithType to DuckDBTypeCast
DerZc Mar 12, 2025
f99cc40
remove an internel error from the ignore list
DerZc Mar 12, 2025
6343041
handle confilct in MySQL
DerZc Mar 22, 2025
3045bad
Merge remote-tracking branch 'upstream/main'
DerZc Mar 22, 2025
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
3 changes: 3 additions & 0 deletions src/sqlancer/cockroachdb/CockroachDBOptions.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -30,6 +30,9 @@ public class CockroachDBOptions implements DBMSSpecificOptions<CockroachDBOracle
@Parameter(names = { "--max-num-indexes" }, description = "The maximum number of indexes that can be created")
public int maxNumIndexes = 20;

@Parameter(names = { "--coddtest-model" }, description = "Apply CODDTest on expression, subquery, or random")
public String coddTestModel = "random";
Copy link
Copy Markdown
Contributor

@mrigger mrigger Jan 19, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Perhaps we could use a similar strategy as the TLP oracles where we have separate enums?


@Override
public List<CockroachDBOracleFactory> getTestOracleFactory() {
return Arrays.asList(oracle);
Expand Down
8 changes: 8 additions & 0 deletions src/sqlancer/cockroachdb/CockroachDBOracleFactory.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -7,7 +7,9 @@

import sqlancer.IgnoreMeException;
import sqlancer.OracleFactory;
import sqlancer.cockroachdb.CockroachDBProvider.CockroachDBGlobalState;
import sqlancer.cockroachdb.gen.CockroachDBExpressionGenerator;
import sqlancer.cockroachdb.oracle.CockroachDBCODDTestOracle;
import sqlancer.cockroachdb.oracle.tlp.CockroachDBTLPAggregateOracle;
import sqlancer.cockroachdb.oracle.tlp.CockroachDBTLPDistinctOracle;
import sqlancer.cockroachdb.oracle.tlp.CockroachDBTLPExtendedWhereOracle;
Expand Down Expand Up @@ -133,6 +135,12 @@ public TestOracle<CockroachDBProvider.CockroachDBGlobalState> create(
public boolean requiresAllTablesToContainRows() {
return true;
}
},
CODDTest {
@Override
public TestOracle<CockroachDBGlobalState> create(CockroachDBGlobalState globalState) throws SQLException {
return new CockroachDBCODDTestOracle(globalState);
}
};

}
52 changes: 52 additions & 0 deletions src/sqlancer/cockroachdb/CockroachDBSchema.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -41,6 +41,8 @@ public static class CockroachDBCompositeDataType {

private CockroachDBCompositeDataType elementType;

private String typeName = "";

public CockroachDBCompositeDataType(CockroachDBDataType dataType) {
this.dataType = dataType;
this.size = -1;
Expand All @@ -60,6 +62,53 @@ public CockroachDBCompositeDataType(CockroachDBDataType dataType, CockroachDBCom
this.elementType = elementType;
}


public CockroachDBCompositeDataType(String typeName) {
switch (typeName) {
case "smallint":
case "integer":
case "bigint":
this.dataType = CockroachDBDataType.INT;
break;
case "boolean":
this.dataType = CockroachDBDataType.BOOL;
break;
case "text":
case "character":
case "character varying":
case "name":
case "regclass":
this.dataType = CockroachDBDataType.STRING;
break;
case "numeric":
this.dataType = CockroachDBDataType.DECIMAL;
break;
case "double precision":
case "real":
this.dataType = CockroachDBDataType.FLOAT;
break;
case "bit":
case "bit varying":
this.dataType = CockroachDBDataType.BIT;
break;

// for ire, maybe not correct
case "unknown":
this.dataType = CockroachDBDataType.BOOL;
break;
default:
throw new AssertionError(typeName);
}

// TODO: check later
if (typeName.equals("bit varying")) {
this.size = 4;
} else {
this.size = -1;
}
this.typeName = typeName;
}

public CockroachDBDataType getPrimitiveDataType() {
return dataType;
}
Expand All @@ -85,6 +134,9 @@ public static CockroachDBCompositeDataType getBit(int size) {

@Override
public String toString() {
if (this.typeName != "") {
return this.typeName;
}
switch (dataType) {
case INT:
switch (size) {
Expand Down
173 changes: 173 additions & 0 deletions src/sqlancer/cockroachdb/CockroachDBToStringVisitor.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,20 +1,35 @@
package sqlancer.cockroachdb;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.stream.Collectors;

import sqlancer.Randomly;
import sqlancer.cockroachdb.CockroachDBSchema.CockroachDBColumn;
import sqlancer.cockroachdb.CockroachDBSchema.CockroachDBTable;
import sqlancer.cockroachdb.ast.CockroachDBAggregate;
import sqlancer.cockroachdb.ast.CockroachDBAlias;
import sqlancer.cockroachdb.ast.CockroachDBAllOperator;
import sqlancer.cockroachdb.ast.CockroachDBAnyOperator;
import sqlancer.cockroachdb.ast.CockroachDBBetweenOperation;
import sqlancer.cockroachdb.ast.CockroachDBCaseOperation;
import sqlancer.cockroachdb.ast.CockroachDBColumnReference;
import sqlancer.cockroachdb.ast.CockroachDBConstant;
import sqlancer.cockroachdb.ast.CockroachDBConstant.CockroachDBNullConstant;
import sqlancer.cockroachdb.ast.CockroachDBExists;
import sqlancer.cockroachdb.ast.CockroachDBExpression;
import sqlancer.cockroachdb.ast.CockroachDBExpressionBag;
import sqlancer.cockroachdb.ast.CockroachDBFunctionCall;
import sqlancer.cockroachdb.ast.CockroachDBInOperation;
import sqlancer.cockroachdb.ast.CockroachDBJoin;
import sqlancer.cockroachdb.ast.CockroachDBMultiValuedComparison;
import sqlancer.cockroachdb.ast.CockroachDBResultMap;
import sqlancer.cockroachdb.ast.CockroachDBSelect;
import sqlancer.cockroachdb.ast.CockroachDBTableAndColumnReference;
import sqlancer.cockroachdb.ast.CockroachDBTableReference;
import sqlancer.cockroachdb.ast.CockroachDBTypeof;
import sqlancer.cockroachdb.ast.CockroachDBValues;
import sqlancer.cockroachdb.ast.CockroachDBWithClause;
import sqlancer.common.visitor.ToStringVisitor;

public class CockroachDBToStringVisitor extends ToStringVisitor<CockroachDBExpression> implements CockroachDBVisitor {
Expand Down Expand Up @@ -75,6 +90,9 @@ public void visit(CockroachDBBetweenOperation op) {

@Override
public void visit(CockroachDBSelect select) {
if (select.getWithClause() != null) {
visit(select.getWithClause());
}
sb.append("SELECT ");
if (select.isDistinct()) {
sb.append("DISTINCT ");
Expand Down Expand Up @@ -231,4 +249,159 @@ public void visit(CockroachDBMultiValuedComparison comp) {
sb.append(")");
sb.append(")");
}


@Override
public void visit(CockroachDBExists existsExpr) {
if (existsExpr.getNegated()) {
sb.append(" NOT");
}
sb.append(" EXISTS(");
visit(existsExpr.getExpression());
sb.append(")");
}

@Override
public void visit(CockroachDBExpressionBag exprBag) {
visit(exprBag.getInnerExpr());
}

@Override
public void visit(CockroachDBValues values) {
LinkedHashMap<CockroachDBColumn, List<CockroachDBConstant>> vs = values.getValues();
int size = vs.values().iterator().next().size();
sb.append("(VALUES ");
for (int i = 0; i < size; i++) {
sb.append("(");
boolean isFirstColumn = true;
for (CockroachDBColumn c: vs.keySet()) {
if (!isFirstColumn) {
sb.append(", ");
}
sb.append(vs.get(c).get(i).toString());
if (!(vs.get(c).get(i) instanceof CockroachDBNullConstant)) {
if (c.getType() != null) {
sb.append("::" + c.getType().toString());
}
}
isFirstColumn = false;
}
sb.append(")");
if (i < size - 1) {
sb.append(", ");
}
}
sb.append(")");
}

@Override
public void visit(CockroachDBWithClause withClause) {
sb.append("WITH ");
visit(withClause.getLeft());
sb.append(" AS (");
visit(withClause.getRight());
sb.append(") ");
}

@Override
public void visit(CockroachDBTableAndColumnReference tableAndColumnReference) {
CockroachDBTable table = tableAndColumnReference.getTable();
sb.append(table.getName());
sb.append("(");
sb.append(table.getColumnsAsString());
sb.append(") ");
}

@Override
public void visit(CockroachDBAlias alias) {
CockroachDBExpression e = alias.getExpression();
if (e instanceof CockroachDBSelect) {
sb.append("(");
}
visit(e);
if (e instanceof CockroachDBSelect) {
sb.append(")");
}
sb.append(" AS ");
sb.append(alias.getAlias());
}

@Override
public void visit(CockroachDBTypeof typeOf) {
sb.append("pg_typeof(");
visit(typeOf.getExpr());
sb.append(")");
}

@Override
public void visit(CockroachDBResultMap expr) {
// use CASE WHEN to express the constant result of a expression
LinkedHashMap<CockroachDBColumnReference, List<CockroachDBConstant>> dbstate = expr.getDbStates();
List<CockroachDBConstant> result = expr.getResult();
int size = dbstate.values().iterator().next().size();
if (size == 0) {
sb.append(" NULL ");
return;
}
sb.append(" CASE ");
for (int i = 0; i < size; i++) {
sb.append("WHEN ");
Boolean isFirstCondition = true;
for (CockroachDBColumnReference columnRef : dbstate.keySet()) {
if (!isFirstCondition) {
sb.append(" AND ");
}
visit(columnRef);
if (dbstate.get(columnRef).get(i) instanceof CockroachDBNullConstant) {
sb.append(" IS NULL");
} else {
sb.append(" = ");
visit(dbstate.get(columnRef).get(i));
if (!(dbstate.get(columnRef).get(i) instanceof CockroachDBNullConstant)) {
if (columnRef.getColumn().getType() != null) {
sb.append("::" + columnRef.getColumn().getType().toString());
}
}
}
isFirstCondition = false;
}
sb.append(" THEN ");
visit(result.get(i));
if (!(result.get(i) instanceof CockroachDBNullConstant)) {
if (expr.getResultType() != null) {
sb.append("::" + expr.getResultType().toString());
}
}
sb.append(" ");
}
sb.append("END ");
}

@Override
public void visit(CockroachDBAllOperator allOperation) {
sb.append("(");
visit(allOperation.getLeftExpr());
sb.append(") ");
sb.append(allOperation.getOperator());
sb.append(" ALL (");
// if (allOperation.getRightExpr() instanceof CockroachDBValues) {
// sb.append("SELECT ");
// }
visit(allOperation.getRightExpr());
sb.append(")");
}

@Override
public void visit(CockroachDBAnyOperator anyOperation) {
sb.append("(");
visit(anyOperation.getLeftExpr());
sb.append(") ");
sb.append(anyOperation.getOperator());
sb.append(" ANY (");
// if (anyOperation.getRightExpr() instanceof CockroachDBValues) {
// sb.append("SELECT ");
// }
visit(anyOperation.getRightExpr());
sb.append(")");
}
}
42 changes: 42 additions & 0 deletions src/sqlancer/cockroachdb/CockroachDBVisitor.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,17 +1,27 @@
package sqlancer.cockroachdb;

import sqlancer.cockroachdb.ast.CockroachDBAggregate;
import sqlancer.cockroachdb.ast.CockroachDBAlias;
import sqlancer.cockroachdb.ast.CockroachDBAllOperator;
import sqlancer.cockroachdb.ast.CockroachDBAnyOperator;
import sqlancer.cockroachdb.ast.CockroachDBBetweenOperation;
import sqlancer.cockroachdb.ast.CockroachDBCaseOperation;
import sqlancer.cockroachdb.ast.CockroachDBColumnReference;
import sqlancer.cockroachdb.ast.CockroachDBConstant;
import sqlancer.cockroachdb.ast.CockroachDBExists;
import sqlancer.cockroachdb.ast.CockroachDBExpression;
import sqlancer.cockroachdb.ast.CockroachDBExpressionBag;
import sqlancer.cockroachdb.ast.CockroachDBFunctionCall;
import sqlancer.cockroachdb.ast.CockroachDBInOperation;
import sqlancer.cockroachdb.ast.CockroachDBJoin;
import sqlancer.cockroachdb.ast.CockroachDBMultiValuedComparison;
import sqlancer.cockroachdb.ast.CockroachDBResultMap;
import sqlancer.cockroachdb.ast.CockroachDBSelect;
import sqlancer.cockroachdb.ast.CockroachDBTableAndColumnReference;
import sqlancer.cockroachdb.ast.CockroachDBTableReference;
import sqlancer.cockroachdb.ast.CockroachDBTypeof;
import sqlancer.cockroachdb.ast.CockroachDBValues;
import sqlancer.cockroachdb.ast.CockroachDBWithClause;

public interface CockroachDBVisitor {

Expand All @@ -37,6 +47,18 @@ public interface CockroachDBVisitor {

void visit(CockroachDBMultiValuedComparison comp);

// CODDTest
void visit(CockroachDBExists existsExpr);
void visit(CockroachDBExpressionBag exprBag);
void visit(CockroachDBValues values);
void visit(CockroachDBWithClause withClause);
void visit(CockroachDBTableAndColumnReference tableAndColumnReference);
void visit(CockroachDBAlias alias);
void visit(CockroachDBTypeof typeOf);
void visit(CockroachDBResultMap resMap);
void visit(CockroachDBAllOperator allOperator);
void visit(CockroachDBAnyOperator anyOperator);

default void visit(CockroachDBExpression expr) {
if (expr instanceof CockroachDBConstant) {
visit((CockroachDBConstant) expr);
Expand All @@ -60,6 +82,26 @@ default void visit(CockroachDBExpression expr) {
visit((CockroachDBAggregate) expr);
} else if (expr instanceof CockroachDBMultiValuedComparison) {
visit((CockroachDBMultiValuedComparison) expr);
} else if (expr instanceof CockroachDBExists) {
visit((CockroachDBExists) expr);
} else if (expr instanceof CockroachDBExpressionBag) {
visit((CockroachDBExpressionBag) expr);
} else if (expr instanceof CockroachDBValues) {
visit((CockroachDBValues) expr);
} else if (expr instanceof CockroachDBWithClause) {
visit ((CockroachDBWithClause) expr);
} else if (expr instanceof CockroachDBTableAndColumnReference) {
visit ((CockroachDBTableAndColumnReference) expr);
} else if (expr instanceof CockroachDBAlias) {
visit ((CockroachDBAlias) expr);
} else if (expr instanceof CockroachDBTypeof) {
visit ((CockroachDBTypeof) expr);
} else if (expr instanceof CockroachDBResultMap) {
visit ((CockroachDBResultMap) expr);
} else if (expr instanceof CockroachDBAllOperator) {
visit((CockroachDBAllOperator) expr);
} else if (expr instanceof CockroachDBAnyOperator) {
visit((CockroachDBAnyOperator) expr);
} else {
throw new AssertionError(expr.getClass());
}
Expand Down
Loading