import XCTest
import GRDB

private struct A: TableRecord { }
private struct B: TableRecord { }
private struct C: TableRecord { }
private struct D: TableRecord { }

class AssociationPrefetchingSQLTests: GRDBTestCase {
    override func setup(_ dbWriter: some DatabaseWriter) throws {
        // A.hasMany(B)
        // A.hasMany(C)
        // B.belongsTo(A)
        // C.belongsTo(A)
        // C.hasMany(D)
        // D.belongsTo(C)
        try dbWriter.write { db in
            try db.create(table: "a") { t in
                t.autoIncrementedPrimaryKey("cola1")
                t.column("cola2", .text)
            }
            try db.create(table: "b") { t in
                t.autoIncrementedPrimaryKey("colb1")
                t.column("colb2", .integer).references("a")
                t.column("colb3", .text)
            }
            try db.create(table: "c") { t in
                t.autoIncrementedPrimaryKey("colc1")
                t.column("colc2", .integer).references("a")
            }
            try db.create(table: "d") { t in
                t.autoIncrementedPrimaryKey("cold1")
                t.column("cold2", .integer).references("c")
                t.column("cold3", .text)
            }
            try db.execute(
                sql: """
                    INSERT INTO a (cola1, cola2) VALUES (?, ?);
                    INSERT INTO a (cola1, cola2) VALUES (?, ?);
                    INSERT INTO a (cola1, cola2) VALUES (?, ?);
                    INSERT INTO b (colb1, colb2, colb3) VALUES (?, ?, ?);
                    INSERT INTO b (colb1, colb2, colb3) VALUES (?, ?, ?);
                    INSERT INTO b (colb1, colb2, colb3) VALUES (?, ?, ?);
                    INSERT INTO b (colb1, colb2, colb3) VALUES (?, ?, ?);
                    INSERT INTO c (colc1, colc2) VALUES (?, ?);
                    INSERT INTO c (colc1, colc2) VALUES (?, ?);
                    INSERT INTO c (colc1, colc2) VALUES (?, ?);
                    INSERT INTO d (cold1, cold2, cold3) VALUES (?, ?, ?);
                    INSERT INTO d (cold1, cold2, cold3) VALUES (?, ?, ?);
                    INSERT INTO d (cold1, cold2, cold3) VALUES (?, ?, ?);
                    INSERT INTO d (cold1, cold2, cold3) VALUES (?, ?, ?);
                    """,
                arguments: [
                    1, "a1",
                    2, "a2",
                    3, "a3",
                    4, 1, "b1",
                    5, 1, "b2",
                    6, 2, "b3",
                    14, nil, "b4",
                    7, 1,
                    8, 2,
                    9, 2,
                    10, 7, "d1",
                    11, 8, "d2",
                    12, 8, "d3",
                    13, 9, "d4",
                ])
        }
    }
    
    func testIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                        .hasMany(B.self)
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT *, "colb2" AS "grdb_colb2" \
                    FROM "b" \
                    WHERE "colb2" IN (1, 2, 3) \
                    ORDER BY "colb1"
                    """])
            }
            
            // Request with avoided prefetch
            do {
                let request = A
                    .none()
                    .including(all: A
                        .hasMany(B.self)
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM \"a\" WHERE 0 ORDER BY \"cola1\"
                    """])
            }
            
            // Request with filters
            do {
                let request = A
                    .filter(Column("cola1") != 3)
                    .including(all: A
                        .hasMany(B.self)
                        .filter(Column("colb1") == 4)
                        .orderByPrimaryKey()
                        .forKey("bs1"))
                    .including(all: A
                        .hasMany(B.self)
                        .filter(Column("colb1") != 4)
                        .orderByPrimaryKey()
                        .forKey("bs2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" \
                    WHERE "cola1" <> 3 \
                    ORDER BY "cola1"
                    """,
                    """
                    SELECT *, "colb2" AS "grdb_colb2" \
                    FROM "b" \
                    WHERE ("colb1" = 4) AND ("colb2" IN (1, 2)) \
                    ORDER BY "colb1"
                    """,
                    """
                    SELECT *, "colb2" AS "grdb_colb2" \
                    FROM "b" \
                    WHERE ("colb1" <> 4) AND ("colb2" IN (1, 2)) \
                    ORDER BY "colb1"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyScalar() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                                .hasMany(B.self)
                                .select(Column("colb2"))
                                .distinct()
                                .order(Column("colb2")))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT DISTINCT "colb2", "colb2" AS "grdb_colb2" \
                    FROM "b" \
                    WHERE "colb2" IN (1, 2, 3) \
                    ORDER BY "colb2"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyWithCompoundForeignKey() throws {
        // We can use the CTE technique
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "parent") { t in
                t.column("parentA", .text)
                t.column("parentB", .text)
                t.primaryKey(["parentA", "parentB"])
            }
            try db.create(table: "child") { t in
                t.column("pA", .text)
                t.column("pB", .text)
                t.column("name", .text)
                t.foreignKey(["pA", "pB"], references: "parent")
            }
            try db.execute(sql: """
                INSERT INTO parent (parentA, parentB) VALUES ('foo', 'bar');
                INSERT INTO parent (parentA, parentB) VALUES ('baz', 'qux');
                INSERT INTO child (pA, pB, name) VALUES ('foo', 'bar', 'foobar1');
                INSERT INTO child (pA, pB, name) VALUES ('foo', 'bar', 'foobar2');
                INSERT INTO child (pA, pB, name) VALUES ('baz', 'qux', 'bazqux1');
                """)
            
            struct Parent: TableRecord { }
            struct Child: TableRecord { }
            
            // Plain request
            do {
                let request = Parent
                    .including(all: Parent
                        .hasMany(Child.self))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" ORDER BY "parentA", "parentB"
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent") \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" WHERE ("pA", "pB") IN "grdb_base"
                    """])
            }
            
            // Request with avoided prefetch
            do {
                let request = Parent
                    .none()
                    .including(all: Parent
                        .hasMany(Child.self))
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" WHERE 0 ORDER BY "parentA", "parentB"
                    """])
            }
            
            // Request with filters
            do {
                let request = Parent
                    .including(all: Parent
                        .hasMany(Child.self)
                        .filter(Column("name") == "foo"))
                    .filter(Column("parentA") == "foo")
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" WHERE "parentA" = 'foo' ORDER BY "parentA", "parentB"
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent" WHERE "parentA" = 'foo') \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" \
                    WHERE ("name" = 'foo') AND (("pA", "pB") IN "grdb_base")
                    """])
            }

            // Limited ordered request (preserve both ordering and limit in the CTE)
            do {
                let request = Parent
                    .including(all: Parent.hasMany(Child.self))
                    .orderByPrimaryKey()
                    .limit(1)

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)

                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" ORDER BY "parentA", "parentB" LIMIT 1
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent" ORDER BY "parentA", "parentB" LIMIT 1) \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" \
                    WHERE ("pA", "pB") IN "grdb_base"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                        .hasMany(C.self)
                        .including(all: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" \
                    FROM "c" \
                    WHERE "colc2" IN (1, 2, 3) \
                    ORDER BY "colc1"
                    """,
                    """
                    SELECT *, "cold2" AS "grdb_cold2" \
                    FROM "d" \
                    WHERE "cold2" IN (7, 8, 9) \
                    ORDER BY "cold1"
                    """])
            }
            
            // Request with avoided prefetch
            do {
                let request = A
                    .including(all: A
                        .hasMany(C.self)
                        .none()
                        .including(all: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" \
                    FROM "c" \
                    WHERE 0 AND ("colc2" IN (1, 2, 3)) \
                    ORDER BY "colc1"
                    """])
            }
            
            // Request with filters
            do {
                let request = A
                    .filter(Column("cola1") != 3)
                    .including(all: A
                        .hasMany(C.self)
                        .filter(Column("colc1") > 7)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .orderByPrimaryKey()
                        .forKey("cs1"))
                    .including(all: A
                        .hasMany(C.self)
                        .filter(Column("colc1") < 9)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .orderByPrimaryKey()
                        .forKey("cs2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * \
                    FROM "a" \
                    WHERE "cola1" <> 3 \
                    ORDER BY "cola1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" \
                    FROM "c" \
                    WHERE ("colc1" > 7) AND ("colc2" IN (1, 2)) \
                    ORDER BY "colc1"
                    """,
                    """
                    SELECT *, "cold2" AS "grdb_cold2" \
                    FROM "d" \
                    WHERE ("cold1" = 11) AND ("cold2" IN (8, 9)) \
                    ORDER BY "cold1"
                    """,
                    """
                    SELECT *, "cold2" AS "grdb_cold2" \
                    FROM "d" \
                    WHERE ("cold1" <> 11) AND ("cold2" IN (8, 9)) \
                    ORDER BY "cold1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" \
                    FROM "c" \
                    WHERE ("colc1" < 9) AND ("colc2" IN (1, 2)) \
                    ORDER BY "colc1"
                    """,
                    """
                    SELECT *, "cold2" AS "grdb_cold2" \
                    FROM "d" \
                    WHERE ("cold1" = 11) AND ("cold2" IN (7, 8)) \
                    ORDER BY "cold1"
                    """,
                    """
                    SELECT *, "cold2" AS "grdb_cold2" \
                    FROM "d" \
                    WHERE ("cold1" <> 11) AND ("cold2" IN (7, 8)) \
                    ORDER BY "cold1"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyIncludingAllHasManyWithCompoundForeignKey() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "parent") { t in
                t.column("parentA", .text)
                t.column("parentB", .text)
                t.column("name", .text)
                t.primaryKey(["parentA", "parentB"])
            }
            try db.create(table: "child") { t in
                t.column("childA", .text)
                t.column("childB", .text)
                t.column("pA", .text)
                t.column("pB", .text)
                t.column("name", .text)
                t.primaryKey(["childA", "childB"])
                t.foreignKey(["pA", "pB"], references: "parent")
            }
            try db.create(table: "grandchild") { t in
                t.column("cA", .text)
                t.column("cB", .text)
                t.column("name", .text)
                t.foreignKey(["cA", "cB"], references: "child")
            }
            try db.execute(sql: """
                INSERT INTO parent (parentA, parentB, name) VALUES ('foo', 'bar', 'foo');
                INSERT INTO parent (parentA, parentB, name) VALUES ('baz', 'qux', 'foo');
                INSERT INTO child (childA, childB, pA, pB, name) VALUES ('a', 'b', 'foo', 'bar', 'blue');
                INSERT INTO child (childA, childB, pA, pB, name) VALUES ('c', 'd', 'foo', 'bar', 'pink');
                INSERT INTO child (childA, childB, pA, pB, name) VALUES ('e', 'f', 'baz', 'qux', 'blue');
                INSERT INTO grandchild (cA, cB, name) VALUES ('a', 'b', 'dog');
                INSERT INTO grandchild (cA, cB, name) VALUES ('a', 'b', 'cat');
                INSERT INTO grandchild (cA, cB, name) VALUES ('c', 'd', 'cat');
                INSERT INTO grandchild (cA, cB, name) VALUES ('e', 'f', 'dog');
                """)
            
            struct Parent: TableRecord { }
            struct Child: TableRecord { }
            struct GrandChild: TableRecord { }
            
            // Plain request
            do {
                let request = Parent
                    .including(all: Parent.hasMany(Child.self)
                                .including(all: Child.hasMany(GrandChild.self)))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" ORDER BY "parentA", "parentB"
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent") \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" WHERE ("pA", "pB") IN "grdb_base"
                    """,
                    """
                    WITH "grdb_base" AS (\
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent") \
                    SELECT "childA", "childB" FROM "child" \
                    WHERE ("pA", "pB") IN "grdb_base"\
                    ) \
                    SELECT *, "cA" AS "grdb_cA", "cB" AS "grdb_cB" \
                    FROM "grandChild" WHERE ("cA", "cB") IN "grdb_base"
                    """])
            }
            
            // Request with avoided prefetch
            do {
                let request = Parent
                    .none()
                    .including(all: Parent.hasMany(Child.self)
                                .including(all: Child.hasMany(GrandChild.self)))
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" WHERE 0 ORDER BY "parentA", "parentB"
                    """])
            }
            do {
                let request = Parent
                    .including(all: Parent.hasMany(Child.self)
                                .none()
                                .including(all: Child.hasMany(GrandChild.self)))
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" ORDER BY "parentA", "parentB"
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent") \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" \
                    WHERE 0 AND (("pA", "pB") IN "grdb_base")
                    """])
            }

            // Request with filters
            do {
                let request = Parent
                    .including(all: Parent.hasMany(Child.self)
                                .including(all: Child.hasMany(GrandChild.self)
                                            .filter(Column("name") == "dog"))
                                .filter(Column("name") == "blue"))
                    .filter(Column("name") == "foo")
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "parent" WHERE "name" = 'foo' ORDER BY "parentA", "parentB"
                    """,
                    """
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent" WHERE "name" = 'foo') \
                    SELECT *, "pA" AS "grdb_pA", "pB" AS "grdb_pB" \
                    FROM "child" WHERE ("name" = 'blue') AND (("pA", "pB") IN "grdb_base")
                    """,
                    """
                    WITH "grdb_base" AS (\
                    WITH "grdb_base" AS (SELECT "parentA", "parentB" FROM "parent" WHERE "name" = 'foo') \
                    SELECT "childA", "childB" FROM "child" \
                    WHERE ("name" = 'blue') AND (("pA", "pB") IN "grdb_base")\
                    ) \
                    SELECT *, "cA" AS "grdb_cA", "cB" AS "grdb_cB" \
                    FROM "grandChild" \
                    WHERE ("name" = 'dog') AND (("cA", "cB") IN "grdb_base")
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyIncludingRequiredOrOptionalHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                        .hasMany(C.self)
                        .including(required: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "c".*, "c"."colc2" AS "grdb_colc2", "d".* \
                    FROM "c" \
                    JOIN "d" ON "d"."cold2" = "c"."colc1" \
                    WHERE "c"."colc2" IN (1, 2, 3) \
                    ORDER BY "c"."colc1", "d"."cold1"
                    """])
            }
            
            // Request with avoided prefetch
            do {
                let request = A
                    .including(all: A
                        .hasMany(C.self)
                        .none()
                        .including(required: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "c".*, "c"."colc2" AS "grdb_colc2", "d".* \
                    FROM "c" \
                    JOIN "d" ON "d"."cold2" = "c"."colc1" \
                    WHERE 0 AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "c"."colc1", "d"."cold1"
                    """])
            }
            
            // Request with filters
            do {
                let request = A
                    .filter(Column("cola1") != 3)
                    .including(all: A
                        .hasMany(C.self)
                        .filter(Column("colc1") > 7)
                        .including(optional: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("d1"))
                        .including(required: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("d2"))
                        .orderByPrimaryKey()
                        .forKey("cs1"))
                    .including(all: A
                        .hasMany(C.self)
                        .filter(Column("colc1") < 9)
                        .including(optional: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("d1"))
                        .including(required: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("d2"))
                        .orderByPrimaryKey()
                        .forKey("cs2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * \
                    FROM "a" \
                    WHERE "cola1" <> 3 \
                    ORDER BY "cola1"
                    """,
                    """
                    SELECT "c".*, "c"."colc2" AS "grdb_colc2", "d1".*, "d2".* \
                    FROM "c" \
                    LEFT JOIN "d" "d1" ON ("d1"."cold2" = "c"."colc1") AND ("d1"."cold1" = 11) \
                    JOIN "d" "d2" ON ("d2"."cold2" = "c"."colc1") AND ("d2"."cold1" <> 11) \
                    WHERE ("c"."colc1" > 7) AND ("c"."colc2" IN (1, 2)) \
                    ORDER BY "c"."colc1", "d1"."cold1", "d2"."cold1"
                    """,
                    """
                    SELECT "c".*, "c"."colc2" AS "grdb_colc2", "d1".*, "d2".* \
                    FROM "c" \
                    LEFT JOIN "d" "d1" ON ("d1"."cold2" = "c"."colc1") AND ("d1"."cold1" = 11) \
                    JOIN "d" "d2" ON ("d2"."cold2" = "c"."colc1") AND ("d2"."cold1" <> 11) \
                    WHERE ("c"."colc1" < 9) AND ("c"."colc2" IN (1, 2)) \
                    ORDER BY "c"."colc1", "d1"."cold1", "d2"."cold1"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyThroughHasManyUsingHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                        .hasMany(D.self, through: A.hasMany(C.self), using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """])
            }
            
            // Request with filters
            do {
                let request = A
                    .filter(Column("cola1") != 3)
                    .including(all: A
                        .hasMany(D.self, through: A.hasMany(C.self).filter(Column("colc1") == 8).forKey("cs1"), using: C.hasMany(D.self))
                        .orderByPrimaryKey()
                        .forKey("ds1"))
                    .including(all: A
                        .hasMany(D.self, through: A.hasMany(C.self).forKey("cs2"), using: C.hasMany(D.self))
                        .filter(Column("cold1") != 11)
                        .orderByPrimaryKey()
                        .forKey("ds2"))
                    .including(all: A
                        .hasMany(D.self, through: A.hasMany(C.self).forKey("cs2"), using: C.hasMany(D.self))
                        .filter(Column("cold1") == 11)
                        .orderByPrimaryKey()
                        .forKey("ds3"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" \
                    WHERE "cola1" <> 3 \
                    ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 8) AND ("c"."colc2" IN (1, 2)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2)) \
                    WHERE "d"."cold1" <> 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2)) \
                    WHERE "d"."cold1" = 11 \
                    ORDER BY "d"."cold1"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyThroughBelongsToUsingHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = B
                    .including(all: B.hasMany(C.self, through: B.belongsTo(A.self), using: A.hasMany(C.self))
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "b" ORDER BY "colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }
            
            // Request with filters
            do {
                // This request is an example of what users are unlikely to
                // want, because of the shared key between the two different
                // pivot associations.
                let request = B
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") == "a1"),
                            using: A.hasMany(C.self))
                        .orderByPrimaryKey())
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") != "a1"),
                            using: A.hasMany(C.self))
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "b" ORDER BY "colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola2" <> 'a1') AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }
            
            // Request with filters
            do {
                // Another example of what users are unlikely to want, still
                // because of the shared key between the two different pivot
                // associations, and despite the distinct hasMany keys.
                let request = B
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") == "a1"),
                            using: A.hasMany(C.self))
                        .forKey("a1")
                        .orderByPrimaryKey())
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") != "a1"),
                            using: A.hasMany(C.self))
                        .forKey("nota1")
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "b" ORDER BY "colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola2" <> 'a1') AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola2" <> 'a1') AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }

            // Request with filters
            do {
                // This request is a "fixed" version of the previous request,
                // where the two different pivot associations do not share the
                // same key.
                let request = B
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") == "a1").forKey("a1"),
                            using: A.hasMany(C.self))
                        .orderByPrimaryKey())
                    .including(all: B
                        .hasMany(
                            C.self,
                            through: B.belongsTo(A.self).filter(Column("cola2") != "a1").forKey("nota1"),
                            using: A.hasMany(C.self))
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "b" ORDER BY "colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" <> 'a1') AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }
        }
    }
    
    func testIncludingAllHasManyThroughHasOneUsingHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = A
                    .including(all: A.hasMany(D.self, through: A.hasOne(C.self), using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """])
            }
            
            // Request with filters
            do {
                // This request is an example of what users are unlikely to
                // want, because of the shared key between the two different
                // pivot associations.
                // However, A.hasMany(C.self) does not conflict. This is an
                // indirect proof that it feeds an association key, "as", which
                // is distinc from "a" (A.hasOne(C.self)).
                let request = A
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") == 7),
                            using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") != 7),
                            using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .including(all: A.hasMany(C.self))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 7) AND ("c"."colc1" <> 7) AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" FROM "c" WHERE "colc2" IN (1, 2, 3)
                    """])
            }
            
            // Request with filters
            do {
                // Another example of what users are unlikely to want, still
                // because of the shared key between the two different pivot
                // associations, and despite the distinct hasMany keys.
                let request = A
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") == 7),
                            using: C.hasMany(D.self))
                        .forKey("c7")
                        .orderByPrimaryKey())
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") != 7),
                            using: C.hasMany(D.self))
                        .forKey("notc7")
                        .orderByPrimaryKey())
                    .including(all: A.hasMany(C.self))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 7) AND ("c"."colc1" <> 7) AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 7) AND ("c"."colc1" <> 7) AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" FROM "c" WHERE "colc2" IN (1, 2, 3)
                    """])
            }

            // Request with filters
            do {
                // This request is a "fixed" version of the previous request,
                // where the two different pivot associations do not share the
                // same key.
                let request = A
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") == 7).forKey("c7"),
                            using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasOne(C.self).filter(Column("colc1") != 7).forKey("notc7"),
                            using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .including(all: A.hasMany(C.self))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 7) AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" <> 7) AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT *, "colc2" AS "grdb_colc2" FROM "c" WHERE "colc2" IN (1, 2, 3)
                    """])
            }
        }
    }

    func testIncludingOptionalBelongsToIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = B
                    .including(optional: B
                        .belongsTo(A.self)
                        .including(all: A
                            .hasMany(C.self)
                            .orderByPrimaryKey())
                    )
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "b".*, "a".* \
                    FROM "b" \
                    LEFT JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    ORDER BY "b"."colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }
            
            // Request with filters
            do {
                let request = B
                    .including(optional: B
                        .belongsTo(A.self)
                        .filter(Column("cola2") == "a1")
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") == 9)
                            .orderByPrimaryKey()
                            .forKey("cs1"))
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") != 9)
                            .orderByPrimaryKey()
                            .forKey("cs2"))
                        .forKey("a1"))
                    .including(optional: B
                        .belongsTo(A.self)
                        .filter(Column("cola2") == "a2")
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") == 9)
                            .orderByPrimaryKey()
                            .forKey("cs1"))
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") != 9)
                            .orderByPrimaryKey()
                            .forKey("cs2"))
                        .forKey("a2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "b".*, "a1".*, "a2".* \
                    FROM "b" \
                    LEFT JOIN "a" "a1" ON ("a1"."cola1" = "b"."colb2") AND ("a1"."cola2" = 'a1') \
                    LEFT JOIN "a" "a2" ON ("a2"."cola1" = "b"."colb2") AND ("a2"."cola2" = 'a2') \
                    ORDER BY "b"."colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" = 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" <> 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a2') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" = 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a2') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" <> 9 \
                    ORDER BY "c"."colc1"
                    """])
            }
        }
    }

    func testIncludingOptionalHasOneIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = A
                    .including(optional: A
                        .hasOne(C.self)
                        .including(all: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                    )
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "a".*, "c".* \
                    FROM "a" \
                    LEFT JOIN "c" ON "c"."colc2" = "a"."cola1" \
                    ORDER BY "a"."cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """])
            }

            // Request with filters
            do {
                let request = A
                    .including(optional: A
                        .hasOne(C.self)
                        .filter(Column("colc1") == 9)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .forKey("c1"))
                    .including(optional: A
                        .hasOne(C.self)
                        .filter(Column("colc1") != 9)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .forKey("c2"))
                    .orderByPrimaryKey()

                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)

                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "a".*, "c1".*, "c2".* \
                    FROM "a" \
                    LEFT JOIN "c" "c1" ON ("c1"."colc2" = "a"."cola1") AND ("c1"."colc1" = 9) \
                    LEFT JOIN "c" "c2" ON ("c2"."colc2" = "a"."cola1") AND ("c2"."colc1" <> 9) \
                    ORDER BY "a"."cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" = 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" <> 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" <> 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" = 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" <> 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" <> 11 \
                    ORDER BY "d"."cold1"
                    """])
            }
        }
    }
    
    func testIncludingOptionalBelongsToIncludingOptionalBelongsToIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = D
                    .including(optional: D
                        .belongsTo(C.self)
                        .including(optional: C
                            .belongsTo(A.self)
                            .including(all: A
                                .hasMany(B.self)
                                .orderByPrimaryKey())))
                    .orderByPrimaryKey()
                    .filter(Column("cold2") != 8)
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "d".*, "c".*, "a".* \
                    FROM "d" \
                    LEFT JOIN "c" ON "c"."colc1" = "d"."cold2" \
                    LEFT JOIN "a" ON "a"."cola1" = "c"."colc2" \
                    WHERE "d"."cold2" <> 8 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "b".*, "c"."colc1" AS "grdb_colc1" \
                    FROM "b" \
                    JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    JOIN "c" ON ("c"."colc2" = "a"."cola1") AND ("c"."colc1" IN (7, 9)) \
                    ORDER BY "b"."colb1"
                    """])
            }
        }
    }
    
    func testIncludingOptionalHasOneThroughIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = D
                    .including(optional: D
                        .hasOne(A.self, through: D.belongsTo(C.self), using: C.belongsTo(A.self))
                        .including(all: A
                            .hasMany(B.self)
                            .orderByPrimaryKey()))
                    .orderByPrimaryKey()
                    .filter(Column("cold2") != 8)
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "d".*, "a".* \
                    FROM "d" \
                    LEFT JOIN "c" ON "c"."colc1" = "d"."cold2" \
                    LEFT JOIN "a" ON "a"."cola1" = "c"."colc2" \
                    WHERE "d"."cold2" <> 8 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "b".*, "c"."colc1" AS "grdb_colc1" \
                    FROM "b" \
                    JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    JOIN "c" ON ("c"."colc2" = "a"."cola1") AND ("c"."colc1" IN (7, 9)) \
                    ORDER BY "b"."colb1"
                    """])
            }
        }
    }

    func testJoiningOptionalBelongsToIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = B
                    .joining(optional: B
                        .belongsTo(A.self)
                        .including(all: A
                            .hasMany(C.self)
                            .orderByPrimaryKey())
                    )
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "b".* \
                    FROM "b" \
                    LEFT JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    ORDER BY "b"."colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola1" IN (1, 2)) \
                    ORDER BY "c"."colc1"
                    """])
            }
            
            // Request with filters
            do {
                let request = B
                    .joining(optional: B
                        .belongsTo(A.self)
                        .filter(Column("cola2") == "a1")
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") == 9)
                            .orderByPrimaryKey()
                            .forKey("cs1"))
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") != 9)
                            .orderByPrimaryKey()
                            .forKey("cs2"))
                        .forKey("a1"))
                    .joining(optional: B
                        .belongsTo(A.self)
                        .filter(Column("cola2") == "a2")
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") == 9)
                            .orderByPrimaryKey()
                            .forKey("cs1"))
                        .including(all: A
                            .hasMany(C.self)
                            .filter(Column("colc1") != 9)
                            .orderByPrimaryKey()
                            .forKey("cs2"))
                        .forKey("a2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "b".* \
                    FROM "b" \
                    LEFT JOIN "a" "a1" ON ("a1"."cola1" = "b"."colb2") AND ("a1"."cola2" = 'a1') \
                    LEFT JOIN "a" "a2" ON ("a2"."cola1" = "b"."colb2") AND ("a2"."cola2" = 'a2') \
                    ORDER BY "b"."colb1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" = 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a1') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" <> 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a2') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" = 9 \
                    ORDER BY "c"."colc1"
                    """,
                    """
                    SELECT "c".*, "a"."cola1" AS "grdb_cola1" \
                    FROM "c" \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND ("a"."cola2" = 'a2') AND ("a"."cola1" IN (1, 2)) \
                    WHERE "c"."colc1" <> 9 \
                    ORDER BY "c"."colc1"
                    """])
            }
        }
    }
    
    func testJoiningOptionalHasOneIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = A
                    .joining(optional: A
                        .hasOne(C.self)
                        .including(all: C
                            .hasMany(D.self)
                            .orderByPrimaryKey())
                    )
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "a".* \
                    FROM "a" \
                    LEFT JOIN "c" ON "c"."colc2" = "a"."cola1" \
                    ORDER BY "a"."cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2, 3)) \
                    ORDER BY "d"."cold1"
                    """])
            }
            
            // Request with filters
            do {
                let request = A
                    .joining(optional: A
                        .hasOne(C.self)
                        .filter(Column("colc1") == 9)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .forKey("c1"))
                    .joining(optional: A
                        .hasOne(C.self)
                        .filter(Column("colc1") != 9)
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") == 11)
                            .orderByPrimaryKey()
                            .forKey("ds1"))
                        .including(all: C
                            .hasMany(D.self)
                            .filter(Column("cold1") != 11)
                            .orderByPrimaryKey()
                            .forKey("ds2"))
                        .forKey("c2"))
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "a".* \
                    FROM "a" \
                    LEFT JOIN "c" "c1" ON ("c1"."colc2" = "a"."cola1") AND ("c1"."colc1" = 9) \
                    LEFT JOIN "c" "c2" ON ("c2"."colc2" = "a"."cola1") AND ("c2"."colc1" <> 9) \
                    ORDER BY "a"."cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" = 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" = 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" <> 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" <> 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" = 11 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc1" <> 9) AND ("c"."colc2" IN (1, 2, 3)) \
                    WHERE "d"."cold1" <> 11 \
                    ORDER BY "d"."cold1"
                    """])
            }
        }
    }
    
    func testJoiningOptionalBelongsToJoiningOptionalBelongsToIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = D
                    .joining(optional: D
                        .belongsTo(C.self)
                        .joining(optional: C
                            .belongsTo(A.self)
                            .including(all: A
                                .hasMany(B.self)
                                .orderByPrimaryKey())))
                    .orderByPrimaryKey()
                    .filter(Column("cold2") != 8)
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "d".* \
                    FROM "d" \
                    LEFT JOIN "c" ON "c"."colc1" = "d"."cold2" \
                    LEFT JOIN "a" ON "a"."cola1" = "c"."colc2" \
                    WHERE "d"."cold2" <> 8 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "b".*, "c"."colc1" AS "grdb_colc1" \
                    FROM "b" \
                    JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    JOIN "c" ON ("c"."colc2" = "a"."cola1") AND ("c"."colc1" IN (7, 9)) \
                    ORDER BY "b"."colb1"
                    """])
            }
        }
    }
    
    func testJoiningOptionalHasOneThroughIncludingAllHasMany() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = D
                    .joining(optional: D
                        .hasOne(A.self, through: D.belongsTo(C.self), using: C.belongsTo(A.self))
                        .including(all: A
                            .hasMany(B.self)
                            .orderByPrimaryKey()))
                    .orderByPrimaryKey()
                    .filter(Column("cold2") != 8)
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                // LEFT JOIN in the first query are useless but harmless.
                // And SQLite may well optimize them out.
                // So don't bother removing them.
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT "d".* \
                    FROM "d" \
                    LEFT JOIN "c" ON "c"."colc1" = "d"."cold2" \
                    LEFT JOIN "a" ON "a"."cola1" = "c"."colc2" \
                    WHERE "d"."cold2" <> 8 \
                    ORDER BY "d"."cold1"
                    """,
                    """
                    SELECT "b".*, "c"."colc1" AS "grdb_colc1" \
                    FROM "b" \
                    JOIN "a" ON "a"."cola1" = "b"."colb2" \
                    JOIN "c" ON ("c"."colc2" = "a"."cola1") AND ("c"."colc1" IN (7, 9)) \
                    ORDER BY "b"."colb1"
                    """])
            }
        }
    }
    
    func testAssociationFilteredByOtherAssociation() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            // Plain request
            do {
                let request = A
                    .including(all: A
                        .hasMany(
                            D.self,
                            through: A.hasMany(C.self)
                                .joining(required: C.belongsTo(A.self).filter(sql: "1")),
                            using: C.hasMany(D.self))
                        .orderByPrimaryKey())
                    .filter(sql: "1 + 1")
                    .orderByPrimaryKey()
                
                sqlQueries.removeAll()
                _ = try Row.fetchAll(db, request)
                
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "a" WHERE 1 + 1 ORDER BY "cola1"
                    """,
                    """
                    SELECT "d".*, "c"."colc2" AS "grdb_colc2" \
                    FROM "d" \
                    JOIN "c" ON ("c"."colc1" = "d"."cold2") AND ("c"."colc2" IN (1, 2, 3)) \
                    JOIN "a" ON ("a"."cola1" = "c"."colc2") AND (1) \
                    ORDER BY "d"."cold1"
                    """])
            }
        }
    }
    
    func testAggregate() throws {
        struct Award: TableRecord { }
        struct Player: TableRecord {
            static let awards = hasMany(Award.self)
        }
        struct Team: TableRecord {
            static let players = hasMany(Player.self)
        }
        
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "team") { t in
                t.autoIncrementedPrimaryKey("teamId")
            }
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("playerId")
                t.column("parentId", .integer).references("team")
                t.column("score")
                t.column("category")
            }
            try db.create(table: "award") { t in
                t.autoIncrementedPrimaryKey("awardId")
                t.column("playerId", .integer).references("player")
            }
            try db.execute(sql: "INSERT INTO team DEFAULT VALUES")
            
            do {
                // Group an association
                sqlQueries.removeAll()
                let association = Team.players.select(max(Column("score"))).group(Column("category"))
                let request = Team.including(all: association)
                _ = try Row.fetchAll(db, request)
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "team"
                    """,
                    """
                    SELECT MAX("score"), "parentId" AS "grdb_parentId" \
                    FROM "player" \
                    WHERE "parentId" = 1 \
                    GROUP BY "category"
                    """])
            }
            
            do {
                // Filter an association with an association aggregate
                sqlQueries.removeAll()
                let association = Team.players.having(Player.awards.isEmpty)
                let request = Team.including(all: association)
                _ = try Row.fetchAll(db, request)
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "team"
                    """,
                    """
                    SELECT "player".*, "player"."parentId" AS "grdb_parentId" \
                    FROM "player" \
                    LEFT JOIN "award" ON "award"."playerId" = "player"."playerId" \
                    WHERE "player"."parentId" = 1 \
                    GROUP BY "player"."playerId" \
                    HAVING COUNT(DISTINCT "award"."awardId") = 0
                    """])
            }
            
            do {
                // Annotate an association with an association aggregate
                sqlQueries.removeAll()
                let association = Team.players.annotated(with: Player.awards.count)
                let request = Team.including(all: association)
                _ = try Row.fetchAll(db, request)
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "team"
                    """,
                    """
                    SELECT "player".*, COUNT(DISTINCT "award"."awardId") AS "awardCount", "player"."parentId" AS "grdb_parentId" \
                    FROM "player" \
                    LEFT JOIN "award" ON "award"."playerId" = "player"."playerId" \
                    WHERE "player"."parentId" = 1 \
                    GROUP BY "player"."playerId"
                    """])
            }
        }
    }
    
    func testDistinct() throws {
        struct Player: TableRecord { }
        struct Team: TableRecord {
            static let players = hasMany(Player.self)
        }
        
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "team") { t in
                t.autoIncrementedPrimaryKey("teamId")
            }
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("playerId")
                t.column("parentId", .integer).references("team")
            }
            try db.execute(sql: "INSERT INTO team DEFAULT VALUES")
            
            do {
                sqlQueries.removeAll()
                let association = Team.players.distinct()
                let request = Team.including(all: association)
                _ = try Row.fetchAll(db, request)
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "team"
                    """,
                    """
                    SELECT DISTINCT *, "parentId" AS "grdb_parentId" \
                    FROM "player" \
                    WHERE "parentId" = 1
                    """])
            }
        }
    }
    
    func testCTE() throws {
        struct Player: TableRecord { }
        struct Team: TableRecord {
            static let players = hasMany(Player.self)
        }
        
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "team") { t in
                t.autoIncrementedPrimaryKey("teamId")
            }
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("playerId")
                t.column("parentId", .integer).references("team")
            }
            try db.execute(sql: "INSERT INTO team DEFAULT VALUES")
            
            do {
                sqlQueries.removeAll()
                let cte = CommonTableExpression(named: "cte", sql: "SELECT 42")
                let association = Team.players.with(cte).filter(Column("playerId") == cte.all())
                let request = Team.including(all: association)
                _ = try Row.fetchAll(db, request)
                let selectQueries = sqlQueries.filter(isSelectQuery)
                XCTAssertEqual(selectQueries, [
                    """
                    SELECT * FROM "team"
                    """,
                    """
                    WITH "cte" AS (SELECT 42) \
                    SELECT *, "parentId" AS "grdb_parentId" \
                    FROM "player" \
                    WHERE ("playerId" = (SELECT * FROM "cte")) AND ("parentId" = 1)
                    """])
            }
        }
    }

    // Return SELECT queries, but omit schema queries.
    private func isSelectQuery(_ query: String) -> Bool {
        return query.contains("SELECT") && !query.contains("sqlite_") && !query.contains("pragma_table_xinfo")
    }
}