SQLiteDriver silently ignores unmatched database schemas since 3.1.0

[lastland]

Since Slick 3.1.0 the generated SQL statements for a selection query on a single table becomes:

select "id", "first", "last" from "users";

comparing with Slick 3.0.0:

select x2."id", x2."first", x2."last" from "users" x2

While this works well with other db drivers, this will cause SQLiteDriver to silently ignore unmatched database schema, and return unexpected results. To understand the problem, see the following examples in SQLite:

sqlite> .schema "users"
CREATE TABLE "users" ("id" integer not null primary key, "first" varchar not null);
sqlite> select "haha" from "users";
sqlite> insert into "users" values (1, "a");
sqlite> select "haha" from "users";
haha
sqlite> insert into "users" values (2, "b");
sqlite> insert into "users" values (3, "c");
sqlite> select "haha" from "users";
haha
haha
haha

PS: invoking queries on case classes with different column names with their corresponding tables in other drivers, including the older versions of SQLiteDriver, will cause a SQLException.

[lastland]

My original description was inaccurate so I have updated it. Please read the latest version on GitHub in case you are reading this issue from mails, etc.

[lastland]

Any updates on this issue? It seems the problem remains in Slick 3.2.0-M1.

[cvogt]

@lastland I am wondering what do do about this. Without table prefix, SQL's return type of a column using a quoted string seems to depend on the fact if this string matches the name of a column (in which case it is the value of the column) or not (in which case it is the string).

That's pretty weird behavior on it's own. How does this surface as a problem in a real world use case with slick?

[lastland]

@cvogt Say you have the following object:

class Users(_tableTag: Tag) extends Table[UsersRow](_tableTag, "users") {
  val id: Rep[Int] = column[Int]("id", O.PrimaryKey)
  val firstname: Rep[String] = column[String]("firstname")
  ...
}

while what you have in your SQLite database is:

CREATE TABLE "users" ("id" integer not null primary key, "first" varchar not null);

Invoking a query to fetch the firstname column on such object would not cause an exception. Instead, the query returns normally, except the returned results would just be the string "firstname" instead of the actual values stored in users.first as users expect.

I guess guaranteeing that a type of Scala objects matches the "type" in the database (i.e. the database schema) is also part of type-safety? In that way, silently ignoring such a type mismatch compromises Slick's type safety.

Of course, using Slick code generator and migration tools like Scala-Forklift could avoid the inconsistency between Scala code and database schemas in the first place, but Slick should not rely on them to be type-safe.

PS: If you are using any other database profile (e.g. H2Profile, MySQLProfile, etc.) than SQLiteProfile in Slick, or using other Slick versions before 3.1.0, such an operation would cause a SQLException.

[cvogt]

@lastland I see. I remember the change, but couldn't find the PR where this happened right now. I agree that an exception would be preferable.

FYI, the SQLite docs say

• If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

Programmers are cautioned not to use the two exceptions described in the previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements will run correctly. Future versions of SQLite might raise errors instead of accepting the malformed statements covered by the exceptions above.

https://www.sqlite.org/lang_keywords.html

If there is an easy way to put a hack into Slick that prevents this, I'd be in favor. As it seems always fully qualifying column names would work, but also make the generate SQL code less readable.

[cvogt]

Any ideas for other hacks?

[trevorsibanda]

@cvogt Isn't using fully qualified column names the better option ? The generated sql might be less readable but I think its better than a hack