xiedeyantu commented on code in PR #5147: URL: https://github.com/apache/calcite/pull/5147#discussion_r3716822892
########## core/src/test/resources/sql/row-equality.iq: ########## @@ -0,0 +1,615 @@ +# row-equality.iq - Tests for equality of ROW values at runtime +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to you under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +!use scott +!set outputformat mysql + +# Test cases for https://issues.apache.org/jira/browse/CALCITE-7678 +# [CALCITE-7678] Runtime equality for ROW values produces incorrect results +# +# Calcite has one ROW type, compared everywhere with the standard's row +# semantics: "(a, b)" and "ROW(a, b)" have the same representation, and a +# CREATE TYPE structured type uses the same RelRecordType as a ROW +# expression. +# +# Each query below carries a comment recording its status on PostgreSQL 14. +# Note that Postgres does NOT implement the standard SQL semantics for nested ROW comparisons. + +##################################################################### +# GROUP BY + +# GROUP BY a flat ROW value. +# Validated on PostgreSQL 14: same result. +SELECT r, COUNT(*) AS c +FROM (SELECT ROW(x, y) AS r + FROM (VALUES (1, 'a'), (1, 'a'), (2, 'b')) AS v(x, y)) AS t +GROUP BY r +ORDER BY c; ++--------+---+ +| R | C | ++--------+---+ +| {2, b} | 1 | +| {1, a} | 2 | ++--------+---+ +(2 rows) + +!ok + +# GROUP BY a ROW value with a NULL field: grouping uses not-distinct +# semantics, so the two ROW(2, NULL) values belong to the same group. +# Validated on PostgreSQL 14: same result. +SELECT r, COUNT(*) AS c +FROM (SELECT ROW(x, y) AS r + FROM (VALUES (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t +GROUP BY r +ORDER BY c; ++-----------+---+ +| R | C | ++-----------+---+ +| {1, a} | 1 | +| {2, null} | 2 | ++-----------+---+ +(2 rows) + +!ok + +# GROUP BY a nested ROW value. +# Validated on PostgreSQL 14: same result. +SELECT r, COUNT(*) AS c +FROM (SELECT ROW(ROW(x, y), z) AS r + FROM (VALUES (1, 'a', 10), (1, 'a', 10), (2, 'b', 20)) AS v(x, y, z)) AS t +GROUP BY r +ORDER BY c; ++--------------+---+ +| R | C | ++--------------+---+ +| {{2, b}, 20} | 1 | +| {{1, a}, 10} | 2 | ++--------------+---+ +(2 rows) + +!ok + +##################################################################### +# DISTINCT + +# SELECT DISTINCT over ROW values, including ones with a NULL field. +# Validated on PostgreSQL 14: same result. +SELECT DISTINCT r +FROM (SELECT ROW(x, y) AS r + FROM (VALUES (1, 'a'), (1, 'a'), (2, NULL), (2, NULL)) AS v(x, y)) AS t +ORDER BY r; ++-----------+ +| R | ++-----------+ +| {1, a} | +| {2, null} | ++-----------+ +(2 rows) + +!ok + +##################################################################### +# Set operations + +# UNION removes duplicate ROW values, including ones with a NULL field. +# Validated on PostgreSQL 14: same result. +SELECT ROW(x, y) AS r +FROM (VALUES (1, 'a'), (2, NULL)) AS v(x, y) +UNION +SELECT ROW(x, y) AS r +FROM (VALUES (2, NULL)) AS w(x, y) +ORDER BY r; ++-----------+ +| R | ++-----------+ +| {1, a} | +| {2, null} | ++-----------+ +(2 rows) + +!ok + +# INTERSECT over ROW values. +# Validated on PostgreSQL 14: same result. +SELECT ROW(x, y) AS r +FROM (VALUES (1, 'a'), (2, 'b')) AS v(x, y) +INTERSECT +SELECT ROW(x, y) AS r +FROM (VALUES (2, 'b'), (3, 'c')) AS w(x, y); ++--------+ +| R | ++--------+ +| {2, b} | ++--------+ +(1 row) + +!ok + +# EXCEPT over ROW values. +# Validated on PostgreSQL 14: same result. +SELECT ROW(x, y) AS r +FROM (VALUES (1, 'a'), (2, 'b')) AS v(x, y) +EXCEPT +SELECT ROW(x, y) AS r +FROM (VALUES (2, 'b'), (3, 'c')) AS w(x, y); ++--------+ +| R | ++--------+ +| {1, a} | ++--------+ +(1 row) + +!ok + +##################################################################### +# JOIN on ROW values, strict equality Review Comment: Thanks for adding that context—this description is super clear now! -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
