What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice

The reason there is no "type conversion / cardinality estimate" warning in this query:

SELECT LEFT(30500600, 2);

...is because there is no cardinality estimate. It's a constant expression being passed into a deterministic function - guaranteed to only return one "row." I think the explanation is as simple as that, since the warning is more about the potential cardinality problems (rather than just the simple existence of the type conversion).

If I get the estimated execution plan for that statement, it's just a single "select without query" element:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5081.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementId="1" StatementText="select LEFT(30500600, 2)" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

For what it's worth, even the table variable version doesn't display the warning in SQL Server 2017 (because it's really a pointless warning when the conversion can't possibly affect a cardinality estimate - as in your example).

To be clear, that warning isn't always pointless. Take this example:

SELECT message_id 
INTO #SomeNumbers
from sys.messages;

SELECT * 
FROM #SomeNumbers
WHERE LEFT(message_id, 2) = '50';

This puts 280,192 rows into a temp table. The SELECT query has this warning, and the warning is legit. SQL Server estimates there are 28,019.2 matching rows (10% of the table), when in fact there are only 2,156 matching rows (<1% of the table).

Regarding your example where you cast this input into the LEFT function, notice that the warning has changed slightly from CONVERT_IMPLICIT to just CONVERT:

Type conversion in expression (CONVERT(varchar(12),[test].[col1],0)) may affect "CardinalityEstimate" in query plan choice

So you're just replacing an implicit conversion with an explicit one, which still warrants a warning.