Mawia HL Mawia HL - 4 months ago 25
SQL Question

Mysql Column cannot be null error when there is no result using LEFT JOIN

I have some data in customer table and there is no data in the other tables. So when I am trying to select using the following query, it gives me


1048-Column 'customerid' cannot be null




. What could be the problem. Is there a solution for this?

SELECT c.*, fd.ruakzat as ruak,
fd.khatzat as khat, 0+0 as belh, 0+0 as neih,
puk.lended as hawh
FROM `customer` c
LEFT JOIN (
SELECT s.customerid, o.orderzat as ruakzat,
f.filled as khatzat
FROM `sale` s
LEFT JOIN (
SELECT SUM(quantity) as orderzat,invoiceno
FROM `order`
WHERE fillstatus='Empty'
GROUP BY invoiceno
) AS o ON s.invoiceno=o.invoiceno
LEFT JOIN (
SELECT SUM(quantity) as filled,invoiceno
FROM `order`
WHERE fillstatus='Filled'
GROUP BY invoiceno
) AS f ON s.invoiceno=f.invoiceno
) AS fd ON c.id=fd.customerid
LEFT JOIN (
SELECT SUM(quantity) as lended, customerid
FROM `lending`
) AS puk ON c.id=puk.customerid
WHERE (puk.customerid IS NULL OR c.name LIKE '%%')


What would be the better approach? Thanks for your help.

Answer

MySQL has a few bug reports around similar issues (https://bugs.mysql.com/bug.php?id=31450 , https://bugs.mysql.com/bug.php?id=35633 , https://bugs.mysql.com/bug.php?id=52441), so it is possible that you encountered the same bug.

To sum it up: if the field in question is non-nullable by definition (defined as not null in the create table), but appears in a subquery where the output result may be null.

Pls check your MySQL version because it may be such a version where one of the above listed bugs do appear.

Furthermore, to me the subquery

         SELECT SUM(quantity) as lended, customerid    
         FROM `lending`    

does not make too much sense, since there is no group by part. This will collapse the lending table into a single record, with customerid randomly chosen from one of the records. So, I would add a group by customerid to the above subquery.