Skip to content

sql result discrepency with sqlite and postgres #13779

New issue
New issue
Closed
Closed
sql result discrepency with sqlite and postgres#13779
Labels
bugSomething isn't working

Description

@Omega359
Omega359
opened on Dec 14, 2024

Describe the bug

sqlite random/expr/slt_good_10.slt:

SELECT ALL + + MIN ( - CAST ( + 6 AS INTEGER ) ) * CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END

Both PG and sqlite have a result of 'null'. DF result is '-6'

The difference looks to be the handling of a case clause that doesn't match and doesn't have an else block. Most db's including PG have the result of a case block with no matches and no else to be 'null'. It seems DF is '1'

> select CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END;
+------------------------------------------------------------------------------------------------------+
| CASE WHEN NULL BETWEEN Int64(-88) AND (- Int64(-91)) * (- avg(Int64(95))) THEN count(Int64(-34)) END |
+------------------------------------------------------------------------------------------------------+
| 1                                                                                                    |
+------------------------------------------------------------------------------------------------------+

The following is duckdb's behavior with the same sql:

D select CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END;
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ CASE  WHEN ((NULL BETWEEN -88 AND (91 * -(avg(95))))) THEN (count(-34)) ELSE NULL END │
│                                         int64                                         │
├───────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                       │
└───────────────────────────────────────────────────────────────────────────────────────┘
D select typeof(CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END);
┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│ typeof(CASE  WHEN ((NULL BETWEEN -88 AND (91 * -(avg(95))))) THEN (count(-34)) ELSE NULL END) │
│                                            varchar                                            │
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ BIGINT                                                                                        │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

To Reproduce

sql above.

Expected behavior

I would expect the same result as PG and other db's.

Additional context

No response

Activity

  1. Omega359
    added
    bugSomething isn't working
    on Dec 14, 2024

  2. Omega359 commented on Dec 14, 2024

    @Omega359
    Omega359
    on Dec 14, 2024
    ContributorAuthor

    I believe another sql that has the same cause is:

    External error: query result mismatch:
[SQL] SELECT ALL + CASE WHEN AVG ( ALL + 88 ) >= 74 + + - 95 - CASE 23 WHEN 41 + - COUNT ( 16 ) THEN + 48 - 12 * + 70 - - ( - 95 ) * + 50 + 37 + + + COALESCE ( - 68, - - ( ( + 66 ) ) / + 28 ) END - + - 38 THEN + AVG ( 19 ) END - + 69
[Diff] (-expected|+actual)
-   NULL
+   -50
at test_files/sqlite/random/expr/slt_good_104.slt:38923

  3. Omega359 commented on Dec 26, 2024

    @Omega359
    Omega359
    on Dec 26, 2024
    ContributorAuthor

    This looks to have been fixed with #13885

    DataFusion CLI v44.0.0
> SELECT ALL + + MIN ( - CAST ( + 6 AS INTEGER ) ) * CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END;
+--------------------------------------------------------------------------------------------------------------------------+
| min((- Int64(6))) * CASE WHEN NULL BETWEEN Int64(-88) AND (- Int64(-91)) * (- avg(Int64(95))) THEN count(Int64(-34)) END |
+--------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.014 seconds.
> SELECT ALL + CASE WHEN AVG ( ALL + 88 ) >= 74 + + - 95 - CASE 23 WHEN 41 + - COUNT ( 16 ) THEN + 48 - 12 * + 70 - - ( - 95 ) * + 50 + 37 + + + COALESCE ( - 68, - - ( ( + 66 ) ) / + 28 ) END - + - 38 THEN + AVG ( 19 ) END - + 69;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CASE WHEN avg(Int64(88)) >= Int64(74) + Int64(-95) - CASE Int64(23) WHEN Int64(41) + (- count(Int64(16))) THEN Int64(48) - Int64(12) * Int64(70) - (- Int64(-95)) * Int64(50) + Int64(37) + coalesce(Int64(-68),(- (- Int64(66))) / Int64(28)) END - Int64(-38) THEN avg(Int64(19)) END - Int64(69) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
  4. Omega359
    closed this as completedon Dec 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions