Tag Archives: SQL

One day of my life – debugging SQL generator for Entity Framework support in .NET provider for Firebird

Leave a reply

Couple of days back I was hunting some problem in code, that processes and later translates LINQ queries preprocessed by Entity Framework. The bottom line is that you are in code, where you have pieces of query, you’re rewriting these (even partially) or exchanging/adding new pieces if needed. You don’t have whole picture, because pieces are changing and so on.

So even if you partially know what you are producing, at the end you have to look at final SQL command and see whether it’s correct and/or semantically same as previous one, if you applied some changes or optimizations.

It took my roughly a day to find cause of issue and fix it. During this I created a lot of notes with commands and did a lot comparisons. I am placing these here, thus you can take a look at these and enjoy the feeling with me. If you’re interested, you can also check the SqlGenerator class, where I was working mainly.

SELECT 
"Skip1"."Limit1"."id_memo" AS "id_memo", 
"Skip1"."Limit1"."id_text_grup" AS "id_text_grup", 
"Skip1"."Extent2"."nom_grup" AS "nom_grup"
FROM ( SELECT SKIP (0) "Limit1"."id_memo" AS "id_memo", "Limit1"."id_text_grup" AS "id_text_grup1", "Extent2"."id_text_grup" AS "id_text_grup2", "Extent2"."nom_grup" AS "nom_grup"
	FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup"
		FROM "memos" AS "Extent1"
		ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1"
	LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup"
	ORDER BY "Limit1"."id_memo" ASC
)  AS "Skip1"

SELECT SKIP (0) 
"Limit1"."id_memo" AS "id_memo", 
"Limit1"."id_text_grup" AS "id_text_grup", 
"Extent2"."nom_grup" AS "nom_grup"
FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup"
	FROM "memos" AS "Extent1"
	ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1"
LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup"

SELECT SKIP (0) 
"Limit1"."id_memo" AS "id_memo", 
"Limit1"."id_text_grup" AS "id_text_grup", 
"Extent2"."nom_grup" AS "nom_grup"
FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup"
	FROM "memos" AS "Extent1"
	ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1"
LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup"

SELECT SKIP (0) 
"Limit1"."id_memo" AS "id_memo", 
"Limit1"."id_text_grup" AS "id_text_grup", 
"Extent2"."nom_grup" AS "nom_grup"
FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup"
	FROM "memos" AS "Extent1"
	ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1"
LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup"
ORDER BY "Limit1"."id_memo" ASC

SELECT SKIP (0) 
"D"."id_memo" AS "id_memo", 
"D"."id_text_grup" AS "id_text_grup", 
"E"."nom_grup" AS "nom_grup"
FROM   (SELECT FIRST (45) "C"."id_memo" AS "id_memo", "C"."id_text_grup" AS "id_text_grup"
	FROM "memos" AS "C"
	ORDER BY "C"."id_memo" ASC ) AS "D"
LEFT OUTER JOIN "texts_grups" AS "E" ON "D"."id_text_grup" = "E"."id_text_grup"
ORDER BY "D"."id_memo" ASC

SELECT SKIP (0) "Limit1"."id_memo" AS "id_memo", "Limit1"."id_text_grup" AS "id_text_grup", "Extent2"."nom_grup" AS "nom_grup" FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup" FROM "memos" AS "Extent1" ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1" LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup"

SELECT SKIP (0) "Limit1"."id_memo" AS "id_memo", "Limit1"."id_text_grup" AS "id_text_grup1", "Extent2"."id_text_grup" AS "id_text_grup2", "Extent2"."nom_grup" AS "nom_grup" FROM   (SELECT FIRST (45) "Extent1"."id_memo" AS "id_memo", "Extent1"."id_text_grup" AS "id_text_grup" FROM "memos" AS "Extent1" ORDER BY "Extent1"."id_memo" ASC ) AS "Limit1" LEFT OUTER JOIN "texts_grups" AS "Extent2" ON "Limit1"."id_text_grup" = "Extent2"."id_text_grup" ORDER BY "Limit1"."id_memo" ASC

SELECT SKIP (0) "D"."id_memo" AS "id_memo", "D"."id_text_grup" AS "id_text_grup", "E"."nom_grup" AS "nom_grup" FROM   (SELECT FIRST (45) "C"."id_memo" AS "id_memo", "C"."id_text_grup" AS "id_text_grup" FROM "memos" AS "C" ORDER BY "C"."id_memo" ASC ) AS "D" LEFT OUTER JOIN "texts_grups" AS "E" ON "D"."id_text_grup" = "E"."id_text_grup" ORDER BY "D"."id_memo" ASC

SELECT
"H"."id_memo_data" AS "id_memo_data", 
"H"."id_memo" AS "id_memo", 
"H"."id_idioma" AS "id_idioma", 
"H"."text" AS "text"
FROM   (SELECT SKIP (3) 
	"C"."id_memo" AS "id_memo", 
	(SELECT 
		COUNT("D"."A1") AS "A1"
		FROM ( SELECT 
			1 AS "A1"
			FROM "memos_data" AS "E"
			WHERE "C"."id_memo" = "E"."id_memo"
		)  AS "D") AS "C1"
	FROM "memos" AS "C"
	ORDER BY "C"."C1" DESC ) AS "G"
INNER JOIN "memos_data" AS "H" ON "G"."id_memo" = "H"."id_memo"

SELECT
"H"."id_memo_data" AS "id_memo_data", 
"H"."id_memo" AS "id_memo", 
"H"."id_idioma" AS "id_idioma", 
"H"."text" AS "text"
FROM   (SELECT SKIP (3) "B"."id_memo" AS "id_memo", "B"."C1" AS "C1"
	FROM ( SELECT 
		"C"."id_memo" AS "id_memo", 
		(SELECT 
			COUNT("D"."A1") AS "A1"
			FROM ( SELECT 
				1 AS "A1"
				FROM "memos_data" AS "E"
				WHERE "C"."id_memo" = "E"."id_memo"
			)  AS "D") AS "C1"
		FROM "memos" AS "C"
	)  AS "B"
	ORDER BY "B"."C1" DESC ) AS "G"
INNER JOIN "memos_data" AS "H" ON "G"."id_memo" = "H"."id_memo"

SELECT 
"H"."id_memo_data" AS "id_memo_data", 
"H"."id_memo" AS "id_memo", 
"H"."id_idioma" AS "id_idioma", 
"H"."text" AS "text"
FROM   (SELECT SKIP (3) "skip"."id_memo" AS "id_memo", "skip"."C1" AS "C1"
	FROM ( SELECT 
		"C"."id_memo" AS "id_memo", 
		(SELECT 
			COUNT("D"."A1") AS "A1"
			FROM ( SELECT 
				1 AS "A1"
				FROM "memos_data" AS "E"
				WHERE "C"."id_memo" = "E"."id_memo"
			)  AS "D") AS "C1"
		FROM "memos" AS "C"
	)  AS "skip"
	ORDER BY "skip"."C1" DESC ) AS "G"
INNER JOIN "memos_data" AS "H" ON "G"."id_memo" = "H"."id_memo"

Differences between “recreate procedure/…” and “create or alter procedure/…”

1 Reply

I was recently in a talk related to Firebird and I found, that people are not aware of these two constructs. Either they don’t know both or don’t know they differ. These statements are doing similar stuff, but the evil is in details.

So what’s the big deal? Both are kind of “updating” the procedure (or other object types). But the first one will first drop the procedure and then create it back again (yes, dependencies may break that). On the other hand, the another is creating the procedure if it doesn’t exist yet or altering it otherwise. Dependencies aside, what else might be “attached” to procedure? Yes, it’s i.e. access rights (grants). The former one will not keep these. You’re responsible to granting access to it again. The other one will, it’s just alter of procedure definition.

If not used carefully, you can easily break the database. Either one isn’t correct in all cases. Always use what’s appropriate for your scenario.

Value of particular column from all (some) tables in database dynamically in Firebird

1 Reply

A question came to me last week. It was simple. Given the column I’d like to query all tables in database for this column (with some condition) and get values back. It was on Firebird so I jumped into system tables and generated query on the fly in execute block (aka anonymous stored procedure).

The idea is simple. First get all table names (views and system tables excluded, but you can also exclude i.e. temporary tables) with this column (to be able to later run the query successfully), then concatenate some strings to build the query (with condition for the column) and finally use execute statement to run the query. The into clause will fill the variable and suspend will send the result (row) to client.

execute block
returns (table_name varchar(100), column_value varchar(100))
as
declare variable column_name varchar(100);
begin
  column_name = upper('id'); /* put here your column name */
  for select rdb$relation_name from rdb$relations r
    where rdb$system_flag = 0 /* no system tables */ and 
    rdb$view_blr is null /* no views */ and
    exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = r.rdb$relation_name and rf.rdb$field_name = :column_name)
    into :table_name do
  begin
    execute statement 'select cast(' || column_name || ' as varchar(100)) from ' || table_name || ' where /* put your condition here */' into :column_value;
    suspend;
  end
end

As a modification you can also instead of running n queries create one big string with union all-ing all queries and run just this one. You should compare execution plans and speed to see which one performs better. Then you would use for execute statement ... do do process results.

Sorting using blob column on Firebird

2 Replies

Imagine you have a blob column and you want to add sorting clause to your query based on that column. Crazy? Might be. On the other hand, why not?

Firebird allows you to use blob column for sorting. No problem. But the behavior might surprise you. I’m not going to deeply describe how the blobs are stored in Firebird database. Simply speaking, it’s stored in separate data pages and inside row only blob id is stored. If you use blob column for sorting, Firebird isn’t fetching the complete blob (though looks straightforward, it would be very slow), but rather uses blob id for sorting. You probably see the problem already – the blob id has nothing to do with content. Hence the sorting will be very likely broken.

But there’s a solution. I’m assuming that you want to mainly sort on text blobs (though you can use it on binary blobs too). Simply cast the blob to i.e. varchar(20) (choose length that fits your needs) and sort using this. Yes, it’s going to be slow, but if you need to do it often, you can precompute this column (using trigger etc.).

Tokenize string in SQL (Firebird syntax)

Leave a reply

Few days ago I wrote “Some thoughts on denormalization” post. Though concatenating data to one string is easy with there introduced function, splitting it back could be harder. I left it as exercise, but I feel, to make it complete, it’s good to show one possible implementation.

Here’s one I came with today (using Firebird syntax, however it’s almost pure SQL), quickly. It’s something I created from start to finish in one row and sure for some cases it could be optimized.

recreate procedure Tokenize(input varchar(1024), token char(1))
returns (result varchar(255))
as
declare newpos int;
declare oldpos int;
begin
  oldpos = 1;
  newpos = 1;
  while (1 = 1) do
  begin
    newpos = position(token, input, oldpos);
    if (newpos > 0) then
    begin
      result = substring(input from oldpos for newpos - oldpos);
      suspend;
      oldpos = newpos + 1;
    end
    else if (oldpos - 1 < char_length(input)) then
    begin
      result = substring(input from oldpos);
      suspend;
      break;
    end
    else
    begin
      break;
    end
  end
end

The procedure splits the input string using the specified token. The string can (or not) end with the token itself, the procedure will handle it.

select * from Tokenize('ab,cd,e', ',')
union all
select * from Tokenize('ab,cd,e,', ',');
union all
select * from Tokenize('ab,cd,e,,', ',');

What it is not handling is some form of quoting in case there's a token inside the element. Mainly because its intended purpose is to tokenize strings you can control (see the previous post) and because it will slow down the execution.

Feel free to improve it etc. (either post link to your solution or post the code in comments).

Type safety of SQL commands

Leave a reply

I don’t know why, but often I hear that the SQL commands are not type safe. That’s kind of badly said. No it’s wrong, without the proper context.

SQL commands are actually type safe. Did you tried to compare blob and date? Yep, it doesn’t work. And you get the error immediately during the prepare phase (or during compilation of i.e. trigger). Yes, you can compare int and date, because there’s a lot of implicit conversions in every database engine. But basically, if you wrote something wrong, the engine will let you know very quickly.

The problem lies in other detail. Nobody creates and application based only on SQL commands. Your application is probably something like C# or Delphi application, created from C# or Delphi code and compiled. And inside this code you have SQL commands, very probably strings. The C# or Delphi compiler isn’t able to verify the commands, hence to be sure it’s OK, your application needs to execute these commads. And that’s runtime.

Summary? No, SQL commands are type safe but only on server. Written in your applications code it’s still type safe, but not from point of view of type safety of your code and compiler’s rules.

It’s similar to some kind of hypothetical “eval” function in i.e. C#. If you write directly the code, it’s OK. But once you put something into this function (corresponds to database engine), compiler can do nothing with it, until executed.