Why Does WHERE IN Read Every Row When the Ranges Table Is Empty?

Picture a fictional batch program that runs every night. It first reads the customer numbers whose payments should be blocked that day into a ranges table lr_kunnr, then reads only those customers' open items with WHERE kunnr IN @lr_kunnr and blocks them. Usually there are two or three customers. One day there were none. The program did not do nothing that day: it read the open items of every customer. After reading this you will know why an empty ranges table becomes "no condition", why the same rule is convenient for SELECT-OPTIONS on a selection screen, and where and how to stop it for a ranges table your program fills.

The Z tables zdemo_blocklist and zdemo_open_items, the program ZDEMO_RANGE_EMPTY and customer numbers such as 0000100001 are fictional, and the code was not run on a real system. The results here follow from the rules on the ABAP Keyword Documentation pages for IN range_tab in ABAP SQL, IN in ABAP comparison expressions and SELECT-OPTIONS (7.58, the first two compared with 7.50).

An empty ranges table makes IN always true

A ranges table is an internal table that writes conditions line by line in four columns: SIGN, OPTION, LOW and HIGH. IN turns those lines into comparisons and combines them into one condition. The documentation states that if the ranges table is initial, that is, has no lines, IN range_tab is always true. The ABAP SQL page goes one step further: in that case no condition is passed to the database, and if there is no condition other than the IN, all rows of the data source are selected.

Back to the example: the moment the first SELECT returns 0 rows, lr_kunnr is empty and the second SELECT's WHERE kunnr IN @lr_kunnr effectively disappears. The result is neither an error nor a warning but a normally completed full read. The 7.50 documentation says the same thing, only calling it a "selection table", so the rule has not changed across releases.

SAP NOTES

One SELECT, three states of the ranges table

Fictional example: SELECT ... FROM zdemo_open_items WHERE kunnr IN @lr_kunnr

• Two I lines

  kunnr = '0000100001' OR kunnr = '0000100007'

  • I EQ 0000100001
  • I EQ 0000100007
  • Reads open items of those two only

• No lines at all

  lines( lr_kunnr ) = 0

  • The blocklist SELECT found 0 rows
  • The IN condition is always true
  • No condition goes to the database

• One E line only

  NOT ( kunnr = '0000100003' )

  • E EQ 0000100003
  • Without I lines only the negated E part remains
  • Reads everything except that customer

Tables, fields and customer numbers are fictional. Results follow from the IN rules in the ABAP Keyword Documentation (7.58, 7.50); they are not run results.

The third case in the figure is a trap of the same family. If the ranges table contains only exclude (E) lines, the documented rule has no include (I) part, so only the negated exclude condition remains. A line added to leave out one customer ends up reading every customer except that one.

How IN builds the condition when there are lines

The documentation describes the combination in this order. The comparisons of all lines with SIGN I are joined with OR. The lines with E are also joined with OR and then negated with NOT. If both kinds exist, the two parts are joined with AND. It is easiest to think of the result as the include set minus the exclude set. An OPTION such as EQ, NE or GE becomes a comparison with LOW; BT and NB become an interval condition between LOW and HIGH.

Some things differ only in ABAP SQL. Lines with CP and NP become LIKE conditions, and that LIKE is case-sensitive, the opposite of a CP comparison inside ABAP. The values of the ranges table are also passed to the database as input values of the SQL statement, so according to the documentation, above a limit that depends on the database system but is usually between 2,000 and 10,000, the exception CX_SY_OPEN_SQL_DB is raised. Invalid SIGN or OPTION values raise an uncatchable exception, and from 7.40 SP08 the strict modes of the syntax check verify that LOW and HIGH match the operand's type under the rules for lossless assignments.

SAP NOTES

How IN turns a ranges table into a condition

Each line's SIGN, OPTION, LOW and HIGH become one comparison, then they are combined

• I lines and E lines

  ( I1 OR I2 ) AND NOT ( E1 OR E2 )

  • I lines are joined with OR
  • E lines are joined with OR, then NOT
  • If both exist, they are joined with AND

• Initial ranges table

  SELECT ... FROM zdemo_open_items

  • IN in ABAP: always true
  • IN in ABAP SQL: always true
  • No other condition: every row

• Differences only in ABAP SQL

  CX_SY_OPEN_SQL_DB

  • CP and NP become case-sensitive LIKE
  • Values are passed as SQL input values
  • Above about 2,000-10,000: exception

Based on the ABAP Keyword Documentation pages rel_exp - IN and sql_cond - IN range_tab (7.58) and their 7.50 versions. The input value limit depends on the database system.

The scope differs from the similar-looking case of passing an empty table to FOR ALL ENTRIES. There the whole WHERE condition is ignored; with an empty ranges table only that one IN condition becomes true, and other conditions such as a date stay in force. The causes of an almost-full result are different, so the fixes need separate thought.

Convenient on a selection screen, dangerous inside a program

The reason for the rule becomes clear on a selection screen. SELECT-OPTIONS s_kunnr FOR ... creates a selection table with the same layout as a ranges table and moves the user's input into its lines. If the user leaves the customer field empty, the selection table is empty and WHERE kunnr IN @s_kunnr does not filter by customer. "A condition you did not enter does not restrict" is exactly what you expect from a report screen.

The problem appears when the same syntax is used for a list the program fills itself. There an empty list means "nothing to process", but IN reads it as "no restriction". So the first step of the fix is deciding the meaning, not writing code. For each ranges table, decide whether empty means "everything" or "nothing", and in the second case cut it off yourself before the SELECT, for example with IF lr_kunnr IS INITIAL. RETURN. ENDIF. This matters most when the result drives a change such as blocking, deleting or posting.

SAP NOTES

Steps that stop an empty ranges table

What to put before the SELECT in the same fictional program

1. 01

   Decide what empty means

   lr_kunnr ≠ s_kunnr

   • Selection screen: empty = no restriction
   • List built by the program: empty = none
   • Do not handle both with the same code
2. 02

   Check before the SELECT

   IF lr_kunnr IS INITIAL. RETURN. ENDIF.

   • If the ranges table is empty, stop
   • SELECT-OPTIONS has a header line
   • Check the body with s_kunnr[]
3. 03

   Block it on the screen

   SELECT-OPTIONS s_kunnr FOR lv_kunnr OBLIGATORY.

   • OBLIGATORY: first input field required
   • Empty field: F8 cannot execute
   • Only the first input field is required

The program and variables are fictional and the code was not run. The SELECT-OPTIONS, OBLIGATORY and header line rules are from the ABAP Keyword Documentation 7.58.

The [] you need when checking a selection table

If a report must not run with an empty selection criterion either, there are two ways. One is adding OBLIGATORY to the declaration. The first input field then becomes mandatory: while it is empty, Execute (F8) cannot leave the screen and only Back, Exit or Cancel work. Note that it is the first input field that becomes mandatory.

The other is checking in the program, and there is a small trap here. The selection table created by SELECT-OPTIONS is a standard table with a header line. A header line is a work area with the same name as the table, and according to the documentation the plain name means the header line, not the table body, in most operand positions. So IF s_kunnr IS INITIAL does not check whether the selection table has lines; it checks whether the single header line is empty. To check the body, add brackets: s_kunnr[] IS INITIAL. The documentation recommends neither reading nor changing the header line in the program. By contrast, lr_kunnr declared with TYPE RANGE OF has no header line, so lr_kunnr IS INITIAL already checks the body.

The editor example below puts this order on one screen: ① the SELECT that collects blocked customers into a ranges table, ② the three lines that stop when it is empty, ③ the line that reads open items with IN, and ④ s_kunnr[], which checks the body of the selection table. Wherever an existing program fills a ranges table with a SELECT or a loop and passes it straight to IN, start by checking whether a line like ② is there.

Remember one thing. To IN, an empty ranges table does not mean "nothing"; it means "no restriction". For a ranges table your program fills, write down what should happen when it is empty, in code, right before the SELECT.