1

When I'm executing the code shown here on the SQL Server database (v12.0), I'm getting an error

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

My code:

CREATE TABLE [dbo].[Table]
(
    [test] [INT] NOT NULL
) ON [PRIMARY]
GO

SELECT * 
FROM [dbo].[Table] AS [Table]
WHERE 1 = 1 
  AND (1 = 1 AND 
( (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( 
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((
((((((((((((((((((((((((((((((((((((((((((((((((((
[Table].[test] BETWEEN 1 AND 1
) ))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
))))))))))))))))))))))))))))))))))))))))))))))))))))

DROP TABLE [dbo].[Table]
GO

If I remove one bracket, everything will work (the number of brackets needed to reproduce this differs from version to version).

Does anybody get the same issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dmitry Pashkevich
  • 19
  • 2
  • 2
    Why do you need ***that*** many parenthesis?! – Thom A Dec 12 '18 at 15:38
  • 2
    For what it's worth, this query is accepted by SQL Server 2017. Although I certainly can't fault earlier versions for not accepting it. A naive approach to parsing this query will produce an enormous parse tree, and the optimizer can very well choke on that. See also: huge `IN` lists, huge `CASE` statements and other things that can unwittingly cause parse tree explosions. This parenthesizing is, of course, deliberate sabotage. To answer your question directly: of course other people can get the same error running these queries as we all use SQL Server -- what's your *real* problem? – Jeroen Mostert Dec 12 '18 at 15:39
  • https://stackoverflow.com/questions/31155348/got-error-some-part-of-your-sql-statement-is-nested-too-deeply-only-on-some-s – UnhandledExcepSean Dec 12 '18 at 15:39
  • 3
    Patient: "Doctor, it hurts when I do this" . . . Doctor: "Then don't do that". – Ben Dec 12 '18 at 15:42
  • 1
    Thanks for responce, but it's very simplified version of the issued sql – Dmitry Pashkevich Dec 12 '18 at 15:48
  • It's strange that I need only to remove one bracket and it works. – Dmitry Pashkevich Dec 12 '18 at 15:49
  • And thanks for the link, if it was fixed in the SP versions - that's good, thanks! – Dmitry Pashkevich Dec 12 '18 at 15:51
  • 2
    If your actual code truly requires nested parenthesis that deep you are doing something horribly wrong. – Sean Lange Dec 12 '18 at 15:52
  • Why is it strange? The parser will have an upper limit to the amount of nesting allowed, and you have happened to discover this limit experimentally. No parser on earth will allow *arbitrary* nesting like this; at the very least it'll be restricted to the maximum buffer size for queries, and more pessimistically to how it's represented as a parse tree. Change your wicked, overly-parenthesized ways and do as the message says: "rewrite the query or break it up into smaller queries". Even if newer versions have more tolerant limits, you're going to hit a ceiling eventually. – Jeroen Mostert Dec 12 '18 at 15:53
  • 1
    To put it another way: if you have a system that can generate queries nested 746 levels deep, you've more than likely got a system that can also nest them 10 or 100 or 1000 times deeper than that, and it probably needs a structural fix somewhere, rather than a more tolerant SQL parser. The chance that you've got a real business case for such a query that just needs that amount of nesting is... slim. – Jeroen Mostert Dec 12 '18 at 15:56
  • But maybe in the newer versions the sql brackets are optimized in a some way, I hope... – Dmitry Pashkevich Dec 12 '18 at 15:57
  • Sorry, not to an order of magnitude. My instance of SQL Server 2017 CU12 will choke on 1360 parens. I haven't done a binary search to narrow down the exact number, because that's a waste of time. Bottom line, don't rely on this working to an arbitrary degree. If you generate real queries that produce lots of "empty" brackets like these, loop in some code to remove them before it gets to the server. If they're not empty, try to find ways to produce more linear parse trees instead of blindly wrapping/recursing. – Jeroen Mostert Dec 12 '18 at 16:00

2 Answers2

1

This occurs when the query you have simply has to nest too much. And versions can differ in how the optimizer parses and organizes your query, in which case, the effective upper limit on nesting can be different depending on where you are running it.

I have typically gotten this while working ad hoc through some data cleanup activities, where it is useful to build a monster query, nest-nest-nesting, sub-selects, big case statements, etc. as you successively encounter one issue after the other. In this regard, it's helpful (and saving lots of time) to have it in one monster query before trying to optimize and come up with the final solution. But take it too far, and here you are.

To address this, I end up chunking it with temp tables, replacing a self-contained chunk/subquery of the SQL (or more) with temp tables. For example, say a super-simplified version of the query is this:

select ...
from 
(
  select ...
  from
    (
      select innerfield1, innerfield2, innerfield3, ...
      from ...
      where ...
    ) inner
    join ...
) outer

Then you might do this:

if object_id('tempdb.dbo.#temp_inner', 'U') is not null drop table #temp_inner;
create table #temp_inner
(
  [innerfield1] int, 
  [innerfield2] nvarchar(100), 
  [innerfield3] datetime, 
  ...
)

then populate it once...

insert into #temp_inner
select innerfield1, innerfield2, innerfield3, ...
from ...
where ...

and now replace this portion of the original query:

select ...
from 
(
  select ...
  from #temp_inner inner
    join ...
) outer

And now you can run this outer query over and over with the now-cached inner. This can even be a good idea even if you haven't hit any kind of limit and just have an inner portion has gotten stable and you're now working on the outer.

As commenters mention, there isn't a case for this in production code; queries should be broken up not just because usually it is optimal, but also for basic reasons like ability to troubleshoot, support, etc. But above is nice for complex, ad hoc/initial investigation queries.

troy
  • 382
  • 2
  • 8
0

Why are you doing this? I don't find the sense in it, but I believe the problem is having too many parenthesis, the server doesn't know what to do with it, the difference between how many brackets do you require to get the error might be on the version since every version has a different way to play with the memory but also the Ram from the server.

JulioL85
  • 1
  • 2