If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.
Continue readingIf 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 readingIf 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.
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.
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 readingIf 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.
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZINTER command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that the key you pass to the ZINTER command holds a sorted set.
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.
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 readingIf 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