Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.11
-
None
-
Linux
Description
I checked this with MS SQL and it behaves correctly.
I have a very large table with one single column, varchar, primary key
These two statements take forever
Set @strvbig:='9544447408';select 1 FROM large_table WHERE did = cast(@strvbig as char) limit 1;
Set @strvbig:=9544447408;select 1 FROM large_table WHERE did = cast(@strvbig as char) limit 1;
while
select 1 FROM large_table WHERE did = '9544447408' limit 1;
returns immediately.
the optimizer is executing the cast on the right side of the Where clause for each row, which is insane since there are no columns involved, therefore it's a constant expression.
In MS SQL, this works perfectly
declare @strvbig bigint=9544447408;
select 1 from large_table where did=cast (@strvbig as varchar);
This problem seems to add an unbelievable amount of inefficiency to MariaDB when writing stored procedures and writing statements like select from large_table where column=variable,
if the variable is of a different datatype.
Attachments
Issue Links
- duplicates
-
MDEV-26729 Optimizer does wrong cast from number to string
-
- Closed
-