This sort of functionality shouldn't be used in application code (ironically, exactly as it's being shown in the example) but is a real treat for analysts and engineers writing adhoc queries over large tables.
For example: Let's say you have a piece of code that does: id, value = query("select * from tbl").fetch_one(). Now that works great if you know you have exactly the columns id and value in there. But let's say they swap places (possible in mysql), or a third column is added, and you don't update your code alongside it. Now that code breaks. Had you done "select id, value from tbl", it wouldn't break.
PS: The sibling answers are also all correct; there's more reasons not to use select * outside of the shell.
Well thank you for explaining your reasons.
Maybe that's the antipattern! But it's also a feature so I feel like you're better off avoiding the . Also, if someone adds additional big columns, you may incur additional, unnecessary cost
But doctor, it hurts when I [don't use an ORM/ORM-generated queries]
Don't litter in the streets, don't litter in your code. If you make an unmaintainable mess and it hurts, you are entirely responsible for owning it.
Then it has to process the data based on the declarative statement that was sent to the query engine. This means it may need temporary spools in memory or on disk to handle sorting, hash joins, etc.
Sometimes the engine is able to optimize this pipeline and only pull in the columns used in the outermost part of the query.
Being explicit in your column selection can greatly reduce the amount of memory and CPU needed to generate the results.
Probably the worst impact this can have is when an application is using a query with SELECT * on the data side when only a handful are used in the application.
Outside of disk IO, memory usage and CPU usage, now you have incurred an overhead by sending data across the network that is never being used.