MySQL Bugs: #52441: ERROR 1048 (23000) when working with variables

Bug #52441	ERROR 1048 (23000) when working with variables
Submitted:	29 Mar 2010 14:42	Modified:	7 Feb 2018 22:28
Reporter:	pedro oriani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.41-ndb-7.0.13-cluster-gpl, 5.1.45, 5.1.46-bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi, I've to notify this bug despite there is still open another one, that my be related with it (#35633).

This query rises ERROR 1048 (23000): Column 'name' cannot be null

select @r, a5.* from (select * from ( select a1.* from (select @r:=0) a0,mysql.plugin a1 inner join ( SELECT * from mysql.plugin where 1=-1) a2 on a1.name = a2.name) a4 where (@r:=@r+1)-@r=0) a5;

instead of an empty set.

How to repeat:
select @r, a5.* from (select * from ( select a1.* from (select @r:=0) a0,mysql.plugin a1 inner join ( SELECT * from mysql.plugin where 1=-1) a2 on a1.name = a2.name) a4 where (@r:=@r+1)-@r=0) a5;

Suggested fix:
a simple workaround 

select @r, a5.* from (select * from ( select a1.* from (select @r:=0) a0,mysql.plugin a1 inner join ( SELECT * from mysql.plugin where 1=-1) a2 on a1.name = a2.name) a4 where (@r:=@r+1)-@r=0 or 1=1) a5;

Verified just as described:

77-52-28-202:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @r, a5.* from (select * from ( select a1.* from (select @r:=0) a0,mysql.plugin a1
    -> inner join ( SELECT * from mysql.plugin where 1=-1) a2 on a1.name = a2.name) a4 where
    -> (@r:=@r+1)-@r=0) a5;
ERROR 1048 (23000): Column 'name' cannot be null
mysql> select @r, a5.* from (select * from ( select a1.* from (select @r:=0) a0,mysql.plugin a1 inner join ( SELECT * from mysql.plugin where 1=-1) a2 on a1.name = a2.name) a4 where (@r:=@r+1)-@r=0 or 1=1) a5;
Empty set (0.00 sec)

We can consider this bug as different as aggregation functions are NOT used here.

I expect this to be the same bug (at least the same error):

How to repeat:

CREATE TABLE users(
	ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	Name VARCHAR(50) NOT NULL
);

CREATE TABLE orders(
	ID INT NOT NULL PRIMARY KEY,
	UserID INTEGER NOT NULL
);

CREATE TABLE order_rows(
	OrderID INT NOT NULL,
	TypeID INT NOT NULL,
	ProductID INT NOT NULL,
	Quantity INT NOT NULL,
	PRIMARY KEY (OrderID, TypeID, ProductID)
);

INSERT INTO users (Name) VALUES ("My Name");
INSERT INTO orders VALUES (1,1);

SET @logged_on_user_id = 1;

SELECT COUNT(*) FROM (
	SELECT
		order_rows.OrderID AS OrderID,
		order_rows.TypeID AS TypeID,
		COUNT(*) as Count
	FROM
		order_rows
	WHERE
		order_rows.OrderID IN (
			SELECT orders.ID FROM orders WHERE orders.UserID = @logged_on_user_id
		)
	GROUP BY
		order_rows.OrderID,
		order_rows.TypeID
) AS inner_query;

I expected the result "0" (the count should be zero), but the last query results in the error ERROR 1048 (23000): Column 'OrderID' cannot be null.

The inner_query gives an empty set so doesn't give a problem, thus the problem seems to arise by nesting it. The problem also disappears when the variable @logged_on_user_id isn't used (but replaced by the literal "1").

OS: Windows XP
MySQL: Server version: 5.1.45-community MySQL Community Server (GPL)

Seems to be fixed in 5.7:

mysql> SELECT COUNT(*) FROM (
    ->  SELECT
    ->   order_rows.OrderID AS OrderID,
    ->   order_rows.TypeID AS TypeID,
    ->   COUNT(*) as Count
    ->  FROM
    ->   order_rows
    ->  WHERE
    ->   order_rows.OrderID IN (
    ->    SELECT orders.ID FROM orders WHERE orders.UserID = @logged_on_user_id
    ->   )
    ->  GROUP BY
    ->   order_rows.OrderID,
    ->   order_rows.TypeID
    -> ) AS inner_query;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Posted by developer:
 
Fixed in 5.6 and up.