Boolean return 0 or 1

[wubx]

Now databend boolean return true/false , like this:

[local] [sql]> select 1>0, 0>1;
[ok] ✅ Query precheck passed!
+---------+---------+
| (1 > 0) | (0 > 1) |
+---------+---------+
| true    | false   |
+---------+---------+
[ok] ✅ read rows: 1, read bytes: 1 B, rows/sec: 500 (rows/sec), bytes/sec: 500 (B/sec), time: 0.002 sec

if we can make boolean return 0/ 1 like this:

[local] [sql]> select 1>0, 0>1;
[ok] ✅ Query precheck passed!
+---------+---------+
| (1 > 0) | (0 > 1) |
+---------+---------+
| 1       | 0        |
+---------+---------+
[ok] ✅ read rows: 1, read bytes: 1 B, rows/sec: 500 (rows/sec), bytes/sec: 500 (B/sec), time: 0.002 sec

if support boolean return 0 or 1 , can optimize sql :
The percentage of delays by carrier for 2007

SELECT Carrier, c, c2, c*100/c2 as c3
FROM
(
    SELECT
        IATA_CODE_Reporting_Airline AS Carrier,
        count(*) AS c
    FROM ontime
    WHERE DepDelay>10
        AND Year=2007
    GROUP BY Carrier
) q
JOIN
(
    SELECT
        IATA_CODE_Reporting_Airline AS Carrier,
        count(*) AS c2
    FROM ontime
    WHERE Year=2007
    GROUP BY Carrier
) qq USING Carrier
ORDER BY c3 DESC;

Better version of the same query:

SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay>10)*100 AS c3
FROM ontime
WHERE Year=2007
GROUP BY Carrier
ORDER BY c3 DESC

[zhang2014]

CC: @zhyass may be interested in this.

[zhyass]

The bendcli code is here:

https://github.com/datafuselabs/databend/blob/main/cli/src/cmds/queries/query.rs#L302-L308

The boolean serialize to json boolean type:

https://github.com/datafuselabs/databend/blob/main/query/src/servers/http/v1/block_to_json.rs#L127

If use mysql client:

mysql> select 1>0, 0>1;
+---------+---------+
| (1 > 0) | (0 > 1) |
+---------+---------+
|       1 |       0 |
+---------+---------+
1 row in set (0.04 sec)
Read 1 rows, 1 B in 0.012 sec., 81.84 rows/sec., 81.84 B/sec.

If use clickhouse client:

databend :) select 1>0, 0>1;

SELECT
    1 > 0,
    0 > 1

Query id: d9224ec2-d519-4d70-8e05-df9295fdff72


┌─(1 > 0)─┬─(0 > 1)─┐
│       1 │       0 │
└─────────┴─────────┘

1 rows in set. Elapsed: 0.045 sec. 

[wubx]

thanks @zhyass by your hit, I change the SQL like :

SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10, int))*100 AS c3 FROM ontime WHERE Year=2019 GROUP BY Carrier ORDER BY c3 DESC;

[local] [sql]> SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10, int))*100 AS c3 FROM ontime WHERE Year=2019 GROUP BY Carrier ORDER BY c3 DESC;

Add cast this sql work fine:
[ok] ✅ Query precheck passed!
+---------+--------------------+
| Carrier | c3 |
+---------+--------------------+
| "F9" | 38.595869945331 |
| "B6" | 37.45927353056881 |
| "G4" | 36.31166611272019 |
| "AS" | 32.1468491329829 |
| "EV" | 30.176043004685077 |
| "OH" | 26.296560019909855 |
| "UA" | 25.893339297982138 |
| "OO" | 25.865059866458644 |
| "MQ" | 25.62391630760198 |
| "YV" | 25.474355823913506 |
| "NK" | 25.258121994678906 |
| "WN" | 24.48117447464929 |
| "AA" | 23.876291752220165 |
| "9E" | 23.276760574335363 |
| "YX" | 22.398974324697935 |
| "DL" | 18.51971701213525 |
| "HA" | 15.483186515836026 |
+---------+--------------------+

Use mysql client ：
mysql> select version();
+-------------------------------------------------------------------------------------+
| version() |
+-------------------------------------------------------------------------------------+
| DatabendQuery v-0.1.0-5019642-simd(1.59.0-nightly-2021-12-05T16:24:01.600873+00:00) |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Read 1 rows, 1 B in 0.000 sec., 5.54 thousand rows/sec., 5.54 KB/sec.

mysql> select 1>0, 0>1;
+---------+---------+
| (1 > 0) | (0 > 1) |
+---------+---------+
| 1 | 0 |
+---------+---------+
1 row in set (0.00 sec)
Read 1 rows, 1 B in 0.000 sec., 4.98 thousand rows/sec., 4.98 KB/sec.

mysql> SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay>10)*100 AS c3 FROM ontime WHERE Year>2011 GROUP BY Carrier ORDER BY c3 DESC;
ERROR 1105 (HY000): Code: 10, displayText = AggregateSumFunction does not support type 'Boolean' (while in select group by).

mysql>
mysql> SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10, int))*100 AS c3 FROM ontime WHERE Year>2011 GROUP BY Carrier ORDER BY c3 DESC;
+---------+--------------------+
| Carrier | c3 |
+---------+--------------------+
| G4 | 38.710751137246206 |
| B6 | 37.42604132033448 |
| F9 | 34.73291048653524 |
| AS | 32.634573048374214 |
| EV | 28.42174304268167 |
| YV | 24.019728622419724 |
| OH | 23.997798186780603 |
| NK | 23.856546227487755 |
| UA | 23.76559236074343 |
| MQ | 22.905840479826278 |
| OO | 21.926666812675645 |
| AA | 21.765970523935362 |
| WN | 20.97468122941792 |
| YX | 19.26245518736928 |
| 9E | 16.817392689336625 |
| DL | 16.307296565719 |
| HA | 15.488634009454394 |
| QX | 15.013808686919408 |
+---------+--------------------+
18 rows in set (0.30 sec)
Read 15580158 rows, 249.28 MB in 0.278 sec., 56.01 million rows/sec., 896.22 MB/sec.

It's also need add cast() , It's seem ok .