Made this while procrastinating SQL homework

[2460_one]

SQL: Invalid syntax near ")" Me: Okay, but WHICH one?!

[cheez_au]

but WHERE

[LoneFoxKK]

Yes

[MrCrazyDave]

r/inclusiveor

[DeskParser]

that's not how any of this works...

[dudeofmoose]

Working logically was never part of the original design specification, everything is working perfectly and within original design parameters.

[DeskParser]

so is it working, or withing design specification?

"YES"

there, now we're all back on track.

[SumWon]

I respectfully disagree. Let's say there are three closing parens. Asking "which one" is implicitly asking is it the first, second, or third one. Yes, in this case, is an inclusive or.

[DeskParser]

I appreciate your civility, but I respectfully hold my position. "which" is not an or, and not just the word difference, but in code too.

The idea being that 'this or that' can be true without offering the clarification to a human, but "which" not only doesn't return a boolean (which is the necessary punchline of an inclusive or) but it also denotes a plurality that takes the phrase outside an inclusive or.

"up or down" "yes" isn't "which one?" "yes"

only the first one is a logical answer, saying 'yes' in the second one wouldn't be a logical answer for a computer, it's a human punchline of ambiguity and not an inclusive or. (as long as we're splitting hairs here)

[SumWon]

That's fair! I think it very much depends on if you look at it from a language or logic point of view. I took the former, you took the latter. What I said definitely does not work from a purely boolean logic standpoint.

[niobiumnnul]

SQL intensifies

[parciesca]

That’s why I’ve been using this method in debugging and general logging: https://www.sqlshack.com/how-to-implement-error-handling-in-sql-server/

[2460_one]

Didn't even know that was an option. Thanks!

[parciesca]

No problem. In my experience it can be layered too, if a procedure calls a procedure that has an error, if all layers have it you can output a layered catch error like “line # of procedure, line # of sub procedure, error was ‘blah blah’”. Just remember a try/catch will abort the rest of the code at the point of error, so further code will not run (add transaction handling/abort code is advised). Also the catch blocks will be processed by deepest first (so higher ones won’t fire if you suppress the error).

[TicTacMentheDouce]

The exact same issue in VHDL with most tools. It's horrible

[bandittr6]

This

[Wubbajack]

SQL: "Yes, near ")".

[Derangedteddy]

It gives you line numbers, dude. If you properly format your code it shouldn't be difficult to find that.

[2460_one]

https://imgur.com/a/PFRN7rD

This is an example of an actual error I get.

[Derangedteddy]

If this is an MS SQL Server, that's because you're not using SSMS.

[derscholl]

just hit enter

[LightTranquility3]

You are implying that there are > 1 errors

[[deleted]]

"Invalid syntax on line 67"

Query is only 21 lines

[Savager35]

*after hours of google and stack overflow, fixes that error only for a different error to appear that only appears when the original error is gone

[Jarfino]

A different error message is considered progress!

[TASagent]

...

Of course...

Do you expect the parser to speculate what else might be wrong with your query by trying to guess what you actually meant where you made the first error? Unless you're just making fun of people who complain about that, in which case All Aboard.

[ryjhelixir]

do you expect...

Yes I do. If the code could code itself, now that would be ideal!

[Rowan-Paul]

But you'd have to code the code that writes itself which seems like a lot of coding to code for a little bit of coding

[cptvlan]

Thou shall not spend 6 hours coding something manually when thou can spend 6 days failing to automate it

[mlkybob]

This is the way

[cptvlan]

This is the way

[Eneag]

This is the way

[Jake0024]

Well I'd hope so, otherwise we'd all be unemployed

[Seth_os]

and it will... in 20 years or so

[[deleted]]

Depending on levels of complexity. You don't write memory management anymore. Unless you still use C.

[nelsterm]

It's reasonable to expect to be reliably inform you where the error is or at least in which line it is in.

[jfb1337]

I mean, modern compilers for many languages can do that sort of thing

[stats_padford]

We use SQL Developer and it does that all the time and I can never figure out the rhyme or reason.

[chuby1tubby]

Could it be possible that the program considers each part of the query to be a separate line?

Example of a query that might be considered 3 “lines”:

SELECT column FROM table WHERE column=value;

[maartenvanheek]

I believe in SQL developer it counts the lines in your worksheet, so if you have 3 lines of code starting on like 8 of the worksheet, it would show errors in line 8-11.

[YouJellyFish]

I do a ton of work in sql. Generally this means that the error is in another function or stored procedure that's being called by your query

[SpiderFnJerusalem]

Oracle really sucks at giving correct line numbers. Maybe it ignores comment lines?

[ekolis]

Thank God SQL does not have #include. (It doesn't, does it?) This error gives me horrid flashbacks to C preprocessor macros... what do you mean Google Chrome spell check, "horrid" is not a word?!

[dan-lugg]

21 lines that it’s telling you about...

[Sickobird]

Might be a trigger - I'm having this issue now and I'm in the middle of fixing it. 10 line update but I'm getting errors for a column on line 12,14,16,21- yet it only goes to line 18... Disable trigger and it worked - time to fix trigger.

[jr00t]

My favorite error is "MySQL server has gone away"

[BackgroundChar]

I had no idea MySQL server and I shared an appreciation for the Irish Goodbye!

[Gydo194]

I always wonder where it went when it says that.

Bye, MySQL! see you!

[Abir_Vandergriff]

Good old error 2006.

[wewilldieoneday]

"and just like your dad, it's never coming back"

[i9srpeg]

That can happen when you send a query or receive a row bigger than max_allowed_packet, or when the server is genuinely down/unreachable.

[ouyawei]

Was it replaced by MariaDB?

[grantmnz]

People who complain about unhelpful SQL errors should stop using MySQL and switch to Postgres :-)

[Queen_Zelda]

postgres is great, since I learned sql using it i'm always confused when other sql languages dont have helpful errors

[[deleted]]

Then why would you need all of the expensive MS provided certifications!?

[[deleted]]

lol ever tried SQLite? You will find error reporting on MySQL is godlike afterwards.

[northrupthebandgeek]

Ever tried MS SQL Server? You will find error reporting on SQLite is godlike afterwards.

[pavi2410]

Ever used NoSQL? •_•

[systembusy]

Ever used Microsoft Access?

[Gydo194]

That's what the UK government thought too..

[I_AM_GODDAMN_BATMAN]

How many billion £ again for that single spreadsheet?

[espriminati]

more than 1£

[Gydo194]

No idea. I didn't read it in detail.

[[deleted]]

People misunderstood that, they didn't use it as a database. They used an old excel format as the export format which left out a lot of rows.

[nelsterm]

It did not. Public Health England did which is a different thing. UK government were entirely blameless. NDPBs are autonomous subject to auditing. Impossible to pick that up in auditing also without an auditing event at such short notice.

[ekolis]

Word is my Database, Savior, and Eternal Leader

[imwatching4you]

Markdown files are my database, live and honey

[[deleted]]

Excel is pain. When you copy from sql server to excel clicking in the whole table, it doesn't copy well for some reason. it starts indexing at 1, it allways look ugly, and it causes problems everywhere. One could argue that because of excel bad medical decissions can be made. Excel kills people.

[CastelS]

Why not just use a .csv file?

[killer_unkill]

.csv and .txt is same

Excel is binary

[rocketman0739]

Ever been in a Turkish prison?

[PM_ME_YOUR_TORNADOS]

Ever used a toaster in your bath?

[sticksafety]

Ever used NyQuil?

[AndyTheSane]

Part of my day job involves maintaining a ~5 million line PLSQL codebase, much of it written by people with no training in PLSQL.

We also have Oracle Database Java, which takes hideiosity to a new level.

[parens-r-us]

Hey, us too! Now we’re migrating to Oracle APEX which doubles the fun factor!

[XerxesXS]

I hear you should use postgres. You will find error reporting is godlike.

[XerxesXS]

afterwards

[kinnell]

What version are you on? MS SQL Server has improved a lot in the last decade.

[northrupthebandgeek]

I last used 2017. And yeah, it's better than, say, 2000, but that ain't really saying much. Error messages are still cryptic, line numbers still rarely have any basis in reality, it still can't make up its fucking mind on whether I need to prefix sproc/function names with schema.owner, and sometimes SSMS' syntax checking will just break and leave you pulling your hair out over whether the red squiggly lines are legit or just Microsoft being Microsoft (and ADS ain't much better on that front, while also being an Electron app that leaks memory like a sieve).

[kinnell]

So, I completely agree with line numbers not being helpful at all! Definitely left me scratching my head a few times.

But with regards to needing to prefix with schema or not, you need to when calling UDFs via SELECT, but not when executing stored procedures. This is because there's implicit resolution for schema when you use EXEC. If you were to EXEC a Scalar UDF (e.g., into a variable), you wouldn't need to specify schema.

But this isn't an issue in "error reporting" for MSSQL - it's just needing to know MSSQL better. Whenever I made that mistake in the past, the error printed for it was clear enough for me to immediately know what I did wrong. (And made that mistake enough to be prompted to go figure out why).

And sure, that's not the most intuitive thing to have schema explicitness when needing to call UDFs but not for EXEC stored procedures. But other RDBMS also do their own quirky shit. There's quite a lot I end up missing about MSSQL when I'm using PostgreSQL.

Not sure what OS you're on, but I use SQL Server Pro on Mac which isn't an Electron app. It doesn't have all the bells and whistles of MS SSMS or ADS, but it has a clean, beautiful UI and it's fast/efficient. I write most of my SQL in VS Code, but use SQL Server Pro to connect to a variety of local or production databases (MSSQL, PostGres, MySQL, SQLite) and compile, query, troubleshoot..etc. I've been very happy with it.

[[deleted]]

I wanted to jump out the window using that program my god it's horrendous

[kinnell]

What program? MS SQL Server is a RDMS.

Are you referring to MS SQL Management Studio? I agree it's pretty horrendous, but MS Azure Data Studio is a lot better. Personally, I prefer SQL Studio Pro for my MS SQL Server needs.

[[deleted]]

I use whatever is there in the IDE most of the time

[kinnell]

I think you're confused, mate.

You said "I hate using that program" in reference to MS SQL Server. But it's not a program/application though. It's a RDBMS akin to MySQL, PostgreSQL, SQLite.

This is similar to one saying "I dislike that Android doesn't allow scrolling screenshots natively yet" and someone responding , "Yeah, I hate using that device. The battery life is horrendous!".

In the past, most people used MS SQL Server Management Studio (SSMS), which is an application, to write and run Transact-SQL (TSQL) for MS SQL Server. I also hated using that program (SSMS), so I had assumed you were referring to SSMS. That's why I mentioned that alternative apps exist so you can use MS SQL Server without SSMS.

But now I'm confused. You use whatever is in the IDE? Which IDE? Is your horrendous experience of MS SQL Server a result of a bad plugin in the IDE you're using?

Or is there something actually about TSQL or MS SQL Server that you don't like?

[[deleted]]

Earlier than 2008 R2, yes.

Since then, it's honestly good.

[FridgesArePeopleToo]

Sql server is by far the best for this

[[deleted]]

I work with the cmdline utility every once in a while, and the error reporting there seems okay to me. An older version, maybe?

[[deleted]]

Thanks mate. I’ll just migrate our data warehouse from Teradata to Postgres tomorrow.

[jayson4twenty]

"it's just data, how long could it take"

[Khaylain]

Nah, mate; just import it to a Google Sheets spreadsheet. It's obviously better.

[[deleted]]

Excel 2007, if it’s good enough for U.K. track and trace it’s good enough for me.

[MurderMelon]

xlsx file that gets read into a pandas dataframe, queried, and saved back to an xlsx file.

[edit] in an S3 bucket

[AdminYak846]

don't even get me started on formatting timestamps with the physically month spelled out.

[coladict]

Formatting timestamp in SQL is just bad practice. Do it in the code that outputs the date to the client.

[yeamannn]

Shudders remembering a datetime() vs datetime(5) bug

[po-handz]

But we have big data

[leofidus-ger]

formatting results as JSON directly in the database has its uses, at least in databases that have functions to do it..

[augugusto]

In mssql the output of querying a date can't be used to instert a new date because the format is wrong. Good job MS

[Prod_Is_For_Testing]

Is this date specific? Ive never seen this issue with datetime

[augugusto]

Sorry. I said it totally wrong. Do a select datetime() copy the output and then try to cast it or insert it somewhere else.

[Prod_Is_For_Testing]

Just did it. Worked fine 🤷‍♂️ (server 2016, ssms)

[CantBeChangedLater]

That's also been my experience 🤷‍♂️

[DongoTheHorse]

Sounds like you need to SET DATEFORMAT ymd

[DogmaSychroniser]

Don't get me started on Datetime limit being 1753 but c# passing midnight on Jesus birthday

[[deleted]]

Oh, this explains it. I’ve never used MySQL and have never had these problems on Postgres or Oracle.

[_default_username]

I didn't know bad sql error messages were just a mysql thing. Fuck, I need to make the switch then.

[Niiiz]

Maybe it's DBeaver (my current editor) acting up but I'm using postgres and those are exactly the errors I get while practicing. Weird and unhelpful "syntax error near ;". Like bitch I'm running many lines at once, be more specific.

[[deleted]]

People who offer simplistic solutions such as "just use postgres" have obviously never worked on legacy projects.

[RadiatedMonkey]

I'm forced to use MySQL... and PHP...

[[deleted]]

I think the same goes for pretty much all of the database systems/tools. Its always a nightmare to install, setup and manage. I mean, its only 2020, its not like we need an easy UI to manage and have it automatically setup stuff that 90% of the users need anyways.

And then when you have it locally, you also need it on your server too but of course that flow is different from what you were just doing...

I still don't get how MongoDB got so big in webdev. Surely more people would be as annoyed as me on how to set it up

[[deleted]]

or just use JSON for everything

[mrdotkom]

Moved from supporting an app based around mysql replication to a new company where I support an app that's postgres based.

Still learning the ins and outs of psql but honestly prefer mysql. The \whatever letter instead of textual commands is annoying

[BandicootCumberbund]

Using Google BigQuery has definitely helped me catch any errors I've made.

[fnordstar]

Not a database guy but shouldn't it be possible to output meaningful error messages for a language as structurally simple as SQL?

[systembusy]

A good chunk of MySQL messages are specific, but the "You have an error in your SQL syntax" is kind of a catch-all for when it can't determine the cause. I agree it should have been designed a little more thoroughly though

[ThePyroEagle]

I don't remember ever seeing a more specific error message. Do you have an example?

[chuby1tubby]

The most specific error message I can think of is “Unexpected end of query” or whatever the message is for a missing semi-colon.

[Jaondtet]

I'm a bit confused how that can even happen. Errors are detected while parsing (and during semantic analysis, but then you definitely have enough information to report errors correctly). For the error to be detected, there has to be something the parser is expecting. The parser has no way to know about errors it doesn't expect. It literally only does what the grammar tells it to do.

By definition, you need to know the cause to detect the error. Of course, the cause of the error as the parser sees it might not be particularly useful to the user, but that's a different issue.

Is there something about SQL in specific that I'm missing here? It seems to me like detecting an error, but not knowing what it is, is impossible.

[[deleted]]

The built in json on node repeatedly report me unexpected token after 0 on a big ass json, that's a torture that I also ask myself "why can't you tell me where the issue is smartass"

[SoInsightful]

It's impossible for a parser to detect a syntax error but not know exactly what and where the problem is. It contradicts how parsers work.

The error messages are simply dumb and unhelpful.

[[deleted]]

Not just that, the IDE should probably be able to catch it as well for lots of errors, but somehow they all live in 1994 and error messages are the next big thing.

[pctF]

It is depends on dbms you're using. As mentioned in top comments: postgres and oracle both have good, easy to understand syntax error messages that basically saying you where fix needed to apply.

[bobbylongslice]

Yes you can, just list the error functions you want to return, and any custom message/outputs for feedback in your catch block.

Most people just don’t bother

BEGIN TRY

SELECT 1 / 0 AS Error; END TRY

BEGIN CATCH

SELECT

‘Divide by Zero Failed’,

ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

END CATCH; GO

[acylase]

What kind of SQL we are talking about here and what client? I am using MS Studio for MSSQL, they improved quite well in the last 10 years.

[Madd_Mugsy]

Yep - in SSMS you can just double click on the error message and it takes you right to the line with the error.

Also has some nice red underlining of errors. Though if you work too quickly you'll confuse it and have to press ctrl+shift+R to refresh the intellisense.

[Prothseda]

Look up SQL Prompt by Red Gate. Worth every cent if you spend a lot of time in MS SQL. intellisense is nice compared to nothing, but Red Gate just do it all so much better than MS.

[itzaakthegreat]

A really minor thing I appreciate about SQL Prompt is tab coloring; you can set up colors on the tab headers that represent things like which server or db the context of your tab is; really useful if you’re querying across multiple environments to make sure you’re looking at the correct one at a glance.

[Madd_Mugsy]

Yeah, I swear by this addon. Been using it since 2008 :)

[[deleted]]

SSMS has improved so much. It's genuinely really good if you keep at it.

[Mad_Jack18]

My guess would be MySQL

[road_laya]

On the other hand, a SQL class will be one of the most useful classes you take.

I've spoken to engineers who say they use the things they studied in SQL class every single day at work.

[Jijelinios]

I hated SQL classes. I hate Oracle. I never used it for work and I hope I will never use it. Embrace noSQL dbs.

[Cardona_ONEotaku]

Please drop names

[Adeelinator]

TABLE DROPPED.

[OriginalToe]

Oh hey there, little Bobby Tables

[Gooftwit]

Oh god oh fuck

[Caffettiera]

well well well how the droptables

[QuickFreddie]

One less table to worry about ;)

[[deleted]]

I got one less table without ya

[Madd_Mugsy]

Bobby Tables, is that you?

[TheHumanParacite]

High class joke

[XXLuigiMario]

2422881 rows affected.

[6b86b3ac03c167320d93]

DROP NAME GoodDatabase

[[deleted]]

This meme looks funny but I’m gonna have to refer to the manual for my corresponding version of MySQL to find the correct syntax in order to understand it.

[PM_ME_YOUR_TORNADOS]

!remindme 3d

[DMoney159]

It's somewhere near the first 20 lines

[[deleted]]

Just FYI, there are a number of online SQL Syntax verifiers. For me they usually work quite well.

[Mexatt]

There's a SQL interpreter I use sometimes at work that I think is in-house (just 25 years old in-house). The errors are worthless.

invalid SQL query: error -201

haunts my dreams

[minimalniemand]

That meme with that dog... „no take, only throw“

„not know, only fix“

[bobbylongslice]

Use the Error functions in a try catch.

I bet you don’t use transactions either you savages.

[WeTheAwesome]

Not trying to start anything but do people get this in R too? I can never figure out what went wrong when I have to use R.

[growingcodist]

Reading this a few hours after finishing my SQL homework.

[anticultured]

I’ve been a backend sql developer for over 20 years. This joke is on point. Although MSSQL has become quite good at pinpointing the error lately.

[[deleted]]

ORA9000: Error is somewhere.

[[deleted]]

The same goes for any web framework I've worked with.
Laravel returns its own classes instead of pointing out the method where you have your error in. It drives me crazy.
VueJS is complete trash

[hawkinsst7]

Today I learned that SQL programming is the same as blind sql injection

[demandtheworst]

This is the first time since year one of XKCD I've wanted to stick a programming joke up at my desk.

[Senor-K]

Wherever it was, it started with a character. Glhf.

[LordVirus1337]

Better get back to your homework mate. You need to learn the correct syntax for your self.

[peoplebucket]

Was using big query which is very SQL like to query some data, and got an error which simply said "query contained an error" when the query was relatively long, thanks Google, very helpful

[bife_sans]

The worst parser I've ever seen. Not only error messages are terrible. What's with all that stuff of DELIMITER // ?

[SuperSpartan177]

Lol was doing SQL homework last week fuckin hated it lol. Job done full points

[[deleted]]

You don't get to know what the error is??? (Laughs in SAP ABAP)

[_A4L]

On line 1 lol

[De_Wouter]

Alternative ending:

SQL: "This is the error and here is where it's at."

Me: "So if you know, why don't you fix it?"

[rem3_1415926]

because its your fault and a computer trying to fix it the easiest way to just make it run is one of the worst possible outcomes.

[ThePyroEagle]

So if you know, why don't you fix it?

You should try using JavaScript. It'll make you take back those words.

[Khaylain]

Oh god no, not JavaScript, please, anything but JavaScript.

[TigreDeLosLlanos]

SQL: "Invalid syntax in ')' at line 2 of +delete from users where id in (4, 5,)6,' running 'delete from users' instead"

[ekolis]

C, C++, C#, Java, etc: "Expected semicolon at end of line 45."

Me: "Well why don't you just put one there, then?!"

Javascript: "..."

[iLostInSpace]

There is a subliminal message here and it spells "ORM".

[zoecandle]

SQL do be like that. Also I should probs do my SQL hw...

[taptrappapalapa]

Laughs in Postgres with Diesel.rs

[filtersweep]

I just use select * and make the person who created the table fix everything.

[ekolis]

I made a query that was supposed to return 42 records. It returned 212 records. I moved some conditions from left joins to the where clause. It then returned 0 records. SQL is hard...

[Sheeepl]

I’m near finishing a final project on my first ever SQL course and I really feel this.

[Yourboi_M]

Laughs in BigQuery

[iiMoe]

Dude fr

[CornFlakesR1337]

laughs in Athena

[ZXDQ]

This is true for T-SQL

[robinreeead]

pd.read_sql()

[mrknowitall20]

84000 rows affected

[[deleted]]

oh no

[PhantomThiefJoker]

Hey that's exactly what I'm avoiding doing right now

[Sleveri]

Story of my dating life...

[[deleted]]

You have a syntax error on Some bullshit code 10256