oracle produce different result for two same queries when only change field,value

Question

I have SQL query . When I declare variable and run the query it produce 39 rows but when I use a literal value instead of variable and run query it produce three rows. How can it be ?

Here is query and results with variables

declare 
    fromnode number :=1;
    CURRENTESTIMATE number :=0;
begin
  for ts in (SELECT  e.fromnode,e.tonode,e.weight
      FROM TS_DIJNODEESTIMATE N 
      INNER JOIN TS_EDGE E ON N.ID=E.TONODE
      WHERE N.DONE=0 AND E.FROMNODE=fromnode  AND (CURRENTESTIMATE+E.WEIGHT)<N.ESTIMATE)
      loop
        dbms_output.put_line(ts.fromnode || ',' || ts.tonode || ',' || ts.weight);
      end loop;

end;

The result is

1,2,1306
1,5,2161
1,6,2661
2,3,919
2,4,629
3,2,919
3,4,435
3,5,1225
3,7,1983
4,2,629
4,3,435
5,3,1225
5,6,1483
5,7,1258
6,5,1483
6,7,1532
6,8,661
7,3,1983
7,5,1258
7,6,1532
7,9,2113
7,12,2161
8,6,661
8,9,1145
8,10,1613
9,7,2113
9,8,1145
9,10,725
9,11,383
9,12,1709
10,8,1613
10,9,725
10,11,338
11,9,383
11,10,338
11,12,2145
12,7,2161
12,9,1709
12,11,2145

With literal instead of variable:

declare 
    fromnode number :=1;
    CURRENTESTIMATE number :=0;
begin
  for ts in (SELECT  e.fromnode,e.tonode,e.weight
      FROM TS_DIJNODEESTIMATE N 
      INNER JOIN TS_EDGE E ON N.ID=E.TONODE
      WHERE N.DONE=0 AND E.FROMNODE=1  AND (0+E.WEIGHT)<N.ESTIMATE)
      loop
        dbms_output.put_line(ts.fromnode || ',' || ts.tonode || ',' || ts.weight);
      end loop;

end;

the result is

1,2,1306
1,5,2161
1,6,2661

The desired result is the second result.

Answer 1

The problem is naming: you gave your variable the same name as the table column:

E.FROMNODE=fromnode

Oracle's scoping rules resolve names using the nearest match, starting from the innermost point, working outwards and upwards. The nearest namespace in a query is the table so it first attempts to resolve fromnode as a table column. The name fits, so the compiler doesn't look any further.

Effectively your WHERE clause filter is logically the same as 1 = 1, and that's why you get more rows. This should not be a surprise as it is the documented behaviour. The PL/SQL documentation covers scope of variable identifier names here and the interaction with variable names here. The key point is the one Alex Poole highlights:

"If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence."

You thought you had avoided this by putting fromnode in lower case but PL/SQL is not case sensitive. The correct approach is to use a naming convention such as identifying variables with a prefix: V for variable - v_fromnode - or L for Local - l_fromnode - are both common conventions.

Answer 2

You should change the name of your variable fromnode to my_fromnode or something like that.

In your firt query inside where the E.FROMNODE=fromnode is actually comparing the column with itself and returns true.