Hello Lukas, > Probably not. It looks like simple comparison predicates can handle > type coercion even in Firebird. This doesn't work, though: > SELECT * FROM my_table WHERE id = (SELECT ? FROM RDB$DATABASE) > > I know you find this query peculiar again :-) > Very much so. This should be simplified into the "normal" syntax: ... SELECT * FROM my_table WHERE id = ? ...
>> Bind parameters cannot change the structure of an SQL statement. That > >> means you cannot use bind parameters for table or column names. > You could still name them: SELECT ? a, ? b FROM DUAL > Yes, but this is the same case as above, Shouldn't we all just rewrite the query so you don't need the DUAL reference and use the var binding directly? > Besides, unknown column names can be generated by the database. Most > databases do this. Firebird seems to fail at this from time to time, > e.g. in derived tables. > Ah, probably because Firebird requires column names in a join (including derived tables) to be unique. > So now we have the query -> we supply the parameters -> execute the query > -> > > and get back exactly the values we put in? > > Why don't just use the value in the first place... ? > As I said, there are dozens of use-cases for such scalar subqueries. > The most simplistic ones just involve projecting the bind values > themselves. Others involve correlated subqueries or derived tables > calculating things. An example: > > SELECT t.*, u.* > FROM my_table t > CROSS JOIN ( > SELECT t.value1 * ? + t.value2 * ? calculation FROM DUAL > ) u > I'm sorry but i still don't see the value of such queries, there's a lot of fluff for no apparent purpose, the following query should return the same thing: ... SELECT t.*, (t.value1 * ? + t.value2 * ?) calculated FROM my_table t ... > Now, not all databases support the above syntax (letting one > correlated subquery reference values from another). As a matter of > fact, Oracle doesn't support it. But in more advanced SQL queries > there are a lot of reasons why one might want to put bind values in a > subquery, regardless if they're just enumerated, or combined in any > sort of column expression. > Since you have more exposure to these sort of queries, i'll take your word for it :) Again. If it's possible in SQL, it should be possible with jOOQ. Being > only a 1% everyday use-case is no excuse for me :-) > I understand that this is probably one of the major design principle of jOOQ, and i respect that. Also I'm not saying that jOOQ should not support them at all, but maybe we should consider the performance cost and complexity involved in doing so. Here's a random pitch: Instead of having jOOQ making guesses, why not provide options? Something like ... bind() -> normal bind, no casting vs bindStrict() -> forced cast bind ... And then let the users choose. > I apologize again, this must be my unfamiliarity with jOOQ that is > confusing > > me. Can you show me the actual statements and how it's used? > Sure. Some databases support this: > --snip-- Thanks for taking the time to write down the examples. Regards, ts.
