greptimedb/tests/cases/standalone/common/prepare/mysql_prepare.result

-- invalid prepare, from
-- https://github.com/duckdb/duckdb/blob/00a605270719941ca0412ad5d0a14b1bdfbf9eb5/test/sql/prepared/invalid_prepare.test
-- SQLNESS PROTOCOL MYSQL
SELECT ?;

Failed to execute query, err: MySqlError { ERROR 1815 (HY000): (EngineExecuteQuery): Execution error: Placeholder '?' was not provided a value for execution. }

-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT ?::int;';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 1;

+----+
| $1 |
+----+
| 1  |
+----+

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 'a';

Failed to execute query, err: MySqlError { ERROR 1210 (HY000): (InvalidArguments): Invalid request parameter: Unable to convert a to datatype Int32(Int32Type) }

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT ?::int WHERE 1=0;';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 1;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 'a';

Failed to execute query, err: MySqlError { ERROR 1210 (HY000): (InvalidArguments): Invalid request parameter: Unable to convert a to datatype Int32(Int32Type) }

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- parameter variants, from:
-- https://github.com/duckdb/duckdb/blob/2360dd00f193b5d0850f9379d0c3794eb2084f36/test/sql/prepared/parameter_variants.test
-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT CAST(? AS INTEGER), CAST(? AS STRING);';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 1, 'hello';

+----+-------+
| $1 | $2    |
+----+-------+
| 1  | hello |
+----+-------+

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- test if placeholder at limit and offset are parsed correctly
-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT 1 LIMIT ? OFFSET ?;';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 1, 2;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- test with data
-- A Mito scan exposes string primary keys as dictionaries, but that physical read type must not
-- leak into INSERT Values planning.
CREATE TABLE insert_values_dictionary (
    ts TIMESTAMP TIME INDEX,
    host STRING PRIMARY KEY
) ENGINE=mito;

Affected Rows: 0

INSERT INTO insert_values_dictionary VALUES(now(), 'a');

Affected Rows: 1

SELECT host FROM insert_values_dictionary;

+------+
| host |
+------+
| a    |
+------+

SELECT array_agg(host) FROM insert_values_dictionary;

+------------------------------------------+
| array_agg(insert_values_dictionary.host) |
+------------------------------------------+
| [a]                                      |
+------------------------------------------+

DROP TABLE insert_values_dictionary;

Affected Rows: 0

CREATE TABLE IF NOT EXISTS "cake" (
    `domain` STRING,
    is_expire BOOLEAN NULL,
    ts TIMESTAMP(3),
    TIME INDEX ("ts"),
    PRIMARY KEY ("domain")
) ENGINE=mito
WITH(
    append_mode = 'true',
    ttl='7days'
);

Affected Rows: 0

INSERT INTO cake(domain, is_expire, ts) VALUES('happy', false, '2025-03-18 12:55:51.758000');

Affected Rows: 1

-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT `cake`.`domain`, `cake`.`is_expire`, `cake`.`ts` FROM `cake` WHERE `cake`.`domain` = ? LIMIT ? OFFSET ?';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 'happy', 42, 0;

+--------+-----------+-------------------------+
| domain | is_expire | ts                      |
+--------+-----------+-------------------------+
| happy  | 0         | 2025-03-18 12:55:51.758 |
+--------+-----------+-------------------------+

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
PREPARE stmt FROM 'SELECT table_name, table_schema FROM information_schema.tables WHERE table_name = ?';

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
EXECUTE stmt USING 'cake';

+------------+--------------+
| table_name | table_schema |
+------------+--------------+
| cake       | public       |
+------------+--------------+

-- SQLNESS PROTOCOL MYSQL
DEALLOCATE stmt;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
DROP TABLE cake;

affected_rows: 0