"SELECT posts.UPPER(title) FROM posts LIMIT 1" behavior difference between TiDB and MySQL

[yahonda]

This difference is found while running Ruby on Rails unit test https://github.com/rails/rails/blame/4986cfb26ed74d7532e27e5355d37a4e2d7f4321/activerecord/test/cases/relation/select_test.rb#L67-L69 against TiDB.

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Startup tiup playground

% tiup playground nightly

2. Connect to the tiup playground

% mysql --comments --host 127.0.0.1 --port 4000 -u root

3. Create posts table and insert some records

create database
use test;

CREATE TABLE `posts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `author_id` bigint(20) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `legacy_comments_count` int(11) DEFAULT '0',
  `taggings_with_delete_all_count` int(11) DEFAULT '0',
  `taggings_with_destroy_count` int(11) DEFAULT '0',
  `tags_count` int(11) DEFAULT '0',
  `indestructible_tags_count` int(11) DEFAULT '0',
  `tags_with_destroy_count` int(11) DEFAULT '0',
  `tags_with_nullify_count` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
KEY `index_posts_on_author_id` (`author_id`)
);

INSERT INTO `posts` (`id`, `author_id`, `title`, `body`, `type`, `legacy_comments_count`, `taggings_with_delete_all_count`, `taggings_with_destroy_count`, `tags_count`, `indestructible_tags_count`, `tags_with_destroy_count`, `tags_with_nullify_count`) VALUES (1, 1, 'Welcome to the weblog', 'Such a lovely day', 'Post', 2, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (2, 1, 'So I was thinking', 'Like I hopefully always am', 'SpecialPost', 1, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (3, 0, 'I don\'t have any comments', 'I just don\'t want to', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (4, 1, 'sti comments', 'hello', 'Post', 5, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (5, 1, 'sti me', 'hello', 'StiPost', 2, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (6, 1, 'habtm sti test', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (7, 2, 'eager loading with OR\'d conditions', 'hello', 'Post', 1, DEFAULT, DEFAULT, 3, DEFAULT, DEFAULT, DEFAULT), (8, 3, 'misc post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (9, 2, 'misc post by mary', 'hullo', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (10, 3, 'other post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (11, 2, 'other post by mary', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT);

4. Run SELECT posts.UPPER(title) FROM posts LIMIT 1;

mysql> SELECT posts.UPPER(title) FROM `posts` LIMIT 1;

2. What did you expect to see? (Required)

It should raise ERROR 1305 (42000): FUNCTION posts.UPPER does not exist as MySQL 8.1.0 does.

mysql> SELECT posts.UPPER(title) FROM `posts` LIMIT 1;
ERROR 1305 (42000): FUNCTION posts.UPPER does not exist
mysql>

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

mysql>

3. What did you see instead (Required)

It returns this record.

mysql> SELECT posts.UPPER(title) FROM `posts` LIMIT 1;
+-----------------------+
| posts.UPPER(title)    |
+-----------------------+
| WELCOME TO THE WEBLOG |
+-----------------------+
1 row in set (0.01 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.4.0-alpha
Edition: Community
Git Commit Hash: 09a83b8a46cc53db68d4a79ff4b51e95cfa6e031
Git Branch: heads/refs/tags/v7.4.0-alpha
UTC Build Time: 2023-09-06 14:25:49
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql>

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.4.0-alpha
Edition: Community
Git Commit Hash: 09a83b8a46cc53db68d4a79ff4b51e95cfa6e031
Git Branch: heads/refs/tags/v7.4.0-alpha
UTC Build Time: 2023-09-06 14:25:49
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

[tiancaiamao]

Seems a parser bug.

mysql> SELECT abd.upper(title) FROM `posts` LIMIT 1;
+-----------------------+
| abd.upper(title)      |
+-----------------------+
| WELCOME TO THE WEBLOG |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT bsadsasdfsdf.upper(title) FROM `posts` LIMIT 1;
+---------------------------+
| bsadsasdfsdf.upper(title) |
+---------------------------+
| WELCOME TO THE WEBLOG     |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT anything.upper(title) FROM `posts` LIMIT 1;
+-----------------------+
| anything.upper(title) |
+-----------------------+
| WELCOME TO THE WEBLOG |
+-----------------------+
1 row in set (0.00 sec)

[Defined2014]

It's casued by ProcedureCall, we support it in parser without spport in server side.