Distinctions of YQL SQL dialect from ANSI SQL

This section describes distinctions of YQL SQL dialect and the ISO/IEC 9075-2 standard (Part 2: Foundation) in the version SQL-2011.
To make the comparison, we took table 38 from the standard
("Feature taxonomy and definition for mandatory features").

Distinctions in behavior between YQL and ANSI SQL

In this table, we've put together the scenarios where the same query produces different results in YQL and ANSI SQL.

ANSI SQL support status

E021 Character string types

Should be supported:

• CHARACTER[(len)] (aka CHAR): A string type of a fixed length.
• VAR CHAR[(len)] (aka CHARACTER VARYING): A string type of a variable length.

It should also support the optional specifier CHARACTER SET cs and setting sorting rules by COLLATE

These types aren't supported in YQL. The nearest analog is the String type
which is a binary string of a variable length.

E021-03 Character literals

According to the standard, string literals have the following format:

<character string literal> ::=
  [ <introducer> <character set specification> ]
      <quote> [ <character representation>... ] <quote>
      [ { <separator> <quote> [ <character representation>... ] <quote> }... ]
<separator> ::=
  { <comment> | <white space> }...
<introducer> ::=
  <underscore>
<character representation> ::=
    <nonquote character>
  | <quote symbol>
<quote symbol> ::=
  <quote> <quote>

In YQL:

• Escaping by ' isn't supported
• <Character set specification> isn't supported
• Implicit concatenation of literals isn't supported (according to the standard, 'aaa' 'bbb' means the same as 'aaabbb)

On the other hand:

• You can enclose string literals into double quotes ("aaa")
• C-style escaping by a backslash can also be used
• Multi-line string literals are supported

E021-05 OCTET_LENGTH function

CHARACTER_LENGTH(<character value expression>) [USING CHARACTERS | OCTETS]
OCTET_LENGTH(<string value expression>)

YQL suports LENGTH(<string value expression>) that returns the length of a string in bytes

E021-06 SUBSTRING function

<character substring function> ::=
  SUBSTRING <left paren> <character value expression> FROM <start position>
      [ FOR <string length> ] [ USING CHARACTERS | OCTETS ] <right paren>

In YQL, it's supported as SUBSTRING(str, start, len).
Character numbering within a string in YQL begins with zero

E021-09 TRIM function

 TRIM([[LEADING | TRAILING | BOTH] [<char value expression>] FROM] <char value expression>)

Deleting a prefix or suffix from a string. Not supported in YQL; you can build a functional equivalent using a String/Unicode UDF.

E021-11 POSITION function

POSITION(S1 IN S2) [USING CHARACTERS | OCTETS]

Searching a substring S1 in a string S2.

Supported in YQL in the format FIND(S2, S1). Positions are numbered beginning from 0. If the string isn't found, NULL is returned.

E031 Identifiers

According to the standard, the IDs (for example, table and column names) can be
regular identifiers and delimited identifiers.
Regular identifiers begin with a letter and can only include letters, digits, and underscores.

E031-01 Delimited identifiers

Delimited identifiers are enclosed in double quotes: "abc def" and can contain arbitrary characters.
Double quotes are escaped by double quotes: "complex ""identifier"""

Instead of double quotes, YQL uses backticks

E051 Basic query specification

According to the standard, the generic format of the SELECT statement is as follows

<query specification> ::=
  SELECT [ <set quantifier> ] <select list> <table expression>
<set quantifier> ::=
    DISTINCT
  | ALL
<select list> ::=
    <asterisk>
  | <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::=
    <derived column>
  | <qualified asterisk>
<qualified asterisk> ::=
    <asterisked identifier chain> <period> <asterisk>
  | <all fields reference>
<asterisked identifier chain> ::=
  <asterisked identifier> [ { <period> <asterisked identifier> }... ]
<asterisked identifier> ::=
  <identifier>
<derived column> ::=
  <value expression> [ <as clause> ]
<as clause> ::=
  [ AS ] <column name>
<all fields reference> ::=
  <value expression primary> <period> <asterisk>
      [ AS <left paren> <all fields column name list> <right paren> ]
<all fields column name list> ::=
  <column name list>

<table expression> ::=
  <from clause>
      [ <where clause> ]
      [ <group by clause> ]
      [ <having clause> ]
      [ <window clause> ]
<from clause> ::=
  FROM <table reference list>
<table reference list> ::=
  <table reference> [ { <comma> <table reference> }... ]

YQL doesn't support multiple tables after FROM (<table reference list> includes more than one item)

E051-01 SELECT DISTINCT

SELECT DISTINCT is supported except for the variant with asterisk/qualified asterisk

E051-07 Qualified * in select list

In YQL:

• <asterisk identifier chain> can only consist of one item
• <All fields reference> doesn't support aliases <all fields column name list>
• <Value expression primary> can only include a link to a table

E061-01 Comparison predicate

YQL supports operators <,>,=,<>,<=,>= for compatible types

E061-02 BETWEEN predicate

<between predicate> ::=
  <row value predicand> <between predicate part 2>
<between predicate part 2> ::=
  [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ]
      <row value predicand> AND <row value predicand>

YQL doesn't support SYMMETRIC/ASSYMETRIC: A BETWEEN B AND C is equivalent to A >= B AND A <= C.

E061-07 Quantified comparison predicate

<quantified comparison predicate> ::=
  <row value predicand> <quantified comparison predicate part 2>
<quantified comparison predicate part 2> ::=
  <comp op> <quantifier> <table subquery>
<quantifier> ::=
    <all>
  | <some>
<all> ::= ALL
<some> ::=
    SOME
  | ANY