Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL syntax error registers as unhashable filter #3138

Closed
alecstein opened this issue Apr 1, 2022 · 5 comments
Closed

SQL syntax error registers as unhashable filter #3138

alecstein opened this issue Apr 1, 2022 · 5 comments
Assignees
@max-hoffman

Comments

@alecstein
Copy link
Contributor

Working off of the housing prices database: https://www.dolthub.com/repositories/dolthub/us-housing-prices

The following (incorrect) query (missing an OR) is

SELECT state,
         city,
         property_type,
         physical_address,
         property_id,
         sale_date,
         year(sale_date),
         sale_price,
         COUNT(state) OVER(PARTITION BY property_id,
         physical_address) AS times_sold,
         MAX(sale_price) OVER(PARTITION BY property_id,
         physical_address) AS sale_price_max,
         MIN(sale_price) OVER(PARTITION BY property_id,
         physical_address) AS sale_price_min
FROM sales
WHERE ( lower(property_type) LIKE "%home%", lower(property_type) LIKE "%dwelling%"
        OR lower(property_type) LIKE "%condo%"
        OR lower(property_type) LIKE "%family%"
        OR lower(property_type) LIKE "%resid%"
        OR lower(property_type) LIKE "%apartment%"
        OR lower(property_type) LIKE "%homesite%"
        OR lower(property_type) LIKE "%duplex%"
        OR lower(property_type) LIKE "%triplex%"
        OR lower(property_type) LIKE "%fourplex%"
        OR lower(property_type) LIKE "%apt%" )
        AND ( year(sale_date) > 2018
        AND sale_price > 10000
        AND physical_address NOT LIKE "^0 %"
        AND physical_address NOT LIKE "^- $"
        AND LENGTH(physical_address) > 5 ) }

results in the error:

Starting server with Config HP="localhost:3306"|U="root"|P=""|T="28800000"|R="false"|L="debug"
2022-04-01T13:18:44-04:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}
2022-04-01T13:18:44-04:00 DEBUG [conn 1] Starting query {connectTime=2022-04-01T13:18:44-04:00, query= EXPLAIN SELECT state, city, property_type, physical_address, property_id, sale_date, year(sale_date), sale_price, COUNT(state) OVER(PARTITION BY property_id, physical_address) AS times_sold, MAX(sale_price) OVER(PARTITION BY property_id, physical_address) AS sale_price_max, MIN(sale_price) OVER(PARTITION BY property_id, physical_address) AS sale_price_min FROM sales WHERE ( lower(property_type) LIKE "%home%", lower(property_type) LIKE "%dwelling%" OR lower(property_type) LIKE "%condo%" OR lower(property_type) LIKE "%family%" OR lower(property_type) LIKE "%resid%" OR lower(property_type) LIKE "%apartment%" OR lower(property_type) LIKE "%homesite%" OR lower(property_type) LIKE "%duplex%" OR lower(property_type) LIKE "%triplex%" OR lower(property_type) LIKE "%fourplex%" OR lower(property_type) LIKE "%apt%" ) AND ( year(sale_date) > 2018 AND sale_price > 10000 AND physical_address NOT LIKE "^0 %" AND physical_address NOT LIKE "^- $" AND LENGTH(physical_address) > 5 ) }
2022-04-01T13:18:44-04:00 INFO [conn %!d(<nil>)] kill query: pid 1 {}
2022-04-01T13:18:44-04:00 INFO [conn 1] ConnectionClosed {}
2022-04-01T13:18:44-04:00 ERROR [conn %!d(<nil>)] mysql_server caught panic:
runtime error: hash of unhashable type expression.Tuple
/usr/local/go/src/runtime/alg.go:108 (0x102ad32e7)
	interhash: panic(errorString("hash of unhashable type " + t.string()))
/usr/local/go/src/runtime/map.go:471 (0x102add15b)
	mapaccess2: hash := t.hasher(key, uintptr(h.hash0))
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/indexes.go:667 (0x1039ac45f)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/indexes.go:293 (0x1039a8a73)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/indexes.go:70 (0x1039a8467)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/plan/walk.go:49 (0x10384f9db)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/plan/walk.go:35 (0x10384f8cf)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/plan/walk.go:40 (0x10384f957)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/plan/walk.go:40 (0x10384f957)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/plan/walk.go:60 (0x1039a822b)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/indexes.go:51 (0x1039a80c0)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/pushdown.go:41 (0x1039bd3b3)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/pushdown.go:37 (0x1039bd233)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/batch.go:94 (0x103998357)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/batch.go:54 (0x103997ed7)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/analyzer.go:378 (0x10399165b)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/analyzer.go:350 (0x10399ced3)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/describe.go:29 (0x10399cea8)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/batch.go:94 (0x103998357)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/batch.go:54 (0x103997ed7)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/analyzer.go:378 (0x10399165b)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/sql/analyzer/analyzer.go:350 (0x1039f3feb)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/engine.go:181 (0x1039f3fac)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/server/handler.go:332 (0x103c9e8a7)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/server/handler.go:597 (0x103ca0ba7)
/go/pkg/mod/github.com/dolthub/go-mysql-server@v0.11.1-0.20220323210729-94c1349de0e4/server/handler.go:158 (0x103c9d19f)
/go/pkg/mod/github.com/dolthub/vitess@v0.0.0-20220323175412-7e0381fb7c3f/go/mysql/conn.go:1288 (0x102f9268f)
/go/pkg/mod/github.com/dolthub/vitess@v0.0.0-20220323175412-7e0381fb7c3f/go/mysql/conn.go:930 (0x102f8f833)
/go/pkg/mod/github.com/dolthub/vitess@v0.0.0-20220323175412-7e0381fb7c3f/go/mysql/server.go:479 (0x102fa6167)
/usr/local/go/src/runtime/asm_arm64.s:1259 (0x102b36db3)
	goexit: MOVD	R1, y+8(FP)
 {}
@timsehn
Copy link
Contributor

timsehn commented Apr 1, 2022

So, to be clear:

lower(property_type) LIKE "%home%", lower(property_type) LIKE "%dwelling%"
                                  ^

that comma should be an or and this panics?

@timsehn
Copy link
Contributor

timsehn commented Apr 1, 2022

This also should be a Dolt bug.

@timsehn timsehn transferred this issue from dolthub/go-mysql-server Apr 1, 2022
@max-hoffman
Copy link
Contributor

I asked him to put this in GMS, its an analyzer panic.

This also should be a Dolt bug.

@timsehn
Copy link
Contributor

timsehn commented Apr 1, 2022

My bad. You can move it back.

max-hoffman added a commit to dolthub/go-mysql-server that referenced this issue Apr 6, 2022
@max-hoffman
Semantic validation start
5937a03 
[Fixes unhashable filter
bug](dolthub/dolt#3138).

Adds improvement on semantic operand validation. Targetted checks easier
to implement.
@max-hoffman max-hoffman mentioned this issue Apr 6, 2022
Merged
max-hoffman added a commit to dolthub/go-mysql-server that referenced this issue Apr 8, 2022
@max-hoffman
Fixes unhashable filter bug, adds semantic validation starter (#941)
e9245f8 
* Semantic validation start

[Fixes unhashable filter
bug](dolthub/dolt#3138).

Adds improvement on semantic operand validation. Targetted checks easier
to implement.

* consider unresolvedColumn
@max-hoffman
Copy link
Contributor

added fix and test for this in dolthub/go-mysql-server#941

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@max-hoffman max-hoffman

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@timsehn @alecstein @max-hoffman