MariaDB behaves differently from MySQL, PostgreSQL, and SQLite in ways that regularly surprise developers. These are the most important differences — sourced from official MariaDB documentation.
MariaDB's CONCAT propagates NULL — if any argument is NULL the entire result is NULL.
✗ Anti-Pattern
SELECT CONCAT(first_name, ' ', last_name) FROM users; -- Returns NULL for any row where either column is NULL
→ NULL (if any argument is NULL)
✓ Correct Approach
SELECT CONCAT_WS(' ', first_name, last_name) FROM users;
-- CONCAT_WS skips NULL arguments and joins the rest→ Non-null parts joined by the separator
NOW() returns the timestamp when the current statement began; SYSDATE() returns the actual clock time at the moment it is called.
✗ Anti-Pattern
-- Assuming a slow stored procedure loop SELECT SYSDATE(); -- returns wall-clock time, not statement start -- Every iteration may return a different timestamp
→ Varies with actual execution time — unpredictable in loops or replication
✓ Correct Approach
SELECT NOW(); -- consistent within a single statement -- Or: SELECT CURRENT_TIMESTAMP;
→ Same timestamp for every call within the same statement
The default collation latin1_swedish_ci (or utf8mb4_general_ci) makes WHERE and UNIQUE comparisons case-insensitive.
✗ Anti-Pattern
SELECT * FROM users WHERE username = 'Admin'; -- Also matches rows where username is 'admin', 'ADMIN', etc.
→ Rows where username is 'admin', 'Admin', 'ADMIN', …
✓ Correct Approach
SELECT * FROM users WHERE username = BINARY 'Admin'; -- Or define the column with a _bin collation at table creation
→ Only rows where username is exactly 'Admin'
MariaDB does not enforce ONLY_FULL_GROUP_BY by default, so you can SELECT columns not in the GROUP BY without an aggregate function.
✗ Anti-Pattern
SELECT department, employee_name, COUNT(*) FROM employees GROUP BY department; -- employee_name is not in GROUP BY and not aggregated
→ An arbitrary employee_name from each department group — result is non-deterministic
✓ Correct Approach
SELECT department, ANY_VALUE(employee_name), COUNT(*) FROM employees GROUP BY department; -- ANY_VALUE() makes the intent explicit
→ One employee name per department (deterministically acknowledged as arbitrary)
TRUE and FALSE are aliases for 1 and 0. Any TINYINT(1) column accepts values outside this range.
✗ Anti-Pattern
UPDATE flags SET active = 2; -- Stores fine SELECT * FROM flags WHERE active = TRUE; -- Misses rows where active = 2
→ Rows where active = 2 are not returned despite being non-zero
✓ Correct Approach
SELECT * FROM flags WHERE active != 0; -- Truthy check -- Or enforce the constraint: CHECK (active IN (0, 1))
→ All rows with a non-zero active value
When a transaction that inserts a row is rolled back, the AUTO_INCREMENT counter is not rolled back — the sequence value is consumed permanently.
✗ Anti-Pattern
START TRANSACTION; INSERT INTO orders (customer_id) VALUES (42); -- Gets id = 101 ROLLBACK; INSERT INTO orders (customer_id) VALUES (42); -- Gets id = 102, not 101
→ Gap at id = 101; next insert gets 102
✓ Correct Approach
-- Accept that AUTO_INCREMENT IDs are not guaranteed to be gapless. -- If a gapless sequence is required, use a separate sequence table -- with SELECT ... FOR UPDATE to manage allocation.
→ Gaps are normal and expected — do not rely on AUTO_INCREMENT for gapless sequences
The first TIMESTAMP column in a table definition gets DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP applied automatically unless you specify otherwise.
✗ Anti-Pattern
CREATE TABLE events ( created_at TIMESTAMP, -- auto-gets DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP title VARCHAR(200) ); UPDATE events SET title = 'New title' WHERE id = 1; -- created_at is now overwritten with the current time
→ created_at changes on every UPDATE even though it was intended as a creation timestamp
✓ Correct Approach
CREATE TABLE events ( created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- explicit, no ON UPDATE updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, title VARCHAR(200) );
→ created_at is set once on insert and never changed; updated_at tracks modifications
MariaDB (and standard SQL) allows multiple NULL values in a UNIQUE indexed column because NULL is not equal to NULL.
✗ Anti-Pattern
-- Expecting only one NULL allowed: INSERT INTO accounts (email) VALUES (NULL); -- OK INSERT INTO accounts (email) VALUES (NULL); -- Also OK — no duplicate violation
→ Both inserts succeed; two rows have NULL email despite the UNIQUE constraint
✓ Correct Approach
-- This is correct and expected behaviour per the SQL standard. -- If you need at most one NULL, add a filtered unique index via application logic -- or use a sentinel value and a CHECK constraint instead of NULL.
→ Multiple NULLs in a UNIQUE column are permitted and intentional
The / operator performs floating-point division even between two integers. Use DIV for integer (truncating) division.
✗ Anti-Pattern
SELECT 7 / 2; -- Returns 3.5000, not 3 SELECT total_seconds / 60 FROM logs; -- Returns DOUBLE even if total_seconds is INT
→ 3.5000 — floating-point result, not an integer
✓ Correct Approach
SELECT 7 DIV 2; -- Returns 3 (integer truncation) SELECT total_seconds DIV 60 FROM logs;
→ 3 — integer result with remainder discarded
Inserting an ENUM value in a different case from its definition still succeeds and the value is normalised to the defined case.
✗ Anti-Pattern
CREATE TABLE users (status ENUM('Active', 'Inactive'));
INSERT INTO users (status) VALUES ('active'); -- Does not error→ 'active' is silently stored as 'Active' — no error or warning
✓ Correct Approach
-- Understand that ENUM normalises case automatically. -- If strict case enforcement matters, validate at the application layer -- or use a CHECK constraint with a VARCHAR column instead.
→ Insert succeeds and stores 'Active' — be aware of the silent normalisation
Defining a DEFAULT on a TEXT, BLOB, or any large object column raises an error in MariaDB (prior to 10.2.1 it was silently ignored).
✗ Anti-Pattern
CREATE TABLE articles ( body TEXT DEFAULT '' -- ERROR in MariaDB );
→ ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value
✓ Correct Approach
CREATE TABLE articles ( body TEXT -- omit DEFAULT; handle NULL at application level ); -- Or use VARCHAR(5000) DEFAULT '' for shorter content
→ Table created successfully; application sets default content on insert
The shorthand JSON path operators -> (extract as JSON) and ->> (extract as text) are only available from MariaDB 10.4.3 onwards.
✗ Anti-Pattern
-- On MariaDB < 10.4.3: SELECT data->'$.name' FROM records; -- Syntax error
→ ERROR: You have an error in your SQL syntax
✓ Correct Approach
-- Compatible with all MariaDB versions that support JSON: SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) FROM records; -- Or on MariaDB 10.4.3+: SELECT data->>'$.name' FROM records;
→ The extracted string value of the 'name' JSON field
MariaDB's REGEXP/RLIKE operator ignores case unless the BINARY keyword or a binary collation is used.
✗ Anti-Pattern
SELECT * FROM products WHERE name REGEXP 'widget'; -- Also matches 'Widget', 'WIDGET', 'wIdGeT'
→ Rows where name contains 'widget' in any combination of cases
✓ Correct Approach
SELECT * FROM products WHERE name REGEXP BINARY 'widget'; -- Only matches lowercase 'widget'
→ Only rows where name contains the exact lowercase string 'widget'
When comparing a string column to an integer, MariaDB silently casts the string to a number — leading characters that are not digits become 0.
✗ Anti-Pattern
SELECT * FROM codes WHERE code = 0; -- code is VARCHAR; 'abc', 'xyz', '' all coerce to 0 -- Returns all rows where the code string starts with a non-digit character
→ Matches every row where code is a non-numeric string (coerced to 0)
✓ Correct Approach
SELECT * FROM codes WHERE code = '0'; -- compare as strings -- Or explicitly cast: WHERE CAST(code AS SIGNED) = 0
→ Only rows where code is the literal string '0'
MariaDB does not allow LIMIT in certain subquery contexts, particularly in IN/ALL/ANY/SOME subqueries, which is a known MySQL/MariaDB restriction.
✗ Anti-Pattern
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers ORDER BY signup_date DESC LIMIT 10 ); -- ERROR 1235: LIMIT not supported in IN subquery
→ ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
✓ Correct Approach
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM (
SELECT id FROM customers ORDER BY signup_date DESC LIMIT 10
) AS top_customers
);→ Orders belonging to the 10 most recently signed-up customers
MariaDB is largely MySQL-compatible but adds window functions, better JSON support, Oracle compatibility modes, and Aria storage engine. The Data Types page covers storage differences. Every function page includes a "vs MySQL" and "vs PostgreSQL" section.