If you're looking at properties long enough, there's a a chance you're eventually going to come across one that says "property sold as is" or something along those lines.
Basically, this is a way of emphasizing "buyer beware" and the seller saying they are not taking responsibility for the condition of the property or making any promises about it. Sometimes it can be an indication that the property will be in rough condition, with deferred maintenance or other issues. As such, some lenders treat this as a potential 'red flag' when looking at the property for a mortgage.
However, it is also quite common on estates and power-of-sale/foreclosures because the seller is not the person who was actively living in the property (although with an estate you might be a bit more familiar with the property than a bank is for a property they are selling). When looking with buyer clients, I'm less concerned to see this statement in one of these situations.
And it's important to understand as a seller, that it doesn't really excuse you from disclosing known latent defects. A latent defect is any problem that may not be visible on inspection. This can include things like knob & tube hidden in the walls (if you know), occasional problems with flooding in the basement (sometimes hard to see unless it's presently wet), foundation problems hidden behind a finished basement, and so on. It does emphasize that the buyer should be doing their own due diligence, but does not entirely remove the sellers' legal obligations.
Clauses can be included in an Agreement of Purchase and Sale that help to emphasize that the buyer is making an informed decision and accepts the property "as is". Even this probably would not really excuse the seller from disclosing known latent defects if it came to a court battle, but it can be quite difficult for a buyer to prove a seller knew something. As such, buyers should always use reasonable caution when buying a property that includes this statement.