MongoSQL Errors

Description: A function (e.g., Sin, Abs, Round) has the incorrect number of arguments.

Common Causes: A function has an extra argument or is missing one. For example, in SELECT ROUND(a,1,5) FROM foo where a is an int , ROUND has an extra argument.

Resolution Steps: The error message indicates which function has the wrong number of arguments and how many it should have. Use this information to correct your query. Corrected example query: SELECT ROUND(a,1) FROM foo.

Description: The specified operation (e.g., Sub, And, Substring) has argument(s) of the incorrect type (e.g., string, int).

Common Causes: A field of the wrong type was mistakenly used in the specified function. For example, SELECT ROUND(str,5) FROM foo where str is of the type string causes this error.

Resolution Steps: The error message indicates which function has an argument of the wrong type and what the expected type is. Use this information to correct your query. Corrected example query: SELECT ROUND(a,5) FROM foo where a is an int.

Description: The argument provided to the aggregation is not of a type that is comparable to itself.

Common Causes: The column you are attempting to aggregate on is either of type object or array which are not comparable to themselves.

For example, SELECT * FROM myCol GROUP BY a AGGREGATE MIN(obj) as min where obj is an object causes this error.

Resolution Steps: Only use AGGREGATE functions on columns of the appropriate type. If you want to AGGREGATE on a value within an array or object , use the UNWIND or FLATTEN data source keywords. Corrected example query: SELECT * FROM myCol GROUP BY a AGGREGATE MIN(a) as min where a is an int.

Description: COUNT(DISTINCT *) is not supported.

Common Causes: Any use of COUNT(DISTINCT *) ( SELECT COUNT(DISTINCT *) FROM foo ) is not supported.

Resolution Steps: Remove any use of COUNT(DISTINCT *) from your queries.

Description: The specified comparison operation (e.g., Lte, Between) could not be executed due to incomparable types of their operands (i.e., comparing an int to a string).

Common Causes: Fields of two different types were used in the specified comparison operation. For example, SELECT * FROM foo WHERE a <= b where a is an int and b is a string. Additionally, note that objects and arrays aren't comparable types and can't be used in comparison operations.

Resolution Steps: The error message indicates which comparison operation has conflicting types. Use this information to correct your query. Corrected example query: SELECT * FROM foo WHERE a1 <= a2 where a1 and a2 are both of the type int.

Description: Cannot access a field, because it cannot be found (and likely doesn't exist).

Common Causes: You are attempting to access a field that doesn't exist, likely due to a typo. For example, SELECT foo.f FROM foo where f is a non-existent field causes this error.

Resolution Steps: When possible, the error message suggests similar field names that may have been the intended input. However, if nothing similar is found, MongoSQL doesn't provide field name suggestions. Use this information to correct your query. Corrected example query: SELECT foo.a FROM foo where a exists as a field of foo.

Description: The cardinality of a scalar subquery's result set may be greater than 1. The result set MUST have a cardinality of 0 or 1.

Common Causes: Your scalar subquery may return more than 1 document (or row in SQL). For example, SELECT (SELECT a FROM foo) FROM bar causes this error, because (SELECT a FROM foo) could return more than 1 document (or row).

Resolution Steps: Add a LIMIT 1 to your subquery to ensure that only 1 document/row is returned. Correct example query: SELECT (SELECT a FROM foo LIMIT 1) FROM bar.

Description: Cannot sort by the specified key, because it is of a type that can't be compared against itself.

Common Causes: The column you are attempting to sort on is either of type object or array , which are not types comparable to themselves. For example, SELECT * FROM foo ORDER BY obj where obj is an object throws this error.

Resolution Steps: Only sort on columns of the appropriate type. If you want to sort on a value within an array or object , use the UNWIND or FLATTEN data source keywords. Corrected example query: SELECT * FROM foo ORDER BY a where a is an int.

Description: Cannot group by the specified key, because it is of a type that can't be compared against itself.

Common Causes: The column you are attempting to group by is either of type object or array which are not types comparable to themselves. For example, SELECT * FROM foo GROUP BY obj where obj is an object throws this error.

Resolution Steps: Only group by columns of the appropriate type. If you want to group by a value within an array or object , use the UNWIND or FLATTEN data source keywords. Corrected example query: SELECT * FROM foo ORDER BY a where a is an int.

Description: UNWIND INDEX name conflicts with existing field name.

Common Causes: INDEX name is the same as an existing field name. For example, if collection foo has a field b , then the query SELECT * FROM UNWIND(foo WITH PATH => a, INDEX => b) causes this error.

Resolution Steps: Change the INDEX name to something that is not an existing field. Corrected example query: SELECT * FROM UNWIND(foo WITH PATH => a, INDEX => b2) where b2 is not an existing field name.

Description: Cannot find the collection in the specified database.

Common Causes: You may be looking in the wrong database, or you have made a typo that is causing the collection to not be found. For example, if collection baz does not exist, the query SELECT * FROM baz causes this error.

Resolution Steps: Ensure everything is spelled correctly and that you are looking in the correct database. Corrected example query: SELECT * FROM foo where foo exists in the current database.

Description: Extended JSON detected in comparison operation. MongoSQL does not support direct comparisons with extended JSON. Use casting instead (look at "Resolution Steps" for an example).

Common Causes: Using extended JSON in queries and expecting it to implicitly cast to the correct data type. For example, someone may write the query select _id from customers where _id = '{"$oid":"5ca4bbcea2dd94ee58162a6a"}' because they think the extended JSON string implicitly casts to an ObjectID. However, this is not the case.

Resolution Steps: Don't use Extended JSON format and always explicitly CAST. The error message tries to recommend what you should do (casting). Corrected example query: select _id from customers where _id = CAST('5ca4bbcea2dd94ee58162a6a' as ObjectID). This query explicitly casts to an ObjectID.

Description: A field has an unsupported BSON type.

Common Causes: A field has a BSON type that is not supported by MongoSQL. For example, if collection foo has a field b of type undefined , then the query SELECT * FROM foo would cause this error.

Resolution Steps: Change the BSON type to something that is supported by MongoSQL. The error message suggests what BSON types are supported. Corrected example query: SELECT * FROM foo where foo exists in the current database and b is not of type undefined.

Description: A field of type Binary data has the unsupported subtype of uuid old.

Common Causes: Historically, different drivers have written Uuids using different byte orders. This may occur for older data written by a driver using the now-unsupported uuid type.

Resolution Steps: Querying this data is not supported by Atlas SQL.

Description: This error is a catch-all for every parser error except unrecognized token.

Common Causes: This error can mean many different things since it is a catchall error. However, it generally means that some token (keyword) was used incorrectly.

Resolution Steps: This error can give several different messages, so paying attention to those messages is important. Additionally, it is a good idea to look over the input query to ensure that every clause (as well as the SQL statement as a whole) is written as specified by the guidelines.

Description: An unexpected or unrecognized token was encountered.

Common Causes: Something may have been spelled wrong or put in the wrong order/format. For example, SELECT ** FROM foo AS f causes this error due to the ** type.

Resolution Steps: Ensure that everything is spelled correctly and put in the correct order/format. Additionally, this error suggests a list of tokens that you may have meant to put. Corrected example query: SELECT * FROM foo AS f.

Description: A SELECT list with multiple values cannot contain a non-namespaced * (i.e., SELECT a, *, b FROM myTable is not supported). A non-namespaced * must be used by itself.

Common Causes: Selecting * and anything else in the same query as shown by this query: SELECT *, a from foo.

Resolution Steps: Either only select * (i.e., SELECT * FROM ... ) or select multiples values and don't include * (i.e., SELECT a, b FROM ... ).

Description: The array data source contains references. Array data sources must be constant.

Common Causes: Accessing a field in an array data source as shown by this query: SELECT * FROM [{'a': foo.a}] AS arr.

Resolution Steps: Modify your array data source to only contain constants. Corrected example query: SELECT * FROM [{'a': 34}] AS arr.

Description: SELECT DISTINCT is not allowed.

Common Causes: SELECT DISTINCT was used in a query. For example, the query SELECT DISTINCT * from foo causes this error.

Resolution Steps: Don't use SELECT DISTINCT in a query.

Description: Distinct UNION is not allowed. You can only do UNION ALL (i.e., duplicate values always have to be allowed).

Common Causes: Using UNION instead of UNION ALL. For example, the query SELECT a FROM foo AS foo UNION SELECT b, c FROM bar AS bar causes this error.

Resolution Steps: Only use UNION ALL when doing unions. Corrected example query: SELECT a FROM foo AS foo UNION ALL SELECT b, c FROM bar AS bar.

Description: A data source referenced in the SELECT list could not be found.

Common Causes: Doing something like SELECT <field>.* FROM ... where <field> does not have any subfields. For example, the query SELECT a.* FROM foo where a is an int causes this error.

Resolution Steps: Remove any field accesses that are trying to access a field that does not exist.

Description: A field could not be found in any data source.

Common Causes: The field is either spelled wrong or doesn't exist, or you're looking in the wrong collection. For example, Select aa from foo would cause this error if aa does not exist in collection foo.

Resolution Steps: Make sure the field is spelled correctly and that you are looking in the correct collection. Additionally, the error message suggests any similar fields you may have meant to write.

Description: A field exists in multiple data sources and is ambiguous.

Common Causes: Two or more fields have the same name in a collection (or some other data source). For example, assuming a is a field in foo , SELECT a FROM foo AS coll JOIN foo AS coll2 would cause this error because collections coll and coll2 both have field a , causing a to be ambiguous.

Resolution Steps: Qualify your references ( <Collection>.<field> instead of field ). In the above example, coll.a or coll2.a would fix this error.

Description: The * argument is only valid in the COUNT aggregate function.

Common Causes: COUNT is the only aggregation operation that supports the * argument, i.e. COUNT(*). Passing the * argument to any other operation, i.e SUM(*) , causes this error. For example, the query SELECT * FROM foo AS arr GROUP BY a AS a AGGREGATE SUM(*) AS gsum.

Resolution Steps: Only use * as an argument to COUNT.

Description: An aggregation function was used in a scalar position.

Common Causes: Using an aggregation function where only a scalar function can be used. For example, the query SELECT VALUE {'suma': SUM(a)} FROM db.bar AS bar GROUP BY a AS a causes this error because SUM(a) is an aggregation function.

Resolution Steps: Remove aggregation functions in places where you can only have scalar functions.

Description: A scalar function was used in an aggregation position.

Common Causes: Using a scalar function where only an aggregation function can be used. For example, the query SELECT * FROM foo GROUP BY a AGGREGATE round(a) AS round causes this error because round is a scalar function.

Resolution Steps: Remove scalar functions in places where you can only have aggregation functions.

Description: A non-aggregation expression was found in a GROUP BY aggregation function list.

Common Causes: Putting anything other than an aggregation or scalar function where an aggregation should be. (Using a scalar function in place of an aggregation causes a different error: Error 3012). For example, the query SELECT * FROM foo GROUP BY a AGGREGATE COUNT(*) + 7 AS whatever causes this error, because there is an addition operation along with an aggregation instead of just an aggregation.

Resolution Steps: Only use aggregations in places where aggregations are allowed. Corrected example query: SELECT * FROM foo GROUP BY a AGGREGATE COUNT(*) AS whatever.

Description: Aggregation functions must have exactly one argument.

Common Causes: An aggregation function has more than one argument. For example, the query SELECT * FROM foo GROUP BY a AGGREGATE min(a,b) AS min causes this error, because it provides two arguments for the min aggregation.

Resolution Steps: Make sure your aggregations only have one argument. Remove any additional arguments. Corrected example query: SELECT * FROM foo GROUP BY a AGGREGATE min(b) AS min.

Description: Scalar functions don't support DISTINCT.

Common Causes: Using DISTINCT in a scalar function. For example, the query SELECT ROUND(DISTINCT a,2) FROM foo causes this error.

Resolution Steps: Don't use DISTINCT in scalar functions. DISTINCT should only be used in aggregation functions. Corrected example query: SELECT ROUND(a,2) FROM foo.

Description: A derived data source has overlapping fields.

Common Causes: Including multiple data sources that each have fields of the same name within a given subquery causes this error. For example, assuming collection bar and collection foo have fields of the same name, then the query SELECT * FROM (SELECT * FROM foo AS foo, bar AS bar) AS derived causes this error.

Resolution Steps: Update common field names to make them unique across data sources. A simple way to accomplish this is by aliasing a unique name for the fields with the same name. Using this technique, here is our corrected example query: SELECT * FROM (SELECT a, b, c, ..., z, FROM foo, a AS bar_a, b AS bar_b, c AS bar_c, ..., z AS bar_z FROM bar) AS derived.

Description: An OUTER JOIN is missing a JOIN condition. OUTER JOINs must specify a JOIN condition.

Common Causes: An OUTER JOIN is missing a JOIN condition. For example, the query SELECT * FROM foo AS foo LEFT OUTER JOIN bar causes this error, because it is missing the required ON <condition> portion of the query.

Resolution Steps: Add a JOIN condition (i.e., ON <condition> ). Corrected example query: SELECT * FROM foo AS foo LEFT OUTER JOIN bar ON a = a.

Description: A schema environment could not be created due to a duplicate field.

Common Causes: Multiple collections have the same alias. For example, the query SELECT * FROM foo AS alias, bar AS alias causes this error.

Resolution Steps: Make sure aliases are unique for collections. Corrected example query: SELECT * from foo as foo_alias, bar as bar_alias.

Description: Scalar subquery expressions must have a degree of 1.

Common Causes: Selecting more than one field (or column in sql) in a scalar subquery expression. For example, the query SELECT (SELECT * FROM foo LIMIT 1) FROM bar AS bar causes this error, because the subquery is executing SELECT * , so it is selecting multiple fields.

Resolution Steps: Modify your subquery so that you are only selecting a single field. Corrected example query: SELECT (SELECT a FROM foo LIMIT 1) FROM bar AS bar

Description: A document has multiple fields with the same name.

Common Causes: Selecting multiple fields from a collection and giving them the same aliases as shown by the following query: SELECT a AS alias, b AS alias FROM foo.

Resolution Steps: Change duplicate aliases for fields to unique names. Corrected example query: SELECT a as a_alias, b as b_alias FROM foo.

Description: The same FLATTEN option is defined more than once.

Common Causes: The same option is defined more than once as shown by the following query: SELECT * FROM FLATTEN(foo WITH depth => 1, depth => 2).

Resolution Steps: Remove any duplicate options to ensure each option is only used once. Corrected example query: SELECT * FROM FLATTEN(foo WITH depth => 1).

Description: Schema information is insufficient to allow for flattening the data source.

Common Causes: Trying to flatten a collection that doesn't have sufficient schema information. For example, assuming you have a collection called noSchemaInfo that has no schema defined, the query SELECT * FROM FLATTEN(noSchemaInfo) causes this error. Another common cause is trying to flatten a collection with a schema or some field in the schema that has additional_properties set to true.

Resolution Steps: Define all fields in the schema to ensure that the schema is sufficiently defined. Additionally, try to avoid setting additional_properties to true. Instead, fully define the schema.

Description: A field within the schema is a polymorphic object type (i.e., consider a field that could either be a document or int ), so it can't be flattened. null and missing object polymorphism is permitted. Flattening only works on object (AKA document) types, so if there is a possibility that some field may not be a document (unless the only other possibilities are null or missing ), then it can't be flattened.

Common Causes: Trying to flatten a collection that has a schema containing a field that is a polymorphic object type. For example, assuming that a collection named coll has a field that is a polymorphic object type, then the query SELECT * FROM FLATTEN(coll) causes this error.

Resolution Steps: You can only flatten object types. If you want a field to have the possibility of being flattened, the schema for that field MUST be an object type. The only exception to this rule is objects that may be null or missing; you can still flatten these objects despite being polymorphic. To fix this error, you must either stop trying to flatten collections that have fields with polymorphic object types, or you must change the schema for those fields to be an object type only.

Description: The same UNWIND option is defined more than once.

Common Causes: The same option is used more than once as shown by the following query: SELECT * FROM UNWIND(foo WITH PATH => a, PATH => b).

Resolution Steps: Remove any duplicate options to ensure each option is only used once. Corrected example query: SELECT * FROM UNWIND(foo WITH PATH => a).

Description: UNWIND is missing the PATH option. UNWIND must specify a PATH option.

Common Causes: UNWIND is missing the PATH option as shown by the following query SELECT * FROM UNWIND(foo).

Resolution Steps: Add the PATH option to the UNWIND. Corrected example query: SELECT * FROM UNWIND(foo WITH PATH => a).

Description: The UNWIND PATH option is not an identifier; however, it must be one.

Common Causes: The UNWIND PATH option is not an identifier as shown by the following query SELECT * FROM UNWIND(foo WITH PATH => {'a': []}.a).

Resolution Steps: Change the UNWIND PATH option into an identifier. Corrected example query: SELECT * FROM UNWIND(foo WITH PATH => a).

Description: The target type of the CAST is an invalid type (i.e., it's either an unknown type or a type that MongoSQL does not support casting for).

Common Causes: Casting to a type that MongoSQL does not support casting for. For example, the query SELECT CAST(a AS DATE) FROM foo

causes this error, because DATE is not a supported target type.

Resolution Steps: Cast only to supported target types. Valid target types are ARRAY, DOCUMENT, DOUBLE, STRING, OBJECTID, BOOL, BSON_DATE, INT, LONG, and DECIMAL, or any of their corresponding SQL-92 type aliases: REAL, FLOAT, VARCHAR, CHAR, CHARACTER, CHAR VARYING, CHARACTER VARYING, DEC, NUMERIC, BIT, BOOLEAN, TIMESTAMP, INTEGER, SMALLINT. Corrected example query: SELECT CAST(a AS BSON_DATE) FROM foo.

Description: A sort key is invalid, because it uses complex expressions (i.e., ORDER BY {'a': b}.a is invalid).

Common Causes: Attempting to ORDER BY complex expressions or "impure" field paths. For example, the query SELECT * FROM foo ORDER BY CAST(d AS DOCUMENT).a

causes this error, because CAST(d AS DOCUMENT) is a complex expression.

Resolution Steps: Make sure you only sort by "pure" field path. A "pure" field path consists only of

identifiers, such as foo.d.a or a.

Description: The non-namespaced result set cannot be returned due to field name conflict(s).

Common Causes: Setting the $sql aggregation excludeNamespaces field to true and querying multiple collections with the same field names causes this error. Because this option removes collection namespaces, fields with the same name that belong to different collections are no longer unique. For example, consider collections foo and bar each with a field a , and a query such as SELECT foo.*, bar.a FROM foo, bar. With collection namespaces in the result set, the two a fields can be differentiated between foo and bar ; however, without collection namespaces, they cannot be distinguished from one another.

Resolution Steps: The best way to fix this error is to use aliasing to make conflicting fields unique. Corrected example query: SELECT foo.*, bar.a AS a_unique_alias from foo, bar.