0

I have strange situation for executing query in Firebird 2.5: 

select ps.ProductId, p.Id, p.DefPurcUOMNr, p.IsStockFlag 
  from ProductSupplier ps left join 
    Product p on p.Id = ps.ProductId 
where 
  (select q.Qty from ProductQtyBMUToUOM$(p.Id, p.DefPurcUOMNr, 1) as q) > 0

Query above is working fine but if add p.IsStockFlag = 1 then p.Id, p.DefPurcUOMNr parameters of stored proc ProductQtyBMUToUOM$ will pass equals NULL for records with p.IsStockFlag = 0. 

select ps.ProductId, p.Id, p.DefPurcUOMNr, p.IsStockFlag 
  from ProductSupplier ps left join 
    Product p on p.Id = ps.ProductId 
where 
  p.IsStockFlag=1 and
  (select q.Qty from ProductQtyBMUToUOM$(p.Id, p.DefPurcUOMNr, 1) as q) > 0

I guess it is correct behavior but i can't find good description of it. Only my guess which based on this observing.

Thanks all in advance.

Arioch 'The
  • 15,799
  • 35
  • 62
Nikita Parhimchik
  • 11
  • 3
  • Your query sets `left join Product p` which means for many rows in `ps` relation all columns of `p` relation would be `null` - that is the definition of how LEFT join is different from symmetric inner join. So, if you need "good description" then just read any SQL entry-level book about what is inner join vs left join vs right join – Arioch 'The Jan 29 '18 at 16:20
  • Additionally, making a separate "select" from the SP looks redundant here. Just lift the SP call to the FROM/JOIN section like it was another procedure http://www.firebirdfaq.org/faq143/ – Arioch 'The Jan 29 '18 at 16:25
  • @Arioch'The that means `where` clause filtering records before they join I was thinking first selected the record set and then it filtered using conditions from `where`. Thanks a lot. – Nikita Parhimchik Jan 30 '18 at 05:54
  • @Arioch'The Relying on [this](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause?rq=1) if I add condition `p.IsStockFlag=1` to `where` it equals that I will add it to `join` condition `from ProductSupplier ps left join Product p on p.Id = ps.ProductId and p.IsStockFlag=1` Right? – Nikita Parhimchik Jan 30 '18 at 06:15
  • Basically "SQL Optimizer" may end up with any different QUERY PLAN that should in theory return the same SET. RDMBS is based on mathematical sets abstraction, and all the clauses are filters upon sets productions. As long as some operation should produce the same subset - it is correct. That means one and the same query in one and the same server may depending on specific data in the tables be executed by different methods (query plans). SQL server is a black box for you, it does some voodoo magic to give you an end result. Binding your program to "implementation details" of voodoo is unsafe – Arioch 'The Jan 30 '18 at 08:57
  • Anyway it dopes not matter. That top "Query above is working fine" is only "fine" on specific lucky data. Put a slightly different data into the tables, and the "fine" query would start issuing all-null p columns too. So if you bind SP call to LEFT JOIN - your SP should be aware of NULLs and be ready to handle them meaningfully. If it does not - it is just a question not luck when it will break. – Arioch 'The Jan 30 '18 at 09:02

2 Answers2

2

When you use a left join, conditions on the second condition should be in the on clause:

select ps.ProductId, p.Id, p.DefPurcUOMNr, p.IsStockFlag 
from ProductSupplier ps left join 
     Product p
     on p.Id = ps.ProductId and p.IsStockFlag = 1
where (select q.Qty from ProductQtyBMUToUOM$(p.Id, p.DefPurcUOMNr, 1) as q) > 0 ;

You say the where clause works fine as is, so I left it. I would be inclined to move it to the on clause -- or even make it a separate join.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I'm not seeing any criteria on p.IsStockFlag. Did you mean the following?

select ps.ProductId, p.Id, p.DefPurcUOMNr, p.IsStockFlag 
  from ProductSupplier ps left join 
    Product p on p.Id = ps.ProductId 
where 
  p.IsStockFlag>0 and
  (select q.Qty from ProductQtyBMUToUOM$(p.Id, p.DefPurcUOMNr, 1) as q) > 0
OwlsSleeping
  • 1,487
  • 2
  • 11
  • 19