The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,862 other subscribers

Archive for the ‘Database Development’ Category

« Previous Entries
Next Entries »

SQL comma bullet point formatting: because AS is optional

Posted by jpluimers on 2022/11/03

Do you see the error below?

(note: OCR via [Wayback/Archive.is] Best Free OCR API, Online OCR, Searchable PDF – Fresh 2021 OCR Software)

SELECT
  license date
  expiration_date,
  renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'
expiration_date renewal_ due date
1 2016-04-08 09:50:00 [NULL]
2 2013-11-14 11:15:00 [NULL]
3 2014-11-20 14:51:00 [NULL]
4 2017-07-21 16:00:00 [NULL]
5 2018-12-17 14:37:46 2020-12-17 14:37:46

All of the expiration_date columns have values, which is contrary to the WHERE clause. This is because the table itself contains an expiration_date column, and the SELECT part aliases license_date into expiration_date.

The result is that you see rows that have expiration_date being NULL, but license_date having a value.

So I totally agree with [Archive.is] Mathias Magnusson on Twitter: “That is one reason I’m a form believer in comma bullet point in my SQL. Problems like that has bit me far too often due to som issue with commas.… “

This is how the SQL should have looked:

SELECT
  license date
, expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

Yes indeed: an alias of a column without the AS keyword is allowed in quite a few SQL dialects (they differ even more widely in SQL extensions like SQL/PSM, T-SQL, PL/SQL, SQL_PL, or ABAB).

Aliases are for output, cannot be used in WHERE (but can in ORDER BY).

You can see what happens (and how hard this can become on one line) with these two dbfiddle queries running on Microsoft SQL Server 2019 dialect (though it works similar in other dialects):

  1. select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    b c
    7 6
    8 4
    9 2

    It shows only the column names a and b, but note the table itself is aliased to t above as well.

  2. select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    a b c
    9 1 2
    8 3 4
    7 5 6

    It shows only the column names a, b and c, but note the table itself is aliased to t above as well.

I really wish various SQL dialects would force the SQL syntax to be (together with a hint that the alias would overwrite an existing field):

SELECT
  license date AS expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

That is not going to happen, so the second best is to wish for tooling to hint/warn about it, and provide better syntax highlighting for it. That seems work in progress by now:

Read the rest of this entry »

Posted in Conventions, Database Development, Development, OracleDB, PL/SQL, SQL, SQL Server, T-SQL | Leave a Comment »

Database fiddle sites

Posted by jpluimers on 2022/10/27

I knew there was JSFiddle for live playing around with JavaScript and more in your browser, so I wondered if there was a similar site for databases and SQL queries.

There are, so here are a few database fiddle sites: SQL playgrounds where you can live play with SQL queries (sometimes even without an underlying example database).

All via [Wayback/Archive.is] database fiddle – Google Search:

Read the rest of this entry »

Posted in Conference Topics, Conferences, Database Development, DB2, Development, Event, Firebird, JavaScript/ECMAScript, JSFiddle, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, Scripting, Software Development, SQL, SQL Server, SQLite, T-SQL | Leave a Comment »

When MySQL characterset ‘utf’ does not allow you to enter some Unicode code points

Posted by jpluimers on 2022/01/06

Contrary to what many believe is that MySQL utf8 is not always full blown UTF-8 support, but actually utf8mb3, which has been deprecated for a while now.

Only utf8mb4 will give you full blown UTF-8 support.

This when someone reminded me of this in a Delphi application:

When I insert :joy: emoji into mysql varchar filed I got an error :
#22007 Incorrect string value: '\xF0\x9F\x98\x82' for column 'remarks' at row 1

database charset is utf8

Note that the :joy: emoji is 😂 and has Unicode code point U+1F602 which is outside the basic multilingual plane.

See:

–jeroen

Posted in Conference Topics, Conferences, Database Development, Delphi, Development, Encoding, Event, MySQL, Software Development, UTF-8, UTF8 | Leave a Comment »

@isotopp: How fast is MySQL? I was casually operating under the impression, that a typical query resolution time is ~3ms. Turns out I was off 1-2 orders of magnitude…

Posted by jpluimers on 2021/07/13

Configured properly, even across network (see the answers in the Twitter thread below).

[WayBack] Thread by @isotopp: “How fast is MySQL? I was casually operating under the impression, that a typical query resolution time is ~3ms. Turns out I was off one or e […]”

How fast is MySQL?

I was casually operating under the impression, that a typical query resolution time is ~3ms.

Turns out I was off one or even two orders of magnitude.

A “select version()” type of nonquery is handled in around 20µs.
A typical query in a point-query or BKA environment (where id = const, … where id in (c1, c2, …)) is typically handled in 350µs.

This is based on looking at

select event_name,
AVG_TIMER_WAIT/1000000 as avg_us, max_timer_wait/1000000 as max_us, min_timer_wait/1000000 as min_us from performance_schema.events_statements_summary_global_by_event_name
where event_name = ‘statement/sql/select’;

| event_name | avg_us | max_us | min_us |
+———————-+———-+———–+———+
| statement/sql/select | 336.4440 | 2218.0450 | 36.4460 |
for about any of our servers with that kind of workload that I have been looking at.
This is obviously answered mostly from the buffer pool, because if you look at storage latencies, what you see here is that MySQL is in the same ballpark as NVME or SSD storage latencies (within 2x).
TL;DR: MySQL is fucking fast, and I had no idea how to quantify fucking fast before I looked into P_S

Read the rest of this entry »

Posted in Database Development, Development, MySQL, Software Development | Leave a Comment »

delphi – How to enable wirecompression on Firebird 3.0 – Stack Overflow

Posted by jpluimers on 2021/07/08

For my link archive:

–jeroen

Posted in Database Development, Delphi, Development, Firebird, Software Development | Leave a Comment »

Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub

Posted by jpluimers on 2021/07/08

For my link archive: [WayBack] Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub:

  • Oracle and H2 changes unquoted identifiers to uppercase.
    Although technically possible, Oracle explicitly recommends to not use lowercase identifers. We do not support H2 with the setting DATABASE_TO_UPPER=FALSE, if this setting is enabled all queries with names and tables in lowercase must be quoted.
  • DB2 Names are not case sensitive.
    For example, the table names CUSTOMER and Customer are the same, but object names are converted to uppercase when they are entered. If a name is enclosed in quotation marks, the name becomes case sensitive. The schema name is case-sensitive, and must be specified in uppercase characters.
  • Postgres changes unquoted identifiers (both columns and alias names) to lowercase.
  • Mysql does not change the case of unquoted tables and schemas.
    It changes in lowercase the unquoted columns. Mysql tables are stored as files in the operating system the server runs on. This means that database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux. The backtick ` is used for enclosing identifiers such as table and column names.
  • Mssqlserver All connection string property names are case-insensitive.
    For example, Password is the same as password. Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation. Default SQL Server is not case sensitive. SELECT * FROM SomeTable is the same as SeLeCT * frOM soMetaBLe. Delimited identifiers are enclosed in double quotation marks (“) or brackets ([]). Identifiers that comply with the rules for the format of identifiers may or may not be delimited.

–jeroen

Posted in Database Development, DB2, Development, MySQL, OracleDB, PostgreSQL, SQL Server | Leave a Comment »

SQL Server, [] brackets, keywords and special characters

Posted by jpluimers on 2021/07/06

A few links for my archive:

  • [WayBack] sql server – What is the use of the square brackets [] in sql statements? – Stack Overflow answer by Michael Haren:

    The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)–but then you’d need to use brackets every time you referred to that column.

    The newer tools add them everywhere just in case or for consistency.

     

  • [WayBack] tsql – What characters are valid in an SQL Server database name? – Stack Overflow answer by Scott Munro:

    Delimited names – surrounded by square brackets or double quotes (if QUOTED_IDENTIFIER is set to ON) – can contain basically anything other than the delimiters themselves. It is even possible to use the delimiters within the name with some escape logic. Note though that it is only the closing escape character that must be escaped. In the first example below, the single instance of the opening escape character in the name does not need to be escaped whereas the closing escape character does have to be escaped (by replacing the single instance with two). I guess the logic here is that whatever code that is parsing these statements is looking for a closing escape character and has is not interested in nested opening escape characters.

    • [Test[Test] -> Test[Test
    • [Test]]Test] -> Test]Test

    The following is a description of the rules surrounding non delimited (nonquoted) identifier names in SQL Server 2012. It is an extract from the document Guide to Migrating from MySQL to SQL Server 2012.

    Schema Object Names

    In SQL Server 2012, an object name can be up to 128 characters long.

    Nonquoted identifier names must follow these rules:

    • The first character must be alphanumeric, an underscore (_), an at sign (@), or a number sign (#).
    • Subsequent characters can include alphanumeric characters, an underscore, an at (@) sign, a number sign, or a dollar sign.
    • The identifier must not be a Transact-SQL reserved word. Guide to Migrating from MySQL to SQL Server 2012 8
    • Embedded spaces or special characters are not allowed.

    Identifiers that start with @ or a number sign have special meanings. Identifiers starting with @ are local variable names. Those that start with a number sign are temporary table names.

    To quote an identifier name in Transact-SQL, you must use square brackets ([]).

  • [WayBack] Database Identifiers – SQL Server | Microsoft Docs:
    1. Classes of Identifiers
    2. Rules for Regular Identifiers
    3. See Also

–jeroen

 

 

Posted in Database Development, Development, SQL, SQL Server | Leave a Comment »

In case you cannot do BIGINT yet: A How-to Guide for Resolving Database Record Limitations | Distillery

Posted by jpluimers on 2021/06/30

Sometimes your development stack is not fully ready to handle signed 64-bit BIGINT values across all layers, which is when [WayBack] A How-to Guide for Resolving Database Record Limitations | Distillery comes in handy.

For data type ranges: [WayBack] int, bigint, smallint, and tinyint (Transact-SQL) – SQL Server | Microsoft Docs

–jeroen

Posted in Database Development, Development, Software Development, SQL Server | Leave a Comment »

SQL server: getting database names and IDs

Posted by jpluimers on 2021/06/29

A few statements go get database names and IDs based on these functions or system tables:

Part of it has the assumption that a master database always exists.

-- gets current database name
select db_name() as name
;
name
--------------------------------------------------------------------------------------------------------------------------------
acc

(1 row affected)

-- gets current database ID
select db_id() as dbid
;
dbid
------
5

(1 row affected)

-- gets all database IDs and names
select dbid,name from sys.sysdatabases
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master
5      acc

(2 rows affected)

-- gets current database name by ID
select db_name(db_id()) as name
;
name
--------------------------------------------------------------------------------------------------------------------------------
acc

(1 row affected)

-- gets case corrected database name for sys.sysdatabases.name having a case insensitive collation sequence
select dbid,name from sys.sysdatabases 
where name='Master'
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master

(1 row affected)

-- gets case corrected database name for sys.sysdatabases.name having a case sensitive collation sequence
select dbid,name from sys.sysdatabases 
where name = 'Master' collate Latin1_General_100_CI_AI
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master

(1 row affected)

Note that:

  • even though by default the SQL server collation sequence is case insensitive, it can make sense to do a case insensitive search, for example by using the upper function, specifying a collation, or casting to binary. I like upper the most, because  – though less efficient – it is a more neutral SQL idiom.
  • the most neutral case insensitive collation seems to be Latin1_General_100_CI_AI

Related:

  • [WayBack] SQL server ignore case in a where expression – Stack Overflow answered by Solomon Rutzky, summarised as:
    • Do not use upper as upper with lower does not always round-trip.
    • Do not use varbinary as it is not case insensitive.
    • Neither the = or like operators are case sensitive by default: both need a collate clause.
    • Find the collation of the column(s) involved; if it contains _CI, then you are done (it is already case insensitive); if it contains _CS, then replace that with _CI (case insensitive) and add that in a collate clause.
    • Collations are per predicate, so not per query, per table, per column nor per database. This means you have to specify them if you want to use a different one than the default.
  • [WayBack] What is Collation in Databases? | Database.Guide
    Latin1_General_100_CI_AI Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
  • [WayBack] Collation Info: Information about Collations and Encodings for SQL Server
  • [WayBack] SQL Instance Collation – Language Neutral Required:

    I recommend using Latin1_General_100_CI_AI. I recommend this because:

    …

    1. If Latin1_General_CI_AI is supported, then there’s almost no chance thatLatin1_General_100_CI_AI (which is a far better choice) isn’t also supported. The version 100 collation has about 15,400 more sort weight definitions, plus 438 more uppercase/lowercase mappings. Not having those sort weights means that 15,400 more characters in the non-100 version equate to space, an empty string, and to each other. Not having those case mappings means that 438 more characters in the non-100 version return the character passed in (i.e. no change) for the UPPER() and LOWER() functions. There is no reason at all to want Latin1_General_CI_AI instead of Latin1_General_100_CI_AI. There might be a need if code was put into place to work around these deficiencies, and that code would behave incorrectly under the newer, better version of that collation. However, it’s highly unlikely that code was put into place to account for this, and extremely unlikely that if such code did exist, that it would error or doing things incorrectly due to the newer collation.
  • [WayBack] Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2) – Sql Quantum Leap
  • [WayBack] How to do a case sensitive search in WHERE clause (I’m using SQL Server)? – Stack Overflow answered by Jonas Lincoln:

    By using collation or casting to binary, like this:

    SELECT *
FROM Users
WHERE   
    Username = @Username COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Username = @Username 
    AND Password = @Password

    The duplication of username/password exists to give the engine the possibility of using indexes. The collation above is a Case Sensitive collation, change to the one you need if necessary.

    The second, casting to binary, could be done like this:

    SELECT *
FROM Users
WHERE   
    CAST(Username as varbinary(100)) = CAST(@Username as varbinary))
    AND CAST(Password as varbinary(100)) = CAST(@Password as varbinary(100))
    AND Username = @Username 
    AND Password = @Password
  • [WayBack] sql – How to get Database name of sqlserver – Stack Overflow

–jeroen

Posted in Database Development, Development, Encoding, internatiolanization (i18n) and localization (l10), SQL Server | Leave a Comment »

I don’t want to learn your garbage query language · Erik Bernhardsson

Posted by jpluimers on 2021/06/24

Interesting read:

This is a bit of a rant but I really don’t like software that invents its own query language. There’s a trillion different ORMs out there. Another trillion databases with their own query language. Another trillion SaaS products where the only way to query is to learn some random query DSL they made up.

Source: [WayBack] I don’t want to learn your garbage query language · Erik Bernhardsson

Related:

Via: [WayBack] Die wichtigste Funktion in jedem ORM ist der Notausgang. Also, wie man literales SQL durch das Ding durch piped, ohne daß der ORM rein pullert. – Kristian Köhntopp – Google+

–jeroen

Posted in Database Development, Development, Software Development | Leave a Comment »

« Previous Entries
Next Entries »