There is a large sql query (almost 1500 lines), inherited, but not functioning correctly. Need to otdebezhit. Tell me how it would be right to understand it?

It now works, but returns 0 rows.

  • perform parts starting from the deepest layers of nesting. minimize the number of glued tables. then add tables and see if the necessary rows are missing - Mike
  • @Mike, on the go, so be it. I hoped that there were some available tools for convenience. - Vasily Koshelev
  • You just bring here the code block of your request, then it will be easier to solve your question - Denis Bubnov
  • With such volumes of text, it is better to first decide whether the logic of what this query does is clear. And to think whether it is not easier to take a stupid way to write your version from zero, rather than digging in a [censored] one. - Akina

1 answer 1

With inheritance in our business, usually lucky :-) sql-request is in the procedural sense one indivisible instruction, so the term "debugging" does not quite fit. recommendations are:

  1. simplifying the request - try to isolate some obvious parts of the request and create a view based on them, make sure first that the view works as it should, and from the request, in turn, turn to the view - this will simplify the logic and probably allow you to localize the problem
  2. analysis of the query execution plan is in any case a useful exercise, since, in addition to identifying logical errors, it will help to identify performance problems (for example, filters without indices, etc.). This is the command EXPLAIN <request> - will give you a log of all actions that the DBMS server performs when executing the request