If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format’” in MySQL, it’s probably because you’re trying to run EXPLAIN ANALYZE with the wrong output format.
The EXPLAIN ANALYZE statement only supports the TREE output format. However, there are a few things to be aware of regarding this.
Either way, the quickest way to fix the error is to use FORMAT=TREE in your EXPLAIN ANALYZE statement.
If you’ve got a table with an invisible column in MySQL, but you no longer want the column to be invisible, you can use the ALTER TABLE statement to make it visible.
Invisible columns are columns that can’t be accessed by queries that use the asterisk wildcard (*) to select all columns (although they can be accessed by explicitly naming the invisible column in the SELECT list).
If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION’” in MySQL, it’s probably because you’re trying to use the FOR CONNECTION option with EXPLAIN ANALYZE, but EXPLAIN ANALYZE doesn’t support the FOR CONNECTION option.
It’s true that we can use the FOR CONNECTION option with the EXPLAIN statement, we can’t use it when using the ANALYZE variant of EXPLAIN (we can use EXPLAIN with or without ANALYZE). At least, this is how it works as of this writing (MySQL 8.3).
To fix, either remove the ANALYZE keyword from the statement or don’t use the FOR CONNECTION option.
Since MySQL 8.0.23 we’ve had the ability to create invisible columns, which are columns that are normally hidden to queries (but can easily be accessed if required).
With the introduction of invisible columns comes the inevitable requirement to check a table to see if it contains any invisible columns.
Fortunately there are many ways we can do this. Below are five ways to check a table for invisible columns.
Continue readingIf you’re getting an error that reads “The function ‘RANK’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the RANK() function without an OVER clause.
The RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause when calling the RANK() function.
MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.
When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.
If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.
When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.
To fix, make sure both queries select the same number of columns.
Continue readingWhen using the NTILE() function in SQL Server, we must include an OVER clause clause with an ORDER BY clause.
If you’re getting error msg 4112 that reads “The function ‘NTILE’ must have an OVER clause with ORDER BY” when using the NTILE() function, it’s because, although you’re (correctly) including an OVER clause, you’re omitting the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.
But we also have the option of naming the columns ourselves.
Continue readingIf you’re getting a SQLite error that reads ‘Parse error: near “LIMIT”‘, it could be that you’re trying to use the LIMIT clause when using the VALUES clause as a stand alone statement.
SQLite doesn’t allow us to apply the LIMIT clause against the VALUES statement.
However, there is a work around. Below is an example of how we can apply the LIMIT clause against the VALUES statement.