Bug #96163 | Use cases for variable assignment in DML statements- do good alternatives exist? | ||
---|---|---|---|
Submitted: | 10 Jul 2019 18:30 | Modified: | 15 Jul 2019 12:31 |
Reporter: | Dean Trower | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S4 (Feature request) |
Version: | 8+ | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[10 Jul 2019 18:30]
Dean Trower
[11 Jul 2019 15:27]
MySQL Verification Team
HI Mr. Trower, Thank you for your bug report. I sincerely do not see what is it that you are asking us to do. Improve documentation or add a feature. If it is documentation, we would like to know exactly which chapter should be updated and with what info. You do not have to provide exact wordings , of course. If it is a feature, you would have to define it in much, much more detail. This is simply, first of all, because assignments in DMLs will NOT come back. This is due to both stochastic nature of the SELECT list evaluation and due to the simple fact that this is not prescribed by latest SQL standards. Hence, define your feature as precisely as possible. Then, it might be accepted.
[11 Jul 2019 18:59]
Dean Trower
Hi. Sorry, that *was* long and rather vague. The actual "feature request" was, more-or-less, this line: >>>> So, what I am actually "feature request"ing here is that MySQL not deprecate or remove useful functionality that can't be efficiently or concisely duplicated in other ways! <<<< Given that you're committed to removing all assignments in DMLs, that therefore transmutes into: "Please MySQL, create or implement some language features that allow the same kind of things to be done that could up till now be done with variables, allowing for a similar level of conciseness (in the SQL syntax) and (computational) efficiency". I don't have a *specific* request, both because this is by nature somewhat open-ended, and because the specification of new syntax should probably be left to the MySQL developers (or even to the SQL standards people). So that's why I listed several use-cases of variable assignment in DML, and then... waffled a bit I guess you could say, on how they could be done differently, using window functions or other things. However, it's clear that a good start would be to allow the use of stored functions as window and aggregate functions, and remove the restriction on using window functions directly in an UPDATE. Perhaps also consider a syntax that allows in-line lambda expressions (for window functions) or defines them just for the one statement (like WITH/CTEs but for stored window & non-window functions?). And allow ORDER BY in multi-table UPDATEs (the assumption being that the window functions would see the previously-updated rows). And remove the restrictions on same-table subqueries. Also nice would be a way to combine the functionality of SELECT (return data to the client) with SELECT...INTO (set some variables) so that a single statement could somehow do both (because if you create a very complex SELECT query, you don't want to have to do it twice it for the two purposes). And perhaps some syntax to allow the explicit definition of a list of sub-expressions, (each which might depend on the previously-defined ones), so that you can build up complex formulas hierarchically inside an SQL statement, rather than writing each expression (column to return, ORDER/GROUP BY expression, etc) out in full. (Yes, I'm aware that technically you *can't* reliably do that with variables... but it's still conceptually related, and something MySQL needs). ... This is a long laundry list of things obviously, but the common theme is "stuff needed to replicate what you could previously do with variable assignments inside DML statements". I'm by no means claiming that this list would cover it; nor were the 4 or 5 use-cases involving variables that I mentioned in any way an exhaustive list. (They were basically just what immediately occurred to me as I was writing the feature request). What I'm getting at is that I think further thought and analysis is definitely called for on this issue, including the opportunity for other MySQL users affected by this (deprecation) to chip in. Maybe that's already happened somewhere, and/or maybe I should have posted on the forums rather than here... but this *IS* a genuine feature request, even if the features being requested aren't well defined! (Only some of what I'd like to be able to *DO* with them). So that's why this bug tracker was my first thought. If another MySQL user comes here with the same concerns I have, and reads this page, maybe they could post their own use-cases for variable assignment in DML, if different than the ones I already mentioned, so that alternative ways of accomplishing the same tasks might be discussed, and examined to see what (if any) SPECIFIC features MySQL might need to implement to replace the use of variable assignments in those particular use-cases? That should better explain my intention here better, I hope...? [Also, I regret the Fibonacci example: A better one would be more like the CRC32 calculation, where the existing data within the table is also used as part of the running calculation]. [Regarding my request for documentation improvements, that was more of an aside. Maybe it doesn't even matter so much since the subject of the docs is being deprecated anyway, and/or maybe I should post a separate bug report for documentation deficiencies. But essentially, as long as variable assignment in DML is still legal, I think that the docs page about user variables should say something about the issue described in the Stack Overflow question that I linked to previously.]
[12 Jul 2019 12:22]
MySQL Verification Team
Hi, In order to verify the feature request, we need it well defined. You do not have to write the syntax. You could refer to some sub-paragraph of the SQL standard instead.
[12 Jul 2019 18:36]
Dean Trower
I'm afraid my knowledge of the SQL standard does not extend much past the fact that it exists. I've never read it, and as it isn't available for free, am not going to satisfy idle curiosity by doing so now. I am also confused as to how it would help. Are you saying that all use-cases I've described in this feature request have already been considered in-depth by the SQL standards committee, and that they ALREADY have syntax in the SQL standard that covers alternative methods for performing them? So that all I need to do is ask you to implement the relevant bits of the standard? ...Without reading it I can't be sure, of course, but that seems very dubious, particularly since these use cases are all based on variable assignment in DML statements, which you already told me the SQL standard doesn't support! (Unless what you said perhaps meant that it *was* supported, but isn't anymore?) As I said, I don't know the SQL standard, but I wouldn't expect any new language feature that I made up and requested to just so happen to already be in it; any such things would presumably need to be proprietary extensions to the standard (which I had assumed would be fine, given that each DBMS has it's own "flavour" of SQL anyway). I consider myself a competent database programmer (meaning I'm very comfortable using databases like MySQL as back-ends for other applications). But I am not a database *engine* programmer - I have only a vague notion how MySQL works internally - and I'm certainly not qualified to be an SQL language-feature architect. So asking me to provide you with specific new language features to implement is asking for something that is well outside my expertise. I could certainly take a stab at it, but I'd expect I'd be knocked back with either "We can't do that because MySQL's internal architecture doesn't support it", or "We won't do that because it subtly (or not-so-subtly) violates the design of the SQL language". In deprecating variable-assignments-in-DML, you are deprecating a feature that is in use, and is (I think, probably) in quite *widespread* use. Certainly I use it quite often, and there are lots of blogs and internet tutorials that demonstrate how to use it in MySQL to achieve certain specific tasks. I am pointing this out, and giving a few examples of this use, and asking for YOU to come up with alternative means (of some kind) to achieve the same goals. I don't think it's unreasonable to expect that it should be YOUR DEVELOPMENT TEAM that decides just what kind of specific features within MySQL need to be modified or added in order to do that, since they are the ones with the expert knowledge required for that. The above notwithstanding, I've already given you a "laundry list" of things that would probably go some fair way towards replacing the deprecated functionality. I can expand on that if you'd like. I can also go ahead and make up some new language features (complete with detailed syntax if you want it), that I think would also be helpful in that regard... *if* you think there's any genuine chance at all that you might really go ahead and implement them. ------------------- Two other things: ------------------- * Should I open a separate bug report for the documentation on page "9.4 User-Defined Variables" failing to mention the row-evaluation-order problem (from the StackOverflow question https://stackoverflow.com/questions/56939034/ )? and * On the same page, where it says "Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.", would you consider adding something like: "Techniques that made use of this functionality will then cease working. New language features such as window functions and recursive common table expressions (see WITH) are already able to replace some use-cases. Other new language features are under consideration that are expected to make up the gap. __Click here___ to view or contribute to a forum discussion with our development team on the proposed changes and how they are expected to replace deprecated code-patterns in legacy code." ...and then providing such a forum, with developer team participation, as described? (Or you could reword it slightly and link directly to this bug report, but frankly in that case I think it'd need to be rewritten and cleaned up a lot first, if the bug tracker provides a way to do so).
[15 Jul 2019 12:31]
MySQL Verification Team
Hi Mr. Trower, Sorry, but we do not need a specific feature request, to use your own words. You do not have to provide the syntax , nor do you have to quote the standard, but you can not be vague, like you were in your descriptions. You can not just ask "to be provided by a replacement for deprecated feature(s)". That is not specific at all. Also, we do not see what is it there to document. To write down that those that are used to deprecated features will not be able to use them is so self-evident that writing something like that is unnecessary. Regarding forums, we have public forums like forums.mysql.com. These are forums where MySQL users can exchange views, experiences and each others. MySQL developers do not partake in those forums. However, MySQL developers do appear frequently on Oracle Open World meetings and various other meetings. They also publish their articles on: https://mysqlserverteam.com