[SQL兼容性]select

select兼容性

query:

select:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]

selectWithoutFrom:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }

projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *

tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
  |   tableExpression CROSS JOIN tableExpression
  |   tableExpression [ CROSS | OUTER ] APPLY tableExpression

joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'

tableReference:
      tablePrimary
      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]

tablePrimary:
      [ [ catalogName . ] schemaName . ] tableName
      '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'

columnDecl:
      column type [ NOT NULL ]

values:
      VALUES expression [, expression ]*

groupItem:
      expression
  |   '(' ')'
  |   '(' expression [, expression ]* ')'
  
SELECT UNION [ALL | DISTINCT] SELECT ...

单表,全局表

会转发sql

对于分片表

生成的sql模板会带有for update语句,在涉及多个存储节点的时候,sql执行的锁的范围比所需的大,所以尽量编写查询单节点的sql

不支持select into outfile

不支持select use/ignore index

不支持 STRAIGHT_JOIN和 NATURAL JOIN

不支持有歧义的别名

projectItem被group by的引用的无聚合函数的字段需要使用any_value

支持可下推的关联子查询,不可下推的关联子查询会在执行的时候报错,或者explain能看出 Correlated 字样

groupby,having,order,limit中的子查询会被上拉优化来实现支持

子查询需要带有别名