Self-Join returning odd results 1

[Katerine]

Hi,
I have a quick question, I hope. The issue is quite simple. I'm trying to create a 5-level (purposely limited to 5) hierarchical query, that's returning odd results. For simplicity, I'll just include the relevant stuff.

I have a table, called, "CONTENT_Items". This table has an ItemID PK and a ParentItemID column, both of type int. This table currently has exactly one row in it:
---
ItemID, ParentItemID
1, 0
---

When I run the following query:

SELECT c1.`ItemID` c1ItemID,
		c2.`ItemID` c2ItemID,
		c3.`ItemID` c3ItemID,
		c4.`ItemID` c4ItemID,
		c5.`ItemID` c5ItemID
	FROM `CONTENT_Items` c1
		LEFT JOIN `CONTENT_Items` c2 ON c2.ParentItemID = c1.ItemID
		LEFT JOIN `CONTENT_Items` c3 ON c3.ParentItemID = c2.ItemID
		LEFT JOIN `CONTENT_Items` c4 ON c4.ParentItemID = c3.ItemID
		LEFT JOIN `CONTENT_Items` c5 ON c5.ParentItemID = c4.ItemID
	WHERE c1.ParentItemID = 0

...I should get the following results:
c1ItemID, c2ItemID, c3ItemID, c4ItemID, c5ItemID
1, NULL, NULL, NULL, NULL

...but instead, I get:
c1ItemID, c2ItemID, c3ItemID, c4ItemID, c5ItemID
1, NULL, 1, NULL, 1

Why is this? And how do I get the desired (first) set of results instead?

Many thanks!

Katie

 

[feherke]

Hi

Interesting. The MySQL 5.7.12 I use returns what you expect :

[blue]test>[/blue] select * from `CONTENT_Items`;
+--------+--------------+
| ItemID | ParentItemID |
+--------+--------------+
|      1 |            0 |
+--------+--------------+
1 row in set (0.00 sec)

[blue]test>[/blue] \. Katerine.sql
+----------+----------+----------+----------+----------+
| c1ItemID | c2ItemID | c3ItemID | c4ItemID | c5ItemID |
+----------+----------+----------+----------+----------+
|        1 |     NULL |     NULL |     NULL |     NULL |
+----------+----------+----------+----------+----------+
1 row in set (0.00 sec)

Feherke.
feherke.ga

 

[Katerine]

Hm. According to "SHOW VARIABLES LIKE "%version%";", the SQL Server version on the server (which I'm unable to change, because it's a shared host) is 5.0.83-community. Is there some sort of bug with that version? Or maybe some setting that could be explaining it that another client requested that the hosting provider set?

[Edit]: Oh, could it possibly have to do with the fact that the default value of ParentItemID is set to 0? Checking... no, removing the default doesn't seem to make a difference. There's no way the other columns in the table could make a difference, is there? Or the fact that ItemID is auto-increment? I'm just brainstorming for random ideas now... I really need this query to work, and quickly.

[Update]: I just created a fresh table, called testtable, with just the ItemID and the ParentItemID column, inserted the one row, and ran the query on it, and it returned the same results. So it doesn't seem to be a table-level problem, but rather a server-level problem? What do you think?

Thank you, btw, for checking into this.

Katie

 

[feherke]

Hi

Sorry, I'm quite new to MySQL, so no idea about such version incompatibilities. And I have no older database available right now. :-(

"community" ? Also interesting. I knew that MySQL's community fork is the MariaDB... I made another test with MariaDB 10.0.23 ( released 18 Dec 2015 ) and same successful result.


Feherke.
feherke.ga

 

[Katerine]

I think the "community" part is referring to the fact that it's a shared MySQL server, shared between many different clients? That's just what I assumed.

Update: I changed ParentItemID to -1, and changed the query's WHERE condition to look for -1, and it worked. It's a workaround, and it sucks, but it'll get the job done. Many thanks again for looking into this!

Katie