Duplicate column name 'id' when select count(*) from ...

[lunny]

go get github.com/go-xorm/tidb

and run go test, then it panic. Then we open the db use interpreter, and the result is below.

➜  tidb git:(master) ✗ interpreter -dbpath=./tidb -store=goleveldb
Welcome to the TiDB.
Version:
Git Commit Hash: None
UTC Build Time:  None

tidb> show tables;
+----------------+
| Tables_in_tidb |
+----------------+
| numeric        |
| picture        |
| userdetail     |
| userinfo       |
+----------------+
tidb> select * from userinfo;
2015/09/07 14:58:22 main.go:202: [error] github.com/pingcap/tidb/rset/rsets/join.go:210: Duplicate column name 'id'
github.com/pingcap/tidb/rset/rsets/join.go:104:
github.com/pingcap/tidb/rset/rsets/join.go:225:
github.com/pingcap/tidb/tidb.go:160:
github.com/pingcap/tidb/session.go:133:
github.com/pingcap/tidb/driver.go:309:
github.com/pingcap/tidb/interpreter/main.go:60:
tidb> desc userinfo;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | BIGINT (20)   | NO   | PRI | NULL    | auto_increment |
| username   | VARCHAR (255) | YES  | UNI | NULL    |                |
| departname | VARCHAR (255) | YES  |     | NULL    |                |
| created    | DATETIME      | YES  |     | NULL    |                |
| detail_id  | INT (11)      | YES  |     | NULL    |                |
| height     | DOUBLE        | YES  |     | NULL    |                |
| avatar     | BLOB          | YES  |     | NULL    |                |
| is_man     | TINYINT (1)   | YES  |     | NULL    |                |
| id         | BIGINT (20)   | NO   | PRI | NULL    | auto_increment |
| username   | VARCHAR (255) | YES  |     | NULL    |                |
| departname | VARCHAR (255) | YES  |     | NULL    |                |
| created    | DATETIME      | YES  |     | NULL    |                |
| detail_id  | INT (11)      | YES  |     | NULL    |                |
| height     | DOUBLE        | YES  |     | NULL    |                |
| avatar     | BLOB          | YES  |     | NULL    |                |
| is_man     | TINYINT (1)   | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

[c4pt0r]

Thanks for your report! We'll try to reproduce and fix it.

[unknwon]

Same here...

[shenli]

Thanks for your report @lunny @unknwon
I checked TiDB code and fixed a few bugs today, but still can not pass go-xorm test.
There are two things I want to discuss with you.

The first one is a potential bug in https://github.com/go-xorm/tidb/blob/master/tidb_driver.go#L42.
tidb_driver use params[1] as DbName, but it maybe "./tidb". I see some sql statement in the log that select column name in some table where the dbname="./tidb". For example:
"[xorm] [debug] 2015/09/07 21:23:48.572562 [time] SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? and TABLE_NAME=? - args [./tidb userinfo] - query took: 367238ns".
I modified a few code and fixed the "Duplicate column name" error.
40 fs := strings.Split(params[1], "/")
41 dbName := fs[len(fs)-1]
42 uri := &core.Uri{
43 DbType: DBType,
44 DbName: dbName,
45 }
46
I try to push my code to a new branch in go-xorm but get permission denied error.

The second one is about auto_increment column.
In TiDB we only guarantee that auto_increment column value is increasing. But I find a test case in https://github.com/go-xorm/tests/blob/master/base.go#L94 which assume the value of auto_increment id.
I checked the insert statements. Those statements do not set id column explicitly. I also checked table content in TiDB, the ids are auto increment but no one in [7, 8, 9]. So the case failed.
tidb> select * from userinfo;
+------+-------------+------------+---------------------+-----------+--------+--------+--------+
| id | username | departname | created | detail_id | height | avatar | is_man |
+------+-------------+------------+---------------------+-----------+--------+--------+--------+
| 1001 | xiaolunwen2 | dev | 2015-09-07 20:51:33 | 1 | 1.78 | | 1 |
| 1002 | xlw | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1003 | xlw2 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1004 | xlw11 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1005 | xlw22 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1006 | 1xlw | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1007 | 1xlw2 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1008 | 1xlw11 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1009 | 1xlw22 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
| 1010 | xlw3 | dev | 2015-09-07 20:51:33 | 0 | 0 | | 0 |
+------+-------------+------------+---------------------+-----------+--------+--------+--------+

[lunny]

Why the auto increment id is not start from 1 but 1001? I think this is the problem.

[shenli]

For performance consideration, we choose an auto increment id assigning algorithm as this:
Get the start_number from global storage. And update global stored start number to a new number which is start_number + 1000. So we get a batch of auto ids and do not need to access global storage before run out of these ids. Then we can assign and increase global-uniq id for auto_increment column.

All the auto increment id related codes for a table share the same start_number. In this case, I think 1~1000 is assigned before inserting column "id". So column "id" inserting gets the start_number 1001.
I will check logs and provide more information.

[unknwon]

Fail to initialize ORM engine: migrate: sync: Try to add a column with the same name of an already exists column.

It's now keep giving me this error.... is this the same behavior of MySQL?

[shenli]

@unknwon May I see your code?
I find lunny update tidb_driver.go to solve adding an existing column problem. https://github.com/go-xorm/tidb/blob/master/tidb_driver.go

[unknwon]

Thanks @shenli ! I've updated driver and it works now, but there is always a warning message:

[warning] New txn:491 in session:1

And it prints a sound, what is the purpose of this?

[unknwon]

SELECT id, user_id, repo_id, mode FROM access WHERE repo_id=? AND mode>=?
, error: line 1 column 77 near "mode"

Is > a invalid operator?

[lunny]

I have updated go-xorm/tests and the autoincrement problem is gone. I will close this, since the problem is resolved. @unknwon, you can make a new issue to report the problem.