キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
データベースの分離レベルをめぐる冒険(I.イントロダクションとII.MySQLの場合)
0
    とある原稿を書いていて、トランザクションまわりをチト調べている私です。

    あまり深みに入らなければ、4つのトランザクション分離レベルと、それぞれのアノーマリを説明して、はい、終わり、ということにしたいのですが、私には以前気づいてしまった以下の記事があり「あれ、四つ多くない?」を細かく調べることなく放置していたのでした。

    Web開発者が知っておくべき八つの分離レベル

    しかし、ここで調べなければもやもやは晴れないですし、
    神林さんがシリアライズでもパフォーマンスに問題のないRDBMSの実装が終わり、
    世の中でがんがん使われるようになるまでは、いましばらくかかるため、手元の道具(RDBMS)が
    どのようにデータベースの分離レベルを実装しているか知って使ったほうがいいだろう、ということで
    ここでしばらく時間をとって、整理したり、まとめたりします。

    I.イントロダクション

    日本語翻訳の原文はこちら。

    Eight Isolation Levels Every Web Developer Should Know

    要は「八つのトランザクション分離レベルがあるので、知っとけ」ということなのですが。。。。

    1.Read Uncommitted
    2.Read Committed
    3.Monotonic View
    4.Snapshot Reads
    5.Cursor Stability
    6.Repeatable Read
    7.Snapshot Isolation
    8.Serializability

    なんか見慣れぬものがごろごろと。。。。実は2.から5.はRC(Read Committed)と、その拡張です。

    Overwritesを禁止(SELECT ... FOR UPDATE)ということで、少なくともOracleとMySQL, Firebirdの
    Read Committedは、ここでいう6.Cursor Stabilityの実装のようです。

    うーん、なんかこう定義みたいなものはないのかなぁ、ということで見つけたドキュメントが以下。

    Weak Consistency: A Generalized Theory and OptimisticImplementations for Distributed Transactions by Atul Adya

    これを順に読んでいけばなんとかなるかなぁ。

    II.MySQLの場合

    ちなみにMySQLでは、以下の5つとなります。

    ----------------------+-----------------------
    この記事での分離レベル MySQLでの分離レベル
    ----------------------+-----------------------
    1.Read Uncommitted   READ UNCOMMITTED
    5.Cursor Stability     READ COMMITTED
    6.Repeatable Read    REPEATABLE READ
    7.Snapshot Isolation   REPEATABLE READ WITH CONSISTENT SNAPSHOT
    8.Serializability      SERIALIZABLE
    ----------------------+-----------------------

    「あれ、四つじゃないの?」と思った人は鋭い! (いや、別の意味では鋭くない)
    実はmysqldumpにて、InnoDB型のテーブル、すなわちトランザクションが利用できる環境で、
    一貫性をもったバックアップを取得するため(--single-transaction)に用意されているものです。

    この分離レベルでSTART TRANSACTIONした場合は、RRのように、一度オープンしたテーブルの内容を
    繰り返し取得できるのではなく、START TRANSACTIONした際のテーブルの内容を
    その後のトランザクション内で参照できる、というバックアップに適した(==そのために)
    実装されている分離レベルとなります。

    13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文


    (次回以降に続く(予定))

    JUGEMテーマ:コンピュータ

    | meijik | MySQL | 09:40 | comments(0) | trackbacks(0) | - | - |
    [便乗企画] 「SQL大量発行処理をいかにして高速化するか」for MySQL
    0
      ツイッターでこういうのがあったので、便乗してMySQLで考えてみるエントリ。




      高速化手法として#1-#10の十個がリストアップされています。

      #1 SQL発行数の削減 その
      Oracle11gの新機能「クライアント結果キャッシュ」は、当然MySQLにはありませんが、
      クエリと結果をサーバ側にキャッシュする「クエリキャッシュ」機能がMySQL 5.7以前では利用できます。

      # 木村はクライアント結果キャッシュをアプリ(ミドルウエア)で実装したことがあります。
      # たとえば数秒間に同じクエリを死ぬほど発行するようなアホアプリには、データベースへの問い合わせをスキップして、同じ結果を返す、など。

      #2, #3はそのまま使えそう。

      #4のinsert allはOracleのみの方言です。この例のように複数のテーブルへのinsertや単一テーブルへの複数の値の組のinsertをまとめて実行することができます。OracleとFirebird以外のRDBMSでは、単一テーブルへの複数の値の組のinsertは以下の記法でバルクインサートできます。

      【Before】
      insert into TBL_A(ID) values(1002);
      insert into TBL_A(ID) values(1003);
      insert into TBL_A(ID) values(1004);

      【After】
      insert into TBL_A(ID) values(1002),(1003),(1004);

      この記法は元の例のような複数のテーブルへのインサートにはつかえません。

      #5のようにPL/SQLでまとめて発行するためには、無名プロシジャ(Anonymous procedure)のサポートが必要ですが、MySQLではサポートしていないため使えません。(一時的に名前をつけたプロシジャを作成して、それを削除するということでエミュレートは可能ですが、作成・削除に時間がかかり、本来のよさがなくなってしまいます)

      以下のWLにはあがっていますが、まだ実装されていません。(「動的複合ステートメント」としてリストアップされています)

      WL#3696: Dynamic Compound Statements

      MySQLからフォークしたMariaDBでは、最近Oracle DBに対する互換性をあげてきており、10.1.1から対応しています。

      MariaDB 10.1.1: Compound statements

      Oracleでの詳細は以下のブログ記事が参考になります。

      PL/SQLの理解と利用に関するシリーズ記事のパート1

      #6,#7,#8 のBLOB,CLOBの最適化、高速化はMySQLでは当てはまるものがないです。(もともと普通のデータ型ちとして、通常のBinary, Characterと同様なアクセスができるので)

      #9はMySQLでも同様にあてはまります。autocommit=1ではなく、0にして、ある程度の処理(1万行など)ごとにコミットするようにしてください。

      #10も同様にあてはまります。

      ということで、わりとあっさりと終わります。。。。

      JUGEMテーマ:コンピュータ



      | meijik | MySQL | 09:57 | comments(0) | trackbacks(0) | - | - |
      プログラマのためのSQL第四版読書会(37) 38.5 その他の時間関数:君のDOW(だう)はどう?
      0
        本日は以下の勉強会でした。

        プログラマのためのSQL 読書会(37)

        38.5 その他の時間関数(Other Temporal Functions)で、DOW(Day Of Week)という曜日を数値で返す関数の説明があり、
        とりあえず翻訳でSQL Anywhereから差し替えられた[PostgreSQL]説明が間違っているのと、各データベースの違いが顕著だったので、ここにまとめておきます。

        [誤] 2〜8の値を返し、8が日曜日である。
        [正] 0〜6の値を返し、0が日曜日である。

        なんかぜんぜんちがうね。。。。

        なお、数値が日曜日スタートか、月曜日スタートか、数値のはじまりが0か1かですげー混乱すると思うんですが。。。。

        ちなみにISOでは、月曜日スタートで、月曜日が1、日曜日が7になります。

        ISO 8601(wikipedia)
        D は曜日を表し、月曜日が 1、日曜日は 7 である。「8」「9」は表記数値とされない(エラー扱い)。

        上記例のPostgreSQLにもisodowという関数があり、ISOと同様な動作になります。

        ちなみにODBCでは、日曜日スタートで、日曜日が1, 土曜日が7になります。
        (プログラマのためのSQLではSQL AnywhereのDOWがとりあげられていて、これと同じ仕様です)
        MySQLのDAYOFWEEKは、このODBC標準に準拠しています。
        MySQLには曜日を求めるもうひとつの関数WEEKDAYがあり、こちらは月曜日スタートで月曜日が0、日曜日が6になります。二つを比較した次のブログエントリがわかりやすいです。

        MySQLで曜日を調べる(xykの日記)

        MySQLにはISO準拠のものがありませんが、WEEKDAYで求めた結果に+1するとISO準拠となります。

        [IBM Db2]
        Db2には1〜7を返す関数が二つあり、
        DAYOFWEEKは日曜日はじまりで、日曜日が1、土曜日が7を返します。
        DAYOFWEEK_ISOは月曜日はじまりで、月曜日が1、日曜日が7を返します。

        [MS SQL Server]

        SQL Serverでは、まず何曜日はじまりか、を設定する変数DATEFIRSTがあり、SET DATEFIRSTで開始曜日を通常7(つまり日曜日スタート)にするか1(つまり月曜日スタート)にするか決定できます。(@@DATEFIRSTで設定内容を確認できます)
        関数としてはDATEPART(weekday, 日付)もしくは短縮形のDATEPART(dw, 日付)で指定し返す値の範囲は1〜7です。
        前者の設定、つまり日曜日スタートでは、ODBC互換。
        後者の設定、つまり月曜日スタートでは、ISO互換になります。

        [Oracle]
        TO_CHAR(日付,'D')
        NLS_TERRITORYで、日曜日はじまりか、月曜日はじまりになるらしい。
        日本、アメリカでは日曜日スタートで、ODBC互換
        欧州では月曜日スタートで、ISO互換になります。

        [SQLite]
        strftime(日付,'%w')日曜日はじまりで、ゼロスタート。日曜日が0, 土曜日が6です。
        +1するとODBC互換になりますね。

        sqlite> select current_date;
        2019-03-01
        sqlite> select strftime('%w',current_date);
        5
        sqlite> select strftime('%w',current_date)+1;
        6

        ISO互換にするには、0を返すときに7を返せばいいので、以下のような感じかな。

        select case strftime(日付,'%w) when 0 then 7 else strftime(日付,'%w) end;

        modifier指定のweekdayは、weekdayを取得するのではなく、元の日付に対してweekday Nで
        指定した日付を返します。2019-03-01は金曜日で5ですので、以下のように4を指定すると木曜日、
        6を指定すると土曜日を返します。木曜日は2019-02-28ではなく、2019-03-07を返すところに注意。

        sqlite> select date(current_date,'weekday 4');
        2019-03-07
        sqlite> select date(current_date,'weekday 6');
        2019-03-02

        [Firebird]
        FirebirdではSQL標準(SQL-99)のEXTRACTを使います。
        EXTRACT(WEEKDAY FROM 日付)
        戻す値はSQLiteと同じです。

        EXTRACTは標準SQLなので、実はOracle, IBM Db2, MySQL, PostgreSQLがサポートしています
        なら、全部EXTRACTで統一すればイイとおもうのですが、実はWEEKDAY, YEARDAYはFirebirdしか
        サポートしていない
        のです。(PostgreSQLはWEEKDAYの代わりにdow, YEARDAYの代わりにdoyをサポートしています)そのためSQL標準だからといって、あまり強く押すことができません。

        JUGEMテーマ:コンピュータ



        | meijik | データベース | 02:00 | comments(0) | trackbacks(0) | - | - |
        おうちで学べるデータベースのきほん、5刷になりました!
        0

          順調に息長く売れてます。ありがとうございます!
          | meijik | 日々 | 08:17 | comments(0) | trackbacks(0) | - | - |
          2019年の木村明治
          0
            2019年の木村明治

            去年はこんな抱負を出していましたが、まぁ2.3.がギリできていたくらいですかね。

            今年の抱負は以下。

            1.新しい本を出す(SQL関連)
            2.ライブにいく、ブログで発信する。
            3.分散処理本の読書会に継続して出る、か?
            4.スケートをまじめに練習する
            5.音楽活動を緩やかに再開

            以下順に、詳細を。。。。

            1.新しい本を出す(SQL関連)
             目黒バイナリ勉強会でSQLに関連する二つの本(プログラマのためのSQL, Effective SQL)を読書会してきましたが、それもそろそろ終わりそうです。これらの読書会を通じてベンダーに依存しないSQLクエリの重要性と、ベンダーごとの差異の明確か、それを利用者に方向性として知らせる情報の少なさを肌で痛感しました。そのちょうどよいタイミングで書籍執筆の話があり、今年の前半は書籍執筆に時間を費やして、その出版を目指します。

            2.ライブにいく、ブログで発信する。
             昨年から、いくつか好きなバンドのライブを継続してみています。今年も平日夜できるだけ参加していくつもりですが、せっかくよいバンド・曲があるので、ブログで紹介していきたいと思います。TWEEDEES赤い公園ねごとyonigeしなまゆ
             しなまゆ対バン(or サポート)つながり?で、ハイエナカーGOOD BYE APRILBARBARSチロルGIVE ME OWTHE SALMON PINKSエルモアスコッティーズ渕上里奈、あたり :)


            3.分散処理本の読書会に継続して出る、か?

            ノーチラステクノロジーの神林さん主催で、複数の読書会が開かれています。
            べた文系の私は毎回異次元ですが w ピースがつながるまで(つながるのか?) もう少し粘ってみようと思います。

            4.スケートをまじめに練習する
             先日、外苑前のスケート場がリニューアルオープンしました。普通には滑れるのですが、スケート習ったことないので、ちょっとウエイティングに申し込んで、通ったらスケートちょっと習ってみたいと思います。

            5.音楽活動を緩やかに再開
             昨年は、自分がもっているカセットテープを整理していて、思いのほか多くのバンドライブ、練習曲、デモテープ、などがでてきました。それれと2.のライブを聞いていると自分でもまたやってみたくなってきました。パートはドラムで、作曲は楽器が弾けないのでコンピュータに頼ることになりますが、まぁ何かアウトプットができるとよい、くらいの緩やかさで開始したいと思います。
             情報処理と同様に音楽の下地はないのですが(笑)高校時代から友人とゲームを作っていて、そのグラフィックと音楽を担当しておりました。(楽器は弾けないのでMMLやなつかしのヤマハ・ミュージックコンピュータを使ってました)
             大学時代もゲームを作っていましたが、広島にいて、留年を含めて(笑) 6年間広島大学のフォークソング同好会でバンドもやっていました。ほとんどコピーでしたが、オリジナル曲もいくつかやっていて、ヤマハのミュージックコンピュータでデモ作成してました。ひとつ上にストリートビーツ、二つか三つくらい上(?) にUNICORNがいました。あと、LORAN浜崎さんのReplicaなど、結構バンド活動盛んでした。
            私がやっていたバンドも、PERSONZや、コレクターズ広島Wodd Streetでの前座をやったりしていました。大学卒業、上京後もがんばってバンド活動やっていましたが、いかんせんドラムの技術不足と、かけれる時間のなさで、結局十年くらい前にすべての活動を終了、手持ちのドラム関連の楽器・パーツもドラムペダル以外は売却してしまいました。

            まぁくたばらないようにゆるゆるがんばりまっす。

            JUGEMテーマ:日記・一般



            | meijik | 日々 | 13:24 | comments(1) | trackbacks(0) | - | - |
            2019年のFirebird
            0
              2019年のFirebird

              本エントリはFirebird Advent Calendar 2018の25日目です。
              (まだいくつかとりこぼしていますが、それはおいおい。。。。)

              さて、2019年のFirebird ! といえば、まずは次版のFirebird 4.0の話になります。

              ....と書いてこれは、去年一昨年のエントリと同じ! うーん。

              ただ、今年は以下のエントリもあり、年内にBeta 1登場の機運が!!

              A Christmas gift is coming? (FirebirdNews)


              Firebirdのいいところ、わるいところ、は昨日のnakagamiさんのエントリに書いてあるとおりで、2019年もこのよいところ、特にLibreOffice6.2Baseのエンジンとなったことで、飛躍の年にしたいですね。

              JUGEMテーマ:コンピュータ



              | meijik | Firebird/InterBase | 00:25 | comments(0) | trackbacks(0) | - | - |
              Firebirdの分析関数の衝撃(15) 全称文を述語で表現する
              0
                Firebirdの分析関数の衝撃(15) 全称文を述語で表現する

                本エントリはFirebird Advent Calendar 2018 の22日目です。
                # もうすでに一週間以上遅れていますが。。。。

                4. 全称文を述語で表現する

                ミックさんの元のクエリ、山岸さんのクエリ(window関数で書き換えたSQL1, group byを使ったSQL1)は動作します。

                その他のクエリはbool_andを利用しているためPostgreSQLしか動作いたしません。
                ポスグレのマニュアルをみると、bool_andはSQL標準のevery集約関数にあたるそうです。
                そのため、もしあなたのRDBMSがevery集約関数をサポートしていれば、bool_andをeveryに置換すれば動作する(はずです)

                9.20. 集約関数(PostgreSQL 10 文書)

                # ポスグレ自体はevery()もサポートしています。

                ただ、Firebirdも含め、ほとんどのRDBMSはまだevery()に対応していません。。。。
                Firebirdで対応しているのは従来のものと、Nakagamiさんが実装してくれたこここらへんのみ、です。

                # JOOQというDBからJavaコードを生成するツールが対応時に説明しています >> every

                A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function(JAVA, SQL AND JOOQ)

                さて、これでFirebird分はなんとか年内に終わりました。
                元ネタとして使わせていただいたミックさん、山岸さんのブログ、プログラマのためのSQL(と読書会に参加していただいた方々)に感謝いたします。来年は読書会がそれぞれ終わるので、また新しい本でSQL界の冒険を継続予定です。ではでは。

                JUGEMテーマ:コンピュータ



                | meijik | Firebird/InterBase | 11:03 | comments(0) | trackbacks(0) | - | - |
                Firebirdの分析関数の衝撃(14) 等しい部分集合を見つける
                0
                  Firebirdの分析関数の衝撃(14) 等しい部分集合を見つける

                  本エントリはFirebird Advent Calendar 2018 の21日目です。
                  # もうすでに一週間以上遅れていますが。。。。

                  3. 等しい部分集合を見つける

                  Firebirdではバルクインサートが使えないので、以下のようにデータを用意します。
                  (textをdomainで定義済みであることを前提としてます。定義していない場合はvarchar, charなどで書き換えてください)

                  create table SupParts(
                  sup text,
                  part text,
                  primary key(sup,part));

                  insert into SupParts values('A','ボルト');
                  insert into SupParts values('A','ナット');
                  insert into SupParts values('A','パイプ');
                  insert into SupParts values('B','ボルト');
                  insert into SupParts values('B','パイプ');
                  insert into SupParts values('C','ボルト');
                  insert into SupParts values('C','ナット');
                  insert into SupParts values('C','パイプ');
                  insert into SupParts values('D','ボルト');
                  insert into SupParts values('D','パイプ');
                  insert into SupParts values('E','ヒューズ');
                  insert into SupParts values('E','ナット');
                  insert into SupParts values('E','パイプ');
                  insert into SupParts values('F','ヒューズ');

                  これで、ミックさんの元ネタや、山岸さんのWindows関数での書き換えも動作します。

                  ただしPostgreSQLの独自関数array_aggを使用した別解1/2は動作しません。(そりゃそうだ)
                  array_aggは行を配列に集約します。以下のブログ記事参照

                  PostgreSQLのあまり知られていない型3種

                  JUGEMテーマ:コンピュータ



                  | meijik | Firebird/InterBase | 03:46 | comments(0) | trackbacks(0) | - | - |
                  Firebirdの分析関数の衝撃(13) 関係除算を表現する
                  0
                    Firebirdの分析関数の衝撃(13) 関係除算を表現する

                    本エントリはFirebird Advent Calendar 2018 の19日目です。

                    2. 関係除算を表現する

                    ミックさんの以下のエントリにあるように関係除算の標準的な定義はありません。

                    注意4:除算の標準的な定義がない

                    そのため、プログラマのためのSQL 第4版にあるようにさまざまな方法が考えられます。
                    「27.2 関係除算」参照のこと。ここでも書かれているように関係除算には二種類あります。
                    剰余を持った除算と、厳密な除算で、今回のエントリが対象とするのは前者です。

                    EXCEPTを使ったものはFirebird(MySQLも)は扱えないので、山岸さんの考えたWindow関数版を動作させますが、この二つともにFirebird/MySQLで動作可能です。

                    -- window関数で書き換えたSQL(Skillsテーブルが空集合の場合に非対応)

                    select a.Emp
                    from EmpSkills a,(select Skill,
                    count(*) over() as SkillCount
                    from Skills) b
                    where a.Skill = b.Skill
                    group by a.Emp,b.SkillCount
                    having count(*) = b.SkillCount;


                    -- window関数で書き換えたSQL(Skillsテーブルが空集合の場合に対応)

                    select a.Emp
                    from EmpSkills a
                    Left Join (select Skill,
                    count(*) over() as needCount
                    from Skills) b
                    on 1=1
                    group by a.Emp,b.needCount
                    having count(case when a.Skill=b.Skill then 1 end)
                    = coalesce(b.needCount,0);

                    EMP
                    ====================
                    神崎
                    相田

                    JUGEMテーマ:コンピュータ



                    | meijik | Firebird/InterBase | 00:17 | comments(0) | trackbacks(0) | - | - |
                    Firebirdの分析関数の衝撃(12) 2つのテーブルが相等であるかどうか
                    0
                      Firebirdの分析関数の衝撃(12) 2つのテーブルが相等であるかどうか

                      本エントリはFirebird Advent Calendar 2018 18日目のエントリです。

                      1. 2つのテーブルが相等なら「等しい」、そうでなければ「異なる」を返すクエリ

                      INTERSECTとEXCEPTがないFirebird(とMySQL)にとっては、この書き換えは迷宮のようなわけのわからなさになることうけあいです。そのため、以下のように一番短い(そのように見える)以下のクエリを題材にしましょう。

                      -- window関数で書き換えたSQL
                      select case when count(*) = 0
                      then '等しい'
                      else '異なる' end as result
                      from (select count(*) over(),* from tbl_A
                      except all
                      select count(*) over(),* from tbl_B) a;

                      [Firebird版の完成系はこちら]
                      -- CTEに複数の定義を書いて、そのサブクエリ化
                      select case when count(*) = 0
                      then '等しい'
                      else '異なる' end as result
                      from
                      (
                      -- S1 EXCEPT ALL S2(Firebird with CTEバージョン)
                      WITH RECURSIVE Series AS
                      (
                      SELECT 1 AS seq from rdb$database
                      UNION ALL
                      SELECT 1+seq FROM Series where seq < 8
                      ),
                      S1 AS (select count(*) over() c, tbl_A.* from tbl_A),
                      S2 AS (select count(*) over() c, tbl_B.* from tbl_B)
                      SELECT D2.a
                      FROM (SELECT D1.a, SUM(cnt)
                      FROM (SELECT a1, COUNT(*)
                      FROM S1
                      GROUP BY a1
                      UNION ALL
                      SELECT a2, -COUNT(*)
                      FROM S2
                      GROUP BY a2) AS D1(a, cnt)
                      GROUP BY D1.a
                      HAVING SUM(cnt) > 0) AS D2(a, dups)
                      INNER JOIN
                      Series ON seq <= D2.dups
                      ) a;

                      元のクエリとは当然大きく異なるのですが、Firebird版では以下の変更が必要です。
                      (1) count(*) over()にカラム名が必要。ここではcとした。つけないと以下のエラー
                      Statement failed, SQLSTATE = 42000
                      Dynamic SQL Error
                      -SQL error code = -104
                      -Invalid command
                      -no column name specified for column number 1 in derived table S1
                      (2) (1)に続く*ではエラー。テーフル名.*の表記が必要。

                      なお、MySQL版では(1)は不要ですがオリジナルのクエリと比較して(2)と以下(3)が必要。
                      (3) テーブルの別名にASが必要。すなわちクエリの最後が) as a;である必要があり。

                      また

                      (4) SELECT 1 AS seq from rdb$databaseのfrom以下が不要、なのはご存知のとおり。

                      以下、[MySQL版]のクエリを組み立てながら説明します。

                      "except all"については、先日のブログでとりあげましたよね。
                      select count(*) over(),* from tbl_AにS1という名称をつけて、
                      select count(*) over(),* from tbl_BにS2という名称をつけて、
                      fromの中を(S1 except all S2)という形にすると、先日のブログと同じように処理できそうです。

                      さて、どうやって名前をつけましょうか? 実はCTEはカンマで繋いで、複数定義できます!

                      with — Organize Complex Queries

                      なので、以下のようにexcept allを表現できます。

                      -- S1 EXCEPT ALL S2(MySQL with CTEバージョン)
                      WITH RECURSIVE Series AS
                      (
                      SELECT 1 AS seq -- from rdb$database
                      UNION ALL
                      SELECT 1+seq FROM Series where seq < 8
                      ),
                      S1 AS (select count(*) over(), tbl_A.* from tbl_A),
                      S2 AS (select count(*) over(), tbl_B.* from tbl_B)

                      SELECT D2.a
                      FROM (SELECT D1.a, SUM(cnt)
                      FROM (SELECT a1, COUNT(*)
                      FROM S1
                      GROUP BY a1
                      UNION ALL
                      SELECT a2, -COUNT(*)
                      FROM S2
                      GROUP BY a2) AS D1(a, cnt)
                      GROUP BY D1.a
                      HAVING SUM(cnt) > 0) AS D2(a, dups)
                      INNER JOIN
                      Series ON seq <= D2.dups;

                      これに使うtbl_A, tbl_Bを用意します。keyをa1,a2に変更して、同一のテーブルを生成します。

                      create table tbl_A(
                      a1 char(1) not null primary key,
                      col_1 int,
                      col_2 int,
                      col_3 int);

                      insert into tbl_A values('A',2,3,4);
                      insert into tbl_A values('B',0,7,9);
                      insert into tbl_A values('C',5,1,6);

                      create table tbl_B(
                      a2 char(1) not null primary key,
                      col_1 int,
                      col_2 int,
                      col_3 int);

                      insert into tbl_B select * from tbl_A;
                      commit;

                      これでCTEの戻り値はEmptyです。(tbl_A, tbl_Bは同一)

                      元のクエリが(S1 except all S2)をサブクエリにしていたので、同様に上のCTEもサブクエリにしてみます。

                      [MySQL版の完成系はこちら]
                      -- CTEに複数の定義を書いて、そのサブクエリ化
                      select case when count(*) = 0
                      then '等しい'
                      else '異なる' end as result
                      from
                      (
                      -- S1 EXCEPT ALL S2(MySQL with CTEバージョン)
                      WITH RECURSIVE Series AS
                      (
                      SELECT 1 AS seq -- from rdb$database
                      UNION ALL
                      SELECT 1+seq FROM Series where seq < 8
                      ),
                      S1 AS (select count(*) over(), tbl_A.* from tbl_A),
                      S2 AS (select count(*) over(), tbl_B.* from tbl_B)
                      SELECT D2.a
                      FROM (SELECT D1.a, SUM(cnt)
                      FROM (SELECT a1, COUNT(*)
                      FROM S1
                      GROUP BY a1
                      UNION ALL
                      SELECT a2, -COUNT(*)
                      FROM S2
                      GROUP BY a2) AS D1(a, cnt)
                      GROUP BY D1.a
                      HAVING SUM(cnt) > 0) AS D2(a, dups)
                      INNER JOIN
                      Series ON seq <= D2.dups
                      ) as a;

                      +-----------+
                      | result |
                      +-----------+
                      | 等しい |
                      +-----------+
                      1 row in set (0.00 sec)

                      一行増やして
                      insert into tbl_A values('X',1,2,3); commit;

                      再実行すると、この結果
                      +-----------+
                      | result |
                      +-----------+
                      | 異なる |
                      +-----------+
                      1 row in set (0.00 sec)

                      JUGEMテーマ:コンピュータ



                      | meijik | Firebird/InterBase | 00:43 | comments(0) | trackbacks(0) | - | - |
                       123456
                      78910111213
                      14151617181920
                      21222324252627
                      282930    
                      << April 2019 >>
                      + RECOMMEND
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊 (JUGEMレビュー »)

                      100人のうちの一人としてミックさんの本を推薦しています。
                      + RECOMMEND
                      MySQL 5.1 Plugin Development
                      MySQL 5.1 Plugin Development (JUGEMレビュー »)
                      Andrew Hutchings,Sergei Golubchik
                      MySQL 5.1 のプラグインを作るならこれ!
                      + RECOMMEND
                      AWKを256倍使うための本 (Ascii 256倍)
                      AWKを256倍使うための本 (Ascii 256倍) (JUGEMレビュー »)
                      志村 拓,鷲北 賢,西村 克信
                      ここにきてまさかの復刊。15〜16年前の本ですが、いまでも十分役立ちますよ!!
                      + RECOMMEND
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION)
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION) (JUGEMレビュー »)
                      松信 嘉範
                      Linux-DB システム構築の新スタンダード。DB Magazine人気連載、待望の書籍化です!!
                      + RECOMMEND
                      + RECOMMEND
                      超・極める!MySQL
                      超・極める!MySQL (JUGEMレビュー »)
                      坂井 恵,志村 和彦,ひろせ まさあき,松信 嘉範
                      内容は少々古くなりましたが、内部説明があり、いろいろ面白いです。
                      + RECOMMEND
                      The Root of .NET Framework
                      The Root of .NET Framework (JUGEMレビュー »)
                      荒井 省三
                      .NETの根っこをつかむ、か?
                      + RECOMMEND
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド (JUGEMレビュー »)
                      奥野 幹也
                      これまでにない構築、そして構築後のMySQLのための本。
                      + RECOMMEND
                      Database Management Systems
                      Database Management Systems (JUGEMレビュー »)
                      Raghu Ramakrishnan,Johannes Gehrke
                      ミックさん連載の参考書籍です。
                      + RECOMMEND
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series)
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series) (JUGEMレビュー »)
                      W. Richard Stevens,Stephen A. Rago
                      必読! といいつつ私が読んだのは初版の日本語訳です。差分はチェックしないといけませんね。
                      + RECOMMEND
                      + RECOMMEND
                      + RECOMMEND
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE (JUGEMレビュー »)

                      DELL SX260の換装用に購入。今はコチラのほうが安いですね。
                      + RECOMMEND
                      情熱プログラマー ソフトウェア開発者の幸せな生き方
                      情熱プログラマー ソフトウェア開発者の幸せな生き方 (JUGEMレビュー »)
                      Chad Fowler
                      OSC2010神戸に出展されたジュンク堂で買いました。
                      + RECOMMEND
                      Software Design 総集編 【2000~2009】(DVD付)
                      Software Design 総集編 【2000~2009】(DVD付) (JUGEMレビュー »)

                      メガトン級の過去記事。売り切れる前にお店に急げ!
                      + RECOMMEND
                      + RECOMMEND
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2 (JUGEMレビュー »)

                      Aspire 1410 HDD換装後、昔のHDDはこれにいれます。
                      + RECOMMEND
                      + RECOMMEND
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22 (JUGEMレビュー »)

                      色違い黒(他に青と白があります): メモリは最大4GBとあるが、がんばれば8GBいけるらしい。
                      + RECOMMEND
                      Firebird 徹底入門
                      Firebird 徹底入門 (JUGEMレビュー »)
                      木村 明治,はやし つとむ,坂井 恵
                      Firebird日本ユーザ会のはやしさん、そしてMyNAから拝借(?) してきた坂井さんとともに書きました。まだ誰も知らないFirebird 2.5や、いままでドキュメントのなかったツール類についてもソースを確認してとりあげた本です。是非ご活用ください。
                      + SELECTED ENTRIES
                      + RECENT COMMENTS
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (03/03)
                      • 2019年の木村明治
                        田中尚 (01/08)
                      • 使い慣れたSQLに潜む実装依存: Firebirdの場合(6) 文字列との結合演算子
                        noname (12/10)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 2015年版 InterBaseの歩み
                        nakagami (12/06)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (11/22)
                      • 分散DB本読書会第46回メモ「STOP AFTER 10」
                        masudahidehiko (09/01)
                      • Windows上でのMySQLビルド方法
                        kinoyasu (03/31)
                      • 気軽に新しいMySQLを試してみる: MySQL Sandboxのススメ
                        MeijiK (09/19)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE