Why sql has 'near somewhere' as an error message?

Question

There were many times that i got frustrated trying to debug a query that just told me 'near that mess of yours there is a mistake'. Why can't it tell me the mistake exactly?

Example:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'systemquery1 as ( (select data from (SELECT u.id as userid, ' at line 12

Answer 1

One of the main reasons for the frustrating syntax error messages is that the MySQL and MariaDB parsers were created with the GNU Bison parser generator. Due to the generic nature of this library, and also the way the SQL parser was implemented, you see where the error is found but not what would be expected or what would be correct.

Something that was mentioned in multiple comments is the fact that you can place the query into a stored procedure and you'll get better error messages.

Answer 2

SQL parsers -- as with the parsers of many languages -- are not that smart. They know what the language looks like. However, they have no imagination. When something doesn't go right they merely know that it isn't right.

Human languages don't generally have this problem. They have a lot of redundancy built in, which helps speakers fill in missing sounds, missing words, make sense of homophones and unrecognized grammar.

Some newer database engines have gotten smarter. For instance, I'm still impressed when BigQuery suggests the (invariably correct) variable name when I misspell something.