Order by expression failed to execute in mysql plugin

[weijunlu]

Order by expression failed to execute in mysql plugin

Steps to reproduce the behavior:

1. The version information is as follows:

apache drill> select commit_message, commit_time from sys.version;
+----------------------------------------------------------------------------------+---------------------------+
| commit_message | commit_time |
+----------------------------------------------------------------------------------+---------------------------+
| DRILL-8314: Add support for automatically retrying and disabling broken storage plugins (https://github.com/apache/drill/pull/2655) | 18.10.2022 @ 18:15:31 CST |

2. Create tables in mysql database.

CREATE TABLE orders( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);

3. Create mysql Plugins in Storage label via http://localhost:8047/storage pages
4. The sql statement is executed as follows

select  
extract(year from o_orderdate) as o_year
from orders 
group by o_year 
order by o_year;

apache drill (mysql.test)> select
2...............semicolon> extract(year from o_orderdate) as o_year
3...............semicolon> from orders
4...............semicolon> group by o_year
5...............semicolon> order by o_year;
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

Sql: SELECT EXTRACT(YEAR FROM `O_ORDERDATE`) AS `o_year`
FROM `test`.`orders`
GROUP BY EXTRACT(YEAR FROM `O_ORDERDATE`)
ORDER BY EXTRACT(YEAR FROM `O_ORDERDATE`) IS NULL, EXTRACT(YEAR FROM `O_ORDERDATE`)
Fragment: 0:0

[Error Id: 43ba28d2-169f-4940-ab9c-5a1fa52de994 on DESKTOP-PHHB7LC:31010] (state=,code=0)

[vvysotskyi]

Drill (actually Calcite) rewrites the query to use the expression specified in the project in the GROUP BY and ORDER BY statements. It is done so because some databases don't support using select aliases in such statements.

As a workaround, it is possible to disable the sql_mode only_full_group_by policy so that the query will succeed.

Btw MariaDB works fine with such types of queries.

[cgivre]

Thanks @vvysotskyi . Can you explain to to disable the sql_mode? Thanks!

[vvysotskyi]

There are several ways to do that. Here are some examples: https://stackoverflow.com/questions/23921117/disable-only-full-group-by

[jnturton]

This feels like a bit of a blind spot in MySQL to me. The docs say that since 5.7.5 MySQL has detected functional dependence of expressions on the grouped columns when ONLY_FULL_GROUP_BY is enabled. Yet the null ordering expression foo is null generated by Calcite is certainly functionally dependent on foo and it still gets rejected.

[vvysotskyi]

@jnturton, bot sure why, but the query above worked fine after disabling only_full_group_by.

[weijunlu]

@vvysotskyi @cgivre. If MySQL disables only_full_group_by, the sql can be executed.

Jupiter (mysql.test)> select 2..........semicolon> extract(year from o_orderdate) as o_year 3..........semicolon> from orders 4..........semicolon> group by o_year 5..........semicolon> order by o_year; +--------+ | o_year | +--------+ | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | +--------+ 7 rows selected (4.079 seconds)