Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: FB2.5 - VARCHAR, содержащие ASCII_CHAR( 0 )

Привет всем.

Суть вопроса в том, что при наличии в поле типа VARCHAR символа с нулевым кодом функция CHAR_LENGTH() возвращает полную длину с у четом символов после #0, а собственно значение поля возвращается обрезанным до первого нулевого символа.

Ниже приведен несложный сценарий для ISQL:

CREATE TABLE ITEMS (
ID INTEGER NOT NULL,
NAME VARCHAR(100) NOT NULL
);

ALTER TABLE ITEMS ADD CONSTRAINT PK_ITEMS PRIMARY KEY (ID);
ALTER TABLE ITEMS ADD CONSTRAINT UNQ_ITEMS UNIQUE (NAME);

INSERT INTO ITEMS( ID, NAME ) VALUES ( 1, 'Name #1' );
COMMIT;


SELECT '<' || NAME || '>', CHAR_LENGTH( NAME )
FROM ITEMS;
/* Здесь все правильно, символа #0 нет */
NAME CHAR_LENGTH
=============================================
<Name #1> 7


/* добавляем нулевой символ */
UPDATE ITEMS
SET NAME = NAME || ASCII_CHAR( 0 ) || '+++';

SELECT '<' || NAME || '>', CHAR_LENGTH( NAME )
FROM ITEMS;
/* Ошибка: CHAR_LENGTH() возвращает большее значение, нежели чем собственно возвращенное значение поля NAME
возвращенное значение поля NAME обрезано до первого нулевого символа */
NAME CHAR_LENGTH
=============================================
<Name #1 11


/* повторно добавляем нулевой символ */
UPDATE ITEMS
SET NAME = NAME || ASCII_CHAR( 0 ) || '+++';
SELECT '<' || NAME || '>', CHAR_LENGTH( NAME )
FROM ITEMS;
/* значение, возвращенное функцией CHAR_LENGTH(), увеличилось на 4
возвращенное значение поля NAME не изменилось (так же обрезано до первого нулевого символа ) */
NAME CHAR_LENGTH
=============================================
<Name #1 15


С уважением, Polesov.
Ответ: Привет всем.

Ниже пояснения по старттопику.

Вводная:

Имеются внешние текстовые данные, которые по связке с неким критерием заносятся в базу через хранимую процедуру.
Соответственно, в связке с этим неким критерием возможны следующие ситуации:
- внешние данные отсутствуют: при этом в БД пишется NULL;
- внешние данные содержат пустую стоку: при этом в БД пишется пустая строка;
- внешние данные введены: при этом в БД пишется строка, обработанная некой UDF.

Объявление UDF:

DECLARE EXTERNAL FUNCTION SOME_UDF
    CSTRING(8192)
RETURNS CSTRING(8192) FREE_IT
ENTRY_POINT 'EntryPoint' MODULE_NAME 'ModuleName.dll';


Естественно, при наличии во входной строке нулевого символа происходила потеря данных.

Решение проблемы – реализация передачи входного параметра типом VARCHAR BY DESCRIPTOR:

DECLARE EXTERNAL FUNCTION SOME_UDF
    VARCHAR(8192) BY DESCRIPTOR,
    VARCHAR(8192) BY DESCRIPTOR
RETURNS PARAMETER 2
ENTRY_POINT 'EntryPoint' MODULE_NAME 'ModuleName.dll';

Структура описания параметра:

  TDSC = packed record
    dsc_dtype : byte;
    dsc_scale : shortint;
    dsc_length : word;
    dsc_sub_type : smallint;
    dsc_flags : word;
    dsc_address : pointer;
  end;


Для типа VARCHAR dsc_address в первых 2-х байтах содержит такое же значение, как и результат функции CHAR_LENGTH(), т.е. реальную длину без учета нулевых символов. При этом надо, что бы входная строка передавалась именно типом VARCHAR (dsc_dtype = 3), что обеспечивается преобразованием
CAST( STR_VALUE AS VARCHAR(8192) )


Кстати, при таком решении логику на равенство параметра пустому значению или на IS NULL можно перенести в саму UDF.

Данные для просмотра строк с нулевым символом можно вытаскивать через
REPLACE( STR_VALUE, ASCII_CHAR(0), ASCII_CHAR(16) )


С уважением, Polesov.

Вам также можеть быть интересно:

Результат SELECT-а в одну строку
parallel
Запрос на выборку
Импорт данных из csv
postgresql для частых инсертов
Вопрос: Varchar vs Text

Нарвался недавно на такую неприятную штуку. Есть такой элементарный запрос:

SELECT t0.Purchase_seriesNumber_UserInvoice FROM Purchase_userInvoice t0 WHERE (CAST('ПИ1111111' AS text)=t0.Purchase_seriesNumber_UserInvoice)


"Seq Scan on purchase_userinvoice t0  (cost=0.00..27994.32 rows=2373 width=22) (actual time=1243.511..1243.511 rows=0 loops=1)"
"  Filter: ('ПИ1111111'::text = (purchase_seriesnumber_userinvoice)::text)"
"  Rows Removed by Filter: 474579"
"Planning time: 0.096 ms"
"Execution time: 1243.529 ms"


Очевидно не хватало индекса. Добавил, план не изменился. Чуть-чуть поменял запрос:

SELECT t0.Purchase_seriesNumber_UserInvoice FROM Purchase_userInvoice t0 WHERE (CAST('ПИ1111111' AS varchar)=t0.Purchase_seriesNumber_UserInvoice)


План:
"Index Only Scan using purchase_seriesnumber_userinvoice_key0_idx_purchase_userinvoice on purchase_userinvoice t0  (cost=0.42..0.68 rows=1 width=22) (actual time=1.145..1.145 rows=0 loops=1)"
"  Index Cond: (purchase_seriesnumber_userinvoice = 'ПИ1111111'::bpchar)"
"  Heap Fetches: 0"
"Planning time: 0.084 ms"
"Execution time: 1.158 ms"


Тип seriesNumber - char(20). План правильный при cast'е к чему угодно, кроме text.

"PostgreSQL 9.4.5, compiled by Visual C++ build 1800, 64-bit"

Собственно 2 вопроса:

1) Что такого волшебного в text?
2) И если вместо text использовать varchar какие могут быть подводные камни?
Ответ: Nitro_Junkie,

обсуждение. И нужен , с которым будем работать, часть`Operator Type Resolution` (первая сверху).
Я смоделировал ситуацию так:
CREATE TABLE t(t_id int4, sn_c char(20));
INSERT INTO t
  SELECT id, chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*99999)::int4+1)
    FROM generate_series(1, 10000) id;
CREATE INDEX i_t_sn_c ON t(sn_c);
VACUUM ANALYZE t;

Рассматриваем такой запрос:
EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 'AB1234';

Выражение имеет форму <left_arg> <op> <right_arg>, в нашем случае <op> = `=`. В документации (пункт первый) сказано, что если оператор не был задан явно (), то рассматриваются все видимые операторы с таким именем (возможно, вы создали оператор равенства в своей схеме? — он таже будетрассматриваться).

Далее начинается проверка типов.
Ищется оператор, который полностью соответствует типу данных аргументов. Если такой есть — используется.
Если один из аргументов имеет тип `unknown`, то считается что он равен типу второго аргумента — ищется оператор и используется.

Интересное начинается, если типы аргументов отличаются и нет соответствующего оператора сравнения (это как раз наш случай). Postgres должен привести оба аргумента к одному типу. Надо выбрать один из типов, `text` или `bpchar` (blank-padded char).
Посмотрите на результат запроса и скажите, к какому типу Postgres будет приводить:
SELECT typname, typcategory, typispreferred FROM pg_type WHERE typname IN ('text', 'varchar', 'bpchar');

Изменение типа колонки (по сути — вызов функции) делает использование индекса невозможным.

Это отвечает на ваш вопрос #1 — тип `text` является предпочтительным и как только он появляется, то все аргументы (при необходимости) будут приведены к нему.

Касаемо типов категории `S` — они (почти все) хранятся одинаково, и обрабатываются одним движком:
SELECT typname, typlen, typbyval, typalign, typstorage FROM pg_type WHERE typname IN ('text', 'bpchar', 'varchar', 'char');

Но у них должны быть различия на уровне SQL:
- text — универсальный
- varchar — тот же `text`, только может иметь ограничение длины
- bpchar — тот же `varchar`, только должен дополнятся пробелами до указанной длины.

Тип `char`стоит отдельно, его надо рассматривать как просто 8-битный символ, который храниться "как есть" (typlen=1, typstorage=p). Полагаю, это историческая часть системы и правильно работать с UTF8 в этом типе невозможно.
Не стоит путать тип данных Postgres'а `char` с типом данных SQL'а `char`! — когда вы создаёте колонку типа `char` в SQL'е, то Postgres использует `bpchar`.

TL;DR — В результате получается:
- используя явное преобразование к `text`, которое не соответствует типу колонки, вы склоняете Postgres к тем последствиям, что вы и описали (ССЗБ);
- лучше оставлять константы без явного преобразования (в тех местах, где это допустимо) — база сама привёдет `unknown` к типу второго аргумента оператора
- т.к. SQL-тип `char` использует Postgres-тип `bpchar`, обрабатываемый одинаково с `text`-ом, то предпочтительней использовать `text`. Мало ли какие ещё сюопризы откопаете в связи с тем, что результат "должен" дополнятся пробелами (в кавычках, т.к. везде ли он дополняется?)
- если нужно ограничить длину `text` колонки — пользуйтесь CHECK constraint или сделайте свой DOMAIN.

Как-то так.
Вопрос: Проблема с varchar(max)

Приветствую.
Столкнулся с такой проблемой.
Есть 2 SQL-сервера 2014, абсолютно одинаковые, тот же collation и т.д.
С каждого из этих SQL-серверов выполняется запрос к Orcale linked-серверу:
declare @p_numer varchar(max) = N'871525'
exec ('select * from table(PKG_DC_LOAD.F_GET_ICREP_TAB(?))', @p_numer) at DWH2
При этом на одном из SQL-серверов запрос возвращает одну строку, а на другом - пусто.
Вот, на том, где возвращается пустой набор данных, попробовал varchar(8000) вместо varchar(max) и сработало.
Т.е. возникает подозрение, что чем-то, всё-таки, эти SQL-сервера отличаются друг от друга.
Хотелось бы, чтобы varchar(max) работал и на другом сервере, т.к. иначе придется переписывать довольно много исходников.
Посоветуйте, где что нудно настроить? Заранее благодарен за конструктивные ответы.
Ответ: Договорился с админами, включили трассировку.
Получил от них ответ:
"Ноль строк возвращается, если передать на вход этой процедуры NULL.
select * from table(PKG_DC_LOAD.F_GET_ICREP_TAB(NULL))
В трассировках в одной из сессий вижу передаваемый бинд, а в другом – нет (видимо пришел NULL)"
Вопрос: select from(<UNION ALL> из varchar-литералов): как опр. их max длина ?

... по первой строке или движок ждёт последнюю и уже тогда начинает выдавать строки "на гора", зная точно макс. длину ?
Ответ: на этапе парсинга ищется максимальная длина из внутренностей юниона, к ней кастятся все строки
Вопрос: date VS varchar(8)

Прошу совета у общественности! : "как лучше и кошернее хранить ДАТУ" ? (без времени)

Варианты:
1 - varchar(8)
2 - date

Лично я склоняюсь к "нормальному" решению - раз это дата, то и жить ей в поле с типом DATE.

На данный момент имеется база, в которой почему-то половина полей в варчаре, а половина в DATE.
Из-за этого сброда куча проблем: там преобразовать в один тип, а сям преобразовать в другой (а если ошибся, то индексы не "отработают", или автоматическое приведение случайно где-то превратит дату 20170101 в строку '2017-01-')

Очень это похоже на какой-то "ХолиВар", если так, прошу подсказать ссылки где обсуждалось.
Ответ:
iap
Проблемы возникнут, когда надо будет строку конвертировать в дату, а это невозможно.
Или строковый формат даты неизвестен: девятое ноября или одиннадцатое сентября?
А это не относится к "когда потребуется манипулировать этими данными как с датами"?
Вопрос: Динамический запрос VARCHAR(MAX)

есть переменная @sql varchar(max)= 'update T1 set col1 = NullIf(value1,null), col2=nullIf(value2,null) where id = MyId'

Длинна переменной @sql = 12000 символов.

Проблема: при выполнении exec(@sql) переменная обрезается до 8000 символов и команда не срабатывает.

В MSDN пишут, что Exec работает на переменной (max), то есть должно 2Гб принимать. Но этого не происходит.

Как можно решить эту проблему?


---------
Победи себя - будешь непобедим.
(А.В. Суворов)
Ответ: нет никакого апдэйтного бага,
вот PRINT не все выводит(или студия обрезает, но мне без разницы, я на результат смотрю,
и если бы обрезалось, откуда бы верный последний символ и верная длина строки?),
но все прекрасно апдэйтится:
create table dbo.t_max(id int, col varchar(max));
insert into dbo.t_max(id, col) values (1, 'a');
go

declare @sql_max varchar(max)= 'update dbo.t_max set col = ''' +
        cast(replicate('a',6000) as varchar(max)) + replicate('b',6000) + replicate('c',6000) +
        ''' where id = 1';
print  @sql_max;

exec(@sql_max);  

select len(col) as len_, RIGHT(col,1) as last_c
from  dbo.t_max; 
---
len_	last_c
18000	c


Microsoft SQL Server 2012 - 11.0.5569.0 (Intel X86) Jan 9 2015 11:41:41 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
Вопрос: Сортировка цифр в поле VARCHAR

Запрос:
select `number` from `orders` where `seller` like "%имя%" order by `number` desc


Результат запрос в приложенном файле.

Мне нужно в этом запросе получить цифру 35, т.е. получить реально самое большое значение отсортиваронное по принципу возрастания. Поле number имеет тип varchar, т.к. некоторые номера документов содержат буквы... но сейчас мне важно реализовать циферную последовательность...

Как правильно сформулировать свой запрос?...

К сообщению приложен файл. Размер - 100Kb
Ответ:
Alexei772
А как отсортировать в случае если у меня есть 1,2,3,4,5 и более цифр в значении?
Если они идут в самом начале - предложенный метод вполне годится.
Если в середине строки - сначала нужно понять, как их выделить.
Вопрос: Преобразование varchar в time и time в int

Всем привет! Ребят помогите пожалуйста преобразовать типы данных varchar в time и time в int. Или сразу varchar в int
Например формат varchar'a :
00:00:06
00:01:47
00:10:45

Всем заранее спасибо!
Ответ:
iap
a_voronin
SELECT CAST(CONVERT(CHAR(8), GETDATE(),112) AS INT)
Попробуйте так поступить со временем.
Вангую: получите круглый ноль!

нет, получит лучше:
declare @t table(dt varchar(10));
insert into @t values
('00:00:06'),
('00:01:47'),
('00:10:45')

select dt,  CAST(CONVERT(CHAR(8), dt,112) AS INT) as dt_int
from @t

Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value '00:00:06' to data type int.
Вопрос: VARCHAR(MAX) VS VARCHAR(N)

MS SQL SERVER 2012

есть ли смысл указывать длину VARCHAR если буду хранить меньше 8000 символов?

если все столбцы сделать VARCHAR(MAX), какие могут быть проблемы?

спасибо.
Ответ:
MSSQLAndDotNet
Сон Веры Павловны
пропущено...

Нормальные пацаны и все строковые поля по VARCHAR(8000) не делают.

почему?
зачем? если надо 4 делаем 4
Вопрос: Типа столбца varchar без указания длины

Всем привет.

Выполняю

create table dev.tmp_tst (s varchar);
insert into dev.tmp_tst values (rpad('s',10000000,'s'));
SELECT length(s) FROM dev.tmp_tst;


Вставляется. Какое максимальное количество символов может быть в поле с типов varchar без параметров? Если как text, то чем отличается от text?
Ответ: Alexey Agafonov,

varchar без параметров ничем не отличается от text.