Tag: errors

Fix ERROR 1140 “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column…” in MySQL

Posted on by Ian

If you’re getting MySQL error 1140, which reads something like “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘world.City.District’; this is incompatible with sql_mode=only_full_group_by“, it could be that you need to introduce a window function to the query.

This error can happen when we want multiple rows to contain aggregate values. For example, we might want to return subtotals of all rows within a group of rows. A window function can help us achieve this outcome.

Continue reading →

Fix ERROR 3582 “Window ‘…’ has a frame definition, so cannot be referenced by another window” in MySQL

Posted on by Ian

If you’re getting MySQL error 3582, which reads something like “Window ‘w1’ has a frame definition, so cannot be referenced by another window“, it’s probably because you’re referencing a named window that has a frame clause.

This can happen when we use the WINDOW clause to define a named window for a window function, and we include a frame clause in that window. When we then try to reference that window, MySQL tells us that we can’t, due to the frame clause.

Continue reading →

Fix ERROR 3583 “Window ‘…’ cannot inherit ‘…’ since both contain an ORDER BY clause” in MySQL

Posted on by Ian

If you’re getting MySQL error 3583, which reads something like “Window ‘<unnamed window>’ cannot inherit ‘wf’ since both contain an ORDER BY clause“, it’s probably because you’re using an ORDER BY clause when referring to a named window that also has its own ORDER BY clause.

To fix this issue, don’t use the ORDER BY clause when referring to a named window that already has an ORDER BY clause in its definition.

Continue reading →

5 Ways to Fix Error 1364 “Field ‘…’ doesn’t have a default value” in MySQL

Posted on by Ian

If you’re getting MySQL error 1364, that reads something like Field 'column_name' doesn't have a default value, where column_name is the name of a column in your table, it’s probably because you’re trying to perform an operation that retrieves the column’s default value, but it doesn’t have a default value.

This can happen when the column is set to NOT NULL and hasn’t been defined with an explicit default value.

The solution to this issue will depend on what we’re trying to do. Below are examples of how to fix this error.

Continue reading →

Fix Error 1138 “Invalid use of NULL value” in MySQL

Posted on by Ian

If you’re getting error 1138 that reads “invalid use of NULL value“, it could be because you’re trying to change the definition of a column to require non-NULL values, but the column contains NULL values.

This can happen when we try to change a column from NOT NULL to NULL. It can also happen when we try to set a column as a primary key, but that column contains NULL values.

To fix this issue, be sure to ensure that the column you’re modifying doesn’t contain any NULL values. Alternatively, if the column should be able to accept NULL values, then you should change your column definition to allow NULL values.

Continue reading →

Fix “column … can only be updated to DEFAULT” in PostgreSQL

Posted on by Ian

If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.

Continue reading →

6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL

Posted on by Ian

If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.

We have a number options when it comes to dealing with this issue.

Continue reading →

6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL

Posted on by Ian

If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.

This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

Continue reading →

4 Ways to Fix Error 1048 “Column ‘…’ cannot be null” in MySQL

Posted on by Ian

If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL value into that column, but the column has a NOT NULL constraint (which prevents NULL values from being inserted).

We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL value for the column. Alternatively, if the column should be able to accept NULL values, then we can remove the NOT NULL constraint from the column. Another option is to use the IGNORE keyword to ignore the error. And another way to deal with the error is to disable strict mode.

Continue reading →