Direct Support for MySQL Syntax

1. Variable Definition

mysql_b=# alter database mysql_b set enable_set_variable_b_format = on;
ALTER DATABASE
mysql_b=# set @var_name := 123;
SET
mysql_b=# select @var_name;
 @var_name
-----------
       123
(1 row)

2. DEFINER Definition

mysql_b=# create definer=bus_user view test_definer as select * from atest limit 5;
CREATE VIEW

3. AUTO_INCREMENT Column

mysql_b=# create table test_AUTO_INCREMENT
mysql_b-# (id int AUTO_INCREMENT,
mysql_b(# val varchar(10),
mysql_b(# PRIMARY KEY (id)
mysql_b(# );
NOTICE:  CREATE TABLE will create implicit sequence "test_auto_increment_id_seq" for serial column "test_auto_increment.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_auto_increment_pkey" for table "test_auto_increment"
CREATE TABLE
mysql_b=# insert into test_AUTO_INCREMENT(val) values ('a');
INSERT 0 1
mysql_b=# insert into test_AUTO_INCREMENT(val) values ('b');
INSERT 0 1
mysql_b=# select * from test_AUTO_INCREMENT;
 id | val
----+-----
  1 | a
  2 | b
(2 rows)

4. Group By Multiple Columns

Error:

ERROR:  column "su.user_type" must appear in the GROUP BY clause or be used in an aggregate function

Solution:

show dolphin.sql_mode;

Disable sql_mode_full_group:

alter database database_test3 set dolphin.sql_mode = 'sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length,auto_recompile_function,error_for_division_by_zero';

5. Implicit Type Conversion

In GBase 8c, incompatible field types will result in a warning for implicit conversion:

select '2020-05-22' - interval '22 day';

Solution:

alter database database_test1_new set dolphin.b_compatibility_mode = on;

Before revision:

After revision:

6. LIKE Matching Function

In MySQL, default collation makes string values case-insensitive for "=" and "LIKE". Accurate string matching requires the "binary" keyword.

Solution:

set B_COMPATIBILITY_MODE = on;
SELECT 'a' like 'A';
SELECT 'a' like binary 'A';

7. ENGINE, CHARSET, COLLATE Keywords in Table Creation

GBase 8c syntax supports MySQL's ENGINE, CHARSET, and COLLATE keywords in CREATE TABLE.

8. dolphin.sql_mode Introduction

Default Value:

‘sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length’

Parameter Explanation:

• sql_mode_strict: Handles data type conversions and default values for non-null columns.
• sql_mode_full_group: Ensures columns in SELECT not using aggregate functions must appear in GROUP BY.
• pipes_as_concat: Controls whether || is treated as a concatenation operator or logical OR.
• ansi_quotes: Treats double quotes as object references instead of string literals.
• no_zero_date: Controls the legality of '0000-00-00' as a date.
• pad_char_to_full_length: Controls whether trailing spaces are removed in CHAR type queries.

Indirect Support for MySQL Syntax

1. Interval Function with Expressions

GBase 8c does not support expressions within functions directly (e.g., select (curdate() - interval (weekday(curdate()) + 7) day)).

Solution:
Extract the function from the interval, and change to:
(solution idea: first calculate the result in the expression, and then convert the unit by multiplying with interval '1 day')

SELECT (CURRENT_DATE - (weekday(curdate()) + 7) * INTERVAL '1 day');

2. Collation Determination Error

Error:

ERROR:  could not determine which collation to use for view column "check_name"
HINT:  Use the COLLATE clause to set the collation explicitly.

Solution:
Add COLLATE utf8mb4_general_ci to the specified fields.

3. Explicit Type Conversion

GBase 8c automatically converts right function parameters to bigint.

Error:

ERROR:  function right(character varying, bigint) does not exist

Solution:
Add explicit type conversion.

Before revision:

After revision:

4. Column Alias Conflicts with GBase 8c Keywords

Solution:
Add double quotes around the conflicting keyword.

Before revision:

After revision:

5. Union of Multiple Query Result Sets

Solution:
Ensure consistent character set collation for all tables.