ORDER BY is ignored in iceberg table on head

[alsugiliazova]

Company or project name

No response

Describe what's wrong

I get unsorted output from iceberg table even though I use ORDER BY clause.

Does it reproduce on the most recent release?

Yes

How to reproduce

SHOW CREATE TABLE `column_rbac_namespace_ebaed36e_b4cc_11f0_af75_e0c26496f172.table_ebaed39f_b4cc_11f0_919a_e0c26496f172`
FORMAT Pretty

Query id: 8e91dc37-ab10-48bf-8c30-4b73ba2d49d5

   ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
   ┃ statement                                                                                                                                                                      ┃
   ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
1. │ CREATE TABLE column_rbac_database_ebaed3bc_b4cc_11f0_8fac_e0c26496f172.`column_rbac_namespace_ebaed36e_b4cc_11f0_af75_e0c26496f172.table_ebaed39f_b4cc_11f0_919a_e0c26496f172`↴│
   │↳(                                                                                                                                                                             ↴│
   │↳    `boolean_col` Nullable(Bool),                                                                                                                                             ↴│
   │↳    `long_col` Nullable(Int64),                                                                                                                                               ↴│
   │↳    `double_col` Nullable(Float64),                                                                                                                                           ↴│
   │↳    `string_col` Nullable(String),                                                                                                                                            ↴│
   │↳    `date_col` Nullable(Date)                                                                                                                                                 ↴│
   │↳)                                                                                                                                                                             ↴│
   │↳ENGINE = Iceberg('http://minio:9000/warehouse/data/', 'admin', '[HIDDEN]')                                                                                                     │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.010 sec. 

Data for repro:

SELECT *
FROM `column_rbac_namespace_ebaed36e_b4cc_11f0_af75_e0c26496f172.table_ebaed39f_b4cc_11f0_919a_e0c26496f172`
FORMAT Values

(false,54,379.64,'Arn','2021-11-20'),(true,760,190.58,'FPLBYt','2023-02-04'),(false,2963,289.1,'67','2020-06-12'),(true,828,190.68,'ZkwPRQeN','2022-12-29'),(true,1339,231.67,'QnuVd','2020-03-06'),(false,3287,134.6,'2tOy','2023-08-09'),(false,1171,133.18,'IqK3','2022-03-28'),(false,1797,498.67,'EU1','2020-11-09'),(true,3459,298.61,'EynCXJf','2023-01-04'),(false,2787,56.66,'4982519','2023-07-04'),(false,4159,304.88,'NCO','2022-12-22'),(true,4907,162.75,'9','2021-09-21'),(true,475,121.19,'0407714528','2022-05-16'),(true,4418,377.88,'t9OB','2022-11-25'),(false,992,124.7,'bC','2023-04-20'),(true,59,36.43,'0','2020-03-05'),(false,581,257.56,'EHS2J','2021-05-12'),(false,3335,96.01,'AA','2022-08-14'),(true,807,31.25,'214422','2020-06-03'),(false,4509,49.86,'998142403','2022-03-10'),(false,3728,143.34,'Sjs8JHUdK','2022-10-26'),(true,466,480.43,'00','2021-04-26'),(false,983,470.86,'5006532632','2020-09-25'),(false,3746,158.77,'49','2020-07-23'),(true,4405,107.36,'98831','2023-01-17'),(false,4334,4.9,'WfTnUMiok','2021-12-03'),(true,2163,253.2,'04207','2023-07-28'),(false,1324,370.87,'Aae24HZKj','2020-09-18'),(true,2526,182.96,'k','2021-05-26'),(true,2898,390.28,'U5okXkz','2021-01-02'),(false,3373,401.32,'iKtc','2022-08-21'),(true,1635,408.48,'pPlbkrSY','2021-07-25'),(false,4174,200.44,'WfYjYnfwL','2021-10-04'),(false,4967,492.13,'Uj','2020-03-31'),(false,14,260.44,'SUPwePut0','2024-01-14'),(false,4155,155.33,'upgvWJU','2023-06-14'),(false,3327,274.42,'o','2022-05-30'),(false,3810,221.45,'D3Xk','2020-06-22'),(false,4223,332.26,'eXmqPhcZFN','2022-07-21'),(false,4716,98.02,'uhJUSUO','2021-03-24'),(true,4244,232.81,'911455374','2024-01-13'),(false,4489,223.51,'1BU','2023-07-29'),(false,4271,242.81,'wLmr','2023-01-11'),(true,1134,76.26,'jnzuCdZ','2022-03-08'),(true,4717,190.81,'p','2022-03-09'),(false,4410,374.01,'3EqEyPXS0','2020-09-18'),(true,3228,296.35,'6','2020-10-04'),(false,1489,26.09,'lqNtNRn','2020-06-16'),(false,159,374.76,'l','2020-05-20'),(true,255,474.86,'6241','2021-03-22'),(false,2100,383.68,'LUh','2020-12-01'),(false,886,289.76,'P7ySe','2023-11-15'),(true,835,348.9,'Lh8Wv','2022-05-27'),(false,565,253.47,'881968','2023-12-19'),(true,3568,88.89,'qYHDBTq0Z','2020-06-26'),(false,3693,122.69,'Lxb0lnXv','2023-05-05'),(false,4554,6.07,'2452','2022-09-02'),(false,3672,396.67,'lJ','2021-09-19'),(false,3877,277.18,'ZCsxpl','2024-01-17'),(true,2585,60.66,'lnES8Grgm','2021-04-10'),(false,1470,151.83,'295618485','2022-08-19'),(false,3609,171.01,'W','2022-09-05'),(true,536,200.95,'Nd','2020-11-01'),(false,698,496.97,'VL','2023-06-20'),(true,4281,190.81,'SZpGFMXk','2021-03-08'),(false,666,79.37,'eaB','2023-05-01'),(false,2386,17.29,'r1eoq','2023-07-04'),(true,3483,58.27,'jqid','2021-09-21'),(false,3598,63.05,'yqHBL','2022-06-03'),(false,4946,125.93,'98','2023-03-24'),(true,2208,288.55,'Awo','2023-04-13'),(false,4029,484.56,'vqfivzIu','2023-01-31'),(true,3726,44.94,'gOSQa','2023-01-16'),(false,3386,28.05,'GzwcngoTz','2020-02-29'),(true,1298,156.06,'z','2021-04-05'),(true,3781,473.58,'WpK','2022-04-30'),(false,3869,122.62,'395882749','2022-11-05'),(false,22,142.14,'JO1BE','2021-11-11'),(false,3731,466.49,'ox1z','2021-10-13'),(false,3124,193.65,'YgY','2021-11-10'),(true,3607,50.76,'74614936','2023-08-23'),(true,2692,426.32,'XGUzCGmJo','2021-04-10'),(true,3556,489.19,'ip','2020-02-29'),(true,2658,398.28,'thBDP','2020-12-14'),(true,647,305.14,'QEKoNCP','2022-08-10'),(false,4478,79.83,'ZpYJxpZS','2020-08-06'),(true,3125,286.39,'ZpYIoDJMcF','2022-10-14'),(false,1886,303.93,'666768','2023-07-10'),(true,323,155.16,'57027115','2022-01-19'),(false,1348,129.23,'MrfYhYSL','2022-03-24'),(false,740,395.55,'B','2022-07-20'),(false,2148,292.7,'646936','2023-08-19'),(true,3804,454.78,'zi6sE','2020-07-18'),(true,4404,68.7,'IMXvMivf','2022-09-29'),(false,2292,17.33,'498065','2023-08-13'),(false,497,199.67,'9802','2023-08-02'),(true,415,393.61,'7285','2023-04-15'),(true,1909,164.95,'a1i','2020-09-23'),(false,1430,55.85,'0','2021-05-02'),(false,130,87.95,'7','2022-12-13')                                                                                       

SELECT string_col
FROM `column_rbac_namespace_ebaed36e_b4cc_11f0_af75_e0c26496f172.table_ebaed39f_b4cc_11f0_919a_e0c26496f172`
ORDER BY string_col ASC

Query id: 9cae597d-ab73-4496-b1ca-cf37e1e10bf1

     ┌─string_col─┐
  1. │ 2tOy       │
  2. │ 0          │
  3. │ 0          │
  4. │ 4982519    │
  5. │ 49         │
  6. │ 67         │
  7. │ 9          │
  8. │ 5006532632 │
  9. │ Arn        │
 10. │ 998142403  │
 11. │ EU1        │
 12. │ EynCXJf    │
 13. │ 0407714528 │
 14. │ 98831      │
 15. │ FPLBYt     │
 16. │ EHS2J      │
 17. │ IqK3       │
 18. │ NCO        │
 19. │ 00         │
 20. │ QnuVd      │
 21. │ 214422     │
 22. │ Aae24HZKj  │
 23. │ SUPwePut0  │
 24. │ 6          │
 25. │ Sjs8JHUdK  │
 26. │ U5okXkz    │
 27. │ P7ySe      │
 28. │ Uj         │
 29. │ 911455374  │
 30. │ Lh8Wv      │
 31. │ VL         │
 32. │ WfTnUMiok  │
 33. │ D3Xk       │
 34. │ WfYjYnfwL  │
 35. │ 3EqEyPXS0  │
 36. │ 2452       │
 37. │ 98         │
 38. │ WpK        │
 39. │ YgY        │
 40. │ B          │
 41. │ ZCsxpl     │
 42. │ Awo        │
 43. │ ZkwPRQeN   │
 44. │ AA         │
 45. │ bC         │
 46. │ eXmqPhcZFN │
 47. │ 6241       │
 48. │ iKtc       │
 49. │ 1BU        │
 50. │ SZpGFMXk   │
 51. │ 395882749  │
 52. │ 666768     │
 53. │ IMXvMivf   │
 54. │ ip         │
 55. │ GzwcngoTz  │
 56. │ jnzuCdZ    │
 57. │ k          │
 58. │ lJ         │
 59. │ lnES8Grgm  │
 60. │ o          │
 61. │ Lxb0lnXv   │
 62. │ eaB        │
 63. │ JO1BE      │
 64. │ MrfYhYSL   │
 65. │ pPlbkrSY   │
 66. │ p          │
 67. │ 881968     │
 68. │ Nd         │
 69. │ ox1z       │
 70. │ 646936     │
 71. │ a1i        │
 72. │ qYHDBTq0Z  │
 73. │ 295618485  │
 74. │ gOSQa      │
 75. │ 57027115   │
 76. │ r1eoq      │
 77. │ 74614936   │
 78. │ t9OB       │
 79. │ 04207      │
 80. │ uhJUSUO    │
 81. │ LUh        │
 82. │ W          │
 83. │ thBDP      │
 84. │ 7          │
 85. │ upgvWJU    │
 86. │ l          │
 87. │ wLmr       │
 88. │ lqNtNRn    │
 89. │ jqid       │
 90. │ QEKoNCP    │
 91. │ 498065     │
 92. │ yqHBL      │
 93. │ XGUzCGmJo  │
 94. │ ZpYIoDJMcF │
 95. │ 7285       │
 96. │ z          │
 97. │ ZpYJxpZS   │
 98. │ vqfivzIu   │
 99. │ zi6sE      │
100. │ 9802       │
     └─string_col─┘

100 rows in set. Elapsed: 0.037 sec. 

Expected behavior

No response

Error message and/or stacktrace

No response

Additional context

No response

[divanik]

@alsugiliazova, could you send the table as an archieve, please (at least metadata .json and .avro files)?
A link to the table on AWS S3 in open bucket will be very great (of course with no sensitive data)

[divanik]

With high probability it is connected with new Read In Order optimization: #88454

[divanik]

I don't have ideas until we have a repro, so assigned Konstantin in case he has any