https://kotlinlang.org logo
Join Slack
Powered by
I'm having trouble accessing optional columns from...
# exposed
a
I'm having trouble accessing optional columns from multiple left joins. Calling 
ResultRow.hasValue
for the joined columns always returns 
true
, which makes it difficult for me to tell which optional columns are actually present. Below is a contrived example, followed by a passing and a failing test. It should illustrate my problem better than my explanation. Am I doing this completely wrong?
Copy code
object BaseUser: IntIdTable("base_users")
object SuperUser: IntIdTable("super_users") {
    val baseId = reference("base_id", BaseUser)
    val name = varchar("name", 128)
}
object NormalUser: IntIdTable("normal_users") {
    val baseId = reference("base_id", BaseUser)
    val name = varchar("name", 128)
}

fun Transaction.getUserName(baseId: EntityID<Int>): String? {
    val row = BaseUser
        .leftJoin(SuperUser, {SuperUser.baseId}, {BaseUser.id})
        .leftJoin(NormalUser, {NormalUser.baseId}, {BaseUser.id})
        .slice(NormalUser.name, SuperUser.name)
        .select { BaseUser.id eq baseId }
        .firstOrNull()

    return when {
        row == null -> null
        row.hasValue(NormalUser.name) -> row[NormalUser.name]  // Still enters here even for SuperUser
        row.hasValue(SuperUser.name) -> row[SuperUser.name]
        else -> null
    }
}

@Test
fun `navigate subtype relationship`() {
    transaction {
        SchemaUtils.create(BaseUser, SuperUser, NormalUser)

        val baseUserId1 = BaseUser.insertAndGetId {}
        val baseUserId2 = BaseUser.insertAndGetId {}

        NormalUser.insert {
            it[baseId] = baseUserId1
            it[name] = "normaldude"
        }
        SuperUser.insert {
            it[baseId] = baseUserId2
            it[name] = "superdude"
        }

        getUserName(baseUserId1) shouldBe "normaldude" // PASS
        getUserName(baseUserId2) shouldBe "superdude" // FAIL; was actually null
    }
}
e
can you try adding aliases to the columns so they won't resolve to the same 
name
column? Also, if you could turn on logging to see the actual SQL statement would be helpful.
a
Ok, so the overlapping names is just a red-herring. I renamed the 
name
columns to something unique per table, and it didn't change the outcome as expected. I've enabled logging and got this output; looks fine to me.
Copy code
[Test worker] DEBUG Exposed - SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
[Test worker] DEBUG Exposed - CREATE TABLE IF NOT EXISTS BASE_USERS (ID INT AUTO_INCREMENT PRIMARY KEY)
[Test worker] DEBUG Exposed - CREATE TABLE IF NOT EXISTS SUPER_USERS (ID INT AUTO_INCREMENT PRIMARY KEY, BASE_ID INT NOT NULL, SUPER_NAME VARCHAR(128) NOT NULL, CONSTRAINT FK_SUPER_USERS_BASE_ID__ID FOREIGN KEY (BASE_ID) REFERENCES BASE_USERS(ID) ON DELETE RESTRICT ON UPDATE RESTRICT)
[Test worker] DEBUG Exposed - CREATE TABLE IF NOT EXISTS NORMAL_USERS (ID INT AUTO_INCREMENT PRIMARY KEY, BASE_ID INT NOT NULL, NORMAL_NAME VARCHAR(128) NOT NULL, CONSTRAINT FK_NORMAL_USERS_BASE_ID__ID FOREIGN KEY (BASE_ID) REFERENCES BASE_USERS(ID) ON DELETE RESTRICT ON UPDATE RESTRICT)
[Test worker] DEBUG Exposed - INSERT INTO BASE_USERS  DEFAULT VALUES
[Test worker] DEBUG Exposed - INSERT INTO BASE_USERS  DEFAULT VALUES
[Test worker] DEBUG Exposed - INSERT INTO NORMAL_USERS (BASE_ID, NORMAL_NAME) VALUES (1, 'normaldude')
[Test worker] DEBUG Exposed - INSERT INTO SUPER_USERS (BASE_ID, SUPER_NAME) VALUES (2, 'superdude')
[Test worker] DEBUG Exposed - SELECT NORMAL_USERS.NORMAL_NAME, SUPER_USERS.SUPER_NAME FROM BASE_USERS LEFT JOIN SUPER_USERS ON SUPER_USERS.BASE_ID = BASE_USERS.ID LEFT JOIN NORMAL_USERS ON NORMAL_USERS.BASE_ID = BASE_USERS.ID WHERE BASE_USERS.ID = 1
[Test worker] DEBUG Exposed - SELECT NORMAL_USERS.NORMAL_NAME, SUPER_USERS.SUPER_NAME FROM BASE_USERS LEFT JOIN SUPER_USERS ON SUPER_USERS.BASE_ID = BASE_USERS.ID LEFT JOIN NORMAL_USERS ON NORMAL_USERS.BASE_ID = BASE_USERS.ID WHERE BASE_USERS.ID = 2
e
have you renamed the exposed columns as well?
fwiw the generated sql looks ok to me too
a
I've renamed both the exposed column and the underlying sql column; I'm certain the names aren't the issue, because the code that this contrived example is derived from uses completely different column names anyway.
e
I see
dumb q: what if you switch the order of hasValue in your when?
a
In that case, getting the name of 
baseUserId1
will fail and getting the name of 
baseUserId2
will pass; the opposite result given in my example
It seems that the 
when
expression will always choose the first case with 
hasValue
e
if you debug through, what is in 
row
when code enters to the 
when
clause?
a
So I guess 
hasValue
is returning 
true
because 
data[0]
is 
null
rather than 
NotInitializedValue
. I guess the question then is whether this is a bug I can submit a patch for, or if there's a more correct way of going about this.
Checking if 
data[it]
is 
null
would be incorrect, because 
null
might be the correct value. We would have to tell whether 
data
has an element for that index, and I'm not sure if that's possible for an 
array
.
e
well... the generated SQL could return with the result of
Copy code
NORMAL_USERS.NORMAL_NAME | SUPER_USERS.SUPER_NAME
-------------------------|-----------------------
normaldude               | null
null                     | superdude
and in that case technically 
row.hasValue()
is true, so I think you might have to adjust your return statement like this:
Copy code
return row?.let { r -> 
   r[NormalUser.name] ?: r[SuperUser.name]
}
so if row is null, it will return null, if normalusername is not null then returns with that else it tries to return with superusername which also can be null.
a
So that does technically work, but 
r[NormalUser.name]
is supposed to be non-null, so the editor thinks the 
?:
is unreachable. If I then go and define and use a nullable alias, then it works too.
Copy code
val nullableNormalName = NormalUser.normalName.nullable().alias("nullableNormalName")
But defining that alias seems to have a weird effects on the rest of my tables, that I can't quite characterize yet.
e
Copy code
return row?.let { r -> 
   val normalUserName: String? = r[NormalUser.name]
   val superUserName: String? = r[SuperUser.name]

   if (normalUserName != null) {
       normalUserName
   } else {
       superUserName
   }
}
would this make your editor complaining as well?
a
Yes, the editor is very smart. Even though we make the 
val normalUsername
nullable, since it knows 
row[NormalTable.normalName]
isn't supposed to be nullable, it still complains about the if-condition.
e
but this means that if you declare the variables without 
?
, you'd get a NPE
a
You would think so, but it works. Even though the return type of 
r[NormalUser.normalName]
is non-null, it actually ends up being a 
null
. So the editor complains, because it thinks it's impossible, but it's wrong. Kind of like how a json mapper that doesn't support Kotlin (i.e. Gson) will happily ignore null-safety when deserializing.
e
weird
r
I didn't read your entire conversation, just want to share my thoughts. I had the same issue a few days ago (or still have). See https://kotlinlang.slack.com/archives/C0CG7E0A1/p1666981868374349 As Tapac said in the following issue ticket, hasValue returning true is the correct behavior since the column has a value, however the value being null because of the left join. IMO Exposed can't differentiate because it is coming from the database. https://github.com/JetBrains/Exposed/issues/196#issuecomment-345311341 If you use a left join and thus your column can be null you should use getOrNull instead of the operator get function. So far so good, but there seems to be something wrong with the type system. Have a look at my attached screenshot. It declares the variable as String and in the debuger you can see that its null. So same as you in one of your snippets.
753 Views
Open in Slack
PreviousNext
Powered by