Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Window function error: InvalidArgumentError("number of columns(27) must match number of fields(35) in schema" #5090

Closed
alamb opened this issue Jan 27, 2023 · 2 comments · Fixed by #5131
Closed

Window function error: InvalidArgumentError("number of columns(27) must match number of fields(35) in schema" #5090

alamb opened this issue Jan 27, 2023 · 2 comments · Fixed by #5131
Labels
bug Something isn't working

Comments

@alamb
Copy link
Contributor

alamb commented Jan 27, 2023

Describe the bug
InvalidArgumentError("number of columns(27) must match number of fields(35) in schema" with window functions and a large number of target partitions

To Reproduce

Using datafusion-cli (note setting the target_partitions is required -- the query executes fine with 16 partitions)

DataFusion CLI v16.0.0
❯ set datafusion.execution.target_partitions = 32;
0 rows in set. Query took 0.001 seconds.
(failed reverse-i-search)`': SELECT a.id, a.name, count(distinct b.id)
❯ set datafusion.execution.target_partitions = 32;
0 rows in set. Query took 0.000 seconds.
❯ CREATE EXTERNAL TABLE aggregate_test_100_by_sql (
  c1  VARCHAR NOT NULL,
  c2  TINYINT NOT NULL,
  c3  SMALLINT NOT NULL,
  c4  SMALLINT,
  c5  INT,
  c6  BIGINT NOT NULL,
  c7  SMALLINT NOT NULL,
  c8  INT NOT NULL,
  c9  BIGINT UNSIGNED NOT NULL,
  c10 VARCHAR NOT NULL,
  c11 FLOAT NOT NULL,
  c12 DOUBLE NOT NULL,
  c13 VARCHAR NOT NULL
)
STORED AS CSV
WITH HEADER ROW
LOCATION 'testing/data/csv/aggregate_test_100.csv';
0 rows in set. Query took 0.001 seconds.
❯ SELECT
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation1,
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation2,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation3,
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation4,
    SUM(c3) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation5,
    SUM(c5) OVER(ORDER BY c3 DESC GROUPS 2 PRECEDING) as summation6,
    SUM(c5) OVER(ORDER BY c3 DESC GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation7,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation8,
    SUM(c4) OVER(ORDER BY c4 DESC GROUPS UNBOUNDED PRECEDING) as summation9,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS CURRENT ROW) as summation10,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation11,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation12,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation13,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation21,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation22,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation23,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation24,
    SUM(c3) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation25,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c3 GROUPS 2 PRECEDING) as summation26,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation27,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation28,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c4 GROUPS UNBOUNDED PRECEDING) as summation29,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS CURRENT ROW) as summation30,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation31,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation32,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation33,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation41,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation42,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation43,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation44,
    SUM(c3) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation45,
    SUM(c5) OVER(ORDER BY c5, c3 GROUPS 2 PRECEDING) as summation46,
    SUM(c5) OVER(ORDER BY c5, c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation47,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation48,
    SUM(c4) OVER(ORDER BY c5, c4 GROUPS UNBOUNDED PRECEDING) as summation49,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS CURRENT ROW) as summation50,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation51,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation52,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation53,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation61,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation62,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation63,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation64,
    SUM(c3) OVER(ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation65,
    SUM(c5) OVER(ORDER BY c3 GROUPS 2 PRECEDING) as summation66,
    SUM(c5) OVER(ORDER BY c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation67,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation68,
    SUM(c4) OVER(ORDER BY c4 GROUPS UNBOUNDED PRECEDING) as summation69,
    SUM(c5) OVER(ORDER BY c4 GROUPS CURRENT ROW) as summation70,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation71,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation72,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation73
FROM aggregate_test_100_by_sql
ORDER BY c9;
ArrowError(ExternalError(ArrowError(ExternalError(ArrowError(ExternalError(ArrowError(ExternalError(ArrowError(InvalidArgumentError("number of columns(27) must match number of fields(35) in schema"))))))))))

Expected behavior

The query should run and get the same answer with fewer partitions:

❯ set datafusion.execution.target_partitions = 16;
0 rows in set. Query took 0.000 seconds.
❯ SELECT
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation1,
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation2,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation3,
    SUM(c4) OVER(ORDER BY c3 DESC GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation4,
    SUM(c3) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation5,
    SUM(c5) OVER(ORDER BY c3 DESC GROUPS 2 PRECEDING) as summation6,
    SUM(c5) OVER(ORDER BY c3 DESC GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation7,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation8,
    SUM(c4) OVER(ORDER BY c4 DESC GROUPS UNBOUNDED PRECEDING) as summation9,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS CURRENT ROW) as summation10,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation11,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation12,
    SUM(c5) OVER(ORDER BY c4 DESC GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation13,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation21,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation22,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation23,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation24,
    SUM(c3) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation25,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c3 GROUPS 2 PRECEDING) as summation26,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation27,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation28,
    SUM(c4) OVER(PARTITION BY c4 ORDER BY c4 GROUPS UNBOUNDED PRECEDING) as summation29,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS CURRENT ROW) as summation30,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation31,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation32,
    SUM(c5) OVER(PARTITION BY c4 ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation33,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation41,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation42,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation43,
    SUM(c4) OVER(ORDER BY c5, c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation44,
    SUM(c3) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation45,
    SUM(c5) OVER(ORDER BY c5, c3 GROUPS 2 PRECEDING) as summation46,
    SUM(c5) OVER(ORDER BY c5, c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation47,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation48,
    SUM(c4) OVER(ORDER BY c5, c4 GROUPS UNBOUNDED PRECEDING) as summation49,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS CURRENT ROW) as summation50,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation51,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation52,
    SUM(c5) OVER(ORDER BY c5, c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation53,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as summation61,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING) as summation62,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 2 PRECEDING AND 2 PRECEDING) as summation63,
    SUM(c4) OVER(ORDER BY c3 GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) as summation64,
    SUM(c3) OVER(ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as summation65,
    SUM(c5) OVER(ORDER BY c3 GROUPS 2 PRECEDING) as summation66,
    SUM(c5) OVER(ORDER BY c3 GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING) as summation67,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as summation68,
    SUM(c4) OVER(ORDER BY c4 GROUPS UNBOUNDED PRECEDING) as summation69,
    SUM(c5) OVER(ORDER BY c4 GROUPS CURRENT ROW) as summation70,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as summation71,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as summation72,
    SUM(c5) OVER(ORDER BY c4 GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) as summation73
FROM aggregate_test_100_by_sql
ORDER BY c9;
+------------+------------+-------------+------------+------------+-------------+-------------+-------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| summation1 | summation2 | summation3  | summation4 | summation5 | summation6  | summation7  | summation8  | summation9 | summation10 | summation11 | summation12 | summation13 | summation21 | summation22 | summation23 | summation24 | summation25 | summation26 | summation27 | summation28 | summation29 | summation30 | summation31 | summation32 | summation33 | summation41 | summation42 | summation43 | summation44 | summation45 | summation46 | summation47 | summation48 | summation49 | summation50 | summation51 | summation52 | summation53 | summation61 | summation62 | summation63 | summation64 | summation65 | summation66 | summation67 | summation68 | summation69 | summation70 | summation71 | summation72 | summation73 |
+------------+------------+-------------+------------+------------+-------------+-------------+-------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| -35127     | -20071     | 1171968280  | -44741     | -60        | -1045189740 | 1645376618  | 15862627961 | 728662     | 61035129    | 2252131671  | 4277743253  | 2191096542  | -16110      |             |             |             |             | 61035129    | 61035129    | 61035129    | -16110      | 61035129    | 61035129    | 61035129    |             | 22943       | 14370       | 41423756    | 51422       | -76         | 152325502   | 813503800   | 15862627961 | 145307      | 61035129    | 65801428664 | 65851295281 | 65740393535 | -37724      | -22668      | 623103518   | 3056        | -94         | 311073214   | -1380600149 | 15862627961 | -512775     | 61035129    | 13671531419 | 11863321054 | 13610496290 |
| 37269      | 20967      | 1213926989  | -54145     | -59        | -1574720463 | 1639694101  | 15862627961 | 951392     | -108973366  | 5255341318  | 5017916272  | 5364314684  | 3917        |             |             |             |             | -108973366  | -108973366  | -108973366  | 3917        | -108973366  | -108973366  | -108973366  |             | 36569       | 15411       | -168758331  | 30244       | -111        | -411945604  | -21912375   | 15862627961 | 131173      | -108973366  | 65779516289 | 65645302382 | 65888489655 | -31020      | -47322      | 1436496767  | 40175       | -79         | 1208315423  | -1743478794 | 15862627961 | -715478     | -108973366  | 10498313277 | 8380366394  | 10607286643 |
| 14062      | 11006      | 61035129    | -15056     | -48        | -1285298976 | 1303653581  | 15862627961 | 695376     | 623103518   | 3999306907  | 2191096542  | 3376203389  | -16974      |             |             |             |             | 623103518   | 623103518   | 623103518   | -16974      | 623103518   | 623103518   | 623103518   |             | -80790      | -20401      | 439738328   | -23739      | 3           | 1649686324  | 2655635766  | 15862627961 | 137382      | 623103518   | 61887961550 | 62474806028 | 61264858032 | -35127      | -38183      | 994303988   | 29390       | -60         | -1045189740 | -3184474087 | 15862627961 | -480353     | 623103518   | 12486424572 | 12926162900 | 11863321054 |
...

| 12156      | 17360      | 2143473091  | 32854      | 30         | 3137829300  | 3279369834  | 15862627961 | 744772     | 2025611582  | 4277743253  | 5449711533  | 2252131671  | -15880      |             |             |             |             | 2025611582  | 2025611582  | 2025611582  | -15880      | 2025611582  | 2025611582  | 2025611582  |             | 9709        | 338         | 1991172974  | 18539       | -95         | 6009977746  | 8137215311  | 15862627961 | 199008      | 2025611582  | 18556152866 | 20549346056 | 16530541284 | 14039       | 19243       | -1808210365 | 14425       | -85         | 2065442845  | 4309797580  | 15862627961 | -528655     | 2025611582  | 13610496290 | 13671531419 | 11584884708 |
+------------+------------+-------------+------------+------------+-------------+-------------+-------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
100 rows in set. Query took 0.049 seconds.
❯

Additional context
found as part of #5082
@alamb alamb added the bug Something isn't working label Jan 27, 2023
@alamb alamb mentioned this issue Jan 27, 2023
Merged
@alamb
Copy link
Contributor Author

alamb commented Jan 27, 2023

cc @metesynnada @mustafasrepo as this may be interesting to you.

@ozankabak
Copy link
Contributor

We will take a look, thanks.

@mustafasrepo mustafasrepo mentioned this issue Jan 31, 2023
Merged
@alamb alamb mentioned this issue Feb 11, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Bug fix: Empty Record Batch handling
2 participants
@alamb @ozankabak