Sqlite: Invalid SQL for reused alias?

[smitpatel]

Test GroupBy_aggregate_from_multiple_query_in_same_projection_3

Generated SQL

SELECT "o"."CustomerID" AS "Key", COALESCE((
SELECT COUNT(*) + (
SELECT COUNT(*)
FROM "Orders" AS "o0"
WHERE ("o"."CustomerID" = "o0"."CustomerID") OR ("o"."CustomerID" IS NULL AND "o0"."CustomerID" IS NULL))
FROM "Employees" AS "e"
WHERE "e"."City" = 'Seattle'
GROUP BY "e"."City"
ORDER BY COUNT(*) + (
SELECT COUNT(*)
FROM "Orders" AS "o0"
WHERE ("o"."CustomerID" = "o0"."CustomerID") OR ("o"."CustomerID" IS NULL AND "o0"."CustomerID" IS NULL))
LIMIT 1), 0) AS "A"
FROM "Orders" AS "o"
GROUP BY "o"."CustomerID"

Throws

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such column: o.CustomerID'. in SQlite

Not sure the reason. Same SQL works fine on SqlServer.

[smitpatel]

@roji - Does this test work correctly on postgre?

[roji]

It's currently skipped in EF (#27130). I copy-pasted the LINQ query from the base:

await AssertQuery(
    async,
    ss => ss.Set<Order>().GroupBy(e => e.CustomerID)
        .Select(
            g => new
            {
                g.Key,
                A = ss.Set<Employee>().Where(e => e.City == "Seattle").GroupBy(e => e.City)
                    .Select(g2 => g2.Count() + g.Count())
                    .OrderBy(e => e)
                    .FirstOrDefault()
            }),
    elementSorter: e => e.Key);

This produces the following SQL which PG executes successfully:

SELECT o."CustomerID" AS "Key", COALESCE((
            SELECT count(*)::int + count(*)::int
            FROM "Employees" AS e
            WHERE e."City" = 'Seattle'
            GROUP BY e."City"
            ORDER BY count(*)::int + count(*)::int NULLS FIRST
            LIMIT 1), 0) AS "A"
        FROM "Orders" AS o
        GROUP BY o."CustomerID"

The SQL works, but the test fails with:

Assert.Equal() Failure
Expected: { Key = ALFKI, A = 8 }
Actual:   { Key = ALFKI, A = 4 }

... maybe some ordering/non-determinism issue... that's usually what it means. Let me know if you need more info on this!

[smitpatel]

The count(*) are unreferenced so the error is not ordering issue but the #27130
Though mainly wanted to know if it is parse-able SQL. So this does look like Sqlite bug. I will investigate more and file a bug.

[smitpatel]

Another question for @roji
Does this test work in 6.0? Looking at the SQL you ran, it is different from erroneous SQL. We did change SQL generated between 6.0 & 7.0 so what you ran was right just didn't verify if the SQL posted in this issue is valid for postgreSQL.

[smitpatel]

Content for issue filing on Sqlite forum

Schema

CREATE TABLE "Orders" (
    "OrderID" INTEGER NOT NULL CONSTRAINT "PK_Orders" PRIMARY KEY AUTOINCREMENT,
    "CustomerID" TEXT NULL,
    "EmployeeID" INTEGER NULL,
    "OrderDate" TEXT NULL,
    CONSTRAINT "FK_Orders_Employees" FOREIGN KEY ("EmployeeID") REFERENCES "Employees" ("EmployeeID")
)

CREATE TABLE "Employees" (
	"EmployeeID" INTEGER NOT NULL CONSTRAINT "PK_Employees" PRIMARY KEY AUTOINCREMENT,
	"LastName" TEXT NOT NULL,
	"FirstName" TEXT NOT NULL,
	"Title" TEXT NULL,
	"City" TEXT NULL
)

Query

SELECT "o"."CustomerID" AS "Key", COALESCE((
	SELECT COUNT(*) + (
		SELECT COUNT(*)
		FROM "Orders" AS "o0"
		WHERE ("o"."CustomerID" = "o0"."CustomerID") OR ("o"."CustomerID" IS NULL AND "o0"."CustomerID" IS NULL))
	FROM "Employees" AS "e"
	WHERE "e"."City" = 'Seattle'
	GROUP BY "e"."City"
	ORDER BY COUNT(*) + (
		SELECT COUNT(*)
		FROM "Orders" AS "o0"
		WHERE ("o"."CustomerID" = "o0"."CustomerID") OR ("o"."CustomerID" IS NULL AND "o0"."CustomerID" IS NULL))
	LIMIT 1), 0) AS "A"
FROM "Orders" AS "o"
GROUP BY "o"."CustomerID"

Throws
SQLite Error 1: 'no such column: o.CustomerID'.

[roji]

@smitpatel yeah, in EF 6.0 the test passes (GroupBy_aggregate_from_multiple_query_in_same_projection_3), in 7.0 the SQL works but fails the AssertQuery check. Below are the SQLs I'm seeing in both cases, let me know if you want more info.

EF 7.0 (SQL works, AssertQuery fails):

SELECT o."CustomerID" AS "Key", COALESCE((
    SELECT count(*)::int + count(*)::int
    FROM "Employees" AS e
    WHERE e."City" = 'Seattle'
    GROUP BY e."City"
    ORDER BY count(*)::int + count(*)::int NULLS FIRST
    LIMIT 1), 0) AS "A"
FROM "Orders" AS o
GROUP BY o."CustomerID"

EF 6.0 (test passes):

SELECT o."CustomerID" AS "Key", COALESCE((
    SELECT COUNT(*)::INT + (
        SELECT COUNT(*)::INT
        FROM "Orders" AS o0
        WHERE (o."CustomerID" = o0."CustomerID") OR (((o."CustomerID" IS NULL)) AND ((o0."CustomerID" IS NULL))))
    FROM "Employees" AS e
    WHERE e."City" = 'Seattle'
    GROUP BY e."City"
    ORDER BY COUNT(*)::INT + (
        SELECT COUNT(*)::INT
        FROM "Orders" AS o0
        WHERE (o."CustomerID" = o0."CustomerID") OR (((o."CustomerID" IS NULL)) AND ((o0."CustomerID" IS NULL)))) NULLS FIRST
    LIMIT 1), 0) AS "A"
FROM "Orders" AS o
GROUP BY o."CustomerID"

[smitpatel]

https://sqlite.org/forum/forumpost/9940a824473a1bac