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.

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

bad BLR -- invalid stream for union select [CORE1992] #2429

Closed
firebird-issue-importer opened this issue Jul 15, 2008 · 6 comments
Closed

bad BLR -- invalid stream for union select [CORE1992] #2429

firebird-issue-importer opened this issue Jul 15, 2008 · 6 comments

Comments

@firebird-issue-importer

Submitted by: @cincuranet

Hello *,

I have FB 2.1 (17798) and when I try to execute select:
SELECT
"Project7"."C12" AS "C1",
"Project7"."C1" AS "C2",
"Project7"."C2" AS "C3",
"Project7"."C3" AS "C4",
"Project7"."C4" AS "C5",
"Project7"."C5" AS "C6",
"Project7"."C6" AS "C7",
"Project7"."C7" AS "C8",
"Project7"."C8" AS "C9",
"Project7"."C9" AS "C10",
"Project7"."C10" AS "C11"
FROM ( SELECT
"UnionAll3"."SchemaName" AS "C1",
"UnionAll3"."Name" AS "C2",
"UnionAll3"."ReturnTypeName" AS "C3",
"UnionAll3"."IsAggregate" AS "C4",
"UnionAll3"."C1" AS "C5",
"UnionAll3"."IsBuiltIn" AS "C6",
"UnionAll3"."IsNiladic" AS "C7",
"UnionAll3"."C2" AS "C8",
"UnionAll3"."C3" AS "C9",
"UnionAll3"."C4" AS "C10",
"UnionAll3"."C5" AS "C11",
1 AS "C12"
FROM (SELECT
"Extent1"."SchemaName" AS "SchemaName",
"Extent1"."Name" AS "Name",
"Extent1"."ReturnTypeName" AS "ReturnTypeName",
"Extent1"."IsAggregate" AS "IsAggregate",
CAST(1 AS SMALLINT) AS "C1",
"Extent1"."IsBuiltIn" AS "IsBuiltIn",
"Extent1"."IsNiladic" AS "IsNiladic",
"UnionAll1"."Name" AS "C2",
"UnionAll1"."TypeName" AS "C3",
"UnionAll1"."Mode" AS "C4",
"UnionAll1"."Ordinal" AS "C5"
FROM (
SELECT
TRIM(f.rdb$function_name) as "Id"
, null as "CatalogName"
, null as "SchemaName"
, TRIM(f.rdb$function_name) as "Name"
, TRIM(CASE fa.rdb$field_type
WHEN 7 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "ReturnTypeName"
, fa.rdb$character_length as "ReturnMaxLength"
, fa.rdb$field_precision as "ReturnPrecision"
, 0 as "ReturnDateTimePrecision"
, fa.rdb$field_scale * (-1) as "ReturnScale"
, null as "ReturnCollationCatalog"
, null as "ReturnCollationSchema"
, null as "ReturnCollationName"
, null as "ReturnCharacterSetCatalog"
, null as "ReturnCharacterSetSchema"
, null as "ReturnCharacterSetName"
, CAST(0 as smallint) as "ReturnIsMultiSet"
, CAST(0 as smallint) as "IsAggregate"
, CAST(0 as smallint) as "IsBuiltIn"
, CAST((select CASE COUNT(*) WHEN 1 THEN 1 ELSE 0 END FROM
rdb$function_arguments fa WHERE fa.rdb$function_name =
f.rdb$function_name) as smallint) as "IsNiladic"
FROM
rdb$functions f INNER JOIN rdb$function_arguments fa ON
(f.rdb$function_name = fa.rdb$function_name AND f.rdb$return_argument
= fa.rdb$argument_position)
WHERE rdb$system_flag = 0
) AS "Extent1"
LEFT OUTER JOIN (SELECT
"Extent2"."Name" AS "Name",
"Extent2"."Ordinal" AS "Ordinal",
"Extent2"."TypeName" AS "TypeName",
"Extent2"."Mode" AS "Mode",
0 AS "C1",
"Extent2"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"

  , null as "Mode"
  , null as "Default"
  FROM
  rdb$database
  WHERE
  0=1
\) AS "Extent2"
          UNION ALL
                  SELECT
                  "Extent3"\."Name" AS "Name",
                  "Extent3"\."Ordinal" AS "Ordinal",
                  "Extent3"\."TypeName" AS "TypeName",
                  "Extent3"\."Mode" AS "Mode",
                  6 AS "C1",
                  "Extent3"\."ParentId" AS "ParentId"
                  FROM \(
  SELECT
  TRIM\(pp\.rdb$procedure\_name\) \|\| 'x' \|\|

TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent3") AS "UnionAll1" ON (0 = "UnionAll1"."C1") AND
("Extent1"."Id" = "UnionAll1"."ParentId")
UNION ALL
SELECT
"Extent4"."SchemaName" AS "SchemaName",
"Extent4"."Name" AS "Name",
CAST(NULL AS varchar(1000)) AS "C1",
CAST(0 AS SMALLINT) AS "C2",
CAST(0 AS SMALLINT) AS "C3",
CAST(0 AS SMALLINT) AS "C4",
CAST(0 AS SMALLINT) AS "C5",
"UnionAll2"."Name" AS "C6",
"UnionAll2"."TypeName" AS "C7",
"UnionAll2"."Mode" AS "C8",
"UnionAll2"."Ordinal" AS "C9"
FROM (
SELECT
TRIM(rdb$procedure_name) as "Id"
, null as "CatalogName"
, '' as "SchemaName" -- bug or not??? need to be not null
, TRIM(rdb$procedure_name) as "Name"
FROM
rdb$procedures
) AS "Extent4"
LEFT OUTER JOIN (SELECT
"Extent5"."Name" AS "Name",
"Extent5"."Ordinal" AS "Ordinal",
"Extent5"."TypeName" AS "TypeName",
"Extent5"."Mode" AS "Mode",
0 AS "C1",
"Extent5"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"
, null as "Mode"
, null as "Default"
FROM
rdb$database
WHERE
0=1
) AS "Extent5"
UNION ALL
SELECT
"Extent6"."Name" AS "Name",
"Extent6"."Ordinal" AS "Ordinal",
"Extent6"."TypeName" AS "TypeName",
"Extent6"."Mode" AS "Mode",
6 AS "C1",
"Extent6"."ParentId" AS "ParentId"
FROM (
SELECT
TRIM(pp.rdb$procedure_name) || 'x' ||
TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent6") AS "UnionAll2" ON (6 = "UnionAll2"."C1") AND
("Extent4"."Id" = "UnionAll2"."ParentId")) AS "UnionAll3"
) AS "Project7"
ORDER BY "Project7"."C1" ASC, "Project7"."C2" ASC, "Project7"."C11" ASC

I get error bad BLR -- invalid stream.

Commits: 8780116 4a77cfb 58cef69 FirebirdSQL/fbt-repository@d99f410

====== Test Details ======

Note: there are TWO separate .fbt in order to check functionality in both dialects: 1 and 3.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 27, 2008

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 30, 2008

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

description: Hello *,

I have FB 2.1 (17798) and when I try to execute select:
SELECT
"Project7"."C12" AS "C1",
"Project7"."C1" AS "C2",
"Project7"."C2" AS "C3",
"Project7"."C3" AS "C4",
"Project7"."C4" AS "C5",
"Project7"."C5" AS "C6",
"Project7"."C6" AS "C7",
"Project7"."C7" AS "C8",
"Project7"."C8" AS "C9",
"Project7"."C9" AS "C10",
"Project7"."C10" AS "C11"
FROM ( SELECT
"UnionAll3"."SchemaName" AS "C1",
"UnionAll3"."Name" AS "C2",
"UnionAll3"."ReturnTypeName" AS "C3",
"UnionAll3"."IsAggregate" AS "C4",
"UnionAll3"."C1" AS "C5",
"UnionAll3"."IsBuiltIn" AS "C6",
"UnionAll3"."IsNiladic" AS "C7",
"UnionAll3"."C2" AS "C8",
"UnionAll3"."C3" AS "C9",
"UnionAll3"."C4" AS "C10",
"UnionAll3"."C5" AS "C11",
1 AS "C12"
FROM (SELECT
"Extent1"."SchemaName" AS "SchemaName",
"Extent1"."Name" AS "Name",
"Extent1"."ReturnTypeName" AS "ReturnTypeName",
"Extent1"."IsAggregate" AS "IsAggregate",
CAST(1 AS SMALLINT) AS "C1",
"Extent1"."IsBuiltIn" AS "IsBuiltIn",
"Extent1"."IsNiladic" AS "IsNiladic",
"UnionAll1"."Name" AS "C2",
"UnionAll1"."TypeName" AS "C3",
"UnionAll1"."Mode" AS "C4",
"UnionAll1"."Ordinal" AS "C5"
FROM (
SELECT
TRIM(f.rdb$function_name) as "Id"
, null as "CatalogName"
, null as "SchemaName"
, TRIM(f.rdb$function_name) as "Name"
, TRIM(CASE fa.rdb$field_type
WHEN 7 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "ReturnTypeName"
, fa.rdb$character_length as "ReturnMaxLength"
, fa.rdb$field_precision as "ReturnPrecision"
, 0 as "ReturnDateTimePrecision"
, fa.rdb$field_scale * (-1) as "ReturnScale"
, null as "ReturnCollationCatalog"
, null as "ReturnCollationSchema"
, null as "ReturnCollationName"
, null as "ReturnCharacterSetCatalog"
, null as "ReturnCharacterSetSchema"
, null as "ReturnCharacterSetName"
, CAST(0 as smallint) as "ReturnIsMultiSet"
, CAST(0 as smallint) as "IsAggregate"
, CAST(0 as smallint) as "IsBuiltIn"
, CAST((select CASE COUNT(*) WHEN 1 THEN 1 ELSE 0 END FROM
rdb$function_arguments fa WHERE fa.rdb$function_name =
f.rdb$function_name) as smallint) as "IsNiladic"
FROM
rdb$functions f INNER JOIN rdb$function_arguments fa ON
(f.rdb$function_name = fa.rdb$function_name AND f.rdb$return_argument
= fa.rdb$argument_position)
WHERE rdb$system_flag = 0
) AS "Extent1"
LEFT OUTER JOIN (SELECT
"Extent2"."Name" AS "Name",
"Extent2"."Ordinal" AS "Ordinal",
"Extent2"."TypeName" AS "TypeName",
"Extent2"."Mode" AS "Mode",
0 AS "C1",
"Extent2"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"
, null as "Mode"
, null as "Default"
FROM
rdb$database
WHERE
0=1
) AS "Extent2"
UNION ALL
SELECT
"Extent3"."Name" AS "Name",
"Extent3"."Ordinal" AS "Ordinal",
"Extent3"."TypeName" AS "TypeName",
"Extent3"."Mode" AS "Mode",
6 AS "C1",
"Extent3"."ParentId" AS "ParentId"
FROM (
SELECT
TRIM(pp.rdb$procedure_name) || 'x' ||
TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent3") AS "UnionAll1" ON (0 = "UnionAll1"."C1") AND
("Extent1"."Id" = "UnionAll1"."ParentId")
UNION ALL
SELECT
"Extent4"."SchemaName" AS "SchemaName",
"Extent4"."Name" AS "Name",
CAST(NULL AS varchar(1000)) AS "C1",
CAST(0 AS SMALLINT) AS "C2",
CAST(0 AS SMALLINT) AS "C3",
CAST(0 AS SMALLINT) AS "C4",
CAST(0 AS SMALLINT) AS "C5",
"UnionAll2"."Name" AS "C6",
"UnionAll2"."TypeName" AS "C7",
"UnionAll2"."Mode" AS "C8",
"UnionAll2"."Ordinal" AS "C9"
FROM (
SELECT
TRIM(rdb$procedure_name) as "Id"
, null as "CatalogName"
, '' as "SchemaName" -- bug or not??? need to be not null
, TRIM(rdb$procedure_name) as "Name"
FROM
rdb$procedures
) AS "Extent4"
LEFT OUTER JOIN (SELECT
"Extent5"."Name" AS "Name",
"Extent5"."Ordinal" AS "Ordinal",
"Extent5"."TypeName" AS "TypeName",
"Extent5"."Mode" AS "Mode",
0 AS "C1",
"Extent5"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"
, null as "Mode"
, null as "Default"
FROM
rdb$database
WHERE
0=1
) AS "Extent5"
UNION ALL
SELECT
"Extent6"."Name" AS "Name",
"Extent6"."Ordinal" AS "Ordinal",
"Extent6"."TypeName" AS "TypeName",
"Extent6"."Mode" AS "Mode",
6 AS "C1",
"Extent6"."ParentId" AS "ParentId"
FROM (
SELECT
TRIM(pp.rdb$procedure_name) || 'x' ||
TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent6") AS "UnionAll2" ON (6 = "UnionAll2"."C1") AND
("Extent4"."Id" = "UnionAll2"."ParentId")) AS "UnionAll3"
) AS "Project7"
ORDER BY "Project7"."C1" ASC, "Project7"."C2" ASC, "Project7"."C11" ASC

I get error bad BLR -- invalid stream.

=>

Hello *,

I have FB 2.1 (17798) and when I try to execute select:
SELECT
"Project7"."C12" AS "C1",
"Project7"."C1" AS "C2",
"Project7"."C2" AS "C3",
"Project7"."C3" AS "C4",
"Project7"."C4" AS "C5",
"Project7"."C5" AS "C6",
"Project7"."C6" AS "C7",
"Project7"."C7" AS "C8",
"Project7"."C8" AS "C9",
"Project7"."C9" AS "C10",
"Project7"."C10" AS "C11"
FROM ( SELECT
"UnionAll3"."SchemaName" AS "C1",
"UnionAll3"."Name" AS "C2",
"UnionAll3"."ReturnTypeName" AS "C3",
"UnionAll3"."IsAggregate" AS "C4",
"UnionAll3"."C1" AS "C5",
"UnionAll3"."IsBuiltIn" AS "C6",
"UnionAll3"."IsNiladic" AS "C7",
"UnionAll3"."C2" AS "C8",
"UnionAll3"."C3" AS "C9",
"UnionAll3"."C4" AS "C10",
"UnionAll3"."C5" AS "C11",
1 AS "C12"
FROM (SELECT
"Extent1"."SchemaName" AS "SchemaName",
"Extent1"."Name" AS "Name",
"Extent1"."ReturnTypeName" AS "ReturnTypeName",
"Extent1"."IsAggregate" AS "IsAggregate",
CAST(1 AS SMALLINT) AS "C1",
"Extent1"."IsBuiltIn" AS "IsBuiltIn",
"Extent1"."IsNiladic" AS "IsNiladic",
"UnionAll1"."Name" AS "C2",
"UnionAll1"."TypeName" AS "C3",
"UnionAll1"."Mode" AS "C4",
"UnionAll1"."Ordinal" AS "C5"
FROM (
SELECT
TRIM(f.rdb$function_name) as "Id"
, null as "CatalogName"
, null as "SchemaName"
, TRIM(f.rdb$function_name) as "Name"
, TRIM(CASE fa.rdb$field_type
WHEN 7 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE fa.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "ReturnTypeName"
, fa.rdb$character_length as "ReturnMaxLength"
, fa.rdb$field_precision as "ReturnPrecision"
, 0 as "ReturnDateTimePrecision"
, fa.rdb$field_scale * (-1) as "ReturnScale"
, null as "ReturnCollationCatalog"
, null as "ReturnCollationSchema"
, null as "ReturnCollationName"
, null as "ReturnCharacterSetCatalog"
, null as "ReturnCharacterSetSchema"
, null as "ReturnCharacterSetName"
, CAST(0 as smallint) as "ReturnIsMultiSet"
, CAST(0 as smallint) as "IsAggregate"
, CAST(0 as smallint) as "IsBuiltIn"
, CAST((select CASE COUNT(*) WHEN 1 THEN 1 ELSE 0 END FROM
rdb$function_arguments fa WHERE fa.rdb$function_name =
f.rdb$function_name) as smallint) as "IsNiladic"
FROM
rdb$functions f INNER JOIN rdb$function_arguments fa ON
(f.rdb$function_name = fa.rdb$function_name AND f.rdb$return_argument
= fa.rdb$argument_position)
WHERE rdb$system_flag = 0
) AS "Extent1"
LEFT OUTER JOIN (SELECT
"Extent2"."Name" AS "Name",
"Extent2"."Ordinal" AS "Ordinal",
"Extent2"."TypeName" AS "TypeName",
"Extent2"."Mode" AS "Mode",
0 AS "C1",
"Extent2"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"

  , null as "Mode"
  , null as "Default"
  FROM
  rdb$database
  WHERE
  0=1
\) AS "Extent2"
          UNION ALL
                  SELECT
                  "Extent3"\."Name" AS "Name",
                  "Extent3"\."Ordinal" AS "Ordinal",
                  "Extent3"\."TypeName" AS "TypeName",
                  "Extent3"\."Mode" AS "Mode",
                  6 AS "C1",
                  "Extent3"\."ParentId" AS "ParentId"
                  FROM \(
  SELECT
  TRIM\(pp\.rdb$procedure\_name\) \|\| 'x' \|\|

TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent3") AS "UnionAll1" ON (0 = "UnionAll1"."C1") AND
("Extent1"."Id" = "UnionAll1"."ParentId")
UNION ALL
SELECT
"Extent4"."SchemaName" AS "SchemaName",
"Extent4"."Name" AS "Name",
CAST(NULL AS varchar(1000)) AS "C1",
CAST(0 AS SMALLINT) AS "C2",
CAST(0 AS SMALLINT) AS "C3",
CAST(0 AS SMALLINT) AS "C4",
CAST(0 AS SMALLINT) AS "C5",
"UnionAll2"."Name" AS "C6",
"UnionAll2"."TypeName" AS "C7",
"UnionAll2"."Mode" AS "C8",
"UnionAll2"."Ordinal" AS "C9"
FROM (
SELECT
TRIM(rdb$procedure_name) as "Id"
, null as "CatalogName"
, '' as "SchemaName" -- bug or not??? need to be not null
, TRIM(rdb$procedure_name) as "Name"
FROM
rdb$procedures
) AS "Extent4"
LEFT OUTER JOIN (SELECT
"Extent5"."Name" AS "Name",
"Extent5"."Ordinal" AS "Ordinal",
"Extent5"."TypeName" AS "TypeName",
"Extent5"."Mode" AS "Mode",
0 AS "C1",
"Extent5"."ParentId" AS "ParentId"
FROM (
SELECT
null as "Id"
, null as "ParentId"
, null as "Name"
, null as "Ordinal"
, null as "TypeName"
, null as "MaxLength"
, null as "Precision"
, null as "DateTimePrecision"
, null as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null as "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, null as "IsMultiSet"
, null as "Mode"
, null as "Default"
FROM
rdb$database
WHERE
0=1
) AS "Extent5"
UNION ALL
SELECT
"Extent6"."Name" AS "Name",
"Extent6"."Ordinal" AS "Ordinal",
"Extent6"."TypeName" AS "TypeName",
"Extent6"."Mode" AS "Mode",
6 AS "C1",
"Extent6"."ParentId" AS "ParentId"
FROM (
SELECT
TRIM(pp.rdb$procedure_name) || 'x' ||
TRIM(pp.rdb$parameter_name) as "Id"
, TRIM(pp.rdb$procedure_name) as "ParentId"
, TRIM(pp.rdb$parameter_name) as "Name"
, pp.rdb$parameter_number+1 as "Ordinal"
, TRIM(CASE f.rdb$field_type
WHEN 7 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'smallint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 8 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'int'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 16 THEN CASE f.rdb$field_sub_type
WHEN 0 THEN 'bigint'
WHEN 1 THEN 'numeric'
WHEN 2 THEN 'decimal'
END
WHEN 10 THEN 'float'
WHEN 27 THEN 'double'
WHEN 12 THEN 'date'
WHEN 13 THEN 'time'
WHEN 35 THEN 'timestamp'
WHEN 261 THEN 'blob'
WHEN 37 THEN 'varchar'
WHEN 14 THEN 'char'
WHEN 40 THEN 'cstring'
END) as "TypeName"
, f.rdb$character_length as "MaxLength"
, f.rdb$field_precision as "Precision"
, 0 as "DateTimePrecision"
, f.rdb$field_scale * (-1) as "Scale"
, null as "CollationCatalog"
, null as "CollationSchema"
, null "CollationName"
, null as "CharacterSetCatalog"
, null as "CharacterSetSchema"
, null as "CharacterSetName"
, CAST(0 as smallint) as "IsMultiSet"
, TRIM(IIF(pp.rdb$parameter_type = 1, 'OUT', 'IN')) as "Mode"
, NULL as "Default"
FROM
rdb$procedure_parameters pp INNER JOIN rdb$fields f ON
(pp.rdb$field_source = f.rdb$field_name)
) AS "Extent6") AS "UnionAll2" ON (6 = "UnionAll2"."C1") AND
("Extent4"."Id" = "UnionAll2"."ParentId")) AS "UnionAll3"
) AS "Project7"
ORDER BY "Project7"."C1" ASC, "Project7"."C2" ASC, "Project7"."C11" ASC

I get error bad BLR -- invalid stream.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 15, 2012

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.1.1 [ 10223 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 15, 2012

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 23, 2013

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Test Details: Note: there are TWO separate .fbt in order to check functionality in both dialects: 1 and 3.

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