Tom Lane writes:
>Andrew Dunstan <andrew@dunslane.net> writes:
>> On 6/7/21 6:10 PM, Tom Lane wrote:
>>> Note that it's not like SQL hasn't heard of projections before.
>>> You can always do "SELECT a, b, d FROM subquery-yielding-a-b-c-d".
>>> So the proposed syntax would save a small amount of typing, but
>>> it's not adding any real new functionality.
>> True, but the problem happens when you have 250 fields and you want to
>> skip 4 of them. Getting that right can be a pain.
>I'm slightly skeptical of that argument, because if you have that
>sort of query, you're most likely generating the query programmatically
>anyway. Yeah, it'd be a pain to maintain such code by hand, but
>I don't see it being much of a problem if the code is built by
>a machine.
Here is the pattern I’m concerned with: the application has an entity layer that for each relationship knows all the fields and can read them and convert them into Java objects.
Developers are typically writing queries that just `SELECT *` from a table or view to load the entity. There could be many different queries with different filter criteria, for example, that are all fed through the same Java code. If the query omits some fields, the Java code can handle that by examining the meta-data and not reading the missing fields.
When new fields are added to a table or view, it is generally only necessary to update the common Java component rather than modifying each individual query. As I said in my original post, that leaves us with the unhappy alternatives of returning the (potentially large) temporary arrays used for sorting or having to explicitly name each column just to omit the unwanted temporary array.
Note that the Oracle START WITH/CONNECT BY syntax avoids this issue entirely because it is not necessary to return the temporary structure used only for sorting and is not needed by the client.
There is a preference for static queries over dynamically generated ones, as those can be statically analyzed for correctness and security issue, so dynamically generating the query is not always an available option.
I expect that this sort of pattern drives database developers crazy (“surely you aren’t using *all* those fields, why don’t you just explicitly list the ones you want?”) but there are other constraints (static validation, provably avoiding SQL Injection attacks, ease of maintenance) that may take precedence. There is value in not needing to make a knight’s tour through the code base every time someone adds a field to a table to update the column lists in all the queries that refer to that table.
Regards,
Mark Z.