Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

21–30 of 222 posts

Re: SQL Anti-Patterns

#21

Post by leptons »

EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.

And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?

Re: SQL Anti-Patterns

#22

Post by JadeNB »

readthenotes1 wrote: post #16
em500 wrote: post #5

Earlier quoted context omitted.

Still waiting for the definitive article on how using the term anti-pattern is an anti-pattern.

If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what? I think most people think an anti-pattern is an aberration in the "solution" section that creates more problems. So here, the anti-pattern is that people use a term so casually (e.g., DevOps) that no…

> If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what?

Strange choice of example! I'm not sure I agree that your example is a common problem, and I'm even less sure that the proposed solution to it is generally useful.

Re: SQL Anti-Patterns

#23

Post by karmakaze »

anthonyIPH wrote: post #8

"Instead you should: query WHERE name = ‘abc’ create an indexed UPPER(name) column" Should there be an "or" between these 2 points, or am I missing something? Why create an UPPER index column and not use it?

[and a third] OR use a case-insensitive collation for the name column.

Re: SQL Anti-Patterns

#24

Post by tremon »

wmonk wrote: post #9

The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index. Unfortunately I learned this the hard way!

The given solution (create an indexed UPPER(name) column) is not the best way to solve this, at least not on MS SQL Server. Not sure if this is equally supported in other databases, but the better solution is to create a case-insensitive computed column:

  ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
(season to taste)

Re: SQL Anti-Patterns

#26

Post by tremon »

dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

If you have select * in your code, there already is something wrong with your code, whether it breaks or not: the performance and possibly output of your code is now dependent on the table definition. I'm pretty sure Rich Hickey has also talked about the importance of avoiding non-local dependencies and effects in your code.

Re: SQL Anti-Patterns

#27

Post by tremon »

jacknews wrote: post #18
LikesPwsh wrote: post #12

Earlier quoted context omitted.

"Dimension table" is the name for lookup tables in a star or snowflake schema.

TIL, Thanks. 'Landed table'? Is that the 'fact table', the one that contains the codes that need to be looked-up?

I'm pretty sure the landed table refers to the local copy of the original source. In an ETL* pipeline, the place where source data is stored for further processing is usually called the landing zone. Fact and Dimension tables are outputs of the process, whereas the landing tables are the inputs.

* in whatever order they're used

Re: SQL Anti-Patterns

#28

Post by skybrian »

> three or four layers of subqueries, each one filtering or aggregating the results of the previous one, totaling over 5000 lines of code

In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?

Re: SQL Anti-Patterns

#29

Post by rileymat2 »

dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

The reasoning is in the article, and true.

> Schema evolution can break your view, which can have downstream effects

Select * is the problem itself in the face of schema evolution and things like name collision.

Re: SQL Anti-Patterns

#30

Post by sql_nitpicker »

dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

I don't see anything wrong with what the article is saying. If you have a view over a join of A and B, and the view uses "select *", then what is gonna happen when A adds a column with the same name as a column in B?

In sqlite, the view definition will be automatically expanded and one of the columns in the output will automatically be distinguished with an alias. Which column name changes is dependent on the order of tables in the join. This can absolutely break code.

In postgres, the view columns are qualified at definition time so nothing changes immediately. But when the view definition gets updated you will get a failure in the DDL.

In any system, a large column can be added to one of the constituent tables and cause a performance problem. The best advice is to avoid these problems and never use "select *" in production code.

Post reply on HN