search

heavyai / heavydb

HeavyDB (formerly OmniSciDB)
https://heavy.ai
Apache License 2.0
2.92k stars 444 forks source link

[GPU Logic Bug] SELECT DISTINCT <column> FROM <table> ORDER BY 1 DESC LIMIT 10 Brings Errors #805

Open qwebug opened 9 months ago

qwebug commented 9 months ago

Describe:

Using SELECT DISTINCT \ FROM \

ORDER BY 1 DESC LIMIT 10 brings different results , when set EXECUTOR_DEVICE 'CPU' and 'GPU'.

SQL:

CREATE TABLE t0(c0 INT, c1 DOUBLE, SHARD KEY(c0)) WITH (shard_count=662);
CREATE TABLE t1(c0 DECIMAL(2)  NULL, c1 FLOAT );
INSERT INTO t0 VALUES(0,0),(-25151,null),(4115,-62),(4165,62),(-16256,null),(-115,-62),(11215,62);
INSERT INTO t1 VALUES(1, 0.71818817),(0, -3.896708E7), (1, 0.9509316), (1, 8.043511E8),(1, 0.585598),(1,null),(null,9.1319142E8);
ALTER SESSION SET EXECUTOR_DEVICE='CPU';
SELECT DISTINCT t0.c0,t1.c1 FROM t0, t1  ORDER BY 1 DESC LIMIT 10;

Result:

c0|c1
11215|9.131914e+08
11215|0.585598
11215|0.7181882
11215|-3.896708e+07
11215|8.043511e+08
11215|NULL
11215|0.9509316
4165|9.131914e+08
4165|0.7181882
4165|NULL

SQL:

ALTER SESSION SET EXECUTOR_DEVICE='GPU'
SELECT DISTINCT t0.c0,t1.c1 FROM t0, t1  ORDER BY 1 DESC LIMIT 10;

Result:

c0|c1
4165|-3.896708e+07
4165|0.7181882
4165|0.9509316
4165|8.043511e+08
4115|0.9509316
4115|8.043511e+08
4115|NULL
4115|0.7181882
4115|0.585598
4115|9.131914e+08

Environment:

Docker Deployment

https://hub.docker.com/layers/heavyai/heavyai-ee-cuda/latest/images/sha256-5af3ad3a00cbc5ce09c299b8b81cda96521a27373dbb1e59209c02358cfd9b1f?context=explore

Docker DIGEST: sha256:5af3ad3a00cbc5ce09c299b8b81cda96521a27373dbb1e59209c02358cfd9b1f

HeavyDB Version: 7.1.0-20230821-eae9ec17da

HeavyDB license: Free Edition

qwebug commented 1 month ago

This problem came up at HeavyDB Version: 7.1.0-20230821-eae9ec17da (Docker: https://hub.docker.com/layers/heavyai/heavyai-ee-cuda/v7.1.0/images/sha256-5af3ad3a00cbc5ce09c299b8b81cda96521a27373dbb1e59209c02358cfd9b1f?context=explore ) . And it has been fixed at HeavyDB Version: 7.2.6 (Docker: https://hub.docker.com/layers/heavyai/heavyai-ee-cuda/v7.2.6/images/sha256-d67d20fc9cf50437169cecce8a0180200418ba732ce25f2a05094eac776560be?context=explore), after my verification. Thanks to the developers for their contributions.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.