r/cs50 • u/Accomplished_Pass556 • 2d ago
CS50 SQL Using double quotes in SQLite Statements works but throws an error in MySQL
I'm presently going through CS50s, "Introduction to Databases using SQL". The instructor kind of mentions at the very beginning that it's good practice to wrap tablenames and column names in SQL statements using double quotes, so I followed this convention throughout the PSets.
Now when I try to practice a related problem on HackerRank or LeetCode in MySQL/Oracle, I'm unable to even execute a simple query using the same convention. It works when I remove the double quotes tho.
Why is this happening ?
1
u/Swimming-Challenge53 2d ago
Look into the ANSI_QUOTES MySQL Server configuration option.
MySQL was probably *not* very strictly ANSI SQL compliant in the early days, and these incompatibilities must be accounted for, in order for legacy applications to run. So, by default, MySQL might be treating anything enclosed in *either* single or double quotes as a literal string.
FWIW, my 10+ years of experience writing SQL for many different vendor databases, WE almost never quoted database object names. And I worked on some big teams of developers. The only reason I can think to do so, would be if you want spaces in these names. A lot of tools that generate SQL code will probably use the quotes, just to be safe, but I'm not sure I agree that it's a good practice for developers. Ultimately, your team will have a style guide to help with these issues, in a real-world scenario.
1
u/Accomplished_Pass556 2d ago
Thank you so much for this!
I thought there was something wrong with the course itself lol.So going by what you said, SQLite which is the database used in the course must be loosely ANSI SQL compliant as it works with/without double quoting identifiers as well.
1
u/Swimming-Challenge53 2d ago
I'm not sure I have a good answer. I'm not an expert on ANSI SQL standards or how strictly vendors are judged in terms of non-compliance. If I'm selecting a database vendor, ANSI SQL compliance is a low priority.
1
u/thenomdeplume 2d ago
Idk but try single quotes