SQL Zoo: The Postgres engine on sql zoo is bugged.

[DrantDumani]

Checks

• [X] This is not a duplicate of an existing issue (please have a look through our open issues list to make sure)
• [X] I have thoroughly read and understand The Odin Project Contributing Guide
• [X] Would you like to work on this issue?

Describe your suggestion

I posted about this in the discord, but I figured this was the proper way to go about this. The POSTGRES option on SqlZoo is bugged. I was doing everything in MySql and I switched to Postgres because I saw the chat. However, it results in some odd behavior.

Some examples:

In the More Join tutorial, there's a question that asks you to find the id of the film titled 'Casablanca'. This is the result in postgres

SELECT id FROM movie WHERE title = 'Casablanca';
-- Outputs TWO ids!
-- id
--  132689
--  60576357

This isn't the case in MySql where you only get one id. The next question, which asks you to list the cast list for Casablanca is also affected.

SELECT name FROM actor JOIN casting ON actor.id = actorid
WHERE casting.movieid = 132689;
-- Outputs no confirmation on a correct answer
-- The second id just outputs an empty list

And then there's the NULL tutorial, which is what prompted me to do some research and make this post to begin with. The question asks you to list all teacher in the teacher table who don't have a department. Here's a link to the tutorial: https://sqlzoo.net/wiki/Using_Null But there's one problem.

SELECT name from teacher WHERE dept IS NULL;
--ERROR: relation "teacher" does not exist LINE 1: SELECT name from teacher WHERE dept IS NULL; ^

According to this post on postgresql.org, this has been an issue with Sql zoo since 2008. https://www.postgresql.org/message-id/[email protected] Sorry about bringing all of this up after you guys made the PR to change from mySql to Postgres for this projecy. I was in the middle of doing everything in MySql before it was brought up.

As a solution, I think we can go back to mentioning the use of MySql. But there can also be a note saying that, while future projects would be using Postgres instead, the solutions to problems on SqlZoo do not require anything further than engine agnostic sql syntax, similar to the assignments in the previous lesson that used sqlite.

Path

Ruby / Rails, Node / JS

Lesson Url

https://www.theodinproject.com/lessons/node-path-databases-sql-zoo

(Optional) Discord Name

Elemeandor

(Optional) Additional Comments

No response

[mao-sz]

#28490 reverting the Postgres commit. We can discuss how we adjust the verbiage of the MySQL if necessary after we confirm what can/can't be done.

[wise-king-sullyman]

Did reverting that commit resolve this issue, or were you wanting to keep this issue open to be a place of discussion moving forward @MaoShizhong ?

[mao-sz]

@wise-king-sullyman with the revert, the current wording seems odd given that both pathways use postgres later (though I believe the Rails course doesn't spend as much time with raw postgres as the Node course does).

I'm not fully familiar with the assigned SQLZoo content yet, so I'm not 100% sure if any of the assigned content might include things where MySQL and Postgres will differ.

If it's confirmed we cannot use the Postgres engine due to SQLZoo's own bugs, then we should go with MySQL but amend the assignment wording to clarify why we use that. More so if there are even small things that differ between them that a learner may encounter in even just one of the exercises.

But since I'm not fully familiar myself, I'd prefer if I can get confirmation from a couple others who are more familiar.

[github-actions[bot]]

This issue is stale because it has had no activity for the last 30 days.

[wise-king-sullyman]

@MaoShizhong do you know if anything else has happened with this discussion?

[mao-sz]

I don't think there was much. I haven't been keeping an eye on the SQL and Node Postgres/Prisma stuff since the release, so I'm not particularly up to date on whether there have been any long term issues with things being kept as is with the MySQL engine.

@DrantDumani Have you any thoughts about this since you opened the issue? @01zulfi Any thoughts yourself? AFAIK, it didn't really affect the Rails course much since they use Active Record for the most part, but we do actually write raw postgresql in the Node course.

If it seems that all is well for now, we could probably just leave it all as is. People do that first before they encounter Postgres later on, so probably not that big of an issue to dive into?

[DrantDumani]

There are some minor differences that may trip users up when using Postgres for the first time after using MySql for SqlZoo and Sqlite for the earlier database lessons.

There's question #8 in the Select From World tutorial that uses the exclusive XOR operator, which doesn't exist in Postgres. While this isn't limited to SqlZoo, users do become familiar with using the LIKE operator during the database course, which is case insensitive in MySql and Sqlite, but not in Postgres. This can be especially confusing once users reach the assignment in the Using Postgres lesson, which asks them to implement a search route that uses SQL to perform the searching. It wouldn't be uncommon for users to reference the database lessons, which is what happens in this discord conversation.

If anything, maybe we could warn users about the differences between Postgres and the Sql engines they've been using up to that point by either informing them to check the documentation when something doesn't line up (since most things will line up outside of a few discrepancies).

[01zulfi]

If the PostgreSQL engine is buggy, we can just ask learners to complete SQLZoo via MySQL. We can add a note on MySQL and PostgreSQL have few differences and they might encounter it. We can ask learners to refer to the docs as well.

[github-actions[bot]]

This issue is stale because it has had no activity for the last 30 days.

[PivtoranisV]

Hi guys, I just started with SQL Zoo, and with the MySQL option, it gives an ERROR. Spent some time trying to figure out what I was doing wrong before I found a Discord message suggesting to switch to another engine. We need to clarify this part in the Assignment. Make sure the dropdown on the upper right of the main page for “Engine” says **“MySQL” (the default)**. Large results will be cut off and not all rows or columns shown, so the “answers” may not look 100% correct.

Will check the entire project with PostgreSQL, and let you know if the issue with PostgreSQL is still there as well

[PivtoranisV]

UPDATE: SELECT from nobel exercise works only with Microsoft SQL

[CouchofTomato]

Maybe it's best if we try and find another solution if this is an issue

[github-actions[bot]]

This issue is stale because it has had no activity for the last 30 days.

[JoshDevHub]

The lesson currently recommends MySQL, and after looking just now, the MySQL engine seems to be working just fine on SQLZoo.

Question for people who've been involved with this issue: does this mean that this is a resolved matter? Or are there still some details about this that need discussion?

[github-actions[bot]]

This issue is stale because it has had no activity for the last 30 days.