> I'm sure you know more about these issues than i do, so i apologize if i'm > going through some basic definitions first, just so we're on the same page.
Sure, no problem > "Normal" binding (as far as i understand it) is when you use a "placeholder" > in place of literal values; so you can replace the values later without > having to construct the whole statement again. JDBC parses and caches these > so the next invocation will be faster. Yes, placeholders can appear wherever value literals can appear. JDBC leaves caching open to the implementation. In Oracle, for instance, caching is mostly done in the database. The database distinguishes between hard-parses and soft-parses. - A hard-parse occurs, when the database sees a statement for the first time (different SQL string and hash). It then calculates execution plans and starts gathering execution histograms - A soft-parse occurs, when the database sees a statement another time (same SQL string and hash). It then evaluates, whether previous plans and histograms are still up to date In addition to the above, JDBC allows client code to keep statements open, which allows for avoiding the soft-parse as well. JDBC implementations are free to implement this any way they want, though. > Which brings us to the following use cases: > A. SELECT * FROM myTable where (id = ?) and (sex = ?) > B. INSERT INTO myTable (id, sex, name) values (?, ?, ?) > C. DELETE FROM myTable where (id = ?) > I believe none of these cases requires any casting. 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 :-) > > "Abnormal" (for contrast purposes, not actual terminology) binding is > binding that should never be allowed in the first place. > Example: > A. SELECT * FROM ? > B. INSERT INTO ? > C. DELETE FROM ? I doubt that any database can handle such a syntax, although I wouldn't be too surprised if Postgres could. >> Bind parameters cannot change the structure of an SQL statement. That >> means you cannot use bind parameters for table or column names. I guess that makes sense as an explanation. >> Now, you may argue that you'll never write SQL that is a corner case >> and you wouldn't need bind value casting as you hadn't needed it so >> far. That's probably true if you're in full control of the rendered >> SQL. Yet, stuff like "SELECT ?, ? from DUAL" is not so uncommon. > > SELECT ?, ? from DUAL is an interesting case, and at first i was a bit > perplexed on why we would EVER need that. > Both the parameters are supposed to be literals in this case, right? Yes > (Since jdbc wouldn't allow parameterized column name anyway) You could still name them: SELECT ? a, ? b FROM DUAL 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. > 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 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. 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 :-) >> With >> multi-record inserts (simulated by "INSERT INTO .. SELECT .. UNION ALL >> SELECT ..") or with MERGE statements, it can be quite frequent. Other >> corner cases appear when using functions, operators, etc. > > 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: INSERT INTO my_table1(num) VALUES (1), (2), (3); INSERT INTO my_table2(num, str) VALUES (1, 'a'), (2, 'b'), (3, 'c') Some don't. E.g. Oracle, or Firebird, as a matter of fact. Yet the simulation of the above is easy: INSERT INTO my_table1(num) SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL INSERT INTO my_table2(num, str) SELECT 1, 'a' FROM DUAL UNION ALL SELECT 2, 'a' FROM DUAL UNION ALL SELECT 3, 'a' FROM DUAL Or, with bind values: INSERT INTO my_table2(num, str) SELECT ?, ? FROM DUAL UNION ALL SELECT ?, ? FROM DUAL UNION ALL SELECT ?, ? FROM DUAL Now, the above example is an every-day example for a query where bind values are simply listed in SELECT clauses and must be cast with Firebird, even if Firebird can handle this case here: INSERT INTO my_table2(num, str) SELECT ?, ? FROM DUAL Another, even simpler example is this: SELECT num * ? FROM my_table SLEECT str || ? FROM my_table > I'm gonna assume that none of these would require casting either... so > yeah, i don't get it :D. There's not much to get. Some databases are more powerful than others. With my jOOQ integration tests, I have seen many corner cases, where saying "Database [XY] is just very strongly typed" is not accurate. Type inferring and coercion is a feature. Powerful databases handle this really well, delaying the decision to raise an error until query execution. Others have a rigid and often wrong (because incomplete) rule-set when they reject queries already at parse-time or maybe at bind-time. jOOQ doesn't take many chances on this. If a new database fails 3-4 jOOQ integration tests merely because it cannot infer / coerce the type of a bind value, then jOOQ will cast ALL bind values. I will investigate, though, if bind value casting can be generally avoided for simple comparison predicates, which make for 80% of the bind value use-cases. This is filed as: #1752 https://github.com/jOOQ/jOOQ/issues/1752 Cheers Lukas
