LIMIT OFFSET returns empty result set

[evenyag]

What type of bug is this?

Incorrect result

What subsystems are affected?

Standalone mode

Minimal reproduce step

Builds and starts the database in standalone mode

Executes the following SQL

select * from information_schema.tables order by table_name limit 10;

select * from information_schema.tables order by table_name limit 10 offset 0;

What did you expect to see?

The database should return the same results.

What did you see instead?

The second statement returns an empty result.

mysql> select * from information_schema.tables order by table_name limit 10;
+---------------+--------------------+---------------------------------------+-----------------+----------+-------------+-----------------+--------------+------------------+----------------+--------+---------+------------+------------+-----------+----------------+----------------------------+-------------+------------+-----------------+----------+----------------+---------------+-----------+
| table_catalog | table_schema       | table_name                            | table_type      | table_id | data_length | max_data_length | index_length | max_index_length | avg_row_length | engine | version | row_format | table_rows | data_free | auto_increment | create_time                | update_time | check_time | table_collation | checksum | create_options | table_comment | temporary |
+---------------+--------------------+---------------------------------------+-----------------+----------+-------------+-----------------+--------------+------------------+----------------+--------+---------+------------+------------+-----------+----------------+----------------------------+-------------+------------+-----------------+----------+----------------+---------------+-----------+
| greptime      | information_schema | build_info                            | LOCAL TEMPORARY |        8 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.116000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | character_sets                        | LOCAL TEMPORARY |        9 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | check_constraints                     | LOCAL TEMPORARY |       12 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | cluster_info                          | LOCAL TEMPORARY |       31 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.116000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | collation_character_set_applicability | LOCAL TEMPORARY |       11 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | collations                            | LOCAL TEMPORARY |       10 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | column_privileges                     | LOCAL TEMPORARY |        6 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | column_statistics                     | LOCAL TEMPORARY |        7 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | columns                               | LOCAL TEMPORARY |        4 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.116000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
| greptime      | information_schema | engines                               | LOCAL TEMPORARY |        5 |           0 |               0 |            0 |                0 |              0 |        |      11 | Fixed      |          0 |         0 |              0 | 2025-02-05 07:01:27.117000 | NULL        | NULL       | utf8_bin        |        0 |                | NULL          | Y         |
+---------------+--------------------+---------------------------------------+-----------------+----------+-------------+-----------------+--------------+------------------+----------------+--------+---------+------------+------------+-----------+----------------+----------------------------+-------------+------------+-----------------+----------+----------------+---------------+-----------+
10 rows in set (0.07 sec)

mysql> select * from information_schema.tables order by table_name limit 10 offset 0;
Empty set (0.06 sec)

What operating system did you use?

Unrelated

What version of GreptimeDB did you use?

main adb5c37

Relevant log output and stack trace

After the TypeConversionRule rewrites the Limit node, fetch and limit exchange their positions.


2025-02-05T07:01:27.142534Z  INFO on_query{protocol="mysql"}:mysql::do_query:do_query:plan:plan:plan_sql: query::optimizer::type_conversion: [DBG] TypeConversionRule: Analyzing logical plan, input:Limit: skip=0, fetch=300
  Sort: information_schema.tables.table_name ASC NULLS LAST
    Projection: *
      TableScan: information_schema.tables

2025-02-05T07:01:27.145052Z  INFO on_query{protocol="mysql"}:mysql::do_query:do_query:plan:plan:plan_sql: query::optimizer::type_conversion: [DBG] TypeConversionRule: Transformed logical plan, output:Limit: skip=300, fetch=0
  Sort: information_schema.tables.table_name ASC NULLS LAST
    Projection: *
      TableScan: information_schema.tables

[evenyag]

The exprs of fetch and limit exchange after with_new_exprs()

greptimedb/src/query/src/optimizer/type_conversion.rs

Lines 89 to 106 in 59b3137

	| LogicalPlan::Limit { .. }
	| LogicalPlan::Union { .. }
	| LogicalPlan::Join { .. }
	| LogicalPlan::Distinct { .. }
	| LogicalPlan::Values { .. }
	| LogicalPlan::Analyze { .. } => {
	let mut converter = TypeConverter {
	schema: plan.schema().clone(),
	query_ctx: ctx.query_ctx(),
	};
	let inputs = plan.inputs().into_iter().cloned().collect::<Vec<_>>();
	let expr = plan
	.expressions_consider_join()
	.into_iter()
	.map(|e| e.rewrite(&mut converter).map(|x| x.data))
	.collect::<Result<Vec<_>>>()?;
	plan.with_new_exprs(expr, inputs).map(Transformed::yes)

It is related to datafusion.

[yihong0618]

in this case offset and limit seems wrong(cmp)

the limit is offset the offset is limit

[evenyag]

upstream fix: apache/datafusion#14496

I'm going to implement a workaround. Usually the limit node doesn't require the type conversion.

[evenyag]

I created a nightly build:
https://github.com/GreptimeTeam/greptimedb/actions/runs/13157240241