• DQ (unregistered)

    The one who created the field probably knew it would never be used. So this was the easiest and cheapest solution...

  • (nodebb)

    "In fact, it needs to run the same regex twice to actually handle the split. " Not really, the optimizer takes care of that and will most likely run it once.

    Still, this mess is what you get when you go anti-relational in a relational database.

  • Dude (unregistered)

    I have since transferred to another team.

    Being a former ERP developer myself (MS Dynamics AX, which at least uses SQL as it's back end), I can confirm this is the only reasonable solution to the problem. Regardless of if there is actually an issue or not.

  • (nodebb)

    where 1=1? urgh. i presume this code has to be generated by another system and not meant to be looked at by humans

    Addendum 2021-12-02 07:37: At least, I hope it was generated, and not written like that

  • Bradley (unregistered)

    Thank god they at least remembered to check that 1 still equals 1.

  • Charvell (unregistered) in reply to thosrtanner

    Unfortunately too many people still believe the 1=1 will "trick" the optimizer into running "faster" and I hate it.

  • Robin (unregistered)

    It's news to me that people actually use OR 1=1 outside of SQL injection attempts!

  • (nodebb)

    I saw the same thing in Dynamics instances - people stuffing multiple values into a column to get around the database schema modification constraints.

    For the millionth time: DO NOT BUY THESE ERPS. They're expensive, they're terrible, and they're useless. I'm 100% certain even a LAMP solution (as much as we all hate PHP) can be developed for much less money and be much more usable and useful.

  • trwtf (unregistered)

    my MBA's ERP class basically came down to: if you're small or medium use MS, if you're big use SAP, if you're stupid use Oracle

  • Pag (unregistered)

    Could they not have stored the batch ID as attribute7 (or similar) in the order table? I'm assuming it's a many-to-one relationship so you don't need the batch-order join table proposed by Remy.

  • Vilu (unregistered)

    I've seen people use "WHERE 1=1" for convenience when working on the query. Say you have something like this:

    SELECT * FROM MyTable WHERE 1=1 AND SomeCol=4 -- AND SomeOtherCol=16

    With the 1=1, you can start any additional clause with "AND" and they'll conveniently work. If you want to comment out the "SomeCol=4" clause, you just have to add the "--". If you want to uncomment the "SomeOtherCol=16" clause, you just have to remove the "--". Without the 1=1, you'd have to add/remove the "AND" operators depending on the case, or even the whole WHERE clause.

    Considering a WHERE 1=1 doesn't do harm in any way, I think it's sensible to leave it there for convenience - even though I wouldn't do that myself.

  • Vilu (unregistered)

    ^ The query in my comment above was supposed to have each clause on a separate line and not everything inline like that, but oh well.

  • Brian (unregistered) in reply to Mr. TA
    people stuffing multiple values into a column to get around the database schema modification constraints.

    I'll do you one better: In the project I'm working on, we have a table in which one of the fields holds a JSON string, and several properties of that JSON object are XML strings (which also represent objects). So yes, XML embedded in JSON jammed in a DB column, all because it's a lot "easier" to just change the text we're writing to the DB rather than change the DB definition to account for feature creep.

  • NotAThingThatHappens (unregistered)

    In our system we do not generate our SQL, we reduce them based on paramters

    Select *
  From table
where  1=1
    | And colum1  = @parameter1 |
    | And colum12= @parameter2 |

    If we want to use parameter 1, we remove the pipes otherwise we remove the line.

    This way we can create dynamic queries base on the parameters, not worry about injection and have the complete query in readable format so we can judge the complexity.

  • (nodebb) in reply to nerd4sale

    Still, this mess is what you get when you go anti-relational in a relational database.

    I have said - many times here and more times not here - that the set of people who dislike <Product>¹ is comprised entirely of folks who don't understand how to use it.

    Except Perl. It's OK to hate Perl. It was designed that way. Which is why I say that when I die, it will become personal between me and Larry Wall in the afterlife.

    ¹ "Product" can be software, hardware, a TV remote, food processor, or even one of those fancy dishwashers with 4,000 buttons.

  • Appalled (unregistered)

    That solution is ridiculous. If they want to store them that way so be it. But to join and interrogate them they should create their own extra table within or without the ERP schema. Add a scheduled job to extract all that crap into a simple Order/Batches table and join on it. If it needs to be more up to the minute, create a stored procedure to suck out the batches for the orders of interest into a temp table of Order/Batches, join on it, and toss it.

  • Ryan (unregistered) in reply to Charvell

    I used to use where 1=1 when I have to build up a where clause and there might not be any conditions added. If I were doing it today, I might build up a list of conditions and then join them by and and prefix them with where if the length of the list is > 0 instead of just tossing where 1=1 at the beginning and calling it good.

  • (nodebb) in reply to Brian

    gunhead emoji [edit: sorry man, I tried. Apparently even admins can't inline images in comments]

  • Mr Bits (unregistered) in reply to Brian

    Especially with the tools available to manage database schema changes. It's been a long time, but back then I was using a tool from Red Gate that made this drop-dead easy (for MSSQL Server, at least). I did software version updates that included schema changes for large multi-national companies with large databases with nary a hiccup.

  • (nodebb)

    Seems to me that the solution wasn't properly tested or architected. Their problem could be solved by creating a materialized view that associates orderlineId and batchid. Then their view is a simple join to that Materialized view. Oracle can either keep the view up to date in near-real-time or be scheduled to rebuild it at specific times.

  • Tchize (unregistered) in reply to Brian

    'XML embedded in JSON jammed in a DB column' I think that's sometimes the definition of noSQL databases...

  • Damien (unregistered)

    Literally. Today. About 3 hours after reading this. "putting duration and offset in the same field, like this 180/15"

  • (nodebb) in reply to Mr. TA

    Apparently even admins can't inline images in comments

    🤯🔫

    You just gotta know the tricks…

  • what_not_to_do (unregistered)

    Talking about going to lengths to avoid changing DB schemas... Many years ago I got handed a defect for a system I knew very little about. At it's core it was a task management system, and it used a component to pull configuration values from a database. There are cleaner ways to do this these days, but back then the idea was, you deploy the app w/ minimal config, and it'll pull all the important stuff from the configuration database. Well the developer responsible for building this "task management" system decided in his wisdom that he would NOT build a DB schema for this application. Instead he would invent cleverly named "configuration keys" that his app could stuff in the configuration DB containing the serialized data the app needed. And to further disguise what he was doing, he would encode the serialized data before writing it to the DB so it didn't resemble anything sensible. Now the configuration database had a max size on the column that stored the config value, and it would truncate any data that didn't fit. Enter the defect: In order to decode an encoded string, you have to have the whole string. Once the app data grew to a size that caused the value field to overflow, it became unusable, turning the whole record into garbage. So much fun telling the users "sorry your data is gone, we can restore a backup from yesterday, but don't add anything new :|"

  • Paul - SharpAx (unregistered) in reply to Mr. TA

    This is why I love the ERP product that I work on and sell, the ability to put actual proper database tables and fields into the system to meet these needs, along with proper keys and indicies all generated appropriately and on the fly.

  • James (unregistered) in reply to Brian

    When you started your rant I though maybe I knew you. We have a project that stores JSON in a relational DB column. We then parse that JSON into Java objects before serializing it back to JSON again when interacting with an external API. I used to think it was the worst design possible, but now I am thankful there is no XML in the mix.

Leave a comment on “A Split in the Database”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article