create schema m_testcase_fix;
set current_schema to 'm_testcase_fix';
create table t1 (a char(2) character set binary);
insert into t1 values ('aa'), ('bb');
select * from t1 where a in (NULL, 'aa');
a
--------
\x6161
(1 row)
select * from t1 where a in ('bb', NULL, 'aa');
a
--------
\x6161
\x6262
(2 rows)
drop table t1;
set dolphin.sql_mode = '';
CREATE TABLE t1
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name TINYBLOB NOT NULL,
modified TIMESTAMP DEFAULT '0000-00-00 00:00:00',
INDEX namelocs (name(255))) ENGINE = InnoDB
PARTITION BY HASH(id) PARTITIONS 2;
NOTICE: CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
reset dolphin.sql_mode;
insert into t1 values (1, 'aa', '2024-10-01 10:10:10');
select * from t1 where name = '\x6161';
id | name | modified
----+--------+------------------------
1 | \x6161 | 2024-10-01 10:10:10-07
(1 row)
drop table t1;
SELECT SUBTIME(120120519090607 | 120120519090607 ,'1 1:1:1.000002');
WARNING: timestamp out of range
CONTEXT: referenced column: subtime
WARNING: date/time field value out of range
CONTEXT: referenced column: subtime
WARNING: date/time field overflow
CONTEXT: referenced column: subtime
subtime
---------
(1 row)
CREATE TABLE t1(a INT) ENGINE=MyISAM;
CREATE TABLE t2(b INT);
CREATE TABLE t3(c INT);
CREATE TABLE t4(d INT);
CREATE VIEW v1 AS SELECT * FROM t2;
RENAME TABLES t1 TO t5, v1 TO v2;
drop view v2;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_a_key" for table "t1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_b_key" for table "t1"
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
insert into t1 values (1, 1, 1);
insert into t1 values (2, 2, 2);
insert into t2 values (1, 1, 1, 1);
insert into t2 values (2, 2, 2, 2);
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
select * from t1;
a | b | c
---+---+---
1 | 1 | 1
2 | 2 | 6
(2 rows)
drop table t1;
drop table t2;
-- decimal(0)
create table t11(c DECIMAL(0,0));
insert into t11 values (9999999999);
insert into t11 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t12(c DECIMAL(0));
insert into t12 values (9999999999);
insert into t12 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t21(c numeric(0,0));
insert into t21 values (9999999999);
insert into t21 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t22(c numeric(0));
insert into t22 values (9999999999);
insert into t22 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t31(c fixed(0,0));
insert into t31 values (9999999999);
insert into t31 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t32(c fixed(0));
insert into t32 values (9999999999);
insert into t32 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t41(c dec(0,0));
insert into t41 values (9999999999);
insert into t41 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
create table t42(c dec(0));
insert into t42 values (9999999999);
insert into t42 values (10000000000);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 0 must round to an absolute value less than 10^10.
CONTEXT: referenced column: c
\d+ t11;
Table "m_testcase_fix.t11"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t12;
Table "m_testcase_fix.t12"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t21;
Table "m_testcase_fix.t21"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t22;
Table "m_testcase_fix.t22"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t31;
Table "m_testcase_fix.t31"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t32;
Table "m_testcase_fix.t32"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t41;
Table "m_testcase_fix.t41"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
\d+ t42;
Table "m_testcase_fix.t42"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+---------+--------------+-------------
c | numeric(10,0) | | main | |
Has OIDs: no
Options: orientation=row, compression=no, collate=1537
Character Set: UTF8
Collate: utf8mb4_general_ci
drop table t11;
drop table t12;
drop table t21;
drop table t22;
drop table t31;
drop table t32;
drop table t41;
drop table t42;
CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200), b TEXT ) ENGINE = InnoDB;
NOTICE: CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE FULLTEXT INDEX idx on t1 (a,b);
insert into t1(a, b) values('test1', 'test2');
insert into t1(a, b) values('test3', 'test1');
insert into t1(a, b) values('test1', 'test1');
insert into t1(a, b) values(NULL, NULL);
SELECT id FROM t1 WHERE MATCH (a,b) AGAINST ('test1' IN NATURAL LANGUAGE MODE);
id
----
1
2
3
(3 rows)
SELECT id FROM t1 WHERE MATCH (a,b) AGAINST (NULL IN NATURAL LANGUAGE MODE);
id
----
(0 rows)
drop table t1;
CREATE TABLE test_ps_sample_pages_used (
a VARCHAR(512), PRIMARY KEY (a)
) ENGINE=INNODB STATS_SAMPLE_PAGES=default;
WARNING: STATS_SAMPLE_PAGES for TABLE is not supported for current version. skipped
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_ps_sample_pages_used_pkey" for table "test_ps_sample_pages_used"
drop table test_ps_sample_pages_used;
-- ONE
select json_contains_path('{ "a": true }', 'ONE', '$.a','$.b' );
json_contains_path
--------------------
t
(1 row)
select json_contains_path('{ "a": true }', 'one', '$.a','$.b' );
json_contains_path
--------------------
t
(1 row)
select json_contains_path('{ "a": true }', 'ALL', '$.a','$.b' );
json_contains_path
--------------------
f
(1 row)
select json_contains_path('{ "a": true }', 'all', '$.a','$.b' );
json_contains_path
--------------------
f
(1 row)
-- var
set enable_set_variable_b_format = on;
set @c:=1;
with recursive q (b, c) as
(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
select sum(b*c) from q;
sum
------------
2668667000
(1 row)
reset enable_set_variable_b_format;
-- window
CREATE TABLE t(a INTEGER);
INSERT INTO t VALUES (1),(2),(3),(4);
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775807 FOLLOWING AND 9223372036854775807 FOLLOWING) as sum FROM t;
sum
-----
(4 rows)
drop table t;
drop schema m_testcase_fix cascade;
reset current_schema;