matrixone icon indicating copy to clipboard operation
matrixone copied to clipboard

Published 20 hours ago • verified publisher icon matrixorigin

[Bug]: CTE error

Open dengn opened this issue 2 years ago • 4 comments

Is there an existing issue for the same bug?

  • [X] I have checked the existing issues.

Environment

- Version or commit-id (e.g. v0.1.0 or 8b23a93):4f0d66482f5c5bcf3c96a8b9e63d799bb1dd3ebc
- Hardware parameters:
- OS type: centos 
- Others:

Actual Behavior

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`)
);



INSERT INTO `customers` VALUES ('103', 'Atelier graphique', 'Schmitt', 'Carine ', '40.32.2555', '54, rue Royale', null, 'Nantes', null, '44000', 'France', '1370', '21000.00');
INSERT INTO `customers` VALUES ('112', 'Signal Gift Stores', 'King', 'Jean', '7025551838', '8489 Strong St.', null, 'Las Vegas', 'NV', '83030', 'USA', '1166', '71800.00');
INSERT INTO `customers` VALUES ('114', 'Australian Collectors, Co.', 'Ferguson', 'Peter', '03 9520 4555', '636 St Kilda Road', 'Level 3', 'Melbourne', 'Victoria', '3004', 'Australia', '1611', '117300.00');
INSERT INTO `customers` VALUES ('119', 'La Rochelle Gifts', 'Labrune', 'Janine ', '40.67.8555', '67, rue des Cinquante Otages', null, 'Nantes', null, '44000', 'France', '1370', '118200.00');
INSERT INTO `customers` VALUES ('121', 'Baane Mini Imports', 'Bergulfsen', 'Jonas ', '07-98 9555', 'Erling Skakkes gate 78', null, 'Stavern', null, '4110', 'Norway', '1504', '81700.00');
INSERT INTO `customers` VALUES ('124', 'Mini Gifts Distributors Ltd.', 'Nelson', 'Susan', '4155551450', '5677 Strong St.', null, 'San Rafael', 'CA', '97562', 'USA', '1165', '210500.00');
INSERT INTO `customers` VALUES ('125', 'Havel & Zbyszek Co', 'Piestrzeniewicz', 'Zbyszek ', '(26) 642-7555', 'ul. Filtrowa 68', null, 'Warszawa', null, '01-012', 'Poland', null, '0.00');
INSERT INTO `customers` VALUES ('128', 'Blauer See Auto, Co.', 'Keitel', 'Roland', '+49 69 66 90 2555', 'Lyonerstr. 34', null, 'Frankfurt', null, '60528', 'Germany', '1504', '59700.00');
INSERT INTO `customers` VALUES ('129', 'Mini Wheels Co.', 'Murphy', 'Julie', '6505555787', '5557 North Pendale Street', null, 'San Francisco', 'CA', '94217', 'USA', '1165', '64600.00');
INSERT INTO `customers` VALUES ('131', 'Land of Toys Inc.', 'Lee', 'Kwai', '2125557818', '897 Long Airport Avenue', null, 'NYC', 'NY', '10022', 'USA', '1323', '114900.00');
INSERT INTO `customers` VALUES ('141', 'Euro+ Shopping Channel', 'Freyre', 'Diego ', '(91) 555 94 44', 'C/ Moralzarzal, 86', null, 'Madrid', null, '28034', 'Spain', '1370', '227600.00');
INSERT INTO `customers` VALUES ('144', 'Volvo Model Replicas, Co', 'Berglund', 'Christina ', '0921-12 3555', 'Berguvsv gen  8', null, 'Lule', null, 'S-958 22', 'Sweden', '1504', '53100.00');
INSERT INTO `customers` VALUES ('145', 'Danish Wholesale Imports', 'Petersen', 'Jytte ', '31 12 3555', 'Vinb ltet 34', null, 'Kobenhavn', null, '1734', 'Denmark', '1401', '83400.00');
INSERT INTO `customers` VALUES ('146', 'Saveley & Henriot, Co.', 'Saveley', 'Mary ', '78.32.5555', '2, rue du Commerce', null, 'Lyon', null, '69004', 'France', '1337', '123900.00');
INSERT INTO `customers` VALUES ('148', 'Dragon Souveniers, Ltd.', 'Natividad', 'Eric', '+65 221 7555', 'Bronz Sok.', 'Bronz Apt. 3/6 Tesvikiye', 'Singapore', null, '079903', 'Singapore', '1621', '103800.00');
INSERT INTO `customers` VALUES ('151', 'Muscle Machine Inc', 'Young', 'Jeff', '2125557413', '4092 Furth Circle', 'Suite 400', 'NYC', 'NY', '10022', 'USA', '1286', '138500.00');
INSERT INTO `customers` VALUES ('157', 'Diecast Classics Inc.', 'Leong', 'Kelvin', '2155551555', '7586 Pompton St.', null, 'Allentown', 'PA', '70267', 'USA', '1216', '100600.00');
INSERT INTO `customers` VALUES ('161', 'Technics Stores Inc.', 'Hashimoto', 'Juri', '6505556809', '9408 Furth Circle', null, 'Burlingame', 'CA', '94217', 'USA', '1165', '84600.00');
INSERT INTO `customers` VALUES ('166', 'Handji Gifts& Co', 'Victorino', 'Wendy', '+65 224 1555', '106 Linden Road Sandown', '2nd Floor', 'Singapore', null, '069045', 'Singapore', '1612', '97900.00');
INSERT INTO `customers` VALUES ('167', 'Herkku Gifts', 'Oeztan', 'Veysel', '+47 2267 3215', 'Brehmen St. 121', 'PR 334 Sentrum', 'Bergen', null, 'N 5804', 'Norway  ', '1504', '96800.00');
INSERT INTO `customers` VALUES ('168', 'American Souvenirs Inc', 'Franco', 'Keith', '2035557845', '149 Spinnaker Dr.', 'Suite 101', 'New Haven', 'CT', '97823', 'USA', '1286', '0.00');
INSERT INTO `customers` VALUES ('169', 'Porto Imports Co.', 'de Castro', 'Isabel ', '(1) 356-5555', 'Estrada da sade n. 58', null, 'Lisboa', null, '1756', 'Portugal', null, '0.00');
INSERT INTO `customers` VALUES ('171', 'Daedalus Designs Imports', 'Ranc', 'Martine ', '20.16.1555', '184, chausse de Tournai', null, 'Lille', null, '59000', 'France', '1370', '82900.00');
INSERT INTO `customers` VALUES ('172', 'La Corne D\'abondance, Co.', 'Bertrand', 'Marie', '(1) 42.34.2555', '265, boulevard Charonne', null, 'Paris', null, '75012', 'France', '1337', '84300.00');
INSERT INTO `customers` VALUES ('173', 'Cambridge Collectables Co.', 'Tseng', 'Jerry', '6175555555', '4658 Baden Av.', null, 'Cambridge', 'MA', '51247', 'USA', '1188', '43400.00');
...

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;

This CTE outputs: ERROR 1105 (HY000): Column 'customername' does not exist

Expected Behavior

In MySQL, this CTE SQL works and produces good results.

Steps to Reproduce

No response

Additional information

No response

dengn avatar Jul 20 '22 12:07 dengn

@dengn Can't reproduce this bug. I tried the queries above and it can output results.

aunjgr avatar Jul 21 '22 10:07 aunjgr

Github is not showing correctly about the table and column name, they were actually quoted by ``. This issue is also because of that behavior.

dengn avatar Jul 22 '22 01:07 dengn

If a column name is backquoted in DDL, and not all lowercase, it has to be also backquoted in DQL. Or it will be unconditionally converted to lowercase by parser, and fails to match.

For example, the below query can work:

WITH customers_in_usa AS (
SELECT
`customerName`, state
FROM
customers
WHERE
country = 'USA'
) SELECT
`customerName`
FROM
customers_in_usa
WHERE
state = 'CA'
ORDER BY `customerName`;

aunjgr avatar Jul 22 '22 08:07 aunjgr

@dengn Is the current behavior acceptable?

aunjgr avatar Jul 28 '22 09:07 aunjgr

related to #4172.

dengn avatar Sep 30 '22 06:09 dengn

waiting for #4172, should we move this to 0.7?

badboynt1 avatar Nov 08 '22 15:11 badboynt1

no progress

badboynt1 avatar Nov 17 '22 11:11 badboynt1

no progress

badboynt1 avatar Nov 24 '22 11:11 badboynt1

same problem as #4172 , assign @iamlinjunhong

badboynt1 avatar Nov 30 '22 09:11 badboynt1

fix later

iamlinjunhong avatar Jan 13 '23 13:01 iamlinjunhong

fixed in 6880d4f6e52ed388648e41f8e6a5e3b0d692cad8

iamlinjunhong avatar Mar 09 '23 06:03 iamlinjunhong

fixed.

mysql> WITH customers_in_usa AS ( SELECT -> SELECT -> customerName, state -> FROM -> customers -> WHERE -> country = 'USA' -> ) SELECT -> customerName -> FROM -> customers_in_usa -> WHERE -> state = 'CA' -> ORDER BY customerName; +------------------------------+ | customername | +------------------------------+ | Mini Gifts Distributors Ltd. | | Mini Wheels Co. | | Technics Stores Inc. | +------------------------------+ 3 rows in set (0.01 sec)

dengn avatar Mar 20 '23 05:03 dengn