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-automations opened this issue Jul 15, 2008 · 6 comments
Closed

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

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

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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