MySQL Correlated sub query table name out of scope-CodePudding

This form of my correlated sub query comes up with the error message "Unknown column 'Invoices.TranDate' in 'where clause'"

select InvoiceID, TranDate
    , ifnull(TotPayments,0) TotPayments, ifnull(CountPayments,0) CountPayments
from Invoices
    left join (select DebtorID, sum(TranAmount) TotPayments, count(*) CountPayments
        from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
        group by DebtorID) PY on PY.DebtorID = Invoices.DebtorID

Yet this version works

select InvoiceID, TranDate
    , (select sum(TranAmount) from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
            and CashTrans.DebtorID = Invoices.DebtorID) TotPayments
    , (select count(*) from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
            and CashTrans.DebtorID = Invoices.DebtorID) CountPayments
from Invoices;

What is wrong with the first query? The only thing I can think of is that on my Windows system I have configured lower_case_table_names=2 as I want to preserve mixed case names. Perhaps that has something to do with the first query not seeing Invoice.TranDate in scope? MySQL Documentation and internet searches have not thrown any light on the matter.

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html says:

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

In SQL:1999, the query becomes legal if the derived tables are preceded by the LATERAL keyword (which means “this derived table depends on previous tables on its left side”):

I have not tested it, but I believe your query could be written this way:

SELECT InvoiceID, TranDate,
  IFNULL(TotPayments,0) AS TotPayments,
  ifnull(CountPayments,0) AS CountPayments
FROM Invoices
LEFT JOIN LATERAL (
  SELECT DebtorID, 
    SUM(TranAmount) AS TotPayments, 
    COUNT(*) AS CountPayments
  FROM CashTrans 
  WHERE CashTrans.TranDate >= Invoices.TranDate
  GROUP BY DebtorID
) AS PY ON PY.DebtorID = Invoices.DebtorID;

Also be aware this requires you to use at least MySQL 8.0.14.