Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10663

Use of Inline table columns in HAVING clause throws 1463 Error

Details

    • 10.1.20

    Description

      Use of Inline table columns in Having Clause throws 1463 Error but use of the same column in Group by works fine.

      These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

      Test Case presented below

      MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> select @@Sql_mode;
      		 
      +-----------------------------------------------------------------------------------+
      		 
      | @@Sql_mode                                                                        |
      		 
      +-----------------------------------------------------------------------------------+
      		 
      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      		 
      +-----------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> CREATE TABLE `example1463` (
      		 
          ->   `Customer` varchar(255) NOT NULL,
      		 
          ->   `DeliveryStatus` varchar(255) NOT NULL,
      		 
          ->   `OrderSize` int(11) NOT NULL
      		 
          -> ) ;
      		 
      Query OK, 0 rows affected (0.02 sec)


      MariaDB [bts]>
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
      		 
      Query OK, 1 row affected (0.01 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
      		 
      Query OK, 1 row affected (0.00 sec)


      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
      		 
          -> FROM (SELECT Customer,
      		 
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
      		 
          -> OrderSize
      		 
          -> FROM example1463) as subQ
      		 
          -> GROUP BY Success, Customer
      		 
          -> WITH ROLLUP;
      		 
      +----------+---------+----------------+
      		 
      | Customer | Success | SUM(OrderSize) |
      		 
      +----------+---------+----------------+
      		 
      | Charlie  | No      |            200 |
      		 
      | David    | No      |            100 |
      		 
      | Edward   | No      |            150 |
      		 
      | NULL     | No      |            450 |
      		 
      | Charlie  | Yes     |            100 |
      		 
      | David    | Yes     |            210 |
      		 
      | Edward   | Yes     |            150 |
      		 
      | NULL     | Yes     |            460 |
      		 
      | NULL     | NULL    |            910 |
      		 
      +----------+---------+----------------+
      		 
      9 rows in set (0.00 sec)


      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
      		 
          -> FROM (SELECT Customer,
      		 
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
      		 
          -> OrderSize
      		 
          -> FROM example1463) as subQ
      		 
          -> GROUP BY Success, Customer
      		 
          -> WITH ROLLUP
      		 
          -> HAVING Success IS NOT NULL;
      		 
      ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause


      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
      		 
          -> FROM (SELECT Customer,
      		 
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
      		 
          -> OrderSize
      		 
          -> FROM example1463) as subQ
      		 
          -> GROUP BY Success, Customer
      		 
          -> ;
      		 
      +----------+---------+----------------+
      		 
      | Customer | Success | SUM(OrderSize) |
      		 
      +----------+---------+----------------+
      		 
      | Charlie  | No      |            200 |
      		 
      | David    | No      |            100 |
      		 
      | Edward   | No      |            150 |
      		 
      | Charlie  | Yes     |            100 |
      		 
      | David    | Yes     |            210 |
      		 
      | Edward   | Yes     |            150 |
      		 
      +----------+---------+----------------+
      		 
      6 rows in set (0.01 sec)


      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
      		 
          -> FROM (SELECT Customer,
      		 
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
      		 
          -> OrderSize
      		 
          -> FROM example1463) as subQ
      		 
          -> GROUP BY Success, Customer
      		 
          -> HAVING Success IS NOT NULL;
      		 
      ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          kgrandhe@us.ibm.com Kishor Grandhe created issue -
          kgrandhe@us.ibm.com Kishor Grandhe made changes -
          Field Original Value New Value
          Summary Use of Inline table columns in HAVING clause throws 1460 Error Use of Inline table columns in HAVING clause throws 1463 Error
          kgrandhe@us.ibm.com Kishor Grandhe made changes -
          Description Use of Inline table columns in Having Clause throws 1460 Error but use of the same column in Group by works fine.

          These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

          Test Case presented below

          MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
          Query OK, 0 rows affected (0.00 sec)

          MariaDB [bts]> select @@Sql_mode;
          +-----------------------------------------------------------------------------------+
          | @@Sql_mode |
          +-----------------------------------------------------------------------------------+
          | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
          +-----------------------------------------------------------------------------------+
          1 row in set (0.00 sec)

          MariaDB [bts]> CREATE TABLE `example1463` (
              -> `Customer` varchar(255) NOT NULL,
              -> `DeliveryStatus` varchar(255) NOT NULL,
              -> `OrderSize` int(11) NOT NULL
              -> ) ;
          Query OK, 0 rows affected (0.02 sec)

          MariaDB [bts]>
          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
          Query OK, 1 row affected (0.01 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | NULL | No | 450 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          | NULL | Yes | 460 |
          | NULL | NULL | 910 |
          +----------+---------+----------------+
          9 rows in set (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> ;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          +----------+---------+----------------+
          6 rows in set (0.01 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause           		Use of Inline table columns in Having Clause throws 1463 Error but use of the same column in Group by works fine.

          These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

          Test Case presented below

          MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
          Query OK, 0 rows affected (0.00 sec)

          MariaDB [bts]> select @@Sql_mode;
          +-----------------------------------------------------------------------------------+
          | @@Sql_mode |
          +-----------------------------------------------------------------------------------+
          | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
          +-----------------------------------------------------------------------------------+
          1 row in set (0.00 sec)

          MariaDB [bts]> CREATE TABLE `example1463` (
              -> `Customer` varchar(255) NOT NULL,
              -> `DeliveryStatus` varchar(255) NOT NULL,
              -> `OrderSize` int(11) NOT NULL
              -> ) ;
          Query OK, 0 rows affected (0.02 sec)

          MariaDB [bts]>
          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
          Query OK, 1 row affected (0.01 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | NULL | No | 450 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          | NULL | Yes | 460 |
          | NULL | NULL | 910 |
          +----------+---------+----------------+
          9 rows in set (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> ;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          +----------+---------+----------------+
          6 rows in set (0.01 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause
          elenst Elena Stepanova made changes -
          Description Use of Inline table columns in Having Clause throws 1463 Error but use of the same column in Group by works fine.

          These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

          Test Case presented below

          MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
          Query OK, 0 rows affected (0.00 sec)

          MariaDB [bts]> select @@Sql_mode;
          +-----------------------------------------------------------------------------------+
          | @@Sql_mode |
          +-----------------------------------------------------------------------------------+
          | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
          +-----------------------------------------------------------------------------------+
          1 row in set (0.00 sec)

          MariaDB [bts]> CREATE TABLE `example1463` (
              -> `Customer` varchar(255) NOT NULL,
              -> `DeliveryStatus` varchar(255) NOT NULL,
              -> `OrderSize` int(11) NOT NULL
              -> ) ;
          Query OK, 0 rows affected (0.02 sec)

          MariaDB [bts]>
          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
          Query OK, 1 row affected (0.01 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | NULL | No | 450 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          | NULL | Yes | 460 |
          | NULL | NULL | 910 |
          +----------+---------+----------------+
          9 rows in set (0.00 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> ;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          +----------+---------+----------------+
          6 rows in set (0.01 sec)

          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause           		Use of Inline table columns in Having Clause throws 1463 Error but use of the same column in Group by works fine.

          These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

          Test Case presented below

          {code:sql}
          MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
          Query OK, 0 rows affected (0.00 sec)

          MariaDB [bts]> select @@Sql_mode;
          +-----------------------------------------------------------------------------------+
          | @@Sql_mode |
          +-----------------------------------------------------------------------------------+
          | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
          +-----------------------------------------------------------------------------------+
          1 row in set (0.00 sec)

          MariaDB [bts]> CREATE TABLE `example1463` (
              -> `Customer` varchar(255) NOT NULL,
              -> `DeliveryStatus` varchar(255) NOT NULL,
              -> `OrderSize` int(11) NOT NULL
              -> ) ;
          Query OK, 0 rows affected (0.02 sec)
          {code}
          {code:sql}
          MariaDB [bts]>
          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
          Query OK, 1 row affected (0.01 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
          Query OK, 1 row affected (0.00 sec)

          MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
          Query OK, 1 row affected (0.00 sec)
          {code}
          {code:sql}
          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | NULL | No | 450 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          | NULL | Yes | 460 |
          | NULL | NULL | 910 |
          +----------+---------+----------------+
          9 rows in set (0.00 sec)
          {code}
          {code:sql}
          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> WITH ROLLUP
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause
          {code}
          {code:sql}
          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> ;
          +----------+---------+----------------+
          | Customer | Success | SUM(OrderSize) |
          +----------+---------+----------------+
          | Charlie | No | 200 |
          | David | No | 100 |
          | Edward | No | 150 |
          | Charlie | Yes | 100 |
          | David | Yes | 210 |
          | Edward | Yes | 150 |
          +----------+---------+----------------+
          6 rows in set (0.01 sec)
          {code}
          {code:sql}
          MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
              -> FROM (SELECT Customer,
              -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
              -> OrderSize
              -> FROM example1463) as subQ
              -> GROUP BY Success, Customer
              -> HAVING Success IS NOT NULL;
          ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause
          {code}
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          Thanks for the report and test case.

          elenst Elena Stepanova added a comment - Thanks for the report and test case.
          elenst Elena Stepanova made changes -
          Component/s Optimizer [ 10200 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 10.1 [ 16100 ]
          Assignee Oleksandr Byelkin [ sanja ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Sprint 10.1.20 [ 119 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Rank Ranked higher
          sanja Oleksandr Byelkin made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin added a comment -

          ONLY_FULL_GROUP_BY is really important for the case.

          sanja Oleksandr Byelkin added a comment - ONLY_FULL_GROUP_BY is really important for the case.
          sanja Oleksandr Byelkin added a comment -

          It looks like without ONLY_FULL_GROUP_BY the problem exists but left unreported with the error.

          sanja Oleksandr Byelkin added a comment - It looks like without ONLY_FULL_GROUP_BY the problem exists but left unreported with the error.
          sanja Oleksandr Byelkin added a comment -

          The problem is that the function which lookup in the GROUP BY (find_field_in_group_list) list check:
          (*(cur_group->item))>real_item()>type()

          and so with merged derived (and probably VIEW) it do not recognize it as an legal field.

          sanja Oleksandr Byelkin added a comment - The problem is that the function which lookup in the GROUP BY (find_field_in_group_list) list check: (*(cur_group->item)) >real_item() >type() and so with merged derived (and probably VIEW) it do not recognize it as an legal field.
          sanja Oleksandr Byelkin added a comment - - edited

          revision-id: 2e7c5fc70c571378ac1f5800fae3d59ba7d7bca5 (mariadb-5.5.53-16-g2e7c5fc)
          parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8
          committer: Oleksandr Byelkin
          timestamp: 2016-12-05 18:15:04 +0100
          message:

          MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error

          check for VIEW/DERIVED fields

          sanja Oleksandr Byelkin added a comment - - edited revision-id: 2e7c5fc70c571378ac1f5800fae3d59ba7d7bca5 (mariadb-5.5.53-16-g2e7c5fc) parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8 committer: Oleksandr Byelkin timestamp: 2016-12-05 18:15:04 +0100 message: MDEV-10663 : Use of Inline table columns in HAVING clause throws 1463 Error check for VIEW/DERIVED fields
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          sanja Oleksandr Byelkin added a comment -

          revision-id: 895309f441e0262229671dcec5d49c4ff8f8ca2c (mariadb-5.5.53-16-g895309f)
          parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8
          committer: Oleksandr Byelkin
          timestamp: 2016-12-06 14:18:24 +0100
          message:

          MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error

          check for VIEW/DERIVED fields

          sanja Oleksandr Byelkin added a comment - revision-id: 895309f441e0262229671dcec5d49c4ff8f8ca2c (mariadb-5.5.53-16-g895309f) parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8 committer: Oleksandr Byelkin timestamp: 2016-12-06 14:18:24 +0100 message: MDEV-10663 : Use of Inline table columns in HAVING clause throws 1463 Error check for VIEW/DERIVED fields
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5.54 [ 22307 ]
          Fix Version/s 10.0.29 [ 22312 ]
          Fix Version/s 10.1.20 [ 22112 ]
          Fix Version/s 10.2.3 [ 22115 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 76794 ] MariaDB v4 [ 150818 ]

          People

            sanja Oleksandr Byelkin
            kgrandhe@us.ibm.com Kishor Grandhe
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.