The simplest solution would be to skip namespaces by passing this argument:

{:as next.jdbc.result-set/as-unqualified-maps}

Thank you

I didn't know about the double fetch thing - I assumed it came in the ResultSet internally but didn't dig into this.

> Pay attention that the namespaces are fetched using an extra query That is false.

Either the ResultSetMetaData provides that information or it doesn't. e.g., Oracle simply doesn't implement that in its drivers. MS SQL Server only provides it if certain settings are present.

Computed columns never provide that information, which is why that column in the OP's query doesn't have a qualifier.

For performance reasons, you are better using plan in all cases since that avoids constructing Clojure hash maps from ResultSet objects.

Thanks @U04V70XH6 - I appreciate your comments and advice. I've kept it as simple as possible and delegated giving the namespaced keys into my application rather than next.jdbc which is working well. Thank you.

As you can probably tell, I get rather annoyed when people just throw out the recommendation to use the unqualified builders... That's not what people should turn to as a solution.

If you use plan for performance, the column names are unqualified, because no builder is used -- which is where the performance boost comes from.

Yes I understand that. I think in my case using plan makes sense given what I'm doing and it makes it explicit. Thank you again.

> That is false I haven't checked this case with other databases, but for Postgres, it's true. Fetching column names from the RSMetadata triggers this query:

Which returns the following result:

One can ensure when running Postgres with -E flag (log everything).

Is that specifically for the .getTableName() call or for ResultSetMetaData overall? Does it run it every time the main query is run or cache it as part of the plan or other data about a query?

I believe it gets executed once when getTableName is called, and then cached

But in fact, every time we select something with f.q. keys, it doubles the number of queries

Given the focus on performance from the metosin folks when they were helping with the initial implementation of next.jdbc, I'm surprised they didn't call that out as a problem (but we were all probably more focused on plan which is the recommended approach anyway).

Maybe because this query is done under the hood by the driver. To spot it, one should enable logging all the expressions with the -E flag. I always set it to true and observe in the Docker console all the expressions executed. Often, there are some really weird ones produced by the drivers.

Looking at the MySQL driver, it seems that all that information is pulled directly as part of what is needed to support even calling .getValue() on a row in a ResultSet -- it uses the field definition which has the table name and column type information all baked in. So I'm guessing different drivers handle this very differently...

(so if MySQL ends up running multiple queries, it isn't .getTableName() that triggers it as far as I can tell from the source code)

It looks like MySQL passes the names of the columns that participate in the result. But Postgres doens't. They only pass OIDs: oid of the type, of the table, and the column. To resolve the name of the tables, one should query the pg_catalog table

It's out of the scope of the origin question as it's about SQLite, but just in case

Interesting... I'll create an issue to add notes to the Tips & Tricks docs and maybe some of the mentions of qualified columns names for PG's behavior. Definitely not what I would have expected, given how PG prides itself on being so advanced 😉 where other DBs / drivers can handle this without the extra query 🙂 TIL.

yeah... another problem with Postgres is enum types. All the standard types have hardcoded OIDs so they're defined in the code. But when someone defines their own enum, it gets a random oid, and on the driver's level, it's unclear what is it.

you only have type_oid = 8883, no name.

Ouch. So user-defined enums have a performance overhead there?

https://github.com/seancorfield/next-jdbc/issues/275

PG weirdness is the bane of my life, both with next.jdbc and with HoneySQL! 🙂

No I just meant it's unclear how to parse these unknown types. Having the name of the type would be great, and the client could provide a mapping like <type_name = parsing function>.

but we have integer oids which might differ depending on the server

Is it Postgres? If yes, you need to extend some protocols and types for automatic conversion. See this link: https://github.com/seancorfield/next-jdbc/blob/44b3cc206fe4635702331814da0860e63261e9ec/doc/tips-and-tricks.md?plain=1#L364

It's Porstgres, right. Thanks for the tip, I'll take a look.

Oracle's JDBC drivers do not provide table name information (as noted in the Oracle-specific Tips & Tricks page). Nothing next.jdbc can do about that, unfortunately.

Yes

and, just reading up on datafy/nav basically for the first time, but it seems to me your implementations of nav would rely on that, wouldn’t they?

It depends on your FK naming conventions, not the qualifiers.

If table foo has bar_id, then datafy/`nav` assumes that will join to table bar column id.

If you need to map table/column pairs in order to define a custom :schema mapping, then no it won't work without qualifiers. But if you have been consistent in your column naming conventions, it should all work.

okay, nice. Thanks @U04V70XH6

(too bad they are not my conventions 😞 )

What are your conventions? The latest next.jdbc allows you to specify more patterns.