SQL Limitations in ShardOne

The below-given queries/clauses are currently unsupported by ShardOne.

Querying with Item Alias mapped to items with different schema
Suppose an item alias, say ‘XYZ’ is mapped to two different items with different schema (different number of columns/datatypes). Let’s say the items are:
1. ‘Person_1’ with fields
  1. id
  2. first_name
  3. member_id-
  4. knows_id
2. ‘Person_2’ with fields
  1. identifier
  2. name
  3. member_id
If a query is run over ‘XYZ’, ShardOne will fetch incorrect/no results. For instance, the query depicted below will not yield the expected results.
```
SELECT * FROM XYZ
```
This is because the schema of the items aligned to the alias are different. To resolve this, we can create and map field aliases to the fields of the items in a way the schema is made the same. For example, the mapping of the aliases could be done in a similar manner as shown below:
1. ‘Person_1’ with fields
  1. id
  2. first_name -> name
  3. member_id
  4. knows_id
2. ‘Person_2’ with fields
  1. identifier -> id
  2. name
  3. member_id
Again, SELECT * would fail as there exists an additional field knows_id in ‘Person_1’, but if the query is modified such that only the necessary fields are projected in the select list, ShardOne would fetch the desired results. The query given below would work as expected.
```
SELECT id, name, member_id FROM XYZ
```
Inconsistency with SELECT * query in ElasticSearch
With the ElasticSearch datasource, ShardOne has an intermittent issue with SELECT * query for a few tables not yielding any results. However, if the query is modified so that only the necessary fields are projected, ShardOne will generate valid results.
COUNT(*)
ShardOne does not support using COUNT(*) at the moment. So, the query given below does not yield results.
```
SELECT Country, COUNT(*) FROM Customers GROUP BY Country;
```
But as a workaround that could yield the desired results, COUNT(Attribute) can be used. So an equivalent of the above query would be
```
SELECT Country, COUNT(Country) FROM Customers GROUP BY Country;
```
An Invalid aggregate query does not throw an error
Consider the given query:
```
SELECT age, COUNT(age) FROM Person 
```
The query is missing the necessary GROUP BY clause to accompany the COUNT function. The query execution should ideally result in an error, but for now, ShardOne returns an incorrect result.

GROUP BY column number/index
Queries that make use of the column number to specify the GROUP BY attribute, are unsupported currently. For instance, the query given below does not fetch the expected results
```
SELECT COUNT(Country), County FROM customer GROUP BY 2;
```
The above-given query implies that GROUP BY is to be performed over the second column in SELECT list i.e. Country. However, ShardOne does support GROUP BY < Attribute >. So the query could be modified into
```
SELECT COUNT(Country), County FROM customer GROUP BY Country;
```
Multiple Aggregation Functions
ShardOne only supports queries with only a single aggregation function present in them. For instance, a query like this is not supported.
```
SELECT AVG(age) AS ageCount, SUM(age) AS ageSum FROM Customers
```
But this could be split into multiple queries with a single aggregation in each of them, to fetch the results. Like,
```
SELECT AVG(age) AS ageCount FROM Customers
SELECT SUM(age) AS ageSum FROM Customers
```
Limitations in ORDER BY clause
1. If in a query, ORDER BY clause uses an SQL alias mapped to a field (as shown below), an error would be thrown.
```
SELECT p.id as identifier, p.patientFirstName, p.patientLastName, city FROM patients as p ORDER BY identifier
```
2. If the ORDER BY clause uses a field that has an SQL alias mapped in the SELECT list, the query would throw an error.
```
SELECT p.id as identifier, p.patientFirstName, p.patientLastName, city FROM patients as p ORDER BY id
```
3. If the field being used in ORDER BY clause has a NULL value in a record, the query would fail.
4. ORDER BY over an ambiguous field in a JOIN query would fail. The queries depicted below are JOIN queries over Person and Group that has a common field ‘id’.
```
SELECT * FROM `Person` p
JOIN `Group` g
ON p.member_id = g.id
ORDER BY p.id
```
```
SELECT * FROM `Person` p
JOIN `Group` g
ON p.member_id = g.id
ORDER BY id
```
  Workarounds for the first two cases would be avoid using SQL aliases or to use a field that has no SQL alias mapped to it in the ORDER BY clause. The workaround for the last case would be create and map a Field Alias in ShardOne so that ambiguity is removed.
Operations on Attribute that is Not Selected
Currently, any operations/transformations that are used down the query are to be present in SELECT list, in case SELECT * is not used. For example, the query given below would throw an error.
```
SELECT COUNT(age) AS ageCount
FROM Customers
GROUP BY age
ORDER BY age
```
Here ‘age’ is not present in the SELECT list. The common workarounds offered are
1. Include the attribute in the SELECT list.
```
SELECT age, COUNT(age) AS ageCount
FROM Customers
GROUP BY age
ORDER BY age
```
2. Modify the query to SELECT * if there is no use of GROUP BY clause
```
SELECT *
FROM Customers
ORDER BY age
```
LIKE Clause
Queries with LIKE Clause in unsupported at the moment. Hence, queries like the one below are unsupported queries.
```
SELECT * FROM Customers
WHERE CustomerName LIKE 'A%'
```
However, matches that use conditional operators are supported.
```
SELECT * FROM Customers
WHERE CustomerName LIKE 'Andrew'
```
BETWEEN Clause
Queries with BETWEEN clause, like the one shown below are unsupported.
```
SELECT age
FROM Customers
WHERE age BETWEEN 40 AND 50
```
A Workaround suggested is by making use of conditional operators. The above-mentioned query could be modified into
```
SELECT age
FROM Customers
WHERE age >= 40 AND age <= 50
```
Order of LIMIT and OFFSET
In ShardOne, the order of OFFSET and LIMIT in a query matters.
```
SELECT age
FROM Customers
OFFSET 3
LIMIT 5
```
The above-given query is unsupported. It can be modified so that LIMIT appears first and then OFFSET.
```
SELECT age
FROM Customers
LIMIT 5
OFFSET 3
```
JOIN ON Constants/Literals
In ShardOne, queries like the ones shown below are not supported.
```
SELECT *
FROM Customers
JOIN Products 
ON 1 = 1
```
```
SELECT *
FROM Customers as c
JOIN Products as p
ON c.age > 60
```
These are valid SQL queries but the logical meaning of the join performed may be incorrect. This acts more like a CROSS JOIN where there 2 tables are joined not based on any specific attribute.
However, ShardOne does support the joining of tables on constant or literals by using the clauses like AND & OR. The query given below is valid in ShardOne.
```
SELECT *
FROM Customers as c
JOIN Products as p
ON c.id = p.id AND c.age > 60
```
LENGTH over CHAR Datatype yields incorrect result
BINARY Datatype is unsupported
ARRAY Datatype is unsupported
Operations over YEAR Datatypes is unsupported
Comparison operators over FLOAT values in Athena Datasource is unsupported
MIN, MAX, SUM, AVG is unsupported in Athena Datasource
% operation
DATE_ADD
DATE_SUB
DATE_FORMAT
ADDTIME
SUBTIME
YEARWEEK
TO_DATE
FORMAT
TRUNC
DAY
CROSS JOIN/Comma JOIN
FULL JOIN/FULL OUTER JOIN
SELF JOIN
UNION
CASE
IS NULL/IS NOT NULL
IFNULL( ), COALESCE( ), NULLIF( )
CAST( )
EXISTS
WITH
ALL
ANY/SOME
Nested Queries
Nested queries, also known as subqueries, are queries that are embedded within another query. The clauses like EXISTS, WITH, ALL, ANY/SOME make use of nested queries for them to function. For now, ShardOne does not support them.