MySQL Bugs: #116244: "Error: Too many columns" appears only on a UNION and not when creating a VIEW

Bug #116244	"Error: Too many columns" appears only on a UNION and not when creating a VIEW
Submitted:	26 Sep 2024 14:32	Modified:	26 Sep 2024 16:59
Reporter:	Abhay Salvi	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bug, too many columns, UNION ALL, VIEW

Description:
Okay so I am working on a client project and they have two views (view A and view B) that has 1029 columns each. Now they wanted me to create another master view to UNION ALL both View A and View B (since the views are identical so union can be performed). Now when I query view A (1029 columns) and view B (1029 columns) individually, it just loads fine.

However, when I do a union of both viewA + viewB then it gives "error: too many columns."

Since it is a union so the combined master view still has 1029 columns only, but what I am still failing to understand is why does it work when I do a select command on View A or View B (having 1029 columns each) producing no errors but when I do a UNION on ViewA + ViewB, then it gives too many columns error? (the union result is of 1029 columns too)

How to repeat:
Note: The create union view query (ViewX) ran successfully and the error that I am getting is received when I run any command after the view creation.

The query:

CREATE OR REPLACE VIEW ViewX AS
SELECT * FROM ViewA
UNION ALL
SELECT * FROM ViewB;

Then on running the select command:
SELECT ID FROM ViewX LIMIT 1

I got this "Error 1117: Too many columns"

The logic for joining the tables to create ViewA is as follows:

CREATE ALGORITHM = UNDEFINED DEFINER = `email@yahoo.com` @`%` SQL SECURITY DEFINER VIEW `ViewA` AS
select
 ec.ID AS ec_ID,
 pcl.ID AS pcl_ID
 ... (1029 columns)

from
  (
    (
      (
        (
          (
            `table1` `cp`
            left join `table2` `pla` on ((`cp`.`ID` = `pla`.`PaymentID`))
          )
          left join `table3` `pc` on ((`cp`.`ID` = `pc`.`PaymentID`))
        )
        left join `table4` `pcl` on ((`pc`.`ID` = `pcl`.`ClaimID`))
      )
      left join `table5` `cla` on ((`pc`.`ID` = `cla`.`ClaimID`))
    )
    left join `table6` `pcla` on ((`pcl`.`ID` = `pcla`.`LineID`))
  )

Suggested fix:
If ViewA having 1029 columns is not returning any errors, and same with ViewB, then why does ViewX (ViewA UNION ViewB) with 1029 columns is giving too many columns error?

It should have given us this error when I was querying ViewA and ViewB as well...

HI MR. Salvi,

Thank you for your bug report.

However, let us inform you that this is a forum for the reports with fully repeatable test cases. Each test case should consist of a set of SQL statements that always lead to the problem that is reported.

In your case we need all the tables and views involved, which include all CREATE and INSERT commands.

We can't repeat your report without such a test case.

Update: If I remove the "CREATE VIEW AS" statement from the UNION query and just run the plain query, it works. But when I put CREATE VIEW AS before the query, it gives "too many column error"

Hi Mr. Salvi,

We have to refer you to our previous comment.

We truly need a fully repeatable test case, as described .......