greptimedb/tests/cases/standalone/common/system/starrocks_compatibility.result

-- Test file for StarRocks External Catalog MySQL Compatibility
-- This test simulates the exact queries StarRocks JDBC connector sends
-- Reference: MysqlSchemaResolver.java in StarRocks
-- Setup: Create test table with partitions
CREATE TABLE test_partitions (
    ts TIMESTAMP TIME INDEX,
    host STRING PRIMARY KEY,
    val DOUBLE
) PARTITION ON COLUMNS (host) ();

Affected Rows: 0

INSERT INTO test_partitions VALUES
    ('2024-01-01 00:00:00', 'host1', 1.0),
    ('2024-01-01 00:00:00', 'host2', 2.0);

Affected Rows: 2

-- ============================================
-- Section 1: JDBC DatabaseMetaData API queries
-- ============================================
-- getCatalogs() -> SHOW DATABASES
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| greptime_private   |
| information_schema |
| public             |
+--------------------+

-- getTables(db, null, null, types) with backtick quoting
SHOW FULL TABLES FROM `public` LIKE '%';

+------------------+-----------------+
| Tables_in_public | Table_type      |
+------------------+-----------------+
| numbers          | LOCAL TEMPORARY |
| test_partitions  | BASE TABLE      |
+------------------+-----------------+

-- getColumns(db, null, tbl, "%") with backtick quoting
SHOW FULL COLUMNS FROM `test_partitions` FROM `public` LIKE '%';

+-------+--------------+-----------+------+------------+---------+---------+---------------+-------+----------------------+
| Field | Type         | Collation | Null | Key        | Default | Comment | Privileges    | Extra | Greptime_type        |
+-------+--------------+-----------+------+------------+---------+---------+---------------+-------+----------------------+
| host  | string       | utf8_bin  | Yes  | PRI        |         |         | select,insert |       | String               |
| ts    | timestamp(3) |           | No   | TIME INDEX |         |         | select,insert |       | TimestampMillisecond |
| val   | double       |           | Yes  |            |         |         | select,insert |       | Float64              |
+-------+--------------+-----------+------+------------+---------+---------+---------------+-------+----------------------+

-- ============================================
-- Section 2: INFORMATION_SCHEMA queries
-- ============================================
-- Schema listing (alternative to SHOW DATABASES)
SELECT catalog_name, schema_name FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'pg_catalog')
ORDER BY schema_name;

+--------------+------------------+
| catalog_name | schema_name      |
+--------------+------------------+
| greptime     | greptime_private |
| greptime     | public           |
+--------------+------------------+

-- Tables listing
SELECT table_catalog, table_schema, table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions';

+---------------+--------------+-----------------+------------+
| table_catalog | table_schema | table_name      | table_type |
+---------------+--------------+-----------------+------------+
| greptime      | public       | test_partitions | BASE TABLE |
+---------------+--------------+-----------------+------------+

-- Columns listing
SELECT table_schema, table_name, column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions'
ORDER BY ordinal_position;

+--------------+-----------------+-------------+--------------+-------------+
| table_schema | table_name      | column_name | data_type    | is_nullable |
+--------------+-----------------+-------------+--------------+-------------+
| public       | test_partitions | ts          | timestamp(3) | No          |
| public       | test_partitions | host        | string       | Yes         |
| public       | test_partitions | val         | double       | Yes         |
+--------------+-----------------+-------------+--------------+-------------+

-- ============================================
-- Section 3: StarRocks Partition Queries
-- These are the specific queries StarRocks sends for partition metadata
-- ============================================
-- List partition names (what StarRocks uses for partition identification)
SELECT PARTITION_DESCRIPTION as NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions'
  AND PARTITION_NAME IS NOT NULL
  AND (PARTITION_METHOD = 'RANGE' or PARTITION_METHOD = 'RANGE COLUMNS')
ORDER BY PARTITION_DESCRIPTION;

+------+
| name |
+------+
|      |
+------+

-- Get partition columns (StarRocks uses this to identify partition key)
SELECT DISTINCT PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions'
  AND PARTITION_NAME IS NOT NULL
  AND (PARTITION_METHOD = 'RANGE' or PARTITION_METHOD = 'RANGE COLUMNS')
  AND PARTITION_EXPRESSION IS NOT NULL;

+----------------------+
| partition_expression |
+----------------------+
| host                 |
+----------------------+

-- Get partitions with modification time (uses IF() function for NULL handling)
-- StarRocks uses this for cache invalidation
-- SQLNESS REPLACE (\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}) DATETIME
SELECT PARTITION_NAME,
       IF(UPDATE_TIME IS NULL, CREATE_TIME, UPDATE_TIME) AS MODIFIED_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions'
  AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_NAME;

+----------------+---------------------+
| partition_name | modified_time       |
+----------------+---------------------+
| p0             | DATETIME |
+----------------+---------------------+

-- Get table modification time (for non-partitioned tables, StarRocks uses this)
-- SQLNESS REPLACE (\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}) DATETIME
SELECT TABLE_NAME AS NAME,
       IF(UPDATE_TIME IS NULL, CREATE_TIME, UPDATE_TIME) AS MODIFIED_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions';

+-----------------+---------------------+
| name            | modified_time       |
+-----------------+---------------------+
| test_partitions | DATETIME |
+-----------------+---------------------+

-- ============================================
-- Section 4: Raw PARTITIONS data inspection
-- Verify GreptimeDB returns appropriate partition metadata
-- ============================================
-- Show what GreptimeDB returns for PARTITIONS
-- SQLNESS REPLACE (\d{13,}) REGION_ID
SELECT table_schema, table_name, partition_name, partition_method,
       partition_expression, partition_description, greptime_partition_id
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_partitions';

+--------------+-----------------+----------------+------------------+----------------------+-----------------------+-----------------------+
| table_schema | table_name      | partition_name | partition_method | partition_expression | partition_description | greptime_partition_id |
+--------------+-----------------+----------------+------------------+----------------------+-----------------------+-----------------------+
| public       | test_partitions | p0             | RANGE            | host                 |                       | REGION_ID         |
+--------------+-----------------+----------------+------------------+----------------------+-----------------------+-----------------------+

-- ============================================
-- Section 5: IF() function tests with timestamps
-- StarRocks heavily uses IF() for NULL timestamp handling
-- ============================================
SELECT IF(1, 'yes', 'no') as result;

+--------+
| result |
+--------+
| yes    |
+--------+

SELECT IF(0, 'yes', 'no') as result;

+--------+
| result |
+--------+
| no     |
+--------+

SELECT IF(NULL, 'yes', 'no') as result;

+--------+
| result |
+--------+
| no     |
+--------+

-- Cleanup
DROP TABLE test_partitions;

Affected Rows: 0