search

matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.79k stars 277 forks source link

[Bug]: Fix `LIMIT` in Master Index #14877

Closed arjunsk closed 8 months ago

arjunsk commented 8 months ago

Is there an existing issue for the same bug?

Branch Name

main

Commit ID

128d0f3e77e8cf60f0a29b7c07cb96f93e7ff5d9

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

SELECT tbl.a100  FROM tbl  WHERE tbl.a1 BETWEEN '3' AND 'y' AND tbl.a2 BETWEEN '2' AND '{' AND tbl.a3 BETWEEN '3' AND '{' AND tbl.a4 BETWEEN '0' AND 'z' AND tbl.a5 BETWEEN '1' AND '{' AND tbl.a6 BETWEEN '2' AND 'z' AND tbl.a7 BETWEEN '1' AND 'y' AND tbl.a8 BETWEEN '0' AND 'y' AND tbl.a9 BETWEEN '3' AND '{' AND tbl.a10 BETWEEN '1' AND '{' AND tbl.a11 BETWEEN '1' AND 'y' AND tbl.a12 BETWEEN '0' AND 'z' AND tbl.a13 BETWEEN '1' AND 'z' AND tbl.a14 BETWEEN '2' AND '{' AND tbl.a15 BETWEEN '2' AND 'y' AND tbl.a16 BETWEEN '3' AND 'z' AND tbl.a17 BETWEEN '2' AND 'y' AND tbl.a18 BETWEEN '3' AND 'z' AND tbl.a19 BETWEEN '0' AND 'z' AND tbl.a20 BETWEEN '2' AND 'y' AND tbl.a21 BETWEEN '3' AND 'z' AND tbl.a22 BETWEEN '0' AND 'y' AND tbl.a23 BETWEEN '1' AND '{' AND tbl.a24 BETWEEN '2' AND '{' AND tbl.a25 BETWEEN '1' AND 'z' AND tbl.a26 BETWEEN '1' AND 'z' AND tbl.a27 BETWEEN '1' AND 'z' AND tbl.a28 BETWEEN '0' AND '{' AND tbl.a29 BETWEEN '0' AND '{' AND tbl.a30 BETWEEN '3' AND 'z' AND tbl.a31 BETWEEN '3' AND 'y' AND tbl.a32 BETWEEN '3' AND 'y' AND tbl.a33 BETWEEN '3' AND 'y' AND tbl.a34 BETWEEN '0' AND 'z' AND tbl.a35 BETWEEN '3' AND 'y' AND tbl.a36 BETWEEN '1' AND 'y' AND tbl.a37 BETWEEN '3' AND '{' AND tbl.a38 BETWEEN '1' AND 'y' AND tbl.a39 BETWEEN '3' AND '{' AND tbl.a40 BETWEEN '2' AND '{' AND tbl.a41 BETWEEN '0' AND 'y' AND tbl.a42 BETWEEN '0' AND 'y' AND tbl.a43 BETWEEN '1' AND '{' AND tbl.a44 BETWEEN '0' AND 'z' AND tbl.a45 BETWEEN '0' AND 'y' AND tbl.a46 BETWEEN '3' AND '{' AND tbl.a47 BETWEEN '3' AND 'y' AND tbl.a48 BETWEEN '0' AND 'z' AND tbl.a49 BETWEEN '2' AND '{' AND tbl.a50 BETWEEN '2' AND 'y' AND tbl.a51 BETWEEN '2' AND '{' AND tbl.a52 BETWEEN '2' AND 'y' AND tbl.a53 BETWEEN '0' AND 'z' AND tbl.a54 BETWEEN '2' AND 'z' AND tbl.a55 BETWEEN '0' AND 'z' AND tbl.a56 BETWEEN '1' AND 'y' AND tbl.a57 BETWEEN '3' AND '{' AND tbl.a58 BETWEEN '2' AND 'y' AND tbl.a59 BETWEEN '1' AND 'y' AND tbl.a60 BETWEEN '1' AND 'y' AND tbl.a61 BETWEEN '2' AND '{' AND tbl.a62 BETWEEN '2' AND 'z' AND tbl.a63 BETWEEN '0' AND 'y' AND tbl.a64 BETWEEN '0' AND 'y' AND tbl.a65 BETWEEN '3' AND 'y' AND tbl.a66 BETWEEN '1' AND '{' AND tbl.a67 BETWEEN '2' AND 'z' AND tbl.a68 BETWEEN '3' AND '{' AND tbl.a69 BETWEEN '2' AND 'z' AND tbl.a70 BETWEEN '1' AND 'z' AND tbl.a71 BETWEEN '0' AND 'y' AND tbl.a72 BETWEEN '3' AND 'y' AND tbl.a73 BETWEEN '3' AND 'z' AND tbl.a74 BETWEEN '3' AND '{' AND tbl.a75 BETWEEN '3' AND 'y' AND tbl.a76 BETWEEN '3' AND '{' AND tbl.a77 BETWEEN '2' AND '{' AND tbl.a78 BETWEEN '3' AND 'z' AND tbl.a79 BETWEEN '1' AND 'z' AND tbl.a80 BETWEEN '0' AND 'y' AND tbl.a81 BETWEEN '3' AND '{' AND tbl.a82 BETWEEN '3' AND 'y' AND tbl.a83 BETWEEN '1' AND 'z' AND tbl.a84 BETWEEN '0' AND '{' AND tbl.a85 BETWEEN '3' AND '{' AND tbl.a86 BETWEEN '2' AND 'y' AND tbl.a87 BETWEEN '3' AND 'y' AND tbl.a88 BETWEEN '2' AND 'y' AND tbl.a89 BETWEEN '3' AND 'y' AND tbl.a90 BETWEEN '1' AND 'z' AND tbl.a91 BETWEEN '0' AND 'y' AND tbl.a92 BETWEEN '2' AND 'y' AND tbl.a93 BETWEEN '1' AND '{' AND tbl.a94 BETWEEN '1' AND '{' AND tbl.a95 BETWEEN '3' AND '{' AND tbl.a96 BETWEEN '1' AND 'z' AND tbl.a97 BETWEEN '0' AND '{' AND tbl.a98 BETWEEN '1' AND 'y' AND tbl.a99 BETWEEN '3' AND '{' limit 0,100;

Empty set

Expected Behavior

SELECT tbl.a100  FROM tbl  WHERE tbl.a1 BETWEEN '3' AND 'y' AND tbl.a2 BETWEEN '2' AND '{' AND tbl.a3 BETWEEN '3' AND '{' AND tbl.a4 BETWEEN '0' AND 'z' AND tbl.a5 BETWEEN '1' AND '{' AND tbl.a6 BETWEEN '2' AND 'z' AND tbl.a7 BETWEEN '1' AND 'y' AND tbl.a8 BETWEEN '0' AND 'y' AND tbl.a9 BETWEEN '3' AND '{' AND tbl.a10 BETWEEN '1' AND '{' AND tbl.a11 BETWEEN '1' AND 'y' AND tbl.a12 BETWEEN '0' AND 'z' AND tbl.a13 BETWEEN '1' AND 'z' AND tbl.a14 BETWEEN '2' AND '{' AND tbl.a15 BETWEEN '2' AND 'y' AND tbl.a16 BETWEEN '3' AND 'z' AND tbl.a17 BETWEEN '2' AND 'y' AND tbl.a18 BETWEEN '3' AND 'z' AND tbl.a19 BETWEEN '0' AND 'z' AND tbl.a20 BETWEEN '2' AND 'y' AND tbl.a21 BETWEEN '3' AND 'z' AND tbl.a22 BETWEEN '0' AND 'y' AND tbl.a23 BETWEEN '1' AND '{' AND tbl.a24 BETWEEN '2' AND '{' AND tbl.a25 BETWEEN '1' AND 'z' AND tbl.a26 BETWEEN '1' AND 'z' AND tbl.a27 BETWEEN '1' AND 'z' AND tbl.a28 BETWEEN '0' AND '{' AND tbl.a29 BETWEEN '0' AND '{' AND tbl.a30 BETWEEN '3' AND 'z' AND tbl.a31 BETWEEN '3' AND 'y' AND tbl.a32 BETWEEN '3' AND 'y' AND tbl.a33 BETWEEN '3' AND 'y' AND tbl.a34 BETWEEN '0' AND 'z' AND tbl.a35 BETWEEN '3' AND 'y' AND tbl.a36 BETWEEN '1' AND 'y' AND tbl.a37 BETWEEN '3' AND '{' AND tbl.a38 BETWEEN '1' AND 'y' AND tbl.a39 BETWEEN '3' AND '{' AND tbl.a40 BETWEEN '2' AND '{' AND tbl.a41 BETWEEN '0' AND 'y' AND tbl.a42 BETWEEN '0' AND 'y' AND tbl.a43 BETWEEN '1' AND '{' AND tbl.a44 BETWEEN '0' AND 'z' AND tbl.a45 BETWEEN '0' AND 'y' AND tbl.a46 BETWEEN '3' AND '{' AND tbl.a47 BETWEEN '3' AND 'y' AND tbl.a48 BETWEEN '0' AND 'z' AND tbl.a49 BETWEEN '2' AND '{' AND tbl.a50 BETWEEN '2' AND 'y' AND tbl.a51 BETWEEN '2' AND '{' AND tbl.a52 BETWEEN '2' AND 'y' AND tbl.a53 BETWEEN '0' AND 'z' AND tbl.a54 BETWEEN '2' AND 'z' AND tbl.a55 BETWEEN '0' AND 'z' AND tbl.a56 BETWEEN '1' AND 'y' AND tbl.a57 BETWEEN '3' AND '{' AND tbl.a58 BETWEEN '2' AND 'y' AND tbl.a59 BETWEEN '1' AND 'y' AND tbl.a60 BETWEEN '1' AND 'y' AND tbl.a61 BETWEEN '2' AND '{' AND tbl.a62 BETWEEN '2' AND 'z' AND tbl.a63 BETWEEN '0' AND 'y' AND tbl.a64 BETWEEN '0' AND 'y' AND tbl.a65 BETWEEN '3' AND 'y' AND tbl.a66 BETWEEN '1' AND '{' AND tbl.a67 BETWEEN '2' AND 'z' AND tbl.a68 BETWEEN '3' AND '{' AND tbl.a69 BETWEEN '2' AND 'z' AND tbl.a70 BETWEEN '1' AND 'z' AND tbl.a71 BETWEEN '0' AND 'y' AND tbl.a72 BETWEEN '3' AND 'y' AND tbl.a73 BETWEEN '3' AND 'z' AND tbl.a74 BETWEEN '3' AND '{' AND tbl.a75 BETWEEN '3' AND 'y' AND tbl.a76 BETWEEN '3' AND '{' AND tbl.a77 BETWEEN '2' AND '{' AND tbl.a78 BETWEEN '3' AND 'z' AND tbl.a79 BETWEEN '1' AND 'z' AND tbl.a80 BETWEEN '0' AND 'y' AND tbl.a81 BETWEEN '3' AND '{' AND tbl.a82 BETWEEN '3' AND 'y' AND tbl.a83 BETWEEN '1' AND 'z' AND tbl.a84 BETWEEN '0' AND '{' AND tbl.a85 BETWEEN '3' AND '{' AND tbl.a86 BETWEEN '2' AND 'y' AND tbl.a87 BETWEEN '3' AND 'y' AND tbl.a88 BETWEEN '2' AND 'y' AND tbl.a89 BETWEEN '3' AND 'y' AND tbl.a90 BETWEEN '1' AND 'z' AND tbl.a91 BETWEEN '0' AND 'y' AND tbl.a92 BETWEEN '2' AND 'y' AND tbl.a93 BETWEEN '1' AND '{' AND tbl.a94 BETWEEN '1' AND '{' AND tbl.a95 BETWEEN '3' AND '{' AND tbl.a96 BETWEEN '1' AND 'z' AND tbl.a97 BETWEEN '0' AND '{' AND tbl.a98 BETWEEN '1' AND 'y' AND tbl.a99 BETWEEN '3' AND '{' limit 0,100;

should return 100 rows

Steps to Reproduce

  1. Import data a.sql.zip
SOURCE /Users/arjunsunilkumar/Downloads/a.sql
  1. Run the above query

Additional information

No response

arjunsk commented 8 months ago

Fixed with this PR: https://github.com/matrixorigin/matrixone/pull/14933

output:

image

image