Right. Most people simply are not in the habit of expressing themselves with enough precision, and might even be missing some essential training.

Whenever precision is required, we end up with methodologies like technical writing, or specialized language like legalese, to provide the necessary precision.

If we didn't have "programming languages", we'd end up with some sort of "computerese" analogous to legalese, and it would still take years to become proficient in using it, and there would still be either people or AI who would translate natural language into "computerese" (but we'd likely prefer human oversight over the AI anyway, given that a human that needs an AI to convert their language into something more precise is likely not capable of judging whether the output is precisely what they meant).

> Most people simply are not in the habit of expressing themselves with enough precision, and might even be missing some essential training.

I agree with this, I don't think we'll be programming in natural languages until the computer is intelligent enough to pick out unintended ambiguity and ask clarifying questions. What I'm unsure about is whether or not the computer will be that intelligent in 50 years. Whenever it is that intelligent, I expect us to transition quickly.

A problem is: How many clarifying questions do we want the computer to ask? How many much judgement do we want the computer to exercise?

There are going to be many tasks for which we don't want any independent judgement to be exercised by the machine. And for those tasks, we're going to still want a highly structured language.

And if the computer needs to ask the person a bunch of clarifying questions because they're providing ambiguous instructions, do we really trust that person's judgement about handling the unanticipated edge cases?

That's been a large part of my job at times - working with product stakeholders who give a high level requirements overview and then trying to figure out all the edge cases they didn't specify up front, so that my team could properly design and estimate the work to build the features.

So: yes, most companies already don't require people designing things to be able to fully spec every edge case in advance, without any dialogue.

The question is: can your job be automated in 50 years? Maybe it can. But the computer has a long way to go. I don't think we have enough evidence to give any estimate of when (if) that will happen.

Thats different. You're the one designing the system -- Exercising judgement about which mechanical rules to implement.

Ah, the good ol' Star Trek TNG computer. Even in the show people were constantly annoyed with it.

Exactly! I wish I could communicate with my wife in code, she seems extremely able to find ambiguous natural language expressions that tip me the wrong way with respect to her intent. I still love her regardless.

I think we want a precise computer language, but with a code completion/suggestion good enough to make transitioning from natural language trivial.

Agree. Most of program languages are context free. Human language is mostly context dependent. The auto completion and auto suggestion are the tools to close the gaps of the user experience.

Moreover, human communication is continuous and conversational. Programming is not. Most of existing code editors are not designed to have a conversation. Jupyter notebook is close but not there yet. I bet with a conversational agent style code editor plus a good auto suggestion and auto completion feature, we don't need to invent technology to use natural language to communicate with a machine. We just need a well designed formal language with precise and concise syntax.

Some people write news conforming to Google translation's performance just to make sure the story can be auto translated to many other languages. Most of these stories have pretty normalized vocabulary. This is how much human can adapt to the new world.

One day when the machine intelligence surpasses human intelligence, we will all speak in Python or whatever the most popular among machines.

There's the opposite problem too! If you over-specify, you may be denying the machine a chance to optimise.

I think that's what he's getting at using the word requirements in this quote - as in, you're not specifying the process, only the outcome. By definition it's not possible to over-specify the outcome, it just is what it is, and there is no theoretical reason that should limit the ability of the machine to optimise getting to that outcome.

In a sense, that's one definition of language power - to what extent can you specify that what without having to specify the how, assuming that the machine can execute the how much more optimally than you can most or all of the time.

> By definition it's not possible to over-specify the outcome, it just is what it is, and there is no theoretical reason that should limit the ability of the machine to optimise getting to that outcome.

I'm not sure what you mean by the outcome, but you can absolutely over-specify the output you want, and it's a relatively common problem. For example, you could say 'I want a system that prints lines with a saffron-based pigment', when all you wanted was an intense yellow-ish color, and don't care about the exact pigment being used. Or, you could say 'I want a program that let's a user choose red or blue or yellow and then paints a line in that color', when you actually mean the user can choose any color.

I've seen many problems of over-specification when talking high-level details with less technical people. They tend to give details to help explain what they mean, without caring about the specific details; or they use metonimy expecting everyone understands what they mean.

Ah yes you're right, I completely missed that and that's true. I was thinking about it more from the point of view of not mixing specifying output with implicit instructions on how to reach it (for example, don't say you want to order an array and then pick the last number, just say you want to pick the greatest number from the array) - but that's only assuming you actually are precise about what outcome you want and don't ask for anything irrelevant, which of course people obviously will do from time to time.

One question might be, in a natural language "conversation" with the database system, could one iteratively refine the results to the needed precision, and then perhaps bookmark the query. It's still an open question if people end up with enough precision, but I think that's an easier and different standard than equivocating a single SQL query to a single natural language query.

The question to be asked is whether your coding language contains non-essential information.

For example, in the case of SQL, the GROUP BY clause is nearly always noise.

So much so that SQL database will tell you exactly what things you need to add to the GROUP BY to make it valid SQL. Those things are extraneous information that the database could (and already did) know.

(IMO, in an aggregated query, SQL ought to implicitly add all non-aggregated fields to the GROUP BY, the user may explicitly add fields to the GROUP BY if desired.)

I just recently learned that the ordering of fields in your GROUP BY clause has meaningful consequences for the output and can differ from what's in your top SELECT. Try it with something like SQL Server's `FOR JSON AUTO` and you'll immediately see why `GROUP BY` demands the explicit order from you.

This is the case for the older equivalent syntax for XML also. Though it isn't quite SQL Server automatically doing anything, i.e. automatically extended the GROUP BY clause as needed. It is simply a short-hand for "use your standard heuristic to guess my schema instead of me giving it to you" and you explicitly tell it you want that heuristic to be followed, it isn't just deciding to because the other things you've told it are ambiguous. Also note that it has not always been version safe: I had trouble between SQL 2005 and 2008 with the way some FOR XML AUTO queries were interpreted having changed.

Maybe a similar syntax, "GROUP BY AUTO", could be useful, but I suspect it would quickly become a code smell - some people would slap "AUTO" in when they get an error in much the same way they slap "DISTINCT" in to fix an accidental cartesian product without actually thinking about the problem.

As irritating as having to list many columns in the GROUP cause of a statement with a wide output, I like that it has to be explicit: if I have the column lists wrong I've made an error and I don't want SQL Server to guess how to fix that error. Perhaps instead of missing a column in the grouping list I've instead messed up in the projection clause. And if the statement is getting inconvenient enough that the grouping clause is a significant irritation, perhaps it needs to be refactored more generally.

One further thing to note is that some DBs do automagically decide what to do when columns are neither in an aggregate or the grouping list: mySQL for instance will just return an arbitrary value from those that exist in the current group for such columns (IIRC the first value hit as the data is read). This leads to situations where the query seems to work fine until something changes in either the data balance or the available indexes (or some change is made to the query itself) that makes the processing happen a different way around so a different arbitrary value starts to get selected for the same group in the same query.

>This leads to situations where the query seems to work fine until something changes in either the data balance or the available indexes (or some change is made to the query itself) that makes the processing happen a different way around so a different arbitrary value starts to get selected for the same group in the same query.

That's a great point

FOR JSON AUTO is a non-standard SQL extension.

And as the name suggests its somewhat magic/implicit in its behavior.

No ANSI SQL result is altered by changing the order of GROUP BY.

> IMO, in an aggregated query, SQL ought to implicitly add all non-aggregated fields to the GROUP BY

I have literally never, ever wanted this. Why in the world would you group by any field not appearing in an aggregate?

Also, the group by can specify cubes and rollups, more over you may not want to group by any field, but rather an expression.

You virtually always want this.

   SELECT count(*), favorite_color, favorite_song
   FROM person
   GROUP BY ...

The "..." is of course favorite_color, favorite_song.

Whether you want this or not, ANSI SQL requires that you group by at least these.

You could group by more more fields, such as favorite_pet, though that would be somewhat odd since you are did not include it in the result set.

Ah, you meant in the select, not the table. I guess I'm just much more in favour of being explicit, but I can understand the usefulness of what you said now that I fully understand it.

This would definitely lead to me being very confused when things I'm selecting that I thought were aggregates were actually being implicitly grouped, or when one thing I'm selecting that I didn't realize is an aggregation results in grouping by all the other fields. There is no way for the query engine to know which direction I made a mistake in, so it just tells me something is wrong and let's me figure out how to fix it. Failing fast is much better than trying to guess at what I'm trying to do.

To summarize: "Writing things twice allows you to know where you went wrong and correct it."

That's true.

I still prefer DRY, but I can appreciate the WET perspective.

Your summary is wrong. When you write "select field" you are saying which field to return from the query. When you write "group by field", you are saying that you want the query to group by that field when generating query results. You are not writing anything twice, you are writing two different things.

When I write "select field" I am saying which field I want to return from the query and which field I want to group by. There's no difference. And the DBMS enforces that there is no difference. *

* Except for the special case when it's non-aggregated and I want duplicate rows in the result set. SQL allowing duplicate rows is a departure from the underlying relational paradigm.

The reason it needs to be explicit is to prevent mistakes, which is why it is a good thing that most databases try to infer which columns are functionally dependent on the GROUP BY columns so that you can skip them for the GROUP BY clause.