требовалось добавлени дополнительного поля + уникальность.
натолкнулся на странное поведение CREATE UNIQUE INDEX если в таблице уже есть данные.
- Уникальный индекс почему-то построился если есть дубликаты.
- Не корректно работает distinct
sqlite3 --version
3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
sqlite3.exe FlylinkDC.sqlite < test-uniq-3.sql
CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth number NOT NULL);
INSERT INTO fly_hash_block VALUES(1,1);
INSERT INTO fly_hash_block VALUES(2,2);
INSERT INTO fly_hash_block VALUES(3,2);
CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);
select * from fly_hash_block;
1|1
2|2
3|2
select distinct tth from fly_hash_block;
1
2
2
select tth,count(*) from fly_hash_block group by tth;
1|1
2|2
в sqlite пришел после Oracle - возможно что-то не учел...
по-моему Oracle в данном случае ведет себя адекватнее:
SQL>
SQL> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth number NOT NULL);
Table created
SQL> INSERT INTO fly_hash_block VALUES(1,1);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(2,2);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(3,2);
1 row inserted
SQL> CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);
CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth)
ORA-01452: CREATE UNIQUE INDEX невозможно; найдены дублирующиеся ключи
SQL> select * from fly_hash_block;
TTH_ID TTH
--------------------------------------- ----------
1 1
2 2
3 2
SQL> select distinct tth from fly_hash_block;
TTH
----------
1
2
SQL> select tth,count(*) from fly_hash_block group by tth;
TTH COUNT(*)
---------- ----------
1 1
2 2
Если индекс построить на пустой таблице, то все хорошо - он запрещает попытку нарушения уникальности при вставке:
CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth number NOT NULL);
CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);
INSERT INTO fly_hash_block VALUES(1,1);
INSERT INTO fly_hash_block VALUES(2,2);
INSERT INTO fly_hash_block VALUES(3,2);
Error: near line 6: UNIQUE constraint failed: fly_hash_block.tth
select * from fly_hash_block;
1|1
2|2
select distinct tth from fly_hash_block;
1
2
select tth,count(*) from fly_hash_block group by tth;
1|1
2|1
Кто знает причину такого поведения?
может какой прагмы не хватает...
хотя с distinct 100% явный баг в движке - отписал разработчикам sqlite пример.
UPD: разработчики sqlite приняли баг у себя в треккере:
http://www.sqlite.org/src/info/9a6daf340df99
Сообщения
