v1.1.14

Fixes:

• Count (distinct col)
  select Category,count(distinct Rating) as cntDis,count(1) as cnt,max(`Rental Duration`) as maxDuration,min(`Rental Duration`) as minDuration,sum(`Length`) as sumLength,avg(`Length`) as avgLength from films group by Category
• Aggregate functions in binary expressions
  select (max(`Length`) + max(`Replacement Cost`) + 1) * min(To_int(`Rental Duration`)) as totVal from films

Functions:

• Extract on postgres

v1.1.12

Fixes

• NOT LIKE returning empty match condition
• Alias prefixing on expressions in select statement
  select filmId,(f.filmId + f.`Rental Duration` + 2) as val from films f where id>1

v1.1.10

Enhancements

• Added join hint OPTIMIZE which works for simple sub select joins to move the on before other expressions
  The $match on sub query joins is applied after the subquery pipeline which can cause performance issues since indexes may not be used. It may be better to put the match before the pipeline to limit the input set depending on the on conditions.
  select c.*,cn.* from customers c inner join (select * from \`customer-notes\` where id>2) \`cn|optimize\` on cn.id=c.id
  If the on uses a computed column it may not be possible:
  select c.*,cn.* from customers c inner join (select sum(id,1) as id from \`customer-notes\` where id>2) \`cn|optimize\` on cn.id=c.id
  will return invalid results since the join field is computed

• Added hint chaining for future use cases:
  select c.*,cn.* from customers c inner join (select * from \`customer-notes\` where id>2) \`cn|optimize|first\` on cn.id=c.id

v1.1.9

Enhancements

• join hints on AS to support sub queries (FIRST,LAST,UNWIND)

select c.*,cn.* from customers c inner join (select * from `customer-notes`` where id>2) `cn|first` on cn.id=c.id

v1.1.8

Fixes:

• STRPOS 1 based index as per Postgres

Enhancements

• NOT query support
  select * from films where NOT (Title = 'Test' or Title = 'test2')

New Functions

• LOCATE MySQL function

v1.1.7

Functions:

• STRPOS
• STARTS_WITH
• ILIKE on PostgresQL

Dev:

• Add database option to tests

v1.1.6

Fixes:

• number and boolean literal in select
  select 1 as c0, filmId from films

v1.1.5

Fixes:

• IS NULL and IS NOT NULL on Case statements
• canQuery not allowed with table aliases
• Match before project when using table aliases
• Auto column prefix when no prefix specified to first table alias if specified
  select f.filmId,Title from films f where f.Title = 'Test' and Name = 'Test'

v1.1.4

Fixes:

• count(1) throwing an error

v1.1.3

Fixes:

• PostgresQL DISTINCT clause not working
• "NOT column IS NULL" clause
• "column IS NULL" clause
• Aggregate functions auto group e.g. "select cum(col1) as s from customers" would not automatically group as per normal SQL behaviour.

New features:

• unsetId option on makeMongoAggregate so that if _id is not specified in the SELECT then it is removed from the results. Some drivers don't like an unknown column coming back.