Presto returning 0 results when the query contains more than 100 tables

[Din7890]

Presto returning 0 results when the query contains more than 100 tables. Here is the sample query

select table_name, column_name from xxxuser.information_schema.columns where xxxuser.information_schema.columns.table_schema = xxx_user' order by xxxuser.information_schema.columns.table_name; The above query returns non zero results if the number of tables are 100 or less.

Your Environment

• Presto version used: 0.263
• Storage (HDFS/S3/GCS..):
• Data source and connector used: Oracle, Oracle connector
• Deployment (Cloud or On-prem): On-prem
• Pastebin link to the complete debug logs:

Expected Behavior

The query should return the tables and columns from the schema specified in the query.

Current Behavior

The query returns correct results if the number of tables are less or equal to 100. If it has more than 100 tables, it returns 0 rows, and it does not throw any errors in the logs

Possible Solution

Is there any configuration change in Presto, that limits the table scans to 100?

Steps to Reproduce

1. Create 100 tables in oracle
2. Run the above query in presto-cli ie command line and it returns the rows
3. Create 101 tables in oracle
4. Run the above query in presto-cli ie commond line and it returns 0 rows

Screenshots (if appropriate)

Context

[kiersten-stokes]

Interesting - I'm not able to test with Oracle, but I tried PostgreSQL as it has a similar JDBC backing and couldn't reproduce. I see that you say no error message is produced. Does the query have "FINISHED" status? What does the query plan look like when using the UI?

[Din7890]

The query has a "FINISHED" status

[kiersten-stokes]

What is the output when you run EXPLAIN ANALYZE on your query?

Have you tried any other queries on information_schema? And if so, do they succeed?

[Din7890]

Here is the explain plan with 100 tables --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> Fragment 1 [ROUND_ROBIN] > CPU: 38.32ms, Scheduled: 1.11s, Input: 100 rows (2.72kB); per task: avg.: 14.29 std.dev.: 34.99, Output: 100 rows (2.72kB) > Output layout: [table_name, column_name] > Output partitioning: SINGLE [] > Stage Execution Strategy: UNGROUPED_EXECUTION >

• LocalMerge[table_name ASC_NULLS_LAST] => [table_name:varchar, column_name:varchar] > CPU: 3.00ms (0.09%), Scheduled: 66.00ms (0.20%), Output: 100 rows (2.72kB) > Input avg.: 0.89 rows, Input std.dev.: 1053.57% >
  • PartialSort[table_name ASC_NULLS_LAST] => [table_name:varchar, column_name:varchar] > CPU: 18.00ms (0.51%), Scheduled: 827.00ms (2.49%), Output: 100 rows (2.72kB) > Input avg.: 0.89 rows, Input std.dev.: 1053.57% >
    • RemoteSource[2] => [table_name:varchar, column_name:varchar] > CPU: 1.00ms (0.03%), Scheduled: 78.00ms (0.24%), Output: 100 rows (2.72kB) > Input avg.: 0.89 rows, Input std.dev.: 1053.57% >

      Fragment 2 [SOURCE] > CPU: 3.50s, Scheduled: 32.21s, Input: 100 rows (4.67kB); per task: avg.: 100.00 std.dev.: 0.00, Output: 100 rows (2.72kB) > Output layout: [table_name, column_name] > Output partitioning: ROUND_ROBIN [] > Stage Execution Strategy: UNGROUPED_EXECUTION >

• ScanFilterProject[table = TableHandle {connectorId='$info_schema@oidsstageuser', connectorHandle='oidsstageuser:information_schema:columns', layout='Optional[oidsstageus> CPU: 3.50s (99.38%), Scheduled: 32.21s (97.07%), Output: 100 rows (2.72kB) > Input avg.: 100.00 rows, Input std.dev.: 0.00% > table_name := table_name > table_schema := table_schema > column_name := column_name > Input: 100 rows (4.67kB), Filtered: 0.00% >

  (1 row)

[Din7890]

Here is the explain plan with 101 tables

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> Fragment 1 [ROUND_ROBIN] > CPU: 29.20ms, Scheduled: 764.43ms, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00, Output: 0 rows (0B) > Output layout: [table_name, column_name] > Output partitioning: SINGLE [] > Stage Execution Strategy: UNGROUPED_EXECUTION >

• LocalMerge[table_name ASC_NULLS_LAST] => [table_name:varchar, column_name:varchar] > CPU: 0.00ns (0.00%), Scheduled: 31.00ms (6.83%), Output: 0 rows (0B) > Input avg.: 0.00 rows, Input std.dev.: ?% >
  • PartialSort[table_name ASC_NULLS_LAST] => [table_name:varchar, column_name:varchar] > CPU: 14.00ms (100.00%), Scheduled: 423.00ms (93.17%), Output: 0 rows (0B) > Input avg.: 0.00 rows, Input std.dev.: ?% >
    • RemoteSource[2] => [table_name:varchar, column_name:varchar] > CPU: 0.00ns (0.00%), Scheduled: 0.00ns (0.00%), Output: 0 rows (0B) > Input avg.: 0.00 rows, Input std.dev.: ?% >

      Fragment 2 [SOURCE] > CPU: 445.50us, Scheduled: 462.50us, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00, Output: 0 rows (0B) > Output layout: [table_name, column_name] > Output partitioning: ROUND_ROBIN [] > Stage Execution Strategy: UNGROUPED_EXECUTION >

• ScanFilterProject[table = TableHandle {connectorId='$info_schema@oidsstageuser', connectorHandle='oidsstageuser:information_schema:columns', layout='Optional[oidsstageus> CPU: 0.00ns (0.00%), Scheduled: 0.00ns (0.00%), Output: 0 rows (0B) > Input avg.: 0.00 rows, Input std.dev.: ?% > table_name := table_name > table_schema := table_schema > column_name := column_name > Input: 0 rows (0B), Filtered: ?% >

  (1 row)

[Din7890]

I have same issue with other schemas too where if the tables are more than 100 the query returns 0 results

[kiersten-stokes]

So different schemas but the same Oracle connector?

[Din7890]

Yes that is correct