[MDEV-6797] intcol=NUM is not used to remove intcol from ORDER BY if NUM is quoted - Jira

XML

Word

Printable

ORDER BY optimizer removes constant items from ORDER BY list. That is, if there is a query

SELECT ... WHERE col1=const  ... ORDER BY col1, ...

then col1 can be removed from ORDER BY list (except for some charsets).

The problem is, this feature doesn't work for some cases where it should.
Test dataset:

CREATE TABLE tb_bug2 (

  pk1 int(11) NOT NULL,

  pk2 varchar(64) NOT NULL,

  col1 varchar(16) DEFAULT NULL,

  PRIMARY KEY (pk1,pk2),

  KEY key1 (pk1,col1,pk2)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_bug2` VALUES

(12321321,'a8f5f167f44f4964e6c998dee827110c','video'),

(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'),

(12321321,'wwafdsafdsafads','video'),

(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'),

(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'),

(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'),

(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd');

Now, lets try two queries, one with pk1=const and the other with pk1='const':

explain

SELECT pk2 FROM tb_bug2 USE INDEX(key1)

WHERE pk1 = 123 AND col1 = 'video'

ORDER BY pk1 DESC, col1 DESC, pk2 DESC LIMIT 21;

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra

1       SIMPLE  tb_bug2 ref     key1    key1    55      const,const     1       Using where; Using index

explain

SELECT pk2 FROM tb_bug2 USE INDEX(key1)

WHERE pk1 = '123' AND col1 = 'video'

ORDER BY pk1 DESC, col1 DESC, pk2 DESC LIMIT 21;

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra

1       SIMPLE  tb_bug2 ref     key1    key1    55      const,const     1       Using where; Using index; Using filesort

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.