1mysql -h{doris_ip} -P9030 -u{doris_user} -p{user_password}

1CREATE CATALOG `lance` PROPERTIES (
2    "type" = "lance",
3    "lance.catalog.type" = "databuilder",
4    "metastore.catalog.id" = "ac7fxxxxxxxxxxxx",
5    "lance.databuilder.catalog_name" = "yzw_catalog"
6);

1SHOW CATALOGS;

1SWITCH lance;

1SHOW DATABASES;

1USE lance_db;

1SHOW TABLES;

1desc spark_all_types;

1+----------------+-----------------------------+------+------+---------+-------+
2| Field          | Type                        | Null | Key  | Default | Extra |
3+----------------+-----------------------------+------+------+---------+-------+
4| col_bool       | boolean                     | Yes  | true | NULL    |       |
5| col_int8       | tinyint                     | Yes  | true | NULL    |       |
6| col_int16      | smallint                    | Yes  | true | NULL    |       |
7| col_int32      | int                         | Yes  | true | NULL    |       |
8| col_int64      | bigint                      | Yes  | true | NULL    |       |
9| col_float32    | float                       | Yes  | true | NULL    |       |
10| col_float64    | double                      | Yes  | true | NULL    |       |
11| col_utf8       | text                        | Yes  | true | NULL    |       |
12| col_binary     | text                        | Yes  | true | NULL    |       |
13| col_decimal    | decimal(18,6)               | Yes  | true | NULL    |       |
14| col_date       | date                        | Yes  | true | NULL    |       |
15| col_timestamp  | datetime(6)                 | Yes  | true | NULL    |       |
16| col_array      | array<int>                  | Yes  | true | NULL    |       |
17| col_fixed_list | array<float>                | No   | true | NULL    |       |
18| col_struct     | struct<name:text,value:int> | Yes  | true | NULL    |       |
19| col_map        | map<text,int>               | Yes  | true | NULL    |       |
20+----------------+-----------------------------+------+------+---------+-------+
2116 rows in set (0.00 sec)

1-- 示例1：查询表 spark_all_types 的全部字段
2SELECT * FROM spark_all_types LIMIT 0, 2;
3
4-- 示例2：指定查询部分字段（col_bool布尔型、col_int8 8位整型、col_float32单精度浮点）
5SELECT col_bool, col_int8, col_float32 FROM spark_all_types LIMIT 2;

1-- 等值过滤
2SELECT * FROM spark_all_types WHERE col_bool = true;
3SELECT * FROM spark_all_types WHERE col_bool = false;
4
5-- 不等过滤
6SELECT * FROM spark_all_types WHERE col_bool != true;
7
8-- NULL 判断
9SELECT * FROM spark_all_types WHERE col_bool IS NULL;
10SELECT * FROM lance1.lance_db.spark_all_types WHERE col_bool IS NOT NULL;
11
12-- 直接作为过滤条件
13SELECT * FROM spark_all_types WHERE col_bool;
14SELECT * FROM spark_all_types WHERE NOT col_bool;

1-- 等值
2SELECT * FROM spark_all_types WHERE col_int8 = 127;
3
4-- 不等
5SELECT * FROM spark_all_types WHERE col_int32 != 0;
6SELECT * FROM spark_all_types WHERE col_int64 <> -1;
7
8-- 比较运算
9SELECT * FROM spark_all_types WHERE col_int16 >= -100;
10SELECT * FROM spark_all_types WHERE col_int32 > 1000 AND col_int32 < 5000;
11
12-- BETWEEN
13SELECT * FROM spark_all_types WHERE col_int8 BETWEEN -128 AND 127;
14SELECT * FROM spark_all_types WHERE col_int16 BETWEEN 10 AND 100;
15SELECT * FROM spark_all_types WHERE col_int32 NOT BETWEEN 0 AND 100;
16
17-- IN / NOT IN
18SELECT * FROM spark_all_types WHERE col_int8 IN (1, 2, 3, 127, -128);
19SELECT * FROM spark_all_types WHERE col_int64 NOT IN (0, -1, 1);
20SELECT * FROM spark_all_types WHERE col_int16 IN (SELECT col_int16 FROM spark_all_types WHERE col_int16 > 0);
21
22-- NULL 判断
23SELECT * FROM spark_all_types WHERE col_int32 IS NULL;
24SELECT * FROM spark_all_types WHERE col_int64 IS NOT NULL;
25
26-- 算术表达式
27SELECT * FROM spark_all_types WHERE col_int32 + 1 > 100;
28SELECT * FROM spark_all_types WHERE col_int32 * 2 < 1000;
29SELECT * FROM spark_all_types WHERE abs(col_int32) < 100;

1-- 等值
2SELECT * FROM spark_all_types WHERE col_float32 = 0.0;
3SELECT * FROM spark_all_types WHERE col_float64 = 3.1415926535;
4
5-- 比较运算符
6SELECT * FROM spark_all_types WHERE col_float32 > 0.0;
7SELECT * FROM spark_all_types WHERE col_float64 >= -1.0 AND col_float64 <= 1.0;
8SELECT * FROM spark_all_types WHERE col_float64 > 1e10;
9
10-- BETWEEN
11SELECT * FROM spark_all_types WHERE col_float32 BETWEEN -1.0 AND 1.0;
12SELECT * FROM spark_all_types WHERE col_float64 BETWEEN 0.0 AND 100.0;
13
14-- 特殊值
15SELECT * FROM spark_all_types WHERE col_float32 IS NULL;
16SELECT * FROM spark_all_types WHERE col_float64 IS NOT NULL;
17SELECT * FROM spark_all_types WHERE col_float64 != col_float64;
18
19-- IN
20SELECT * FROM spark_all_types WHERE col_float64 IN (1.0, 0.0, -1.0);
21
22-- 跨类型比较
23SELECT * FROM spark_all_types WHERE col_float32 > col_int32;
24SELECT * FROM spark_all_types WHERE col_float64 = CAST(col_int64 AS DOUBLE);

1-- 等值
2SELECT * FROM spark_all_types WHERE col_utf8 = 'hello world';
3SELECT * FROM spark_all_types WHERE col_utf8 = '';
4
5-- 不等
6SELECT * FROM spark_all_types WHERE col_utf8 != 'hello world';
7
8-- 比较（按字典序）
9SELECT * FROM spark_all_types WHERE col_utf8 > 'e';
10SELECT * FROM spark_all_types WHERE col_utf8 < 'z';
11SELECT * FROM spark_all_types WHERE col_utf8 >= 'abc' AND col_utf8 <= 'xyz';
12
13-- LIKE 模式匹配
14SELECT * FROM spark_all_types WHERE col_utf8 LIKE 'hello%';
15SELECT * FROM spark_all_types WHERE col_utf8 LIKE '%world';
16SELECT * FROM spark_all_types WHERE col_utf8 NOT LIKE '%error%';
17
18-- RLIKE / REGEXP 正则
19SELECT * FROM spark_all_types WHERE col_utf8 RLIKE '^[A-Z].*';
20SELECT * FROM spark_all_types WHERE col_utf8 RLIKE '^(hello world|emoji test)$';
21
22-- IN / NOT IN
23SELECT * FROM spark_all_types WHERE col_utf8 IN ('emoji test', 'hello world', 'cherry');
24SELECT * FROM spark_all_types WHERE col_utf8 NOT IN ('', NULL);
25SELECT * FROM spark_all_types WHERE col_utf8 IN ('', NULL);
26
27-- NULL判断
28SELECT * FROM spark_all_types WHERE col_utf8 IS NULL;
29SELECT * FROM spark_all_types WHERE col_utf8 IS NOT NULL;
30
31-- 字符串函数
32SELECT * FROM spark_all_types WHERE length(col_utf8) > 10;
33SELECT * FROM spark_all_types WHERE upper(col_utf8) = 'HELLO WORLD';
34SELECT * FROM lance1.lance_db.spark_all_types WHERE lower(col_utf8) = 'hello world';
35SELECT * FROM spark_all_types WHERE trim(col_utf8) != col_utf8;
36SELECT * FROM spark_all_types WHERE col_utf8 LIKE concat('%', 'test', '%');

1-- NULL 判断 (下推Rust)
2SELECT * FROM lance1.lance_db.spark_all_types WHERE col_binary IS NULL;
3SELECT * FROM lance1.lance_db.spark_all_types WHERE col_binary IS NOT NULL;
4
5-- 长度过滤(非下推Rust)
6SELECT * FROM spark_all_types WHERE length(col_binary) = 4;

1-- 等值
2SELECT * FROM spark_all_types WHERE col_decimal = 88.88;
3SELECT * FROM spark_all_types WHERE col_decimal = -99999.123456;
4
5-- 比较
6SELECT * FROM spark_all_types WHERE col_decimal < -50.5;
7SELECT * FROM spark_all_types WHERE col_decimal >= 0 AND col_decimal <= 999.999999;
8
9-- BETWEEN
10SELECT * FROM spark_all_types WHERE col_decimal BETWEEN -1000.0 AND 1000.0;
11SELECT * FROM spark_all_types WHERE col_decimal NOT BETWEEN 0 AND 100;
12
13-- IN
14SELECT * FROM spark_all_types WHERE col_decimal IN (88.88, 0, 3.5);
15
16-- NULL判断
17SELECT * FROM spark_all_types WHERE col_decimal IS NULL;
18SELECT * FROM spark_all_types WHERE col_decimal IS NOT NULL;
19
20-- 精度边界
21SELECT * FROM spark_all_types WHERE col_decimal = 999999999999.999999;
22SELECT * FROM spark_all_types WHERE col_decimal = -999999999999.999999;

1-- 等值
2SELECT * FROM spark_all_types WHERE col_date = DATE '2025-05-15';
3SELECT * FROM spark_all_types WHERE col_date = CAST('2025-05-15' AS DATE);
4-- 比较
5SELECT * FROM spark_all_types WHERE col_date > DATE '2024-01-01';
6SELECT * FROM spark_all_types WHERE col_date >= DATE '2024-06-01' AND col_date <= DATE '2028-06-30';
7
8-- BETWEEN
9SELECT * FROM spark_all_types WHERE col_date BETWEEN DATE '2024-01-01' AND DATE '2028-12-31';
10SELECT * FROM spark_all_types WHERE col_date NOT BETWEEN DATE '2020-01-01' AND DATE '2020-12-31';
11
12-- IN
13SELECT * FROM spark_all_types WHERE col_date IN (DATE '2024-02-29', DATE '2025-05-15');
14
15-- NULL
16SELECT * FROM spark_all_types WHERE col_date IS NULL;
17SELECT * FROM spark_all_types WHERE col_date IS NOT NULL;

1-- 等值
2SELECT * FROM spark_all_types WHERE col_timestamp = TIMESTAMP '2025-05-15 14:30:00';
3
4-- 比较
5SELECT * FROM spark_all_types WHERE col_timestamp > TIMESTAMP '2024-01-01 00:00:00';
6SELECT * FROM spark_all_types WHERE col_timestamp >= TIMESTAMP '2024-06-01 00:00:00' AND col_timestamp < TIMESTAMP '2028-07-01 00:00:00';
7
8-- BETWEEN
9SELECT * FROM spark_all_types WHERE col_timestamp BETWEEN TIMESTAMP '2024-01-01 00:00:00' AND TIMESTAMP '2025-12-31 23:59:59';
10
11-- IN
12SELECT * FROM spark_all_types WHERE col_timestamp IN (TIMESTAMP '2024-01-01 00:00:00',  TIMESTAMP '2025-05-15 14:30:00' );
13
14-- NULL
15SELECT * FROM spark_all_types WHERE col_timestamp IS NULL;
16SELECT * FROM spark_all_types WHERE col_timestamp IS NOT NULL;
17
18-- 时间戳函数
19SELECT * FROM spark_all_types WHERE year(col_timestamp) = 2024;
20SELECT * FROM spark_all_types WHERE month(col_timestamp) >= 6;
21SELECT * FROM spark_all_types WHERE hour(col_timestamp) = 12;
22SELECT * FROM spark_all_types WHERE minute(col_timestamp) = 0;
23SELECT * FROM spark_all_types WHERE second(col_timestamp) > 30;

1-- NULL 判断
2SELECT * FROM lance1.lance_db.spark_all_types WHERE col_array IS NULL;
3SELECT * FROM spark_all_types WHERE col_array IS NOT NULL;
4
5-- 数据大小
6SELECT * FROM spark_all_types WHERE size(col_array) > 0;
7
8-- 元素访问
9SELECT * FROM spark_all_types WHERE col_array[1] > 10;
10SELECT * FROM spark_all_types WHERE element_at(col_array, 1) = 42;
11
12-- 包含判断
13SELECT * FROM spark_all_types WHERE array_contains(col_array, 5);
14SELECT * FROM spark_all_types WHERE NOT array_contains(col_array, 0);
15
16-- 数组函数
17SELECT * FROM spark_all_types WHERE array_max(col_array) > 100;
18SELECT * FROM spark_all_types WHERE array_min(col_array) < 0;

1-- 字段访问
2SELECT * FROM spark_all_types WHERE STRUCT_ELEMENT(col_struct, 'name') = 'Bob';
3SELECT * FROM spark_all_types WHERE STRUCT_ELEMENT(col_struct,'value') > 100;
4SELECT * FROM lance1.lance_db.spark_all_types WHERE STRUCT_ELEMENT(col_struct,'name') IS NOT NULL;
5SELECT * FROM lance1.lance_db.spark_all_types WHERE STRUCT_ELEMENT(col_struct,'value') BETWEEN 0 AND 50;
6
7-- 结构体整体
8SELECT * FROM lance1.lance_db.spark_all_types WHERE col_struct IS NULL;
9 SELECT * FROM lance1.lance_db.spark_all_types WHERE col_struct IS NOT NULL;
10 
11-- 结构体字段组合
12SELECT * FROM spark_all_types WHERE STRUCT_ELEMENT(col_struct,'name') LIKE 'Al%' AND STRUCT_ELEMENT(col_struct,'value') > 0;
13SELECT * FROM lance1.lance_db.spark_all_types WHERE STRUCT_ELEMENT(col_struct,'name') IN ('Alice', 'Bob', 'baz');
14SELECT * FROM spark_all_types WHERE length(STRUCT_ELEMENT(col_struct,'name')) > 5;

1SELECT * FROM lance1.lance_db.spark_all_types WHERE col_utf8 <=> NULL;
2SELECT * FROM lance1.lance_db.spark_all_types WHERE col_int32 <=> 0;
3SELECT * FROM lance1.lance_db.spark_all_types WHERE NOT (col_utf8 <=> col_utf8);

1SELECT * FROM lance1.lance_db.spark_all_types WHERE col_utf8 IS NOT NULL ORDER BY col_utf8 DESC LIMIT 5;
2SELECT * FROM lance1.lance_db.spark_all_types WHERE col_float64 > 0 ORDER BY col_float64 LIMIT 10 OFFSET 5;
3SELECT * FROM lance1.lance_db.spark_all_types WHERE col_date  IS NOT NULL ORDER BY col_date LIMIT 20;

1-- AND
2SELECT * FROM lance1.lance_db.spark_all_types WHERE col_int32 > 0 AND col_utf8 IS NOT NULL;
3 SELECT * FROM lance1.lance_db.spark_all_types WHERE col_bool = true AND col_int64 > 100 AND col_float64 < 1.0;
4
5-- OR
6SELECT * FROM lance1.lance_db.spark_all_types WHERE col_int32 < 0 OR col_int32 > 1000;
7SELECT * FROM lance1.lance_db.spark_all_types WHERE col_utf8 = 'Lance Chinese Test ' OR col_utf8 = 'warning';
8
9-- NOT
10SELECT * FROM lance1.lance_db.spark_all_types WHERE NOT (col_bool = true AND col_int32 > 0)
11
12-- 复杂组合
13SELECT * FROM lance1.lance_db.spark_all_types WHERE (col_int32 > 0 AND col_float64 < 100.0)    OR (col_utf8 LIKE 'special%' AND col_bool = true);
14SELECT * FROM spark_all_types WHERE col_date > DATE '2024-01-01'   AND (col_int32 IN (1, 2, 3) OR col_utf8 IS NOT NULL)   AND col_decimal BETWEEN 0 AND 1000;
15SELECT * FROM lance1.lance_db.spark_all_types WHERE NOT (col_int64 IS NULL OR col_float32 IS NULL)   AND (STRUCT_ELEMENT(col_struct,'value') > 50 OR array_contains(col_array, 99));
16SELECT * FROM lance1.lance_db.spark_all_types WHERE (col_int8 = 1 OR col_int8 = 2 OR col_int8 = 3 OR col_int8 = 4 OR col_int8 = 5);

1SELECT * FROM spark_all_types WHERE col_int32 = col_int64;
2SELECT * FROM spark_all_types WHERE CAST(col_int32 AS BIGINT) < col_int64;
3SELECT * FROM spark_all_types WHERE col_int32 > STRUCT_ELEMENT(col_struct, 'value');

1SELECT * FROM spark_all_types WHERE CAST(col_int32 AS STRING) = '12345';
2SELECT * FROM spark_all_types WHERE CAST(col_date AS STRING) = '2025-05-15';
3SELECT * FROM lance1.lance_db.spark_all_types WHERE CAST(col_utf8 AS INT) > 100;
4SELECT * FROM spark_all_types WHERE CAST(col_float64 AS INT) = 3;
5SELECT * FROM spark_all_types WHERE CAST(col_timestamp AS DATE) = DATE '2025-05-15';
6SELECT * FROM lance1.lance_db.spark_all_types WHERE CAST(col_int8 AS INT) + col_int32 > 200;

1SELECT * FROM lance1.lance_db.spark_all_types WHERE CASE WHEN col_int32 > 0 THEN 'positive' ELSE 'non_positive' END = 'positive';
2SELECT * FROM lance1.lance_db.spark_all_types WHERE CASE     WHEN col_float64 > 100 THEN 1     WHEN col_float64 > 0 THEN 2     ELSE 3 END = 2;

1SELECT col_bool, count(*) AS cnt FROM lance1.lance_db.spark_all_types WHERE col_int32 > 0 GROUP BY col_bool HAVING count(*) > 1;
2SELECT year(col_date) AS yr, avg(col_float64) AS avg_val FROM lance1.lance_db.spark_all_types WHERE col_float64 IS NOT NULL AND col_date IS NOT NULL GROUP BY year(col_date) HAVING avg(col_float64) > 10.0;

1-- topk 向量检索
2SELECT *,__DISTANCE FROM spark_all_types ORDER BY ann_distance(col_fixed_list, '[0.1, 0.2, 0.3, 0.4]') ASC LIMIT 5;
3
4-- 带标量过滤的 topk 向量检索
5SELECT *,__DISTANCE FROM spark_all_types where col_int8 >=1 AND col_int16 >= 500  ORDER BY ann_distance(col_fixed_list, '[0.1, 0.2, 0.3, 0.4]') ASC LIMIT 5;

1-- COUNT
2SELECT COUNT(col_int32) FROM lance1.lancedb.spark_all_types;
3SELECT COUNT(col_float32) FROM lance1.lancedb.spark_all_types;
4SELECT COUNT(DISTINCT col_decimal) FROM lance1.lancedb.spark_all_types;
5
6-- SUM聚合
7SELECT SUM(col_int32) FROM lance1.lancedb.spark_all_types;
8SELECT SUM(col_float32) FROM lance1.lancedb.spark_all_types;
9SELECT SUM(col_decimal) FROM lance1.lancedb.spark_all_types;
10
11-- AVG聚合
12SELECT AVG(col_int32) FROM lance1.lancedb.spark_all_types;
13SELECT AVG(col_float32) FROM lance1.lancedb.spark_all_types;
14SELECT AVG(col_decimal) FROM lance1.lancedb.spark_all_types;
15
16-- MIN / MAX聚合
17SELECT MIN(col_int32) FROM lance1.lancedb.spark_all_types;
18SELECT MIN(col_float32) FROM lance1.lancedb.spark_all_types;
19SELECT MAX(col_bool) FROM lance1.lancedb.spark_all_types;
20SELECT MAX(col_int32) FROM lance1.lancedb.spark_all_types;
21
22-- GROUP BY聚合
23SELECT col_int32 FROM lance1.lancedb.spark_all_types GROUP BY col_int32;
24SELECT col_float32 FROM lance1.lancedb.spark_all_types GROUP BY col_float32;
25SELECT col_utf8 FROM lance1.lancedb.spark_all_types GROUP BY col_utf8;
26SELECT col_binary FROM lance1.lancedb.spark_all_types GROUP BY col_binary;

1SELECT
2    COALESCE(d.doc_id, e.doc_id) AS doc_id,
3    d.title,
4    __RANK
5FROM doc_text_vectors d
6HYBRID doc_image_vectors e ON d.doc_id = e.doc_id
7ORDER BY hybrid_search(
8    '{"rerank":"RRF","c":60,"candidates":100}',
9    ann_distance(
10        d.text_embedding,
11        embedding('apple market cap', 'text', 'text_embedding_model')
12    ),
13    ann_distance(e.image_embedding, [0.1, 0.2, 0.3, 0.4])
14)
15LIMIT 10;

1SELECT
2    COALESCE(d.doc_id, e.doc_id) AS doc_id,
3    d.title,
4    __RANK
5FROM doc_text_vectors d
6HYBRID doc_image_vectors e ON d.doc_id = e.doc_id
7ORDER BY hybrid_search(
8    '{"rerank":"WEIGHT","weights":[0.4,0.6],"candidates":200}',
9    ann_distance(
10        d.text_embedding,
11        embedding('apple market cap', 'text', 'text_embedding_model')
12    ),
13    ann_distance(e.image_embedding, [0.1, 0.2, 0.3, 0.4])
14)
15LIMIT 10;

1select
2    l_returnflag,
3    l_linestatus,
4    sum(l_quantity) as sum_qty,
5    sum(l_extendedprice) as sum_base_price,
6    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
7    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
8    avg(l_quantity) as avg_qty,
9    avg(l_extendedprice) as avg_price,
10    avg(l_discount) as avg_disc,
11    count(*) as count_order
12from
13    iceberg.icebergdb.lineitem
14where
15    l_shipdate <= date '1998-12-01' - interval '90' day
16group by
17    l_returnflag,
18    l_linestatus
19order by
20    l_returnflag,
21    l_linestatus;

1select
2    s_acctbal,
3    s_name,
4    n_name,
5    p_partkey,
6    p_mfgr,
7    s_address,
8    s_phone,
9    s_comment
10from
11    lance.lancedb.part,
12    lance.lancedb.supplier,
13    iceberg.icebergdb.partsupp,
14    lance.lancedb.nation,
15    lance.lancedb.region
16where
17    p_partkey = ps_partkey
18    and s_suppkey = ps_suppkey
19    and p_size = 15
20    and p_type like '%BRASS'
21    and s_nationkey = n_nationkey
22    and n_regionkey = r_regionkey
23    and r_name = 'EUROPE'
24    and ps_supplycost = (
25        select
26            min(ps_supplycost)
27        from
28            iceberg.icebergdb.partsupp,
29            lance.lancedb.supplier,
30            lance.lancedb.nation,
31            lance.lancedb.region
32        where
33        p_partkey = ps_partkey
34        and s_suppkey = ps_suppkey
35        and s_nationkey = n_nationkey
36        and n_regionkey = r_regionkey
37        and r_name = 'EUROPE'
38)
39order by
40    s_acctbal desc,
41    n_name,
42    s_name,
43    p_partkey
44limit 100;

1select
2    l_orderkey,
3    sum(l_extendedprice * (1 - l_discount)) as revenue,
4    o_orderdate,
5    o_shippriority
6from
7    lance.lancedb.customer,
8    iceberg.icebergdb.orders,
9    iceberg.icebergdb.lineitem
10where
11    c_mktsegment = 'BUILDING'
12    and c_custkey = o_custkey
13    and l_orderkey = o_orderkey
14    and o_orderdate < date '1995-03-15'
15    and l_shipdate > date '1995-03-15'
16group by
17    l_orderkey,
18    o_orderdate,
19    o_shippriority
20order by
21    revenue desc,
22    o_orderdate
23limit 10;

1select
2    o_orderpriority,
3    count(*) as order_count
4from
5    iceberg.icebergdb.orders
6where
7    o_orderdate >= date '1993-07-01'
8    and o_orderdate < date '1993-07-01' + interval '3' month
9    and exists (
10        select
11            *
12        from
13            iceberg.icebergdb.lineitem
14        where
15                l_orderkey = o_orderkey
16          and l_commitdate < l_receiptdate
17    )
18group by
19    o_orderpriority
20order by
21    o_orderpriority;

1select
2    n_name,
3    sum(l_extendedprice * (1 - l_discount)) as revenue
4from
5    lance.lancedb.customer,
6    iceberg.icebergdb.orders,
7    iceberg.icebergdb.lineitem,
8    lance.lancedb.supplier,
9    lance.lancedb.nation,
10    lance.lancedb.region
11where
12    c_custkey = o_custkey
13    and l_orderkey = o_orderkey
14    and l_suppkey = s_suppkey
15    and c_nationkey = s_nationkey
16    and s_nationkey = n_nationkey
17    and n_regionkey = r_regionkey
18    and r_name = 'ASIA'
19    and o_orderdate >= date '1994-01-01'
20    and o_orderdate < date '1994-01-01' + interval '1' year
21group by
22    n_name
23order by
24    revenue desc;

1select
2    sum(l_extendedprice * l_discount) as revenue
3from
4    iceberg.icebergdb.lineitem
5where
6    l_shipdate >= date '1994-01-01'
7    and l_shipdate < date '1994-01-01' + interval '1' year
8    and l_discount between .06 - 0.01 and .06 + 0.01
9    and l_quantity < 24;

1select
2    supp_nation,
3    cust_nation,
4    l_year,
5    sum(volume) as revenue
6from
7    (
8        select
9            n1.n_name as supp_nation,
10            n2.n_name as cust_nation,
11            extract(year from l_shipdate) as l_year,
12            l_extendedprice * (1 - l_discount) as volume
13        from
14            lance.lancedb.supplier,
15            iceberg.icebergdb.lineitem,
16            iceberg.icebergdb.orders,
17            lance.lancedb.customer,
18            lance.lancedb.nation n1,
19            lance.lancedb.nation n2
20        where
21            s_suppkey = l_suppkey
22            and o_orderkey = l_orderkey
23            and c_custkey = o_custkey
24            and s_nationkey = n1.n_nationkey
25            and c_nationkey = n2.n_nationkey
26            and (
27                (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
28                or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
29            )
30            and l_shipdate between date '1995-01-01' and date '1996-12-31'
31    ) as shipping
32group by
33    supp_nation,
34    cust_nation,
35    l_year
36order by
37    supp_nation,
38    cust_nation,
39    l_year;

1select
2    o_year,
3    sum(case
4        when nation = 'BRAZIL' then volume
5        else 0
6    end) / sum(volume) as mkt_share
7from
8    (
9        select
10            extract(year from o_orderdate) as o_year,
11            l_extendedprice * (1 - l_discount) as volume,
12            n2.n_name as nation
13        from
14            lance.lancedb.part,
15            lance.lancedb.supplier,
16            iceberg.icebergdb.lineitem,
17            iceberg.icebergdb.orders,
18            lance.lancedb.customer,
19            lance.lancedb.nation n1,
20            lance.lancedb.nation n2,
21            lance.lancedb.region
22        where
23            p_partkey = l_partkey
24            and s_suppkey = l_suppkey
25            and l_orderkey = o_orderkey
26            and o_custkey = c_custkey
27            and c_nationkey = n1.n_nationkey
28            and n1.n_regionkey = r_regionkey
29            and r_name = 'AMERICA'
30            and s_nationkey = n2.n_nationkey
31            and o_orderdate between date '1995-01-01' and date '1996-12-31'
32            and p_type = 'ECONOMY ANODIZED STEEL'
33    ) as all_nations
34group by
35    o_year
36order by
37    o_year;

1select
2    nation,
3    o_year,
4    sum(amount) as sum_profit
5from
6    (
7        select
8            n_name as nation,
9            extract(year from o_orderdate) as o_year,
10            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
11        from
12            lance.lancedb.part,
13            lance.lancedb.supplier,
14            iceberg.icebergdb.lineitem,
15            iceberg.icebergdb.partsupp,
16            iceberg.icebergdb.orders,
17            lance.lancedb.nation
18        where
19            s_suppkey = l_suppkey
20            and ps_suppkey = l_suppkey
21            and ps_partkey = l_partkey
22            and p_partkey = l_partkey
23            and o_orderkey = l_orderkey
24            and s_nationkey = n_nationkey
25            and p_name like '%green%'
26    ) as profit
27group by
28    nation,
29    o_year
30order by
31    nation,
32    o_year desc;

1select
2    c_custkey,
3    c_name,
4    sum(l_extendedprice * (1 - l_discount)) as revenue,
5    c_acctbal,
6    n_name,
7    c_address,
8    c_phone,
9    c_comment
10from
11    lance.lancedb.customer,
12    iceberg.icebergdb.orders,
13    iceberg.icebergdb.lineitem,
14    lance.lancedb.nation
15where
16    c_custkey = o_custkey
17    and l_orderkey = o_orderkey
18    and o_orderdate >= date '1993-10-01'
19    and o_orderdate < date '1993-10-01' + interval '3' month
20    and l_returnflag = 'R'
21    and c_nationkey = n_nationkey
22group by
23    c_custkey,
24    c_name,
25    c_acctbal,
26    c_phone,
27    n_name,
28    c_address,
29    c_comment
30order by
31    revenue desc
32limit 20;

1select
2    ps_partkey,
3    sum(ps_supplycost * ps_availqty) as value
4from
5    iceberg.icebergdb.partsupp,
6    lance.lancedb.supplier,
7    lance.lancedb.nation
8where
9    ps_suppkey = s_suppkey
10    and s_nationkey = n_nationkey
11    and n_name = 'GERMANY'
12group by
13    ps_partkey having
14    sum(ps_supplycost * ps_availqty) > (
15        select
16        sum(ps_supplycost * ps_availqty) * 0.000002
17        from
18            iceberg.icebergdb.partsupp,
19            lance.lancedb.supplier,
20            lance.lancedb.nation
21        where
22            ps_suppkey = s_suppkey
23            and s_nationkey = n_nationkey
24            and n_name = 'GERMANY'
25    )
26order by
27    value desc;

1select
2    l_shipmode,
3    sum(case
4        when o_orderpriority = '1-URGENT'
5            or o_orderpriority = '2-HIGH'
6            then 1
7        else 0
8    end) as high_line_count,
9    sum(case
10        when o_orderpriority <> '1-URGENT'
11            and o_orderpriority <> '2-HIGH'
12            then 1
13        else 0
14    end) as low_line_count
15from
16    iceberg.icebergdb.orders,
17    iceberg.icebergdb.lineitem
18where
19    o_orderkey = l_orderkey
20    and l_shipmode in ('MAIL', 'SHIP')
21    and l_commitdate < l_receiptdate
22    and l_shipdate < l_commitdate
23    and l_receiptdate >= date '1994-01-01'
24    and l_receiptdate < date '1994-01-01' + interval '1' year
25group by
26    l_shipmode
27order by
28    l_shipmode;

1select
2    c_count,
3    count(*) as custdist
4from
5    (
6        select
7            c_custkey,
8            count(o_orderkey) as c_count
9        from
10            lance.lancedb.customer left outer join iceberg.icebergdb.orders on
11                c_custkey = o_custkey
12                and o_comment not like '%special%requests%'
13        group by
14            c_custkey
15    ) as c_orders
16group by
17    c_count
18order by
19    custdist desc,
20    c_count desc;

1select
2    100.00 * sum(case
3        when p_type like 'PROMO%'
4            then l_extendedprice * (1 - l_discount)
5        else 0
6    end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
7from
8    iceberg.icebergdb.lineitem,
9    lance.lancedb.part
10where
11    l_partkey = p_partkey
12    and l_shipdate >= date '1995-09-01'
13    and l_shipdate < date '1995-09-01' + interval '1' month;

1select
2    s_suppkey,
3    s_name,
4    s_address,
5    s_phone,
6    total_revenue
7from
8    lance.lancedb.supplier,
9    (
10        select
11            l_suppkey as supplier_no,
12            sum(l_extendedprice * (1 - l_discount)) as total_revenue
13        from
14            iceberg.icebergdb.lineitem
15        where
16            l_shipdate >= date '1996-01-01'
17            and l_shipdate < date '1996-01-01' + interval '3' month
18        group by
19            l_suppkey
20    ) revenue0
21where
22    s_suppkey = supplier_no
23    and total_revenue = (
24        select
25            max(total_revenue)
26        from
27            (
28                select
29                    l_suppkey as supplier_no,
30                    sum(l_extendedprice * (1 - l_discount)) as total_revenue
31                from
32                    iceberg.icebergdb.lineitem
33                where
34                    l_shipdate >= date '1996-01-01'
35                    and l_shipdate < date '1996-01-01' + interval '3' month
36                group by
37                    l_suppkey
38            ) revenue0
39    )
40order by
41    s_suppkey;

1select
2    p_brand,
3    p_type,
4    p_size,
5    count(distinct ps_suppkey) as supplier_cnt
6from
7    iceberg.icebergdb.partsupp,
8    lance.lancedb.part
9where
10    p_partkey = ps_partkey
11    and p_brand <> 'Brand#45'
12    and p_type not like 'MEDIUM POLISHED%'
13    and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
14    and ps_suppkey not in (
15        select
16            s_suppkey
17        from
18            lance.lancedb.supplier
19        where
20            s_comment like '%Customer%Complaints%'
21    )
22group by
23    p_brand,
24    p_type,
25    p_size
26order by
27    supplier_cnt desc,
28    p_brand,
29    p_type,
30    p_size;

1select
2    sum(l_extendedprice) / 7.0 as avg_yearly
3from
4    iceberg.icebergdb.lineitem,
5    lance.lancedb.part
6where
7    p_partkey = l_partkey
8    and p_brand = 'Brand#23'
9    and p_container = 'MED BOX'
10    and l_quantity < (
11        select
12            0.2 * avg(l_quantity)
13        from
14            iceberg.icebergdb.lineitem
15        where
16            l_partkey = p_partkey
17    );

1select
2    c_name,
3    c_custkey,
4    o_orderkey,
5    o_orderdate,
6    o_totalprice,
7    sum(l_quantity)
8from
9    lance.lancedb.customer,
10    iceberg.icebergdb.orders,
11    iceberg.icebergdb.lineitem
12where
13    o_orderkey in (
14        select
15            l_orderkey
16        from
17            iceberg.icebergdb.lineitem
18        group by
19            l_orderkey having
20                sum(l_quantity) > 300
21    )
22    and c_custkey = o_custkey
23    and o_orderkey = l_orderkey
24group by
25    c_name,
26    c_custkey,
27    o_orderkey,
28    o_orderdate,
29    o_totalprice
30order by
31    o_totalprice desc,
32    o_orderdate
33limit 100;

1select
2    sum(l_extendedprice* (1 - l_discount)) as revenue
3from
4    iceberg.icebergdb.lineitem,
5    lance.lancedb.part
6where
7    (
8        p_partkey = l_partkey
9        and p_brand = 'Brand#12'
10        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
11        and l_quantity >= 1 and l_quantity <= 1 + 10
12        and p_size between 1 and 5
13        and l_shipmode in ('AIR', 'AIR REG')
14        and l_shipinstruct = 'DELIVER IN PERSON'
15    )
16    or
17    (
18        p_partkey = l_partkey
19        and p_brand = 'Brand#23'
20        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
21        and l_quantity >= 10 and l_quantity <= 10 + 10
22        and p_size between 1 and 10
23        and l_shipmode in ('AIR', 'AIR REG')
24        and l_shipinstruct = 'DELIVER IN PERSON'
25    )
26    or
27    (
28        p_partkey = l_partkey
29        and p_brand = 'Brand#34'
30        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
31        and l_quantity >= 20 and l_quantity <= 20 + 10
32        and p_size between 1 and 15
33        and l_shipmode in ('AIR', 'AIR REG')
34        and l_shipinstruct = 'DELIVER IN PERSON'
35    );

1select
2    s_name,
3    s_address
4from
5    lance.lancedb.supplier,
6    lance.lancedb.nation
7where
8    s_suppkey in (
9        select
10            ps_suppkey
11        from
12            iceberg.icebergdb.partsupp
13        where
14            ps_partkey in (
15                select
16                    p_partkey
17                from
18                    lance.lancedb.part
19                where
20                        p_name like 'forest%'
21            )
22            and ps_availqty > (
23                select
24                    0.5 * sum(l_quantity)
25                from
26                    iceberg.icebergdb.lineitem
27                where
28                    l_partkey = ps_partkey
29                    and l_suppkey = ps_suppkey
30                    and l_shipdate >= date '1994-01-01'
31                    and l_shipdate < date '1994-01-01' + interval '1' year
32            )
33    )
34    and s_nationkey = n_nationkey
35    and n_name = 'CANADA'
36order by
37    s_name;

1select
2    s_name,
3    count(*) as numwait
4from
5    lance.lancedb.supplier,
6    iceberg.icebergdb.lineitem l1,
7    iceberg.icebergdb.orders,
8    lance.lancedb.nation
9where
10    s_suppkey = l1.l_suppkey
11    and o_orderkey = l1.l_orderkey
12    and o_orderstatus = 'F'
13    and l1.l_receiptdate > l1.l_commitdate
14    and exists (
15        select
16            *
17        from
18            iceberg.icebergdb.lineitem l2
19        where
20                l2.l_orderkey = l1.l_orderkey
21          and l2.l_suppkey <> l1.l_suppkey
22    )
23    and not exists (
24        select
25            *
26        from
27            iceberg.icebergdb.lineitem l3
28        where
29                l3.l_orderkey = l1.l_orderkey
30          and l3.l_suppkey <> l1.l_suppkey
31          and l3.l_receiptdate > l3.l_commitdate
32    )
33    and s_nationkey = n_nationkey
34    and n_name = 'SAUDI ARABIA'
35group by
36    s_name
37order by
38    numwait desc,
39    s_name
40limit 100;

1select
2    cntrycode,
3    count(*) as numcust,
4    sum(c_acctbal) as totacctbal
5from
6    (
7        select
8            substring(c_phone, 1, 2) as cntrycode,
9            c_acctbal
10        from
11            lance.lancedb.customer
12        where
13            substring(c_phone, 1, 2) in
14            ('13', '31', '23', '29', '30', '18', '17')
15            and c_acctbal > (
16                select
17                    avg(c_acctbal)
18                from
19                    lance.lancedb.customer
20                where
21                    c_acctbal > 0.00
22                    and substring(c_phone, 1, 2) in
23                      ('13', '31', '23', '29', '30', '18', '17')
24            )
25            and not exists (
26                select
27                    *
28                from
29                    iceberg.icebergdb.orders
30                where
31                    o_custkey = c_custkey
32            )
33    ) as custsale
34group by
35    cntrycode
36order by
37    cntrycode;

1mysql -h{doris_ip} -P9030 -u{doris_user} -p{user_password}