sqlancer · DerZc · Jan 5, 2025 · Jan 11, 2025 · Jan 13, 2025 · Jan 16, 2025
diff --git a/.github/workflows/main.yml b/.github/workflows/main.yml
@@ -278,6 +278,7 @@ jobs:
         run: |
           mvn -Dtest=TestDuckDBTLP test
           mvn -Dtest=TestDuckDBNoREC test
+          mvn -Dtest=TestDuckDBCODDTest test
 
   h2:
     name: DBMS Tests (H2)

diff --git a/pom.xml b/pom.xml
@@ -314,7 +314,7 @@
     <dependency>
       <groupId>org.duckdb</groupId>
       <artifactId>duckdb_jdbc</artifactId>
-      <version>1.2.0</version>
+      <version>1.2.2.0</version>
     </dependency>
     <dependency>
       <groupId>com.facebook.presto</groupId>

diff --git a/src/sqlancer/common/ast/SelectBase.java b/src/sqlancer/common/ast/SelectBase.java
@@ -1,5 +1,6 @@
 package sqlancer.common.ast;
 
+import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
 
@@ -14,6 +15,23 @@ public class SelectBase<T> {
     T havingClause;
     T limitClause;
     T offsetClause;
+    T withClause;
+
+    public SelectBase() {
+    }
+
+    public SelectBase(SelectBase<T> other) {
+        fetchColumns = new ArrayList<>(other.fetchColumns);
+        groupByExpressions = new ArrayList<>(other.groupByExpressions);
+        orderByExpressions = new ArrayList<>(other.orderByExpressions);
+        joinList = new ArrayList<>(other.joinList);
+        fromList = new ArrayList<>(other.fromList);
+        whereClause = other.whereClause;
+        havingClause = other.havingClause;
+        limitClause = other.limitClause;
+        offsetClause = other.offsetClause;
+        withClause = other.withClause;
+    }
 
     public void setFetchColumns(List<T> fetchColumns) {
         if (fetchColumns == null || fetchColumns.isEmpty()) {
@@ -40,6 +58,13 @@ public void setFromTables(List<T> tables) {
         setFromList(tables);
     }
 
+    public void addToFromList(T fromNode) {
+        if (fromNode == null) {
+            throw new IllegalArgumentException();
+        }
+        this.fromList.add(fromNode);
+    }
+
     public List<T> getFromList() {
         if (fromList == null) {
             throw new IllegalStateException();
@@ -126,4 +151,12 @@ public List<T> getGroupByClause() {
     public void setGroupByClause(List<T> groupByExpressions) {
         setGroupByExpressions(groupByExpressions);
     }
+
+    public void setWithClause(T withClause) {
+        this.withClause = withClause;
+    }
+
+    public T getWithClause() {
+        return this.withClause;
+    }
 }
diff --git a/src/sqlancer/common/ast/newast/NewExistsNode.java b/src/sqlancer/common/ast/newast/NewExistsNode.java
@@ -0,0 +1,19 @@
+package sqlancer.common.ast.newast;
+
+public class NewExistsNode<T> {
+    private final T expr;
+    private final Boolean isNot;
+
+    public NewExistsNode(T expr, Boolean isNot) {
+        this.expr = expr;
+        this.isNot = isNot;
+    }
+
+    public T getExpr() {
+        return expr;
+    }
+
+    public Boolean getIsNot() {
+        return isNot;
+    }
+}
diff --git a/src/sqlancer/common/ast/newast/NewToStringVisitor.java b/src/sqlancer/common/ast/newast/NewToStringVisitor.java
@@ -35,12 +35,21 @@ public void visit(E expr) {
             visit((NewPostfixTextNode<E>) expr);
         } else if (expr instanceof NewTernaryNode<?>) {
             visit((NewTernaryNode<E>) expr);
+        } else if (expr instanceof NewExistsNode<?>) {
+            visit((NewExistsNode<E>) expr);
+        } else if (expr instanceof NewValuesNode<?>) {
+            visit((NewValuesNode<E>) expr);
+        } else if (expr instanceof NewWithNode<?>) {
+            visit((NewWithNode<E>) expr);
         } else {
             visitSpecific(expr);
         }
     }
 
     public void visit(List<E> expressions) {
+        if (!expressions.isEmpty() && expressions.get(0) instanceof NewValuesNode) {
+            sb.append("VALUES ");
+        }
         for (int i = 0; i < expressions.size(); i++) {
             if (i != 0) {
                 sb.append(", ");
@@ -59,7 +68,9 @@ public void visit(TableReferenceNode<E, ?> tableRef) {
     }
 
     public void visit(NewAliasNode<E> alias) {
+        sb.append("(");
         visit(alias.getExpr());
+        sb.append(")");
         sb.append(" AS ");
         sb.append(alias.getAlias());
     }
@@ -161,6 +172,35 @@ public void visit(NewTernaryNode<E> ternaryNode) {
         sb.append(")");
     }
 
+    public void visit(NewExistsNode<E> existExpr) {
+        if (existExpr.getIsNot()) {
+            sb.append(" NOT");
+        }
+        sb.append(" EXISTS(");
+        visit(existExpr.getExpr());
+        sb.append(")");
+    }
+
+    public void visit(NewValuesNode<E> valuesExpr) {
+        sb.append("(");
+        List<E> values = valuesExpr.getValues();
+        for (int i = 0; i < values.size(); ++i) {
+            if (i != 0) {
+                sb.append(", ");
+            }
+            visit(values.get(i));
+        }
+        sb.append(")");
+    }
+
+    public void visit(NewWithNode<E> withExpr) {
+        sb.append("WITH ");
+        visit(withExpr.getLeftExpr());
+        sb.append(" AS(");
+        visit(withExpr.getRightExpr());
+        sb.append(") ");
+    }
+
     public String get() {
         return sb.toString();
     }

diff --git a/src/sqlancer/common/ast/newast/NewValuesNode.java b/src/sqlancer/common/ast/newast/NewValuesNode.java
@@ -0,0 +1,15 @@
+package sqlancer.common.ast.newast;
+
+import java.util.List;
+
+public class NewValuesNode<T> {
+    private final List<T> valuesList;
+
+    public NewValuesNode(List<T> valuesList) {
+        this.valuesList = valuesList;
+    }
+
+    public List<T> getValues() {
+        return this.valuesList;
+    }
+}
diff --git a/src/sqlancer/common/ast/newast/NewWithNode.java b/src/sqlancer/common/ast/newast/NewWithNode.java
@@ -0,0 +1,21 @@
+package sqlancer.common.ast.newast;
+
+import java.util.List;
+
+public class NewWithNode<T> {
+    private final T leftExpr;
+    private final List<T> rightExpr;
+
+    public NewWithNode(T leftExpr, List<T> rightExpr) {
+        this.leftExpr = leftExpr;
+        this.rightExpr = rightExpr;
+    }
+
+    public T getLeftExpr() {
+        return this.leftExpr;
+    }
+
+    public List<T> getRightExpr() {
+        return this.rightExpr;
+    }
+}
diff --git a/src/sqlancer/common/gen/UntypedExpressionGenerator.java b/src/sqlancer/common/gen/UntypedExpressionGenerator.java
@@ -55,6 +55,10 @@ public List<E> generateOrderBys() {
         return generateExpressions(Randomly.smallNumber() + 1);
     }
 
+    public List<E> generateGroupBys() {
+        return generateExpressions(Randomly.smallNumber() + 1);
+    }
+
     // override this class to generate aggregate functions
     public E generateHavingClause() {
         allowAggregates = true;

diff --git a/src/sqlancer/duckdb/DuckDBErrors.java b/src/sqlancer/duckdb/DuckDBErrors.java
@@ -57,6 +57,14 @@ public static List<String> getExpressionErrors() {
         errors.add("Cannot subtract infinite timestamps");
         errors.add("Timestamp difference is out of bounds");
 
+        // added by CODDTest
+        // errors.add("must appear in the GROUP BY clause or be used in an aggregate function");
+        // errors.add("must appear in the GROUP BY clause or must be part of an aggregate function");
+        // errors.add("GROUP BY term out of range - should be between");
+        // errors.add("INTERNAL Error: Failed to bind column reference");
+        errors.add("Binder Error: Aggregate with only constant parameters has to be bound in the root subquery");
+        errors.add("COLLATE can only be applied to varchar columns");
+
         return errors;
     }
 
@@ -68,6 +76,9 @@ public static List<Pattern> getExpressionErrorsRegex() {
         errors.add(Pattern.compile("Cannot mix values of type .* and .* in COALESCE operator"));
         errors.add(Pattern.compile("Cannot compare values of type .* and type .*"));
 
+        // added by CODDTest
+        // https://github.com/duckdb/duckdb/issues/15554
+        errors.add(Pattern.compile("Binder Error: Referenced table \"(.+?)\" not found!"));
         return errors;
     }
 
@@ -89,7 +100,9 @@ private static List<String> getFunctionErrors() {
         errors.add("SUBSTRING cannot handle negative lengths");
         errors.add("is undefined outside [-1,1]"); // ACOS etc
         errors.add("invalid type specifier"); // PRINTF
+        errors.add("Invalid type specifier"); // PRINTF
         errors.add("argument index out of range"); // PRINTF
+        errors.add("Argument index \"0\" out of range"); // PRINTF
         errors.add("invalid format string"); // PRINTF
         errors.add("number is too big"); // PRINTF
         errors.add("Like pattern must not end with escape character!"); // LIKE
@@ -129,6 +142,12 @@ public static List<String> getInsertErrors() {
         errors.add("create unique index, table contains duplicate data");
         errors.add("Failed to cast");
 
+        // added by CODDTest
+        errors.add("violates primary key constraint");
+        errors.add("Referenced update column rowid not found in table");
+        errors.add("does not have a column with name \"rowid\"");
+        errors.add("violates unique constraint");
+
         return errors;
     }
 

diff --git a/src/sqlancer/duckdb/DuckDBOptions.java b/src/sqlancer/duckdb/DuckDBOptions.java
@@ -77,6 +77,25 @@ public class DuckDBOptions implements DBMSSpecificOptions<DuckDBOracleFactory> {
     @Parameter(names = "--max-num-updates", description = "The maximum number of UPDATE statements that are issued for a database", arity = 1)
     public int maxNumUpdates = 5;
 
+    public enum CODDTestModel {
+        RANDOM, EXPRESSION, SUBQUERY;
+
+        public boolean isRandom() {
+            return this == RANDOM;
+        }
+
+        public boolean isExpression() {
+            return this == EXPRESSION;
+        }
+
+        public boolean isSubquery() {
+            return this == SUBQUERY;
+        }
+    }
+
+    @Parameter(names = { "--coddtest-model" }, description = "Apply CODDTest on EXPRESSION, SUBQUERY, or RANDOM")
+    public CODDTestModel coddTestModel = CODDTestModel.RANDOM;
+
     @Parameter(names = "--oracle")
     public List<DuckDBOracleFactory> oracles = Arrays.asList(DuckDBOracleFactory.QUERY_PARTITIONING);
 

diff --git a/src/sqlancer/duckdb/DuckDBOracleFactory.java b/src/sqlancer/duckdb/DuckDBOracleFactory.java
@@ -10,7 +10,9 @@
 import sqlancer.common.oracle.TLPWhereOracle;
 import sqlancer.common.oracle.TestOracle;
 import sqlancer.common.query.ExpectedErrors;
+import sqlancer.duckdb.DuckDBProvider.DuckDBGlobalState;
 import sqlancer.duckdb.gen.DuckDBExpressionGenerator;
+import sqlancer.duckdb.test.DuckDBCODDTestOracle;
 import sqlancer.duckdb.test.DuckDBQueryPartitioningAggregateTester;
 import sqlancer.duckdb.test.DuckDBQueryPartitioningDistinctTester;
 import sqlancer.duckdb.test.DuckDBQueryPartitioningGroupByTester;
@@ -81,6 +83,12 @@ public TestOracle<DuckDBProvider.DuckDBGlobalState> create(DuckDBProvider.DuckDB
             oracles.add(GROUP_BY.create(globalState));
             return new CompositeTestOracle<DuckDBProvider.DuckDBGlobalState>(oracles, globalState);
         }
+    },
+    CODDTest {
+        @Override
+        public TestOracle<DuckDBGlobalState> create(DuckDBGlobalState globalState) throws SQLException {
+            return new DuckDBCODDTestOracle(globalState);
+        }
     };
 
 }
diff --git a/src/sqlancer/duckdb/DuckDBSchema.java b/src/sqlancer/duckdb/DuckDBSchema.java
@@ -41,9 +41,19 @@ public static class DuckDBCompositeDataType {
 
         private final int size;
 
+        // This is used to handle the type that out of the scope of our code
+        private final String typeName;
+
         public DuckDBCompositeDataType(DuckDBDataType dataType, int size) {
             this.dataType = dataType;
             this.size = size;
+            this.typeName = "";
+        }
+
+        public DuckDBCompositeDataType(String typeName) {
+            this.dataType = null;
+            this.size = 0;
+            this.typeName = typeName;
         }
 
         public DuckDBDataType getPrimitiveDataType() {
@@ -82,6 +92,9 @@ public static DuckDBCompositeDataType getRandomWithoutNull() {
 
         @Override
         public String toString() {
+            if (getPrimitiveDataType() == null) {
+                return this.typeName;
+            }
             switch (getPrimitiveDataType()) {
             case INT:
                 switch (size) {