VALUES statement is not supported #21486

kennytm · 2020-12-03T17:32:46Z

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

values row(1);

2. What did you expect to see? (Required)

mysql> values row(1);
+----------+
| column_0 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

The query throws the 1051 Unknown Table error.

mysql> values row(1);
ERROR 1051 (42S02): Unknown table ''

4. What is your TiDB version? (Required)

e33b377

Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:52:40
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

The text was updated successfully, but these errors were encountered:

kennytm · 2020-12-03T17:34:24Z

/label sig/planner

lzmhhh123 · 2020-12-08T03:18:36Z

/assign @xuyifangreeneyes

lzmhhh123 · 2020-12-08T05:05:43Z

/assign @xuyifangreeneyes

tisonkun · 2021-07-15T10:15:00Z

on master d05b6f4 the stack changes to

[planner:1051]Unknown table ''
github.com/pingcap/errors.AddStack
	/Users/tison/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:174
github.com/pingcap/errors.(*Error).GenWithStackByArgs
	/Users/tison/go/pkg/mod/github.com/pingcap/[email protected]/normalize.go:159
github.com/pingcap/tidb/planner/core.(*PlanBuilder).unfoldWildStar
	/Users/tison/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:3115
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
	/Users/tison/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:3443
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
	/Users/tison/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:657
github.com/pingcap/tidb/planner.optimize
	/Users/tison/go/src/github.com/pingcap/tidb/planner/optimize.go:234
github.com/pingcap/tidb/planner.Optimize
	/Users/tison/go/src/github.com/pingcap/tidb/planner/optimize.go:124
github.com/pingcap/tidb/executor.(*Compiler).Compile
	/Users/tison/go/src/github.com/pingcap/tidb/executor/compiler.go:63
github.com/pingcap/tidb/session.(*session).ExecuteStmt
	/Users/tison/go/src/github.com/pingcap/tidb/session/session.go:1601
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
	/Users/tison/go/src/github.com/pingcap/tidb/server/driver_tidb.go:218
github.com/pingcap/tidb/server.(*clientConn).handleStmt
	/Users/tison/go/src/github.com/pingcap/tidb/server/conn.go:1809
github.com/pingcap/tidb/server.(*clientConn).handleQuery
	/Users/tison/go/src/github.com/pingcap/tidb/server/conn.go:1681
github.com/pingcap/tidb/server.(*clientConn).dispatch
	/Users/tison/go/src/github.com/pingcap/tidb/server/conn.go:1215
github.com/pingcap/tidb/server.(*clientConn).Run
	/Users/tison/go/src/github.com/pingcap/tidb/server/conn.go:978
github.com/pingcap/tidb/server.(*Server).onConn
	/Users/tison/go/src/github.com/pingcap/tidb/server/server.go:482
runtime.goexit
	/usr/local/Cellar/go/1.16/libexec/src/runtime/asm_amd64.s:1371

SunRunAway · 2023-02-15T07:44:56Z

I try to use values to find the max value of a row, but it seems not work on MySQL 8.
@dveeden do you know if it is a bug?

mysql> select * from mytable;
+----+----+----+----+
| ID | a  | b  | c  |
+----+----+----+----+
|  1 | a1 | b1 | c1 |
|  2 | a2 | b2 | c2 |
+----+----+----+----+
2 rows in set (0.00 sec)

mysql> select ID, (select max(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+------+
| ID | max  |
+----+------+
|  1 | c1   |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

dveeden · 2023-02-15T09:17:47Z

This works:

sql> SELECT *, MAX(column_0) OVER () FROM (VALUES ROW(1,2,3),ROW(10,20,30)) a;
+----------+----------+----------+-----------------------+
| column_0 | column_1 | column_2 | MAX(column_0) OVER () |
+----------+----------+----------+-----------------------+
|        1 |        2 |        3 |                    10 |
|       10 |       20 |       30 |                    10 |
+----------+----------+----------+-----------------------+
2 rows in set (0.0005 sec)

sql> SELECT MAX(column_0) FROM (VALUES ROW(1,2,3),ROW(10,20,30)) a;
+---------------+
| MAX(column_0) |
+---------------+
|            10 |
+---------------+
1 row in set (0.0007 sec)

dveeden · 2023-02-15T09:33:15Z

sql> select ID, (select max(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+------+
| ID | max  |
+----+------+
|  1 | c1   |
|  2 | NULL |
+----+------+
2 rows in set (0.0016 sec)

sql> select ID, (select GROUP_CONCAT(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+----------+
| ID | max      |
+----+----------+
|  1 | a1,b1,c1 |
|  2 | NULL     |
+----+----------+
2 rows in set (0.0015 sec)

So the MAX() of ['a1','b1','c1'] is indeed c1.

dveeden · 2023-02-15T09:35:10Z

Note also this:

sql> select ID, (select GROUP_CONCAT(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable WHERE ID=2;
+----+----------+
| ID | max      |
+----+----------+
|  2 | a2,b2,c2 |
+----+----------+
1 row in set (0.0005 sec)

This is a bit of a weird dependent subquery, not sure it makes much sense to me.

SunRunAway · 2023-02-15T10:52:36Z

sql> select ID, (select max(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+------+
| ID | max  |
+----+------+
|  1 | c1   |
|  2 | NULL |
+----+------+
2 rows in set (0.0016 sec)

sql> select ID, (select GROUP_CONCAT(col1)    from (values row(a), row(b), row(c)) as x(col1)) as max from mytable;
+----+----------+
| ID | max      |
+----+----------+
|  1 | a1,b1,c1 |
|  2 | NULL     |
+----+----------+
2 rows in set (0.0015 sec)

So the MAX() of ['a1','b1','c1'] is indeed c1.

I do not get why the second row of 'max' is NULL.
Postgres seems do it right: https://dbfiddle.uk/nbpDA-Tc

SunRunAway · 2023-02-15T11:21:56Z

I sent a bug to MySQL team and it is verified: https://bugs.mysql.com/bug.php?id=110076

dveeden · 2023-07-21T10:35:17Z

Another small difference with MySQL:

MySQL:

sql> CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.0535 sec)

sql> INSERT INTO t1 VALUES ROW();
Query OK, 1 row affected (0.0199 sec)

sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.0006 sec)

TiDB:

sql> CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.1716 sec)

sql> INSERT INTO t1 VALUES ROW();
ERROR: 1051 (42S02): Unknown table ''

sql> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 5.7.25-TiDB-v7.2.0 |
+--------------------+
1 row in set (0.0012 sec)

kennytm · 2024-06-05T04:00:19Z

TABLE t seems to work fine on TiDB v8.1 (but perhaps needs more tests to verify), and VALUES no longer crash with NPE just returns an error.

Updated the description to reflect the current development.

kennytm added the type/bug The issue is confirmed as a bug. label Dec 3, 2020

ti-srebot added the sig/planner SIG: Planner label Dec 3, 2020

kennytm changed the title ~~VALUES statement panics~~ TABLE and VALUES statement panics Dec 3, 2020

This was referenced Dec 3, 2020

Support VALUES and TABLE statements syntax #19934

Closed

parser.y: populate the Fields field for TABLE and VALUES statements pingcap/parser#1118

Merged

*: ensure TABLE statement works #21487

Closed

ichn-hu mentioned this issue Dec 3, 2020

Welcome to contribute #20804

Closed

lzmhhh123 added challenge-program help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. labels Dec 4, 2020

jebter added the severity/major label Dec 4, 2020

ti-srebot assigned xuyifangreeneyes Dec 8, 2020

xuyifangreeneyes mentioned this issue Dec 8, 2020

planner: avoid TABLE and VALUES statement panic by returning unsupported statement error #21569

Closed

XuHuaiyu added type/feature-request Categorizes issue or PR as related to a new feature. and removed type/bug The issue is confirmed as a bug. severity/major labels Dec 9, 2020

XuHuaiyu assigned kennytm and unassigned xuyifangreeneyes Dec 10, 2020

tisonkun added type/compatibility type/new-feature and removed challenge-program type/compatibility type/feature-request Categorizes issue or PR as related to a new feature. labels Jul 15, 2021

dveeden mentioned this issue Jan 2, 2023

MySQL 8.0 Compatibility #7968

Open

70 tasks

dveeden added type/compatibility compatibility-mysql8 This is a compatibility issue with MySQL 8.0(but NOT 5.7) labels Jul 21, 2023

kennytm changed the title ~~TABLE and VALUES statement panics~~ VALUES statement is not supported Jun 5, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

VALUES statement is not supported #21486

VALUES statement is not supported #21486

kennytm commented Dec 3, 2020 •

edited

Loading

kennytm commented Dec 3, 2020

lzmhhh123 commented Dec 8, 2020

lzmhhh123 commented Dec 8, 2020

tisonkun commented Jul 15, 2021

SunRunAway commented Feb 15, 2023

dveeden commented Feb 15, 2023 •

edited

Loading

dveeden commented Feb 15, 2023

dveeden commented Feb 15, 2023

SunRunAway commented Feb 15, 2023

SunRunAway commented Feb 15, 2023

dveeden commented Jul 21, 2023

kennytm commented Jun 5, 2024 •

edited

Loading

VALUES statement is not supported #21486

VALUES statement is not supported #21486

Comments

kennytm commented Dec 3, 2020 • edited Loading

Description

Bug Report

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

kennytm commented Dec 3, 2020

lzmhhh123 commented Dec 8, 2020

lzmhhh123 commented Dec 8, 2020

tisonkun commented Jul 15, 2021

SunRunAway commented Feb 15, 2023

dveeden commented Feb 15, 2023 • edited Loading

dveeden commented Feb 15, 2023

dveeden commented Feb 15, 2023

SunRunAway commented Feb 15, 2023

SunRunAway commented Feb 15, 2023

dveeden commented Jul 21, 2023

kennytm commented Jun 5, 2024 • edited Loading

kennytm commented Dec 3, 2020 •

edited

Loading

dveeden commented Feb 15, 2023 •

edited

Loading

kennytm commented Jun 5, 2024 •

edited

Loading