Supported SQL via the Infobright Optimizer

Select Statements

SELECT [ ALL | DISTINCT | DISTINCTROW ]
SELECT expr,
AS alias_name
FROM table reference
WHERE where condition
GROUP BY {col_name | expr | position}
HAVING where_condition
ORDER BY {col_name | expr | position} ASC|DESC
ORDER BY NULL
LIMIT { [ offset,] row_count | row_count OFFSET offset}
INTO OUTFILE file_name export_options
JOIN SYNTAX – all supported
UNION SYNTAX – all supported (except Row Subqueries)

Control Flow Functions

CASE
IF
IFNULL
NULLIF

String Functions

ASCII
BIN
BIT_LENGTH
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT_WS
CONCAT
ELT
EXPORT_SET
FIELD
FIND_IN_SET
FORMAT
HEX
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MID
NOT REGEXP
OCTET_LENGTH
ORD
POSITION
QUOTE
REGEXP
REPEAT
REPLACE
REVERSE
RIGHT
RLIKE
RPAD
RTRIM
SOUNDEX
SPACE
STRCMP
SUBSTR
SUBSTRING_INDEX
SUBSTRING
TRIM
UCASE
UPPER

Group By Aggregate Functions

AVG
COUNT
MIN
MAX
SUM
STDDEV
STDDEV_SAMP
STDDEV_POP
VARRIANCE
VAR_SAMP
VAR_POP

Arithmetic Operators

addition +
subtraction -
multiplication *
division /
modulo %

Comparison Functions and Operators

equal =
not equal <>, !=
less than or equal <=
less than <
greater than or equal >=
greater than >
IS NULL
IS NOT NULL
BETWEEN ... AND (except in join conditions)
NOT BETWEEN ... AND
IN
NOT IN

Logical Operators

NOT, ! (except in join conditions)
AND, &&
OR, | |

Mathematical Functions

ABS
ACOS
ASIN
ATAN2, ATAN
ATAN
CEIL
CEILING
CONV
COS
COT
DEGREES
EXP
FLOOR
LN
LOG10
LOG2
LOG
MOD
OCT
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE

Date and Time Functions

ADDDATE+
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE_ADD
DATE_FORMAT
DATE_SUB
DATE
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_UNIXTIME
HOUR
LOCALTIME
LOCALTIMESTAMP
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SUBDATE
SUBTIME
SYSDATE
TIME_FORMAT
TIME
TO_DAYS
UNIX_TIMESTAMP
WEEK
YEARWEEK

Cast Functions and Operators

CAST
CONVERT

Data Manipulation Statements

LOAD DATA INFILE
SELECT (see detailed list)
INSERT
UPDATE
DELETE

Data Definition Statements

CREATE DATABASE
CREATE TABLE
DROP DATABASE
DROP TABLE