Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

plan-cache: the results is different if decimal column exist some larger value #31730

Closed
ChenPeng2013 opened this issue Jan 17, 2022 · 1 comment · Fixed by #31769
Closed
Assignees
Labels
affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.0 epic/plan-cache severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@ChenPeng2013
Copy link
Contributor

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

prepared-plan-cache.enabled =true

use test;
drop table if exists PK_S_MULTI_37;
 CREATE TABLE `PK_S_MULTI_37` (
  `COL1` decimal(55,0) NOT NULL,
  `COL2` decimal(55,0) NOT NULL,
  PRIMARY KEY (`COL1`,`COL2`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into PK_S_MULTI_37 values(-9999999999999999999999999999999999999999999999, 1);

prepare stmt from 'SELECT SUM(COL1+?), col2 FROM PK_S_MULTI_37 GROUP BY col2';
set @a=1;
execute stmt using @a;

SELECT SUM(COL1 + 1), col2 FROM PK_S_MULTI_37 GROUP BY col2;

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

MySQL [test]> prepare stmt from 'SELECT SUM(COL1+?), col2 FROM PK_S_MULTI_37 GROUP BY col2';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set @a=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a;
+-------------------------------------------------+------+
| SUM(COL1+?)                                     | col2 |
+-------------------------------------------------+------+
| -9999999999999999999999999999999999999999999998 |    1 |
+-------------------------------------------------+------+
1 row in set (0.00 sec)

MySQL [test]>
MySQL [test]> SELECT SUM(COL1 + 1), col2 FROM PK_S_MULTI_37 GROUP BY col2;
+-------------------------------------------------+------+
| SUM(COL1 + 1)                                   | col2 |
+-------------------------------------------------+------+
| -9999999999999999999999999999999999999999999998 |    1 |
+-------------------------------------------------+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> prepare stmt from 'SELECT SUM(COL1+?), col2 FROM PK_S_MULTI_37 GROUP BY col2';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set @a=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a;
Empty set (0.00 sec)

MySQL [test]>
MySQL [test]> SELECT SUM(COL1 + 1), col2 FROM PK_S_MULTI_37 GROUP BY col2;
+-------------------------------------------------+------+
| SUM(COL1 + 1)                                   | col2 |
+-------------------------------------------------+------+
| -9999999999999999999999999999999999999999999998 |    1 |
+-------------------------------------------------+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-634-g02c4179
Edition: Community
Git Commit Hash: 02c4179dd2fe284f2364b24cbfe794467fe9141b
Git Branch: release-5.4
UTC Build Time: 2022-01-17 01:01:46
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
@ChenPeng2013 ChenPeng2013 added type/bug The issue is confirmed as a bug. sig/planner SIG: Planner severity/major epic/plan-cache affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. labels Jan 17, 2022
@Reminiscent
Copy link
Contributor

The hashAgg can work correctly. But the streamAgg will get an error.

mysql> prepare stmt from 'SELECT /*+ HASH_AGG() */ SUM(COL1+?), col2 FROM PK_S_MULTI_37 GROUP BY col2';
Query OK, 0 rows affected (0.01 sec)

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
Empty set (0.00 sec)

mysql> 
mysql> prepare stmt from 'SELECT /*+ STREAM_AGG() */ SUM(COL1+?), col2 FROM PK_S_MULTI_37 GROUP BY col2';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
ERROR 1265 (01000): Data truncated for column '%s' at row %d

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.0 epic/plan-cache severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants