CASE WHEN issue in 1.17.3

[ondohotola]

Given the file p.csv :

A,B 1,2 2,3 4,5 0,0

the command in CSVQ

SELECT CASE WHEN A=0 THEN A=9 ELSE A END FROM p

results in

%!s(<nil>): cannot evaluate as a value

Can you reproduce and if so advise, please?

greetings, el

[derekmahar]

What is the result when you run query

SELECT CASE WHEN A=0 THEN 9 ELSE A END AS A FROM p

?

I haven't yet tested this query.

[derekmahar]

In SQLite, the query

DROP TABLE IF EXISTS test;
CREATE TABLE test(A INT, B INT);
INSERT INTO test VALUES (1,2),(2,3),(4,5),(0,0);
SELECT CASE WHEN A=0 THEN A=9 ELSE A END AS A FROM test;

results in

A
1
2
4
0

However, the query

DROP TABLE IF EXISTS test;
CREATE TABLE test(A INT, B INT);
INSERT INTO test VALUES (1,2),(2,3),(4,5),(0,0);
SELECT CASE WHEN A=0 THEN 9 ELSE A END AS A FROM test;

results in

A
1
2
4
9

I'm surprised that SQLite doesn't report an for the first query.

[mithrandie]

This bug occurs when using CASE WHEN expression and has been present since version 1.17.1.

CASE value WHEN… works without errors.

Thank you for reporting.

[mithrandie]

This bug has been fixed in version 1.17.5.

$ csvq -v
csvq version 1.17.5
$ csvq 'SELECT CASE WHEN A=0 THEN A=9 ELSE A END FROM p'
+---------------------------------------+
| CASE WHEN A = 0 THEN A = 9 ELSE A END |
+---------------------------------------+
| 1                                     |
| 2                                     |
| 4                                     |
|                 FALSE                 |
+---------------------------------------+

[ondohotola]

Thank you.

I had found a way around it in production, in a tool which I don’t use often, but is essential to (a financial aspect of) my practice, but reporting bugs can only improve this amazing tool.

Once the upgrade has reached me I’ll version back and see what happens.

As usual, great work.

greetings, el On 26. Jun 2022, 04:50 +0200, mithrandie/csvq @.***>, wrote:

A

[ondohotola]

Works as advertized :-)-O

el

On 2022-06-26 12:35 , Dr Eberhard W Lisse wrote:

Thank you.

I had found a way around it in production, in a tool which I don’t use often, but is essential to (a financial aspect of) my practice, but reporting bugs can only improve this amazing tool.

Once the upgrade has reached me I’ll version back and see what happens.

As usual, great work.

greetings, el On 26. Jun 2022, 04:50 +0200, mithrandie/csvq @.***>, wrote:

A

-- Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist @.** / | Telephone: +264 81 124 6733 (cell) PO Box 8421 Bachbrecht \ / If this email is signed with GPG/PGP 10007, Namibia ;____/ Sect 20 of Act No 4 of 2019 may apply

[derekmahar]

$ csvq -v
csvq version 1.17.5
$ csvq 'SELECT CASE WHEN A=0 THEN A=9 ELSE A END FROM p'
+---------------------------------------+
| CASE WHEN A = 0 THEN A = 9 ELSE A END |
+---------------------------------------+
| 1                                     |
| 2                                     |
| 4                                     |
|                 FALSE                 |
+---------------------------------------+

Are you sure that the last row shouldn't return 0? As it stands, the column is mixing integer and boolean values. For comparison, SQLite coerces the result of A = 9 to an integer (0), while PostgreSQL reports an error because the expression results in a type mismatch in the column. Like SQLite, I think csvq is less strict about data types, but in general I think it makes sense to preserve type consistency in all rows. What does the SQL standard require or recommend?

[ondohotola]

That was an issue between screen and chair.

But it’s fixed now in 1.17.5

el

— Sent from Dr Lisse’s iPhone/iPad On 27. Jun 2022, 00:02 +0200, Derek Mahar @.***>, wrote:

$ csvq -v csvq version 1.17.5 $ csvq 'SELECT CASE WHEN A=0 THEN A=9 ELSE A END FROM p' +---------------------------------------+ | CASE WHEN A = 0 THEN A = 9 ELSE A END | +---------------------------------------+ | 1 | | 2 | | 4 | | FALSE | +---------------------------------------+ Are you sure that the last row shouldn't return 0? As it stands, the column is mixing integer and boolean values. For comparison, SQLite coerces the result of A = 9 to an integer (0), while PostgreSQL reports an error because the expression results in a type mismatch in the column. Like SQLite, I think csvq is less strict about data types, but in general I think it makes sense to preserve type consistency in all rows. What does the SQL standard require or recommend? — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>