Hologres:Migrate MySQL data to Hologres

Quotation marks

Hologres is case-insensitive by default. To reference a case-sensitive identifier, wrap it in double quotation marks ("").

Replace MySQL backtick syntax with double quotes:

-- MySQL
SELECT `A` FROM b;

-- Hologres
SELECT "A" FROM b;

Filter conditions

Hologres requires exact type matching in filter conditions and does not perform implicit type conversion. If the types don't match, you'll get an error such as:

operator does not exist: text = integer

To resolve this, create custom type casts:

CREATE CAST (TEXT AS INTEGER) WITH INOUT AS IMPLICIT;
CREATE CAST (TEXT AS BIGINT) WITH INOUT AS IMPLICIT;
CREATE CAST (TEXT AS DECIMAL) WITH INOUT AS IMPLICIT;
CREATE CAST (TEXT AS TIMESTAMP) WITH INOUT AS IMPLICIT;
CREATE CAST (NUMERIC AS TEXT) WITH INOUT AS IMPLICIT;

Example: If the ds column is TEXT and you filter with an integer value, the following query fails in Hologres:

SELECT * FROM business_module WHERE ds = 20210329;

After creating the appropriate cast, the query runs without error.

Pagination

Replace the MySQL two-argument LIMIT syntax with the standard OFFSET/LIMIT form:

-- MySQL
LIMIT 0, 10

-- Hologres
OFFSET 0 LIMIT 10

Sorting

MySQL and Hologres differ in how they sort NULL values in ascending order:

To get consistent results, specify the NULL sort order explicitly:

ORDER BY column DESC NULLS LAST

Grouping

Hologres does not support GROUP BY on non-exact types (FLOAT, DOUBLE) by default. Two options:

• Cast the column to DECIMAL before grouping.

• Enable the hg_experimental_enable_double_equivalent parameter:

  -- Session level
  SET hg_experimental_enable_double_equivalent = on;
  
  -- Database level
  ALTER DATABASE <database_name> SET hg_experimental_enable_double_equivalent = on;

This parameter requires Hologres V0.10 or later. If your instance runs an earlier version, contact technical support through the Hologres DingTalk group to request an upgrade. For details, see How do I get more online support?.

UNION

The UNION operator requires matching column types across all queries. MySQL performs implicit type conversion; Hologres does not. A type mismatch produces:

UNION types bigint and text cannot be matched

Cast mismatched columns explicitly:

-- Fails if project_id is BIGINT in tableA and TEXT in tableB
SELECT project_id FROM tableA
UNION ALL
SELECT project_id FROM tableB;

-- Fix: cast to a matching type
SELECT project_id FROM tableA
UNION ALL
SELECT CAST(project_id AS BIGINT) FROM tableB;

Division by zero

MySQL returns NULL for division by zero. Hologres returns an error:

ERROR: division by zero

Option 1: Use NULLIF to treat the divisor as NULL when it is zero:

SELECT a / NULLIF(b, 0) FROM table;

Option 2 (Hologres V1.3.21 and later): Enable the mysql_compatible extension to tolerate division by zero without changing individual queries:

-- Run once per database (requires Superuser)
CREATE EXTENSION IF NOT EXISTS mysql_compatible;

-- Enable division-by-zero tolerance for the current session
SET mysql_compatible.enable = on;

This covers multiple scenarios:

-- Scenario 1: Constant division of the same type
SET mysql_compatible.enable = on;
SELECT 1 / 0;

-- Scenario 2: Constant division with type conversion
SET mysql_compatible.enable = on;
SELECT 1.0 / 0;

-- Scenario 3: Dividend is a column expression
SET mysql_compatible.enable = on;
SELECT SUM(c) / 0 FROM (SELECT generate_series(1, 100) AS c) AS t;

-- Scenario 4: Divisor is a column expression
SET mysql_compatible.enable = on;
SELECT MAX(c) / SUM(d)
FROM (
  SELECT generate_series(1, 101) AS c,
         generate_series(-50, 50) AS d
) AS t;

-- Scenario 5: Tolerate division by zero during INSERT
CREATE TABLE IF NOT EXISTS test_insert_divide_by_zero (c1 INT);
SET mysql_compatible.strict_mode = off;
SET mysql_compatible.enable = on;
INSERT INTO test_insert_divide_by_zero SELECT 100 / 0.0;

If you encounter issues, see Common upgrade preparation failure errors or contact technical support through the Hologres DingTalk group. For details, see How do I obtain additional online support?.

Integer division

MySQL returns a decimal when dividing two integers with a remainder; Hologres truncates to an integer.

Cast one operand to a floating-point type to get the decimal result:

SELECT 1 / 2::FLOAT;

IF function

Hologres does not support the IF function. Replace it with a CASE WHEN expression:

-- MySQL
IF(condition, true_value, false_value)

-- Hologres
CASE WHEN condition THEN true_value ELSE false_value END

IFNULL function

Replace the MySQL IFNULL function with COALESCE:

-- MySQL
IFNULL(x, y)

-- Hologres
COALESCE(x, y)

LENGTH function

Replace the MySQL LENGTH function with CHAR_LENGTH:

-- MySQL
LENGTH(string)

-- Hologres
CHAR_LENGTH(string)

COUNT DISTINCT returns different results for multiple columns

Symptom: A multi-column COUNT DISTINCT query returns different row counts in MySQL and Hologres.

Cause: MySQL's count(distinct column_1, column_2, ...) excludes rows where any of the specified columns is NULL. Hologres's count(distinct(column_1, column_2, ...)) includes rows even when some columns are NULL, which produces a higher count.

Fix: Concatenate the columns so that any NULL propagates through the expression, matching MySQL's behavior:

SELECT count(distinct column_1 || column_2 || ...) FROM table;

Example:

CREATE TABLE count_distinct_test (
  a TEXT,
  b TEXT
);

INSERT INTO count_distinct_test VALUES
  ('a', 'b'),
  ('a', NULL),
  (NULL, 'b'),
  ('a', 'b');

-- Hologres default: includes rows with NULL columns → returns 3
SELECT count(distinct(a, b)::text) FROM count_distinct_test;

-- MySQL-compatible: NULL propagates through concatenation → returns 1
SELECT count(distinct a || b) FROM count_distinct_test;