Using sub-selectors will fail if too many pages are chosen

[jlahijani]

Let's say I have this simple selector to choose all the orders in my system (I'm using findMany):

parent=/orders/,template=order

That works fine, but I want it to be more precise by finding only the pages based on checking something in a repeater that is part of the order template (called "timings", which is where I'm storing when an order should proceed to production, when it should be redacted, when it should be entirely deleted).

parent=/orders/,template=order,timings=[timing_type.name=proceed-after,datetime!='',datetime<{$time}]

That works fine too, but in my situation if there exceeds a certain amount of 'order' pages for which that potentially matches, MariaDB fails with this:

SQLSTATE[0A000]: Feature not supported: 140 Aria table '#sql-temptable-236b0e-c5f-14db' has too many columns and/or indexes and/or unique constraints.

That seems to be an incredibly rare issue since Googling that error it brings up 1-2 pages if searching it like so: https://www.google.com/search?q=%22has+too+many+columns+and%2For+indexes+and%2For+unique+constraints%22

I investigated and tried changing $config->lazyPageChunkSize to something much lower than the default of 250 would help, but it didn't.

I then asked ChatGPT about the error and it said:

---

Details

The error you're seeing:

SQLSTATE[0A000]: Feature not supported: 140 Aria table '#sql-temptable-236b0e-c5f-14db' has too many columns and/or indexes and/or unique constraints.

means MariaDB/MySQL attempted to create a temporary table using the Aria storage engine, but the number of columns, indexes, or unique constraints exceeded a limit that Aria supports.

What's happening:

• MySQL/MariaDB sometimes creates internal temporary tables (e.g. for GROUP BY, ORDER BY, UNION, complex joins).
• If the result set is small or simple, it uses in-memory tables.
• If not, it spills to disk using Aria (formerly MyISAM) for the temporary table.
• Aria has stricter limits than InnoDB (e.g. max 1,017 columns per table).

Common causes:

• A SELECT with many columns
• A GROUP BY or ORDER BY on many fields
• A subquery or derived table with excessive complexity

Solutions:

✅ Preferred: Reduce result complexity

• SELECT fewer columns — only what you need
• Avoid SELECT * on tables with lots of fields
• Simplify complex JOINs or UNIONs
• Avoid over-indexing temporary derived tables

⚙️ Optional: Force different temporary storage

You can change how temporary tables are handled:

SET SESSION internal_tmp_mem_storage_engine = 'TempTable';

Or if needed (but not recommended long-term):

SET SESSION internal_tmp_disk_storage_engine = 'InnoDB';

This forces MySQL/MariaDB to use InnoDB for disk-based temp tables, which supports more columns and features than Aria.

How to confirm what’s going on:

Run:

SHOW VARIABLES LIKE '%tmp%engine%';

To see what your current temp storage engines are.

If you're getting this in application code, try simplifying the query or enabling query logging to inspect what SQL is triggering it.

Let me know if you want help breaking down or optimizing a specific query.

---

I don't know the inner-workings of how selectors ultimately join tables and such, but I'm guessing it's hitting some sort of limit.

To fix for now, I'm falling back to just doing the simple selector:

parent=/orders/,template=order

Then using conditional logic to see if the timing is what I want:

if($order->timings->get("[timing_type.name=proceed-after,datetime!='',datetime<{$time}]")) {
  // code to proceed on this order
}

[jlahijani]

@ryancramerdesign This is a pretty big issue and it also occurs when using owner selector on large sets of data.

I'm getting bit by this one and having to use additional fields with the same data to work-around it.

[jlahijani]

Actually it's not an incredibly rare issue. I Googled wrongly. Better search results: https://www.google.com/search?q=mysql+too+many+columns

[jlahijani]

I spent a couple hours ~~slopping~~ using Copilot in Agent mode with Claude Sonnet 4.

Long story short, the issue has to do with FieldtypeRepeater.php and how it does a page subfield lookups: https://github.com/processwire/processwire/blob/dev/wire/modules/Fieldtype/FieldtypeRepeater/FieldtypeRepeater.module#L1486

If the subfield is a page and you're trying to match on the page's name (for example), it will work if you have around 1000 pages. But somewhere above that (1500 for sure), it will cause the "Aria too many columns" issue, assuming default MariaDB configuration.

I tried to get Copilot to fix it and ... ... ... it didn't work, although it's fun to see the solutions it tried which I won't get into. It slopped (I love using that word) through it with confidence but none of the slop worked. Actually these agent-based AIs that have all of your codebase in context are pretty good but definitely not in these really extreme edge cases although it felt like it was getting close with each iteration. Eventually I gave up.

A human brain will be needed for this.

@ryancramerdesign Let me know if you need additional info. I'm guessing you never experienced this bug because you probably didn't test subselectors (where the subselector is a page field) that would match 1500+ pages.

Note: If the subselector is not a page field, it works without any issues.

[JanRomero]

I’m curious what the generated SQL query looks like. Are you able to share it?

[BrendonKoz]

Would a count of matches of the subselector, set as an option in a PW selector string, be an acceptable tradeoff of speed (requiring a separate query prior to the intended selector query) to temporarily change the table storage mechanism? That would leave the issue up to the developer to take advantage of the option rather than PW always having to handle the edge case just in case.

I'm not sure if that's an appropriate solution, but at least maybe a potential one that might not require too much brain strain.