SQL Mode¶

sql_mode is a system parameter in MatrixOne that specifies the mode in which MatrixOne performs queries and operations. sql_mode can affect the syntax and semantic rules of MatrixOne, changing the behavior of MatrixOne queries for SQL. In this article, you will be introduced to the mode of sql_mode, what it does, and how to set SQL mode.

By default, sql_mode includes ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, and STRICT_TRANS_TABLES. MatrixOne accepts multiple MySQL-compatible SQL mode names, but only a tested subset changes parser or execution behavior. Do not assume that every accepted mode implements all MySQL semantics.

Effective mode behavior¶

Mode	MatrixOne behavior
`ANSI_QUOTES`	Treats double-quoted tokens as identifiers instead of string literals.
`PIPES_AS_CONCAT`	Treats `
`STRICT_TRANS_TABLES` / `STRICT_ALL_TABLES`	Enables strict assignment and conversion checks in supported DML and DDL paths, including width, numeric, and temporal conversions. Coverage is not identical to MySQL for every data type.
`NO_ZERO_DATE`	Changes zero-date/zero-temporal write handling only when combined with `STRICT_TRANS_TABLES` or `STRICT_ALL_TABLES`. `NO_ZERO_DATE` alone, or a strict mode without `NO_ZERO_DATE`, does not enable this combined rejection behavior.
`ERROR_FOR_DIVISION_BY_ZERO`	Together with a strict mode, makes division by zero in non-`IGNORE` `INSERT`/`UPDATE` operations an error. A plain `SELECT` still returns `NULL`; `INSERT IGNORE`/`UPDATE IGNORE` do not raise that error.
`ONLY_FULL_GROUP_BY`	When `ONLY_FULL_GROUP_BY` is enabled, nonaggregate columns must be listed in `GROUP BY`. MatrixOne does not currently implement MySQL’s functional-dependency or single-value `WHERE` exceptions for this check. When `ONLY_FULL_GROUP_BY` is absent, bare columns use MySQL-compatible `ANY_VALUE` handling; `MATRIXONE_NATIVE` does not change this grouping rule.
`MATRIXONE_NATIVE`	Enables the MatrixOne-specific behavior described below.

Other accepted mode names can be used in sql_mode, but their complete MySQL behavior is not guaranteed unless it is documented and verified separately.

MATRIXONE_NATIVE¶

MATRIXONE_NATIVE is a MatrixOne-specific compatibility mode. It currently changes at least these behaviors:

It permits MatrixOne-native DML constructs that MySQL rejects, such as a window function in an UPDATE assignment. It does not make window functions valid in row-local CHECK constraints.
It requires string-to-number conversions to consume a complete valid token instead of accepting a numeric prefix. MatrixOne numeric extensions such as 0x, 0b, and 0o prefixes remain available when the token is valid.

Setting sql_mode replaces the complete mode list. For example, SET SESSION sql_mode = 'MATRIXONE_NATIVE' also removes the default ONLY_FULL_GROUP_BY mode, so permissive grouping behavior applies. Including both modes does not change the ONLY_FULL_GROUP_BY grouping rule; MATRIXONE_NATIVE affects other compatibility behavior, not functional-dependency or single-value WHERE exceptions.

SET sql_mode = CONCAT(@@sql_mode, ',MATRIXONE_NATIVE');

View sql_mode¶

View sql_mode in MatrixOne using the following command:

SELECT @@global.sql_mode; 
-- global mode
SELECT @@session.sql_mode; 
-- session mode

Set sql_mode¶

Set sql_mode in MatrixOne using the following command:

-- Global mode; takes effect for new connections.
SET GLOBAL sql_mode = 'xxx';
-- Session mode; takes effect in the current session.
SET SESSION sql_mode = 'xxx';

Examples¶

DROP DATABASE IF EXISTS sql_mode_demo;
CREATE DATABASE sql_mode_demo;
USE sql_mode_demo;

CREATE TABLE student (
    id INT,
    name CHAR(20),
    age INT,
    nation CHAR(20)
);

INSERT INTO student VALUES (1,'tom',18,'shanghai'),(2,'jan',19,'shanghai'),(3,'jen',20,'beijing'),(4,'bob',20,'beijing'),(5,'tim',20,'guangzhou');

-- Fails while ONLY_FULL_GROUP_BY is enabled.
-- Expected-Success: false
SELECT * FROM student GROUP BY nation;

SET session sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES';

-- Succeeds once ONLY_FULL_GROUP_BY is turned off for the current session.
SELECT * FROM student GROUP BY nation;

DROP DATABASE sql_mode_demo;